aboutblognotesprojectsresearch

More posts

Why not SQLite?

1 February 2021 (3 months ago)🏷️ #100daystooffload🏷️ #technology🏷️ #opinion

💯 100 Days to Offload

This article is one of a series of posts in the 100 Days to Offload challenge . The challenge focuses on writing frequency rather than quality, and so posts may not always be fully planned out. They are simply a way to offload thoughts.

View other articles in this series

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.

--

This article is part of a collection of posts involved in the #100DaysToOffload series. As such it may have been written quickly and should be considered more as a thought "dump" rather than a fully-fledged essay. Thanks for reading!

📲 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