rqlite: a high-availability and distributed SQLite alternative
There are many limitations, but there are also many use cases for rqlite instead of SQlite.
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
Read more
rqlite
Leave a comment
Comment anonymously or log in to comment.
Comments (1)
Leave a reply
Reply anonymously or log in to reply.
I'm the creator of rqlite -- nice article. I'm glad you find the software useful.
Philip
(https://www.philipotoole.com)
Most viewed
- Using Python's pyOpenSSL to verify SSL certificates downloaded from a host
- Using PyInstaller and Cython to create a Python executable
- Reducing page response times of a Flask SQLAlchemy website
- Connect to a service on a Docker host from a Docker container
- Using UUIDs instead of Integer Autoincrement Primary Keys with SQLAlchemy and MariaDb
- SQLAlchemy: Using Cascade Deletes to delete related objects