Replicating SQLite with rqlite#

I tried out rqlite today, a “lightweight, distributed relational database, which uses SQLite as its storage engine”. It uses the Raft consensus algorithm to allow multiple SQLite instances to elect a leader and replicate changes amongst themselves.

By default rqlite asks you to use its own custom HTTP API - but I wanted to try running it against Datasette. rqlite author Philip O’Toole confirmed that this should work provided any writes go through the API - each node can be configured to write to an on-disk database file which Datasette can then read from (the default is to use in-memory databases and an on-disk Raft log).

Here’s how I got that working on my macOS laptop. I used the latest macOS binary from https://github.com/rqlite/rqlite/releases (rqlite is written in Go and provides pre-complied binaries for different systems).

1
cd /tmp
2
curl -L https://github.com/rqlite/rqlite/releases/download/v5.7.0/rqlite-v5.7.0-darwin-amd64.tar.gz \
3
  -o rqlite-v5.7.0-darwin-amd64.tar.gz
4
tar xvfz rqlite-v5.7.0-darwin-amd64.tar.gz
5
cd rqlite-v5.7.0-darwin-amd64

I started the first node like this:

1
./rqlited -on-disk ~/node.1

This created a SQLite database file in ~/node.1/db.sqlite which I could open with Datasette.

Note that the -on-disk option has to come before the ~/node.1 - if it comes afterwards it silently fails to take effect.

I created a table and inserted a record using the client binary like this:

1
./rqlite
2
CREATE TABLE foo (id INTEGER NOT NULL PRIMARY KEY, name TEXT)
3
INSERT INTO foo(name) VALUES("fiona")

Running Datasette against ~/node.1/db.sqlite confirmed the table had been created.

Then I started a second node like this:

1
./rqlited -on-disk -node-id 2 -http-addr localhost:4003 \
2
    -raft-addr localhost:4004 -join http://localhost:4001 \
3
    ~/node.2

Since it was running on the same machine I had to pick different ports for it, then tell it to join the existing cluster at http://localhost:4001

This created a database file at ~/node.2/db.sqlite.

I confirmed that this database had the foo table in it, then inserted another row and watched as both of my database files were updated with the new record.