Siren Call of SQLite on the Server

Posted on Feb 14, 2025

At Terrateam, we are big fans of Fly.io. The service is hosted there and it’s served us well. Just deploy your TOML file, get your infrastructure, do something else with the rest of your day.

One of the interesting sides of Fly is that they invest heavily in server-side SQLite. They’ve written a number of blog posts on how they enable server-side SQLite:

  1. I’m All-In on Server-Side SQLite - Ben Johnson, the author of BoltDB, joins Fly to work on Litestream, a SQLite replication solution.

  2. Introducing LiteFS - The introduction of LiteFS, which is a FUSE file system designed to replicate SQLite transactions over the network.

  3. LiteFS Cloud: Distributed SQLite with Managed Backups - Introducing backups and restores for LiteFS.

There is the occasional question on the internet about using SQLite server-side. And a solid blog post, Consider SQLite, by Wesley Aptekar-Cassels, on the topic.

The SQLite project has been pretty clear on its goal. It is not to be a client/server database alternative, it’s to be an ad-hoc application data file alternative. But it is software and with a bit of hard work, you can make software do amazing things.

SQLite does not compete with client/server databases. SQLite competes with fopen().
— SQLite Docs
When To Use

The thing is…​just don’t run SQLite server-side in production. Unless you have a really good reason.

It isn’t because SQLite might lose your data (it won’t), or it doesn’t scale well (it scale’s just fine), it’s because using SQLite as your primary data store just makes your life harder.

Why?

  1. The value of SQLite is that it’s infrastructure-less. You don’t have to run anything additional to use it. But once you decide to run it server-side in production you now need, at the very least, backups. If you want to run the service across multiple machines you can use LiteFS, you’re also going to need some sort of leader-election system as only one machine can write. LiteFS supports Consul, so now you need to run Consul. All this, to avoid running a PostgreSQL, which pretty much any cloud vendor has a packaged solution for you that does backups?

  2. Migrations are not great in SQLite. If you search online you’ll find all sorts of workarounds that sort-of-kind-of work but it’s not great.

  3. Decoupling storage from compute is the default architecture because it’s a really good idea. We’ve spent the lifetime of this industry learning how to decouple components correctly. It increases your optionality. There are lots of options for managing scale and failure in a boring app with a database server architecture.

  4. Migrating from SQLite is not incredibly hard, but it’s not easy, and it’s still pointless work. If you are in a situation where you need to scale, do you really want to be moving to a new storage engine in the midst of that? Migrating state is hard to do without downtime. SQLite, by default, is not very strict with your data, like enforcing types or foreign key constraints, so your data may not even directly go into something like PostgreSQL without some massaging. Interacting with a database library vs a server also has very different latency profiles, so you might have to refactor your queries to be more server-latency friendly.

When we were first designing Terrateam, we were inspired a lot by Atlantis. Atlantis uses BoltDB as its database and has been, IMO, suffering ever since. BoltDB is like SQLite, in that it’s a database-as-a-library. High availability has been a long open ticket. In order to cluster Atlantis one needs to share the file system via a networked file system, like NFS, and the redis locking engine. With Terrateam, we knew that we wanted the option to run it as a SaaS, requiring both high availability and scalability, so we chose a traditional architecture. Atlantis benefits from requiring less infrastructure to run. But how big of a win is that? IMO, it’s not worth it.

I write a lot of my own day-to-day software. One of them is a little app for listening to podcasts. There is a server that fetches the podcast information and a client for listening. The server uses SQLite. And it’s fine. Its a service I only use for myself. I only run one instance of it. I rsync the database to my laptop for backups. It’s easy to develop locally. I chose to use SQLite because I wanted to experiment with it. All that is to say that there are situations where it’s totally fine to use SQLite server-side. For myself, I default to a traditional architecture for production unless there is a very obvious and strong need to diverge.