Using the latest version of SQLite with Haskell on Windows

This is one of those installation odysseys.

Recent versions of SQLite have support for features like RETURNING clauses and ALTER TABLE DROP COLUMN. I wanted to use them from a Haskell program, on Windows.

For example, for this trivial schema:

CREATE TABLE foo (
 f1 INTEGER not null primary key,
 f2 text,
 f3 real
);

I want this trivial program to work:

import Database.SQLite3
import Data.Text qualified as T
main :: IO ()
main = do
 db <- open (T.pack “foo.db”)
 execPrint db (T.pack “select * from foo;”)
 execPrint db (T.pack “begin transaction;”)
 execPrint db (T.pack “alter table foo drop column f3;”)
 execPrint db (T.pack “rollback;”)
 close db

The package manager

I used cabal-install as the package manager, out of habit.

I invoked all cabal commands inside a Git Bash shell, in case some of the installed packages had a ./configure.sh script, which wouldn’t work from Powershell.

Finding a SQLite library

Searching from SQLite libraries on Hackage, I looked for recent ones that didn’t gave bitrotty bives. First I tried sqlite-simple but one of its deps didn’t compile. Then I settled on direct-sqlite (which serves as the base for the higher-level persistent-sqlite, which I havent yet tried).

It seems that direct-sqlite comes with the SQLite C code included, so you don’t need to fiddle with configuring native libraries and such. Sadly, that code corresponds to a version from 2 years ago, so it won’t support the newer features.

Happily, the package supports a systemlib flag, which lets you use a system library instead of the one which comes pre-packaged.

Now I need to find out two things: how to configure package flags with cabal-install, and how to tell cabal-install about the location of the libraries.

Configuring package flags with cabal-install

This is done in the cabal.project file of your project (or the global ~/.cabal/config) by adding a constraints section:

packages:
 ./
constraints: direct-sqlite +systemlib

Found it through this SO answer.

Telling cabal-install about the native libraries

First, I needed to download the compiled native library for Windows, and also the library headers.

• The compiled native library is under Precompiled Binaries for Windows: sqlite-dll-win64-x64–3350300.zip
• The headers are under Source Code: sqlite-amalgamation-3350300.zip

I uncompressed both files into two folders.

Now the somewhat tricky part. To notify Cabal of the location of the library, I thought that it would be enough to add the following lines to cabal.project:

extra-include-dirs: C:/Users/myuser/somedir/sqlite-amalgamation-3350300
extra-lib-dirs: C:/Users/myuser/somedir/sqlite-dll-win64-x64–3350300

Alas, it wasn’t working. The libraries weren’t being found and I couldn’t compile my project.

After some rummaging, I found the solution from a comment in this GitHub issue. It seems that extra-include-dirs and extra-lib-dirs at the “top level” only affect the “local” packages of a project, the ones you are actively developing. But they don’t affect packages that are external dependencies.

For external dependencies, one should specify the libraries with an explicit package section in cabal.project.local:

package direct-sqlite
 extra-include-dirs: C:/Users/myuser/somedir/sqlite-amalgamation-3350300
 extra-lib-dirs: C:/Users/myuser/somedir/sqlite-dll-win64-x64–3350300

Why cabal.project.local instead of cabal.project? The location of those libraries is likely to be machine-dependent so, as the Cabal documentation says:

The expectation is that a cabal.project is checked into your source control, to be used by all developers of a project. If you need to make local changes, they can be placed in cabal.project.local (which should not be checked in.)

Appendix: a helper wrapper for sqlite3.exe

This is not directly relating to installing the libraries, but might come in handy.

The sqlite-tools-win32-x86–3350300.zip archive contains a useful command-line shell. Problem is, it seems that on Windows it doesn’t have readline-like bindings by default, which is a pain.

It seems that they can be added by re-compiling the program, but I opted for writing a simplistic rlwrap clone in Haskell using the haskeline library. I invoke it like this:

PS> hlwrap .\sqlite_history — sqlite3 -interactive
SQLite version 3.35.3 2021–03–26 12:12:52
Enter “.help” for usage hints.
sqlite>