The author presents the latest features of libsql: an open source + contribution fork of sqlite, that aims to bring modern databases features to sqlite. The presented features are the extension of network support in sqlite, in the sense that it can be accessed over the internet using Postgres wire protocol and HTTP.
libSQL “server mode” (sqld) enables access to SQLite-based databases using Postgres and HTTP network protocols.
Applications built on SQLite are very easy to get started with. SQLite requires no setup, no maintenance, and no scaling, and the result of that execution lies entirely in a single file that you could drop into your CI/CD for quick verification. What’s not to like?
But it’s not a common choice for production backends because of its lack of network accessibility for things like monitoring and backups. Most modern applications also need replicas for availability. And thanks to platforms such as Netlify, Vercel, and Cloudflare. applications are moving to the edge: they are now deployed everywhere, instead of somewhere.
Historically, these situations have made SQLite an infeasible option. But no more!
SQLite gets network accessibility
Aside from any technical consideration, the SQLite project is Open Source, but not Open Contribution, which prevents the creation of a community of people and organizations pushing the project in new directions.
In October 2022, we announced a fork of SQLite that is also Open Contribution and distributed under an Open Source License (MIT), indicating our intent to evolve the project into some new and spicy directions. We call it libSQL.
The latest addition is called “server mode” (known as `sqld`) that enables network access to libSQL, as well as replication to multiple instances.
With this first iteration, we support:
The Postgres wire protocol
The Postgres wire protocol over websocket
HTTP
sqld in action
To see how it works, we start the server and indicate we want to serve it over http and postgres. The -dswitch specifies the database file.
Since sqld supports the Postgres wire protocol, standard Postgres tooling works, including the psqlcommand shell. We’ll use it to create a table and insert a row. The commands use SQLite syntax and types:
SQLite standard tooling also works, so we can use the sqlite3command shell to inspect the resulting file:
$ sqlite3 foo.db
SQLite version 3.37.0 2021–12–09 01:34:53
Enter ".help" for usage hints.
sqlite> select \* from databases;
libsql
HTTP and a native client
Last, but not least, you can issue commands over HTTP, so you can just curlto it with a JSON payload, without managing connection pools or anything of the sort.
HTTP support was added to support restricted environments, such as edge functions, where very little besides HTTP is present. To make that even easier, we also provide a native TypeScript client that encapsulates the details of the protocol:
Transpiling and executing the following code:
import { connect } from "@libsql/client"
async function example() {
const config = {
url: process.env.DB\_URI
};
const db = connect(config);
const rs = await db.execute("SELECT \* FROM databases");
console.log(rs);
}
example()
A fair question to ask is this: “There are other projects attempting to merge SQLite and networking, and they don’t fork SQLite. So is a fork really needed?”
A full explanation of how SQLite writes and reads data is out of the scope of this article. There are many other sources that go into great detail about that.
Suffice to say, there are two entities that are relevant when interacting with SQLite’s storage: the VFS, and the WAL (Write-Ahead Log).
WAL Virtualization
While SQLite does offer a virtualized interface for the VFS, it does not allow for the virtualization of the WAL methods. The main work in the core of libSQL was to allow for WAL virtualization.
Once the WAL is virtualized, we can capture any new updates to the database as they happen. This gives us the flexibility of VFS, but with a log-structured API.
Log-structured APIs are easier to replicate, since it is a natural point for streaming changes. In WAL mode, a writer can work in parallel with readers, which provides better concurrency guarantees for mixed and read-intensive workloads, compared to the default rollback journal mode.
Moreover, SQLite has a very interesting feature that isn’t generally available yet — BEGIN CONCURRENT transactions, which use the power of optimistic concurrency control to allow multiple writers to work in parallel. This feature is built on top of WAL mode only.
For us, it’s more than sqld
This is as good a time as any to remember the primary reason for our fork: our goals with libSQL go beyond server mode. Many projects extend SQLite, making it hard to unify these efforts.
We would love to hear how you might like to use libSQL’s server mode in your next project. Give us a shout out on Twitter and join our Discord community.
More importantly, we aim to be a welcoming home for new contributions around the idea of what an embeddable database can be, as described in our motivations. Your support is welcome on our Github!
