SQLite is your Swiss Army Knife. Use it.

The hidden tool you always wish you had... has always been available for you

Photo by Maksym Kaharlytskyi on Unsplash

Throughout my work with tech teams I have observed engineers touch upon SQLite, glance at it, pass by it… and ignore it because they had a <RDMBS of choice> instance already.

Yet, it’s those same individuals that are trying really hard to recreate custom data structure in-memory or using their favorite ORM to cut corners on data translations, processing or building projections.

It doesn’t always have to be like that…

Here’s a few creative ways you can use SQLite tomorrow to your immediate advantage.

But first, the obvious question.

What the heck is SQLite?

In terms of complexity SQLite is the simplest database you can imagine. It can moonlight as a CSV, excel, json, SQL or even file system replacement. All in a single library with a stupid-simple API.

It comes in most operation systems—desktop, mobile, embedded alike.

Simply put it is a just-in-time SQL storage that you can spin up in a single file on disk or in memory.

The commands can be as simple as

sqlite3 :memory:
or
sqlite3 db.db

In nodejs with TypeScript it looks like this:

import { Database } from 'sqlite3';

// Open a SQLite database, stored in the file db.sqlite
const db = new Database('db.sqlite');

Use Cases

Most websites on the web do not require a multi-threaded, sharded, partition-tolerant atomic, ACID-compatible database with type hinting.

I’ve seen 99% of tech teams I speak to skip over the simplest iteration of any feature: Build it with SQLite first.

From the official sqlite website, here are some high-level use cases:

• Embedded devices and the internet of things

• Application file format

• Websites with less than 500k reads/day where the db is colocated with the server with low concurrent writers

• Data analysis

• Cache for enterprise data

• Data transfer format - sending csv’s or jsons over the wire for bulk imports? Send an sqlite db instead. You can convert it to any format!

• Replacement for ad hoc disk files - SQLite can be faster than the file system!

• Internal or temporary databases

• Stand-in for an enterprise database during demos or testing

• Education and Training

Still not convinced?

Let’s look at some examples

.mode json

Have you ever written a large JSON array to be send over an API or archive?

Insert into an sqlite db instead and read it with .mode json

.mode json
select * from books;

// Result
[{"title": "Foo", "Author": "Bob"},
{"title": "Bar", "Author": "Steve"}]

When multiple processes are reading while it is being written, the array format will always terminate correctly rather than relying on synchronisation to lock the final closing tags.

This makes these kinds of databases very stream-friendly as long as you have only a single writer as you would have in a job runner or pub/sub setup.

Open it in :memory: instead of a file

Need a quick in-memory dataset for a fixture?

Do you need to re-order tabular data for a quick map/reduce in a projection?

Chances are sqlite could save you needless imports of data structure and data processing tools.

Do it in SQL instead by running a select, group by, etc.

Parse CSVs dirt-cheap and quickly

sqlite> .mode csv
sqlite> .import convertcsv.csv People
sqlite> select * from People;
1,Roger,Hawkins,59,"Bobej Point",Jicsorvu,PA,97161,$1306.83,RED,07/01/1988
2,Jessie,Robertson,44,"Ebipug Circle",Nezdueza,AZ,89126,$7944.22,RED,10/21/1968
...

// It will create a schema for us from the header file
sqlite> .schema
CREATE TABLE IF NOT EXISTS "People"(
  "seq" TEXT,
  "name/first" TEXT,
  "name/last" TEXT,
  "age" TEXT,
  "street" TEXT,
  "city" TEXT,
  "state" TEXT,
  "zip" TEXT,
  "dollar" TEXT,
  "pick" TEXT,
  "date" TEXT
);

And combine it with the previous tip…

sqlite> .mode json
sqlite> select * from People;
[{"seq":"1","name/first":"Roger","name/last":"Hawkins","age":"59","street":"Bobej Point","city":"Jicsorvu","state":"PA","zip":"97161","dollar":"$1306.83","pick":"RED","date":"07/01/1988"},
... the rest of the table in JSON format

You can also use sqlite’s sqldiff <db1> <db2> on two versions of the same database table to quickly create SQL statements that convert one into the other. This is PERFECT for testing fixtures, event sourcing corrections and synchronising jobs

Run it directly from the command line

Following on the example above, you can run a query directly from bash or zsh

$ sqlite3 db.db 'SELECT * FROM People;'

1|Roger|Hawkins|59|Bobej Point|Jicsorvu|PA|97161|$1306.83|RED|07/01/1988
2|Jessie|Robertson|44|Ebipug Circle|Nezdueza|AZ|89126|$7944.22|RED|10/21/1968
3|Christine|Gross|44|Lafsep River|Catechev|KS|35698|$1080.06|YELLOW|06/27/2010

Don't dismiss a tool for its simplicity.

Everyday Engineering Use Cases

• Setup unit testing fixtures

• Use it as a drop-in replacement for an SQL database (in-memory fake rather than using a mock)

• Buffer inputs and outputs to export/import. You can store in memory, write to disk on demand, changing formats between tsv, csv, json, markdown, etc. in the process

• Expand jsons quicly - import a json to an sqlite database and use its powerful json object manipulation tools to expand, zip or unwrap json objects and arrays while returning them back to the shell or another json or csv format

• Using excel spreadsheets, notion and ChatGipity for complex data queries? Perform the data transformation in SQLite. Maybe the queries too.

There are many applications that could run on SQLite which are using hosted databases needlessly. Is yours one of them?