SQLite3 Replication: A Wizard's Guide🧙🏽
Tags: tutorial, database, sqlite, sql
Published: 2024-02-27 19:57:05

## 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](https://litestream.io/).

### 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.


##### References :
- https://litestream.io/guides/
- https://www.sqlite.org/index.html
- https://docs.python.org/3/library/sqlite3.html
- https://www.infoworld.com/article/3331923/why-you-should-use-sqlite.html