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:
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().
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.
-
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? -
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. -
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. -
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.