A Love Letter to SQLite
My project for the last year+ at $WORK has a variety of little
services. I hesitate to call them microservices as they all
interact with a central system instead of with each other (more
of a "star" topology than a mesh). It turns out that SQLite has been
a fantastic fit for this kind of thing. I've been "using it in anger"
for over a year now and I've found it to deserve its reputation
as a battle-tested, reliable piece of software. You might be
surprised at the level of traffic SQLite can handle even with
unoptimized client config - I know I was.
I appreciate the simplicity of a file-based database that still
gives me well-understood and supported SQL semantics. Removing
a network service that has to be monitored is removing a point
of failure in the system. However, SQLite brings much more
than that to the table.
JSON
SQLite's JSON Extension [1] is incredibly useful. I've found
that certain things make more sense as a blob of json than
a bunch of intermediary tables. The JSON extension covers all
the basics here, allowing path queries and the like.
Backups
As you might imagine, backups of a single-file database are
a snap. I've used the "naive" method of acquiring a lock
and cp'ing the file somewhere, but SQLite also has a
Backup API [2] to make it foolproof.
Full-Text Search
Most people seem to reach for something like Elasticsearch
as soon as they need some full text searching in an app.
Many of the projects I've worked on just needed full-text
searching for specific parts of an application, so another
server wasn't really necessary. As a side-note, if you do
find you really need Elasticsearch, I beg you to pay
Amazon or someone else to administer it for you. It's
non-trivial to maintain a production deployment of it.
OK with that said, SQLite's FTS5 Module [3] provides
a very capable full-text search right out of the box.
As expected, it has the usual WHERE clause functions
for finding rows that match a specific query, but it
also has some nice auxiliary functions like highlight,
which returns a match where the matching word or phrase
is bracketed by characters you choose. This is great for formatting.
I wouldn't recommend using this with HTML tags as the
documentation says, as this could easily turn into an XSS
vector. Instead, bracket it with pipes or something and
parse it yourself to add trusted HTML, escaping anything that
came from the DB.
The bm25 auxiliary function returns a value that ranks the
"best" match - this is very useful for ranking results, which
you'll almost certainly want to do.
I hope you'll consider using SQLite for your next (even medium-sized)
project. Happy hacking!
[1] https://sqlite.org/json1.html
[2] https://sqlite.org/backup.html
[3] https://www.sqlite.org/fts5.html
