aboutblognotesprojectsresearch

More posts

Why not SQLite?

1 February 2021 (a year ago)🏷️ #100daystooffload🏷️ #technology🏷️ #opinion

💯 100 Days to Offload

This article is one of a series of posts I have written for the 100 Days to Offload challenge . Disclaimer: The challenge focuses on writing frequency rather than quality, and so posts may not always be fully planned out!

View other articles in this series

🕰️ This is an old post

Please note that this article was posted quite a while ago and may now be out-of-date or inaccurate.

If you need a database for your next project, why not first consider if SQLite 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.

Why not Sqlite?

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:

By default it uses a sqlite database; for production use you should connect it to a separate postgres database. - matrixdotorg/synapse

Don't get me wrong. I totally get its meaning, but at the same time do personal Matrix servers or home Nextcloud servers not count as "production"?

Pieter Levels famously used SQLite to help drive revenues from some of his products to well over six-digit dollar values, and SQLite's own 'appropriate uses' list 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

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.

As Paul Graham said, "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 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 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, has easily accessible bindings for many languages, and you can get started in minutes - all you need is a file.

📲 Enjoyed this article? Subscribe to updates!

If you would like to read more posts like this, then you can subscribe via RSS.

Subscribe to an RSS feed