This is the first in a series of articles praising some of the tools that I have found to be “Secret Weapons”—tools that have proven unreasonably and repeatedly effective throughout my career, and that I think are not as well known or appreciated as they should be.
Let us now praise SQLite.
From the homepage:
SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine.
SQLite isn’t exactly hurting for users—it’s the most used database engine in the world, with an estimated 1 trillion instances. It’s not exactly unknown, either, as quick search on Hacker News will convince you. But, for all that, I still think it’s underappreciated.
People generally reach for SQLite when they want a database for their modest-sized application and don’t want to deal with a remote database server, but they’re missing out in a bunch of other cases. Here’s a partial list of the ways SQLite has helped me or a team I was on succeed (or saved my butt).
-
That time when I was wrestling with massive Excel or Google Docs sheets with 1,000s of rows I was supposed to modify in place under a deadline (using some crazy pivot tables or something, which I know nothing about). Instead I exported the sheets to csv, imported them to an SQLite database on the command line, did a bunch of crazy manipulation on the data with SQL (including, in more recent releases, window functions!), and then exported back to csv. Hugely easier if you know SQL.
-
That time when we needed to store fine-grained samples of running queries from an Oracle database that was having problems. We shoved billions of rows into an SQLite db and used it to drive an analysis and cure the suffering Oracle db.
-
That time when I needed to do a bunch of analytical number-crunching in a CLI program. I sucked in the data, created an in-memory SQLite db, populated the tables, ran some queries against them, and spit out the answers, at which point the database vanished, having done all its work modestly behind the scenes. The CLI app was blazingly fast to run—the database never even touched the disk–and to write, because I got to write 7 or 8 moderately complex SQL statements instead of a couple pages of nested dictionaries and lists to do the grouping and filtering and aggregation that SQL is literally built to do.
-
That time when we needed to co-ordinate a very long-running set of jobs in a way that let us pause execution, turn logging on and off without restarting, adjust parallelism on the fly, and so on. My partner (still the best software engineer I’ve ever seen) hacked up a governing program that ran in a loop, each time consulting an SQLite database for its task list and configuration. We could run quick SQL statements to check progress and look at problematic jobs, adjust parallelism on the fly, and so on. It was pretty great.
SQLite is an extremely capable relational database, and its library-backed / serverless nature makes it suitable for things you’d normally never use a database for—application formats, calculation shortcuts, and so on. If you haven’t used it, give it a look. If you have used it, be on the lookout for other places to pull it out. You won’t be sorry.