Rqlite

What: rqlite is a lightweight, distributed relational database built on SQLite.

Installation

Linux and macOS

Check the official binaries: https://github.com/rqlite/rqlite/releases

Installation on Raspberry Pi

Note: There are no rqlite binaries for the RPi, however it is possible to compile it quite easily. You only need to compile it on *one* RPi, after that you can simply copy the resulting binaries to other RPis of your cluster.

Compilation

• as root
• install latest version of golang for ARMv6 architecture (https://golang.org/dl/):

cd /usr/src
wget https://dl.google.com/go/go1.10.2.linux-armv6l.tar.gz
tar -C /usr/local -xzvf go1.10.2.linux-armv6l.tar.gz
export PATH=$PATH:/usr/local/go/bin

• prepare environment for building rqlite (as root or regular user)

cd
mkdir go
cd go
mkdir bin pkg src
export GOPATH=$PWD

• build rqlite (this will take a while, monitor with (h)top if you like)

go get -u -t github.com/rqlite/rqlite/...   # yes you need the '...' at the end
cd $GOPATH/src/github.com/rqlite/rqlite/cmd/rqlite
go build -v .
sudo cp rqlite /usr/local/bin
cd $GOPATH/src/github.com/rqlite/rqlite/cmd/rqlited
go build -v .
cp rqlited /usr/local/bin

That's it!

Installation on other nodes

You just need to copy the binaries rqlite and rqlited to /usr/local/bin.

A simple 3 nodes cluster

This is assuming that you have working rqlite and rqlited on 3 different machines.

Note: According to the documentation you need at least 3 nodes to test a fault tolerant cluster. In practice, to be fault tolerant, you need N/2 + 1 nodes up and running where N is the number of nodes in the cluster, to be fault tolerant.

Create initial leader

To create a cluster you must first launch a node that can act as the initial leader. Do this as follows on host1:

rqlited -node-id 1 -http-addr host1:4001 -raft-addr host1:4002 ~/node

Note: host1 needs to resolve to an IP reachable by the other nodes. You can also directly used an IP, which can be useful for running rqlite with a VPN.

Create subsequent node N

To join a second node to this leader, execute the following command on hostN:

rqlited -node-id 2 -http-addr hostN:4001 -raft-addr hostN:4002 -join http://host1:4001 ~/node

or

rqlited -http-addr 10.0.0.2:4001 -raft-addr 10.0.0.2:4002 -join http://10.0.0.10:4001 ~/node 

Note: hostN needs to resolve to an IP reachable by the other nodes. You can also directly used an IP, which can be useful for running rqlite with a VPN.

Testing the cluster!

on leader/host1

• make sure rqlited is running (see above)
• create some entries

rqlite -H host1

# to create a table:
host1> CREATE TABLE foo (id INTEGER NOT NULL PRIMARY KEY, name TEXT)
# to view tables in db
host1> .tables
# to make a entry into the db
host1> INSERT INTO foo(name) VALUES("fiona")
# to display the entry on that table
host1> SELECT * FROM foo

on hostN

• make sure rqlited is running (see above)
• create some entries

rqlite -H host1     # YES it's host1!

# to view tables in db
host1> .tables
# to make a entry into the db
host1> INSERT INTO foo(name) VALUES("anotherentry")
# to display the entry on that table
host1> SELECT * FROM foo

More information

The documentation of rqlite has more information and details about specific commands and tweaks: https://github.com/rqlite/rqlite/tree/master/DOC

Use rqlite with your sqlalchemy application

install pyrqlite

git clone https://github.com/rqlite/pyrqlite.git
pip3 install ./pyrqlite

install sqlalchemy-rqlite

git clone https://github.com/rqlite/sqlalchemy-rqlite.git
cd sqlalchemy-rqlite
sudo python3 ./setup.py install

insert into your app:

(replace IP & PORT with yours)

registry.register("rqlite.pyrqlite", "sqlalchemy_rqlite.pyrqlite", "dialect")
app.config['SQLALCHEMY_DATABASE_URI'] = 'rqlite+pyrqlite://localhost:4001/'