💾 Archived View for wilw.capsule.town › log › 2021-02-01-why-not-sqlite.gmi captured on 2023-09-08 at 16:16:01. Gemini links have been rewritten to link to archived content
⬅️ Previous capture (2023-04-19)
-=-=-=-=-=-=-
If you need a database for your next project, why not first consider if SQLite [1] might be a good option? And I don't mean just for getting an MVP off the ground or for small personal systems; I mean for "real" production workloads.
Many people will be quick to jump on this with chimes of "it's not designed for production", but I think it depends on what is actually _meant_ by "production"? Sure, it's not the right choice for every scenario - it wouldn't work well in distributed workloads or for services expected to receive a very high volume of traffic - but it has been used successfully in many real-world cases.
What made me feel the need to write this article was seeing this sentence in the README of the Synapse Docker repo [2]:
By default it uses a sqlite database; for production use you should connect it to a separate postgres database. - matrixdotorg/synapse [3]
Don't get me wrong. I totally get its meaning, but at the same time do personal Matrix servers or home Nextcloud servers [4] not count as "production"?
Pieter Levels [5] famously used SQLite to help drive revenues from some of his products to well over six-digit dollar values [6], and SQLite's own 'appropriate uses' list [7] explains where it can be useful:
> SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites) - sqlite.org [8]
Even if your site or service does eventually outgrow SQLite (which will be a nice problem to have), your application code will still be using SQL and so it should be relatively easy to migrate to something like PostgreSQL [9].
As Paul Graham said [10], "do things that don't scale".
Of course, it is backed by disk and so is subject to the usual I/O constraints applicable to any file, but nearly all VPS providers offer SSD-backed instances these days and SQLite claims to be faster than filesystem I/O [11] anyway.
It's worth remembering that there can be huge overheads and costs in setting up "production-ready" database servers. You'll need to think about provisioning the instance itself, installation of dependencies, certificates, the usual networking hardening (firewalls, ports, etc.) - and then keeping all of this up-to-date too. Even when using managed database services there are still user roles, authentication and rotating credentials to worry about, along with securely provisioning your applications with the connection strings.
Having all of these things to worry about carries the additional risk of encouraging people to become lazy or to not have the time needed to make sure everything is done properly; an easy way to accidentally introduce security issues. Plus, if you have multiple environments (e.g. for staging or testing) then these factors, and the associated costs, amplify.
There is also some interesting discussion on the topic in this Hacker News thread [12] from last year.
I just think it's definitely worth a go before jumping straight into alternative heavier options. It's free, has a smaller footprint [13], has easily accessible bindings for many languages, and you can get started in minutes - all you need is a file [14].