rqlite: a high-availability and distributed SQLite alternative

In a project I am using a SQLite database. The data is not critical, it can be reloaded at any time. Still, I do not want part of the application to become unresponsive when the SQLite database is temporarily unavailable.

I was looking for a fast, more or less fault-tolerant database, and also distributed, so I can replicate some reader modules. There are a few solutions that came up when searching the internet and rqlite seemed a good choice.

In this post I bring up an rqlite cluster with three nodes using Docker-Compose and then access the nodes with a Python application.

As always I am running Ubuntu 22.04.

Limitations of rqlite

I start with the limitations first because they may not suite your case. Here they are:

• Transactions are not supported.
• There is a small risk of data loss in the event the node crashes before queued data is persisted.
• Speed. Do not expect the same times as when accessing a SQLite database directly. Not only there is the networking overhead, also write latency is much higher than that of SQLite caused by the Raft consensus algorithm. Using bulk writes improves performance dramatically.
• Non-deterministic functions and others, see the rqlite 'Developer Guide'

There is more information in the document 'Comparing Litestream, rqlite, and dqlite', see links below.

The docker-compose.yml and '.env' files

There are many ways to setup a rqlite cluster. Here we use the rqlite Docker image to create a cluster of three rqlite nodes following the instructions for 'Automatic Bootstrapping', see links below.

We do not expose ports to the host system, but make the nodes (only) available on a Docker network. Essential is setting the hostname for the nodes!  The hostname is used by the rqlite nodes for discovery and other applications use the hostnames to access the cluster of nodes. And we use Volumes because we want to preserve the data stored in the Raft even if the cluster goes down for a short time.

Important: After changing the docker-compose.yml and/or '.env' file, remove the data from the mounted directories (./data/rqlite-node-1, ./data/rqlite-node-2, ./data/rqlite-node-3). If you do not do this you can get all sorts of weird behaviour!

The docker-compose.yml file:

# docker-compose.yml

version: '3.7'

services:
  rqlite-node-1:
    image: rqlite/rqlite:7.21.4
    hostname: ${RQLITE_NODE_1_HOSTNAME}
    volumes:
      - ./data/rqlite-node-1:/rqlite/file/data
    command:
      - rqlited
      - -node-id
      - "${RQLITE_NODE_1_NODE_ID}"
      - -http-addr
      - "${RQLITE_NODE_1_HTTP_ADDR}"
      - -raft-addr
      - "${RQLITE_NODE_1_RAFT_ADDR}"
      - -http-adv-addr
      - "${RQLITE_NODE_1_HTTP_ADV_ADDR}"
      - -raft-adv-addr
      - "${RQLITE_NODE_1_RAFT_ADV_ADDR}"
      # join
      - -bootstrap-expect
      - "3"
      - -join
      - "${RQLITE_NODE_1_JOIN_ADDR},${RQLITE_NODE_2_JOIN_ADDR},${RQLITE_NODE_3_JOIN_ADDR}"
      - /rqlite/file/data
    networks:
      - rqlite-cluster-network

  rqlite-node-2:
    image: rqlite/rqlite:7.21.4
    hostname: ${RQLITE_NODE_2_HOSTNAME}
    volumes:
      - ./data/rqlite-node-2:/rqlite/file/data
    command:
      - rqlited
      - -node-id
      - "${RQLITE_NODE_2_NODE_ID}"
      - -http-addr
      - "${RQLITE_NODE_2_HTTP_ADDR}"
      - -raft-addr
      - "${RQLITE_NODE_2_RAFT_ADDR}"
      - -http-adv-addr
      - "${RQLITE_NODE_2_HTTP_ADV_ADDR}"
      - -raft-adv-addr
      - "${RQLITE_NODE_2_RAFT_ADV_ADDR}"
      # join
      - -bootstrap-expect
      - "3"
      - -join
      - "${RQLITE_NODE_1_JOIN_ADDR},${RQLITE_NODE_2_JOIN_ADDR},${RQLITE_NODE_3_JOIN_ADDR}"
      - /rqlite/file/data
    networks:
      - rqlite-cluster-network

  rqlite-node-3:
    image: rqlite/rqlite:7.21.4
    hostname: ${RQLITE_NODE_3_HOSTNAME}
    volumes:
      - ./data/rqlite-node-3:/rqlite/file/data
    command:
      - rqlited
      - -node-id
      - "${RQLITE_NODE_3_NODE_ID}"
      - -http-addr
      - "${RQLITE_NODE_3_HTTP_ADDR}"
      - -raft-addr
      - "${RQLITE_NODE_3_RAFT_ADDR}"
      - -http-adv-addr
      - "${RQLITE_NODE_3_HTTP_ADV_ADDR}"
      - -raft-adv-addr
      - "${RQLITE_NODE_3_RAFT_ADV_ADDR}"
      # join
      - -bootstrap-expect
      - "3"
      - -join
      - "${RQLITE_NODE_1_JOIN_ADDR},${RQLITE_NODE_2_JOIN_ADDR},${RQLITE_NODE_3_JOIN_ADDR}"
      - /rqlite/file/data
    networks:
      - rqlite-cluster-network

networks:
  rqlite-cluster-network:
    external: true
    name: rqlite-cluster-network

The '.env'-file:

# .env

COMPOSE_PROJECT_NAME=rqlite-cluster

# RQLITE_NODE_1
RQLITE_NODE_1_HOSTNAME=rqlite-node-1
RQLITE_NODE_1_NODE_ID=rqlite-node-1
RQLITE_NODE_1_DATA_DIR=/rqlite/file/data
RQLITE_NODE_1_HTTP_ADDR=rqlite-node-1:4001
RQLITE_NODE_1_RAFT_ADDR=rqlite-node-1:4002
RQLITE_NODE_1_HTTP_ADV_ADDR=rqlite-node-1:4001
RQLITE_NODE_1_RAFT_ADV_ADDR=rqlite-node-1:4002
# join
RQLITE_NODE_1_JOIN_ADDR=rqlite-node-1:4001

# RQLITE_NODE_2
RQLITE_NODE_2_HOSTNAME=rqlite-node-2
RQLITE_NODE_2_NODE_ID=rqlite-node-2
RQLITE_NODE_2_DATA_DIR=/rqlite/file/data
RQLITE_NODE_2_HTTP_ADDR=rqlite-node-2:4001
RQLITE_NODE_2_RAFT_ADDR=rqlite-node-2:4002
RQLITE_NODE_2_HTTP_ADV_ADDR=rqlite-node-2:4001
RQLITE_NODE_2_RAFT_ADV_ADDR=rqlite-node-2:4002
# join
RQLITE_NODE_2_JOIN_ADDR=rqlite-node-2:4001

# RQLITE_NODE_3
RQLITE_NODE_3_HOSTNAME=rqlite-node-3
RQLITE_NODE_3_NODE_ID=rqlite-node-3
RQLITE_NODE_3_DATA_DIR=/rqlite/file/data
RQLITE_NODE_3_HTTP_ADDR=rqlite-node-3:4001
RQLITE_NODE_3_RAFT_ADDR=rqlite-node-3:4002
RQLITE_NODE_3_HTTP_ADV_ADDR=rqlite-node-3:4001
RQLITE_NODE_3_RAFT_ADV_ADDR=rqlite-node-3:4002
# join
RQLITE_NODE_3_JOIN_ADDR=rqlite-node-3:4001

Now create the the Docker network:

> docker network create rqlite-cluster-network

And start the cluster:

> docker-compose up

The messages in the terminal show the rqlite nodes contacting each other. Is the cluster really up? To check this, open another terminal and enter one of the rqlite containers:

> docker exec -it rqlite-cluster_rqlite-node-3_1 sh

Then connect to one of the nodes:

# rqlite -H rqlite-node-1

Result:

Welcome to the rqlite CLI. Enter ".help" for usage hints.
Version v7.21.4, commit 971921f1352bdc73e4e66a1ec43be8c1028ff18b, branch master
Connected to rqlited version v7.21.4
rqlite-node-1:4001>

Issue the command '.nodes'. Result:

rqlite-node-2:
  leader: false
  time: 0.001115574
  api_addr: http://rqlite-node-2:4001
  addr: rqlite-node-2:4002
  reachable: true
rqlite-node-3:
  leader: false
  time: 0.001581149
  api_addr: http://rqlite-node-3:4001
  addr: rqlite-node-3:4002
  reachable: true
rqlite-node-1:
  time: 0.000009044
  api_addr: http://rqlite-node-1:4001
  addr: rqlite-node-1:4002
  reachable: true
  leader: true

There we are, the cluster is up!

Now, let's try a SQL command from another container connected to the cluster network, here we use the 'nicolaka/netshoot' image:

> docker run -it --network rqlite-cluster-network nicolaka/netshoot bash

Issue the command to create a table:

# curl -XPOST 'rqlite-node-2:4001/db/execute?pretty&timings' -H "Content-Type: application/json" -d '[
    "CREATE TABLE foo (id INTEGER NOT NULL PRIMARY KEY, name TEXT, age INTEGER)"
]'

Result:

{
    "results": [
        {
            "time": 0.000179355
        }
    ],
    "time": 0.018545186
}

Repeat the command and the result is:

{
    "results": [
        {
            "error": "table foo already exists"
        }
    ],
    "time": 0.017034644
}

Great, our rqlite cluster is up and running.

Access the rqlite cluster with Python

The rqlite project also has several clients, including pyrqlite, a client for Python. We use the pyrqlite example on the rqlite Github page.  We make two changes:

• The 'host'.
• We drop the table if it already exists.

On the host system, create a subdirectory 'code' and add the following file:

# rqlite_test.py

import pyrqlite.dbapi2 as dbapi2

# Connect to the database
connection = dbapi2.connect(
    host='rqlite-node-2',
    port=4001,
)

try:
    with connection.cursor() as cursor:
        cursor.execute('DROP TABLE IF EXISTS foo') 
        cursor.execute('CREATE TABLE foo (id integer not null primary key, name text)')
        cursor.executemany('INSERT INTO foo(name) VALUES(?)', seq_of_parameters=(('a',), ('b',)))

    with connection.cursor() as cursor:
        # Read a single record with qmark parameter style
        sql = "SELECT `id`, `name` FROM `foo` WHERE `name`=?"
        cursor.execute(sql, ('a',))
        result = cursor.fetchone()
        print(result)
        # Read a single record with named parameter style
        sql = "SELECT `id`, `name` FROM `foo` WHERE `name`=:name"
        cursor.execute(sql, {'name': 'b'})
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()

Start and enter a Python container, connected to the 'rqlite-cluster-network', and mounting our code on the host system at '/code' inside the container:

> docker run -it --net rqlite-cluster-network -v ${PWD}/code:/code python:3.11.5-slim-bullseye bash

Inside the container, install pyrqlite:

# pip install pyrqlite

Inside the container, change to the '/code' directory and run the script:

# python rqlite_test.py

Result:

OrderedDict([('id', 1), ('name', 'a')])
OrderedDict([('id', 2), ('name', 'b')])

Working!

Summary

Although it seems like it is very easy to use rqlite instead of SQLite, it is not! You must determine (read, read, read) if rqlite meets your requirements, which is not easy because the differences and limitations are mentioned on several pages in the documentation.

Creating an rqlite cluster is not difficult when using Docker, or Docker Swarm. There is also a guide for Kubernetes. The rqlite cluster gives us a distributed, more or less fault-tolerant, SQLite database.

To obtain fault-tolerance at application level, we must add a list of rqlite nodes (hosts) to our application and add some code to switch to another rqlite node, when a rqlite node is not available. Anyway, for my case, rqlite is a perfect solution!

Links / credits

Comparing Litestream, rqlite, and dqlite
https://gcore.com/learning/comparing-litestream-rqlite-dqlite

rqlite
https://rqlite.io

rqlite - Automatic clustering: Automatic Bootstrapping
https://rqlite.io/docs/clustering/automatic-clustering

rqlite - Developer Guide
https://rqlite.io/docs/api

rqlite/rqlite Docker image
https://hub.docker.com/r/rqlite/rqlite