SQLite3 Replication: A Wizard's Guide🧙🏽

Abhishek Mishra — Tue, 27 Feb 2024 19:57:05 +0000

Introduction:

Let's admit it – SQLite often doesn't receive the credit it deserves as a database. I mean, it has no configuration overhead, no external dependencies, doesn't need a server or a process to run on, can also run "in-memory"⚡.

Often, with databases something that comes up is "Replication". Other databases such as Postgres, MySQL offer their own replication mechanisms. SQLite does not offer something natively when it comes to replication.

This post intends to help you setup replication for SQLite using Litestream.

What do you need?

Any Cloud Storage (AWS S3 for this Tutorial)
A SQLite Database
Litestream

Installing SQLite and Litestream:

SQLite:

Execute the below commands on your terminal:

sudo apt update
sudo apt install sqlite3

And then verify the installation using sqlite3 --version command.

Litestream:

Download the .deb file for Litestream ,which contains Litestream and it's systemd service using below command and install it:

wget https://github.com/benbjohnson/litestream/releases/download/v0.3.13/litestream-v0.3.13-linux-amd64.deb
sudo dpkg -i litestream-v0.3.13-linux-amd64.deb

This should install Litestream on your system, to verify the installation, execute litestream --version command.

Creating a SQLite Database & Tables:

For the sake of the tutorial, I will create a database named pokedex.db which contains information about different Pokemon speicies.

Create the SQLite Database using sqlite3 pokedex.db command. It will invoke the sqlite3 shell and allow you to modify pokedex.db which we just created.

golu360@ip-127-0-0-1:~$ sqlite3 pokedex.db
SQLite version 3.39.5 2022-10-14 20:58:05
Enter ".help" for usage hints.
sqlite>

Create the Pokemon Table using below query:

CREATE TABLE Pokemon (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    type TEXT NOT NULL,
    level INTEGER,
    evolution_stage INTEGER
);

Insert some data into this table:

INSERT INTO Pokemon (name, type, level, evolution_stage) VALUES ('Bulbasaur', 'Grass', 5, 1);
INSERT INTO Pokemon (name, type, level, evolution_stage) VALUES ('Ivysaur', 'Grass', 16, 2);
INSERT INTO Pokemon (name, type, level, evolution_stage) VALUES ('Venusaur', 'Grass', 32, 3);
INSERT INTO Pokemon (name, type, level, evolution_stage) VALUES ('Charmander', 'Fire', 5, 1);
INSERT INTO Pokemon (name, type, level, evolution_stage) VALUES ('Charmeleon', 'Fire', 16, 2);
INSERT INTO Pokemon (name, type, level, evolution_stage) VALUES ('Charizard', 'Fire', 36, 3);
INSERT INTO Pokemon (name, type, level, evolution_stage) VALUES ('Squirtle', 'Water', 5, 1);
INSERT INTO Pokemon (name, type, level, evolution_stage) VALUES ('Wartortle', 'Water', 16, 2);
INSERT INTO Pokemon (name, type, level, evolution_stage) VALUES ('Blastoise', 'Water', 36, 3);
INSERT INTO Pokemon (name, type, level, evolution_stage) VALUES ('Caterpie', 'Bug', 3, 1);

So, we have an SQLite Database with some data setup. Now we can replicate this into our S3 Bucket.

The Good Part:

Litestream runs as a background service on your machine which uses a configuration file.

Enable Litestream to run as a background service

sudo systemctl enable litestream
sudo systemctl start litestream

and then verify if the service is running or not using sudo journalctl -u litestream -f command.

The Litestream config path is /etc/litestream.yml. Use cat /etc/litestream.yml to checkout the contents of this file and ensure it is present in the mentioned path.

The config file contents should look like :

#access-key-id: AKIAxxxxxxxxxxxxxxxx
#secret-access-key: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/xxxxxxxxx

#dbs:
#  - path: /path/to/local/db
#    replicas:
#      - url: s3://BUCKETNAME/PATHNAME

In the access-key-id and secret-access-key mention your AWS Access Key and Secret Access Keys. Ensure that this user has needed permissions on the S3 bucket you want to replicate to.

Mention the DB Path of your Database and the S3 Bucket URL in which you want to replicate the database into. For our pokedex.db, it should look something like below:

access-key-id: AKIA12345GGF
secret-access-key: EXAMPLESECRET-key

dbs:
  - path: /home/golu360/pokedex.db
    replicas:
      - url: s3://pokedex-replica/db

Since we have changed the Litestream config, you can simply restart the Litestream service using sudo systemctl restart litestream command. To ensure all is running fine, check the service status using sudo systemctl status litestream.

Voila! Litestream should now start replicating your DB into S3.

Simulating a Disaster and Restoring the DB:

To simulate the worst possible disaster for a SQLite Database, just delete the database file. Yes, delete it. Wield the rm pokedex.db spell, and cast it on your terminal screen.

Now onto the restoration of this database.

litestream restore -o pokedex.db s3://pokedex-replica/db

Above command should simply restore your database to it's state prior to deletion. Your database is back to life.

Conclusion:

We just setup a replication setup for SQLite using Litestream to add replication and point in-time failure recovery, without any code changes to your application code.

Litestream also supports replication to Azure Blob Storage, Google Cloud Storage and more.

DEV Community: Abhishek Mishra