tqlite is a distributed SQL database with replication, fault-tolerance, tunable consistency and leader election. It uses SQLite, a small, fast and self-contained SQL engine, as the basic unit in the cluster.
SQLite is a popular embedded SQL database. It is lightweight, full-featured, and easy to use. However, it is prone to single-point-of-failure due to its single-file-based nature.
tqlite provides you a lightweight, reliable and highly available SQL cluster, with easy deployment, and operation. Think of tqlite as a SQL version of etcd or Consul.
tqlite ensures the system state is in accordance with a quorum of nodes in the cluster using Raft, a well-kown concensus algorithm in a distributed system.
- Lightweight deployment with a single binary
- Support dumping, backing up, and restoring database
- Straightforward HTTP data API
- Distributed consensus system
- Tunable read consistency
Docker container is available:
docker pull minghsu0107/tqlite:v1Or you could build from source:
git clone https://github.com/minghsu0107/tqlite.git
go build -o tqlite -v ./cmd/tqlite
go build -o tqlited -v ./cmd/tqlitedYou can start a single tqlite node first:
docker network create tqlite-net
docker run --name node1 -p 4001:4001 --network tqlite-net minghsu0107/tqlite:v1 -node-id 1 -http-addr 0.0.0.0:4001 -http-adv-addr localhost:4001 -raft-addr 0.0.0.0:4002 -raft-adv-addr node1:4002This single node becomes the leader automatically. You can pass -h to tqlited to list all configuration options.
To be fault-tolerant, we could run tqlite in the cluster mode. For example, we could join the second and third node to the cluster by simply running:
docker run --name node2 -p 4011:4001 --network tqlite-net minghsu0107/tqlite:v1 -node-id 2 -http-addr 0.0.0.0:4001 -http-adv-addr localhost:4011 -raft-addr 0.0.0.0:4002 -raft-adv-addr node2:4002 -join http://node1:4001
docker run --name node3 -p 4021:4001 --network tqlite-net minghsu0107/tqlite:v1 -node-id 3 -http-addr 0.0.0.0:4001 -http-adv-addr localhost:4021 -raft-addr 0.0.0.0:4002 -raft-adv-addr node3:4002 -join http://node1:4001Now you have a fully replicated cluster where a majority, or a quorum, of nodes are required to reach conensus on any change to the cluster state. A quorum is is defined as (N/2)+1 where N is the number of nodes in the cluster. In this example, a 3-node cluster is able to tolerate a single node failure.
Now, we are going to use tqlite client CLI to insert some data to the leader node. The leader will then replicate data to all followers within the cluster.
docker exec -it node1 bash
tqlite$ tqlite
127.0.0.1:4001> CREATE TABLE students (id INTEGER NOT NULL PRIMARY KEY, name TEXT);
0 row affected
127.0.0.1:4001> .schema
+--------------------------------------------------------------------+
| sql |
+--------------------------------------------------------------------+
| CREATE TABLE students (id INTEGER NOT NULL PRIMARY KEY, name TEXT) |
+--------------------------------------------------------------------+
127.0.0.1:4001> INSERT INTO students(name) VALUES("ming");
1 row affected
127.0.0.1:4001> SELECT * FROM students;
+----+------+
| id | name |
+----+------+
| 1 | ming |
+----+------+
You can see that tqlite client CLI is compatible with SQLite, minimizing the operation costs.
Inspired by Elasticsearch, tqlite exposes data by a rich HTTP API, allowing full control over nodes to query from or write to. We could use HTTP API to do CRUD operations with tunable consistency. Take above students table as an example:
# query
curl -XPOST 'localhost:4001/db/query?pretty&timings' -H "Content-Type: application/json" -d '[
"SELECT * FROM students"
]'Query result:
{
"results": [
{
"columns": [
"id",
"name"
],
"types": [
"integer",
"text"
],
"values": [
[
1,
"ming"
]
],
"time": 0.000053034
}
],
"time": 0.000098828
}
In addition, you could pass parameterized statements to avoid SQL injections:
# write
curl -XPOST 'localhost:4001/db/execute?pretty&timings' -H "Content-Type: application/json" -d '[
["INSERT INTO students(name) VALUES(?)", "alice"]
]'
# read
curl -XPOST 'localhost:4001/db/query?pretty&timings' -H "Content-Type: application/json" -d '[
["SELECT * FROM students WHERE name=?", "alice"]
]'You could start a transaction by adding transaction query parameter:
curl -XPOST 'localhost:4001/db/execute?pretty&transaction' -H "Content-Type: application/json" -d "[
\"INSERT INTO students(name) VALUES('alan')\",
\"INSERT INTO students(name) VALUES('monica')\"
]"Multiple insertions or updates in a transaction are contained within a single Raft log entry and will not be interleaved with other requests.
Any write request received by followers will be fowarded to the leader. A write request received by the leader is accepted once it replicates the data to a quorum of nodes through Raft successfully. In the below command, we send a write request to node2, a follower. Thus the request will be redirected to the leader:
curl -i -XPOST 'localhost:4021/db/execute?pretty&timings' -H "Content-Type: application/json" -d '[
["INSERT INTO students(name) VALUES(?)", "bob"]
]'Result:
HTTP/1.1 301 Moved Permanently
Content-Type: application/json; charset=utf-8
Location: http://localhost:4001/db/execute?pretty&timings
X-Tqlite-Version: 1
Date: Mon, 07 Jun 2021 17:25:13 GMT
Content-Length: 0
Then it is up the clients to re-issue the query command to the leader.
As for read operations, query with consistency level none will result in a local read. That is, the node simply queries its local SQLite database directly. In HTTP data API, We should set the query string parameter level to none to enable it:
curl -i -XPOST 'localhost:4021/db/query?pretty&timings&level=none' -H "Content-Type: application/json" -d '[
["SELECT * FROM students WHERE name=?", "alice"]
]'In the above query, we send read request to node2 and will receive an instant response from node2 without checking its leadership with other peers in the cluster.
If we send read request to the leader node with consistency level set to weak, which is the default consistency level, tqlite will instruct the leader to check its local state that it is the leader before querying local SQLite database. If the node receiving the read request is a follower, the request will be redirected to the leader. However, a very small window of time (milliseconds by default) during which the node may return stale data. This is because after the leader check, but before querying local SQLite database, another node could be elected Leader and make changes to the cluster.
If we send read request to the leader node with consistency level set to strong, tqlite will read from leader node and send the request through Raft consensus system, ensuring that the node remains the leader at all times during query processing. If the node receiving the read request is a follower, the request will be redirected to the leader. The strong consistency solves the issue associated with weak consistency. However, this will involve the leader contacting at least a quorum of nodes and will therefore increase query response times.
Redirection example:
curl -i -XPOST 'localhost:4021/db/query?pretty&timings&level=strong' -H "Content-Type: application/json" -d '[
["SELECT * FROM students WHERE name=?", "alice"]
]'Result:
HTTP/1.1 301 Moved Permanently
Content-Type: application/json; charset=utf-8
Location: http://localhost:4001/db/query?pretty&timings&level=strong
X-Tqlite-Version: 1
Date: Mon, 07 Jun 2021 17:25:57 GMT
Content-Length: 0
To enhance the performance, tqlite runs SQLite in-memory by default, meaning that there is no actual file created on disk. The data durability is guaranteed by the Raft journal, so the database could be recreated in the memory on restart. However, you could still enable the disk mode by adding flag -on-disk to tqlited.