Compiling and running sqlite3-rsync#

Today I heard about the sqlite3-rsync command, currently available in a branch in the SQLite code repository. It provides a mechanism for efficiently creating or updating a copy of a SQLite database that is running in WAL mode, either locally or via SSH to another server.

Update: Thanks to gary_0 on Hacker News here’s a MUCH simpler recipe:

1
git clone https://github.com/sqlite/sqlite.git
2
cd sqlite
3
./configure
4
make sqlite3-rsync

So it looks like the sqlite-rsync command is no longer limited to a branch.

How I compiled it without using make sqlite3-rsync#

After some poking around (and some hints from Claude) I found a recipe for compiling and running it that seems to work:

1
cd /tmp
2
git clone https://github.com/sqlite/sqlite.git
3
cd sqlite
4
git checkout sqlite3-rsync
5
./configure
6
make sqlite3.c
7
cd tool
8
gcc -o sqlite3-rsync sqlite3-rsync.c ../sqlite3.c -DSQLITE_ENABLE_DBPAGE_VTAB
9
./sqlite3-rsync --help

Here I’m cloning from the GitHub mirror of the SQLite Fossil repo, purely to save myself from learning how to use Fossil.

The first step is to run ./configure and then make sqlite3.c in the root directory. This creates the SQLite amalgamation file - a single C file containing the full implementation of SQLite, which I can then use as part of compiling the sqlite3-rsync tool.

It took me quite a few iterations to get to this recipe for compiling the tool itself:

1
gcc -o sqlite3-rsync sqlite3-rsync.c ../sqlite3.c -DSQLITE_ENABLE_DBPAGE_VTAB

The -DSQLITE_ENABLE_DBPAGE_VTAB flag is necessary to enable the dbpage virtual table, which is used by the sqlite3-rsync tool. Without that I got this error when trying to run it:

ERROR: unable to prepare SQL [SELECT hash(data) FROM sqlite_dbpage WHERE pgno<=min(1,7338) ORDER BY pgno]: no such table: sqlite_dbpage

Trying out sqlite-rsync#

Having compiled the utility I tested it out like this:

1
curl https://datasette.io/content.db -o /tmp/content.db
2
# Enable WAL mode
3
sqlite3 /tmp/content.db "PRAGMA journal_mode=WAL"
4
# And run the copy
5
./sqlite3-rsync /tmp/content.db /tmp/content-copy.db

Then I made a change to /tmp/content.db:

1
sqlite3 /tmp/content.db 'create table test (id integer primary key)'

Confirmed it was not present in /tmp/content-copy.db:

1
sqlite3 /tmp/content-copy.db '.tables'

And ran this command to sync them up again:

1
./sqlite3-rsync /tmp/content.db /tmp/content-copy.db

Which resulted in that new table being created in the copy:

1
sqlite3 /tmp/content-copy.db '.tables' | grep test

I haven’t yet tried running the command over SSH: you first need to compile and deploy the sqlite3-rsync binary to the remote server and drop it somewhere on the path (the documentation suggests /usr/local/bin).

Having done that this should work:

1
sqlite3-rsync user@host:/path/to/remote.db /path/to/local-copy.db