This repository provides the
dqlite Go package, which can be used to replicate a SQLite database across a cluster, using the Raft algorithm.
- No external processes needed: dqlite is just a Go library, you link it it to your application exactly like you would with SQLite.
- Replication needs a SQLite patch which is not yet included upstream.
- The Go Raft package from Hashicorp is used internally for replicating the write-ahead log frames of SQLite across all nodes.
How does it compare to rqlite?
The main differences from rqlite are:
- Full support for transactions
- No need for statements to be deterministic (e.g. you can use
- Frame-based replication instead of statement-based replication, this means in dqlite there’s more data flowing between nodes, so expect lower performance. Should not really matter for most use cases.
SQLite is a database engine that makes it simple to store and work with relational data. Much like the csvformat, SQLite stores data in a single file that can be easily shared with others. Most programming languages and environments have good support for working with SQLite databases. Python is no exception, and a library to access SQLite databases, called
sqlite3, has been included with Python since version
2.5. In this post, we’ll walk through how to use
sqlite3 to create, query, and update databases. We’ll also cover how to simplify working with SQLite databases using the pandas package. We’ll be using Python
3.5, but this same approach should work with Python
Before we get started, let’s take a quick look at the data we’ll be working with. We’ll be looking at airline flight data, which contains information on airlines, airports, and routes between airports. Each route represents a repeated flight that an airline flies between a source and a destination airport.
All of the data is in a SQLite database called
flights.db, which contains three tables –
routes. You can download the data here.
Working with SQLite Databases using Python and Pandas
Every once in a while, someone suggests that beets should use a “real database.” I think this means storing music metadata in PostgreSQL or MySQL as an alternative to our current SQLite database. The idea is that a more complicated DBMS should be faster, especially for huge music libraries.
The pseudo-official position of the beets project is that supporting a new DBMS is probably not worth your time. If you’re interested in performance, please consider helping to optimize our database queries instead.
There are three reasons I’m unenthusiastic about alternative DBMSes: I’m skeptical that they will actually help performance; it’s a clear case of premature optimization; and SQLite is unbeatably convenient.
Detailed background on rqlite can be found on these blog posts. Note that master represents 2.0 development (which is still in progress), with a new API and Raft consensus module. If you want to work with 1.0 rqlite, you can find it here.
rqlite is a distributed system that provides a replicated SQLite database. rqlite is written in Go and uses Raft to achieve consensus across all the instances of the SQLite databases. rqlite ensures that every change made to the database is made to a quorum of SQLite files, or none at all.
rqlite gives you the functionality of a fault-tolerant, replicated relational database, but with very easy installation, deployment, and operation.
“SQLite is a “self-contained, serverless, zero-configuration, transactional SQL database engine”. However, it doesn’t come with replication built in, so if you want to store mission-critical data in it, you better back it up. The usual approach is to continually copy the SQLite file on every change.
I wanted SQLite, I wanted it distributed, and I really wanted a more elegant solution for replication. So rqlite was born…”
Replicating SQLite using Raft Consensus
“rqlite is a distributed system that provides a replicated SQLite database. rqlite is written in Go and uses Raft to achieve consensus across all the instances of the SQLite databases. rqlite ensures that every change made to the database is made to a majority of underlying SQLite files, or none-at-all…”
Replicating SQLite using Raft Consensus