SQLite Simple but Mighty

Many of y’all may be familiar with SQLite. For some of you, it is that little file you used in school when learning about databases, maybe their internal data structures, and how to query them. For others, it might be that tiny tool you used to get that hackathon project up and running that you will definitely get back to and finish.

Before going into what you can use it for, let's talk about what's under the hood that makes it special. Let's start with the definition, we'll grab it straight from the official website, "SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine". These are pretty impressive claims, but how does it accomplish them? Let's go down the list.

Small, the entire library sits at around 700kb in size. In a world where some web applications can ship over a megabyte of javascript to the browser, this is lightweight. When compared to managed databases like PostgreSQL and MySQL, it is outright tiny. PostgreSQL will tell you in their docs that "the rule is you can never have too much memory", and goes on to detail that at minimum we will need around 36mb for /usr/src/pgsql, /usr/local/pgsql, and empty database; looking through the MySQL Installer guide, the full bundle is over 300mb. Why does this matter? It means that not only is it useful for prototyping, it can be directly embedded into full fledged applications, especially where installing and running a full database server is not possible. For example, IoT devices in industrial settings where there is limited storage and memory, it is not feasible to run an entire managed database, whereas SQLite can be run and interfaced with from a single file.

Fast can be interpreted a few different ways. One such way is setup speed. Starting completely from scratch, within a minute I can download a precompiled binary for my operating system. It downloads as a 3.7mb zip file. I can then extract it, go to my terminal and cd to where the binary was unzipped to, type sqlite3 blazingfast.db, and I'm able to start creating tables. Let's compare that with PostgreSQL and MySQL, now full disclosure, I've set up a database for my local development environment enough times that a lot of it is second nature, but let's unpack the steps I had to take. My download for MySQL came out to 554.7mb for the dmg, and PostgreSQL was 387.8mb.  Then for both I had to go through the installers. In the case of PostgreSQL, after installing, the psql command was not working, and I did not want to spend time at the moment troubleshooting, so I had to launch pgAdmin. Well first wait for it to launch, and then navigate the GUI to set up my database. Now let's go back to MySQL. I let the installer run to completion. I tried my terminal, but the mysql command did not work. I remembered I probably don't have MySQL Server running from my settings, so I made my way over to my laptop's settings, and hit "Start MySQL Server". Green dot, great news! Next I click "Initialize Database '', and after entering my admin credentials, it freezes, and I see a red dot with the button saying start instead of stop server. Now I'm not trying to rag on MySQL and Postgres, I've used them both extensively in my career and they serve their purpose well. But illustrating my point, not even considering Docker and the troubleshooting vector that can introduce, if we only look at the speed at which you can get up and running, SQLite is an obvious champion.

Now let's go through some of the other ways SQLite is fast in addition to its setup speed. Due the footprint is so small, SQLite can run and operate entirely in memory. Thanks to its internal B-Tree data structures for tables and indexing, it is able to accomplish speedy read operations. Data is also typically stored in contiguous blocks on disk, which makes sequential lookups execute quickly. Reading data is great but what about inserting you ask? Well SQLite has you covered there, thanks to its minimal locking mechanism and ability to reduce overhead by grouping multiple inserts in a batch, it is able to handle thousands of inserts per second.

Self-contained is pretty straightforward to explain. The entire database fits on a single file on the disk. That's it. No extra dependencies. It's so self-contained that Django, Rails, and Laravel make it the happy path database when they bootstrap a new project. This also means there is no database administrator to set up to manage the database. A separate server does not need to run in order to interact with your data, all of the input and output operations are abstracted away by this small C library.

What does it mean to be high-reliability? Well in the case of standards, it means that SQLite boasts some of the highest when it comes to testing commercial software. For those interested, they detail it extensively on their website. It has been designed to handle a variety of error paths. And the developers have stated their intention to support the software through 2050.

What about full-featured? What does that mean? Well, despite its small amount of space it takes up, SQLite supports many of the important features found in managed database solutions. It supports most of the SQL-92 Standard. This means it supports tables, indexes, triggers, and views. It allows for adding constraints on columns, aggregate functions, joining tables, unions, and the other basic features we would expect out of a database. We even have features like JSON support and full text search. For a full list, I highly recommend checking out their website.

So what do we know about SQLite? Well we know it does not take a whole lot of effort to set up. We also know that it is not something that needs to be immediately replaced by the "more powerful" tool. Managed databases are powerful, but they also introduce a system that has more points of failure than SQLite. It cannot do everything, but it is worth considering if you really need a fully managed database or if a simple system to store your data in is fine.