Compile and run a new SQLite version with the existing sqlite3 Python library on macOS#

I’ve been trying to figure this out for years. Previous notes include Using LD_PRELOAD to run any version of SQLite with Python (Linux only), and Building a specific version of SQLite with pysqlite on macOS/Linux and Using pysqlite3 on macOS (both using the pysqlite3 package). But the dream was always to find a way to let me easily run a different SQLite version with the sqlite3 module from the Python standard library directly on my Mac.

Alex Garcia helped me find the solution I’ve been looking for this morning.

It’s pretty simple: you compile a new libsqlite3.0.dylib module from the SQLite amalgamation release, then point DYLD_LIBRARY_PATH to it before loading Python and importing sqlite3.

:warning: This works against Python 3 installed via Homebrew, but has been reported not to work against a Python 3 installed directly from www.python.org

The amalgamation builds are released as zip files on the SQLite downloads page - let’s grab 3.42.0, the most recent stable release:

1
wget 'https://www.sqlite.org/2023/sqlite-amalgamation-3420000.zip'

Then:

1
unzip sqlite-amalgamation-3420000.zip
2
cd sqlite-amalgamation-3420000

Running ls -lah shows there’s only four files in that zip file:

1
-rw-rw-r--  1 simon  wheel   847K May 16 06:45 shell.c
2
-rw-rw-r--  1 simon  wheel   8.3M May 16 06:45 sqlite3.c
3
-rw-rw-r--  1 simon  wheel   611K May 16 06:45 sqlite3.h
4
-rw-rw-r--  1 simon  wheel    37K May 16 06:45 sqlite3ext.h

How do I compile that? I asked GPT-4 and got this recipe:

1
gcc -dynamiclib sqlite3.c -o libsqlite3.0.dylib -lm -lpthread

On my machine that runs in less than a second and produces a 1.2MB libsqlite3.0.dylib file.

To use it, set DYLD_LIBRARY_PATH to the folder that contains that .dylib file. We can use $PWD for that:

1
DYLD_LIBRARY_PATH=$PWD python3 -c "import sqlite3; print(sqlite3.sqlite_version)"

Outputs:

1
3.42.0

If I run that without the environment variable I get the older version:

1
python3 -c "import sqlite3; print(sqlite3.sqlite_version)"

Outputs:

1
3.41.1

And this works to run Datasette as well:

1
DYLD_LIBRARY_PATH=$PWD datasette --get /-/versions.json | jq

Shows:

1
{
2
  "python": {
3
    "version": "3.10.10",
4
    "full": "3.10.10 (main, Mar 21 2023, 13:41:05) [Clang 14.0.6 ]"
5
  },
6
  "datasette": {
7
    "version": "1.0a4"
8
  },
9
  "asgi": "3.0",
10
  "uvicorn": "0.23.2",
11
  "sqlite": {
12
    "version": "3.42.0",
13
    "fts_versions": [],
14
    "extensions": {
15
      "json1": null
16
    },
17
    "compile_options": [
18
      "ATOMIC_INTRINSICS=1",
19
      "COMPILER=clang-14.0.3",
20
      "DEFAULT_AUTOVACUUM",
21
      "DEFAULT_CACHE_SIZE=-2000",
22
      "DEFAULT_FILE_FORMAT=4",
23
      "DEFAULT_JOURNAL_SIZE_LIMIT=-1",
24
      "DEFAULT_MMAP_SIZE=0",
25
      "DEFAULT_PAGE_SIZE=4096",
26
      "DEFAULT_PCACHE_INITSZ=20",
27
      "DEFAULT_RECURSIVE_TRIGGERS",
28
      "DEFAULT_SECTOR_SIZE=4096",
29
      "DEFAULT_SYNCHRONOUS=2",
30
      "DEFAULT_WAL_AUTOCHECKPOINT=1000",
31
      "DEFAULT_WAL_SYNCHRONOUS=2",
32
      "DEFAULT_WORKER_THREADS=0",
33
      "MALLOC_SOFT_LIMIT=1024",
34
      "MAX_ATTACHED=10",
35
      "MAX_COLUMN=2000",
36
      "MAX_COMPOUND_SELECT=500",
37
      "MAX_DEFAULT_PAGE_SIZE=8192",
38
      "MAX_EXPR_DEPTH=1000",
39
      "MAX_FUNCTION_ARG=127",
40
      "MAX_LENGTH=1000000000",
41
      "MAX_LIKE_PATTERN_LENGTH=50000",
42
      "MAX_MMAP_SIZE=0x7fff0000",
43
      "MAX_PAGE_COUNT=1073741823",
44
      "MAX_PAGE_SIZE=65536",
45
      "MAX_SQL_LENGTH=1000000000",
46
      "MAX_TRIGGER_DEPTH=1000",
47
      "MAX_VARIABLE_NUMBER=32766",
48
      "MAX_VDBE_OP=250000000",
49
      "MAX_WORKER_THREADS=8",
50
      "MUTEX_PTHREADS",
51
      "SYSTEM_MALLOC",
52
      "TEMP_STORE=1",
53
      "THREADSAFE=1"
54
    ]
55
  }
56
}

Without the environment variable I get this instead:

1
{
2
  "python": {
3
    "version": "3.10.10",
4
    "full": "3.10.10 (main, Mar 21 2023, 13:41:05) [Clang 14.0.6 ]"
5
  },
6
  "datasette": {
7
    "version": "1.0a4"
8
  },
9
  "asgi": "3.0",
10
  "uvicorn": "0.23.2",
11
  "sqlite": {
12
    "version": "3.41.1",
13
    "fts_versions": [
14
      "FTS5",
15
      "FTS4",
16
      "FTS3"
17
    ],
18
    "extensions": {
19
      "json1": null
20
    },
21
    "compile_options": [
22
      "ATOMIC_INTRINSICS=1",
23
      "COMPILER=clang-14.0.6",
24
      "DEFAULT_AUTOVACUUM",
25
      "DEFAULT_CACHE_SIZE=-2000",
26
      "DEFAULT_FILE_FORMAT=4",
27
      "DEFAULT_JOURNAL_SIZE_LIMIT=-1",
28
      "DEFAULT_MMAP_SIZE=0",
29
      "DEFAULT_PAGE_SIZE=4096",
30
      "DEFAULT_PCACHE_INITSZ=20",
31
      "DEFAULT_RECURSIVE_TRIGGERS",
32
      "DEFAULT_SECTOR_SIZE=4096",
33
      "DEFAULT_SYNCHRONOUS=2",
34
      "DEFAULT_WAL_AUTOCHECKPOINT=1000",
35
      "DEFAULT_WAL_SYNCHRONOUS=2",
36
      "DEFAULT_WORKER_THREADS=0",
37
      "ENABLE_COLUMN_METADATA",
38
      "ENABLE_DBSTAT_VTAB",
39
      "ENABLE_FTS3",
40
      "ENABLE_FTS3_TOKENIZER",
41
      "ENABLE_FTS4",
42
      "ENABLE_FTS5",
43
      "ENABLE_GEOPOLY",
44
      "ENABLE_MATH_FUNCTIONS",
45
      "ENABLE_RTREE",
46
      "ENABLE_UNLOCK_NOTIFY",
47
      "MALLOC_SOFT_LIMIT=1024",
48
      "MAX_ATTACHED=10",
49
      "MAX_COLUMN=2000",
50
      "MAX_COMPOUND_SELECT=500",
51
      "MAX_DEFAULT_PAGE_SIZE=8192",
52
      "MAX_EXPR_DEPTH=10000",
53
      "MAX_FUNCTION_ARG=127",
54
      "MAX_LENGTH=1000000000",
55
      "MAX_LIKE_PATTERN_LENGTH=50000",
56
      "MAX_MMAP_SIZE=0x7fff0000",
57
      "MAX_PAGE_COUNT=1073741823",
58
      "MAX_PAGE_SIZE=65536",
59
      "MAX_SQL_LENGTH=1000000000",
60
      "MAX_TRIGGER_DEPTH=1000",
61
      "MAX_VARIABLE_NUMBER=250000",
62
      "MAX_VDBE_OP=250000000",
63
      "MAX_WORKER_THREADS=8",
64
      "MUTEX_PTHREADS",
65
      "SECURE_DELETE",
66
      "SYSTEM_MALLOC",
67
      "TEMP_STORE=1",
68
      "THREADSAFE=1"
69
    ]
70
  }
71
}

Note that the dylib version appears to be missing the FTS extension.

Adding compiler options#

Here’s a modification that includes full-text search support:

1
gcc -dynamiclib sqlite3.c -o libsqlite3.0.dylib -lm -lpthread \
2
  -DSQLITE_ENABLE_FTS3 \
3
  -DSQLITE_ENABLE_FTS3_PARENTHESIS \
4
  -DSQLITE_ENABLE_FTS4 \
5
  -DSQLITE_ENABLE_FTS5

And now:

1
DYLD_LIBRARY_PATH=$PWD datasette --get /-/versions.json | jq .sqlite

Outputs:

1
{
2
  "version": "3.42.0",
3
  "fts_versions": [
4
    "FTS5",
5
    "FTS4",
6
    "FTS3"
7
  ],
8
  "extensions": {
9
    "json1": null
10
  },
11
  "compile_options": [
12
    "ATOMIC_INTRINSICS=1",
13
    "COMPILER=clang-14.0.3",
14
    "DEFAULT_AUTOVACUUM",
15
    "DEFAULT_CACHE_SIZE=-2000",
16
    "DEFAULT_FILE_FORMAT=4",
17
    "DEFAULT_JOURNAL_SIZE_LIMIT=-1",
18
    "DEFAULT_MMAP_SIZE=0",
19
    "DEFAULT_PAGE_SIZE=4096",
20
    "DEFAULT_PCACHE_INITSZ=20",
21
    "DEFAULT_RECURSIVE_TRIGGERS",
22
    "DEFAULT_SECTOR_SIZE=4096",
23
    "DEFAULT_SYNCHRONOUS=2",
24
    "DEFAULT_WAL_AUTOCHECKPOINT=1000",
25
    "DEFAULT_WAL_SYNCHRONOUS=2",
26
    "DEFAULT_WORKER_THREADS=0",
27
    "ENABLE_FTS3",
28
    "ENABLE_FTS3_PARENTHESIS",
29
    "ENABLE_FTS4",
30
    "ENABLE_FTS5",
31
    "MALLOC_SOFT_LIMIT=1024",
32
    "MAX_ATTACHED=10",
33
    "MAX_COLUMN=2000",
34
    "MAX_COMPOUND_SELECT=500",
35
    "MAX_DEFAULT_PAGE_SIZE=8192",
36
    "MAX_EXPR_DEPTH=1000",
37
    "MAX_FUNCTION_ARG=127",
38
    "MAX_LENGTH=1000000000",
39
    "MAX_LIKE_PATTERN_LENGTH=50000",
40
    "MAX_MMAP_SIZE=0x7fff0000",
41
    "MAX_PAGE_COUNT=1073741823",
42
    "MAX_PAGE_SIZE=65536",
43
    "MAX_SQL_LENGTH=1000000000",
44
    "MAX_TRIGGER_DEPTH=1000",
45
    "MAX_VARIABLE_NUMBER=32766",
46
    "MAX_VDBE_OP=250000000",
47
    "MAX_WORKER_THREADS=8",
48
    "MUTEX_PTHREADS",
49
    "SYSTEM_MALLOC",
50
    "TEMP_STORE=1",
51
    "THREADSAFE=1"
52
  ]
53
}