SQLite: a simple database for smol projects

2024-12-17 12:30

Working on small (or smol) projects often calls for something light and reliable. That's where SQLite comes in. Instead of juggling a heavy client-server setup, it all fits neatly into a single file. There's no extra daemon to run, no complicated configuration to maintain, just a clean, tidy solution that travels easily.

For a single-user environment, this approach feels natural. There's less overhead, and the trade-off in features never seems to bite too hard. Most personal applications won't need grand scaling; they benefit more from quick queries and instant availability. It's reassuring to know that the data sits right there, ready to be read and updated as needed.

Simplicity goes hand in hand with portability. The database file can come along wherever your code lives, whether on a familiar desktop setup or a small remote machine. This portability doesn't just help with development; it also makes backups and versioning straightforward. Keeping track of what matters becomes far less of a hassle.

In short, SQLite's modest scope and single-file philosophy match well with the calm efficiency of a mono-user context. It keeps your data management anchored and accessible without fuss, enabling you to focus on what truly matters: building something that works.

I would like to share some tips about SQLite

Coordinate writes carefully

SQLite manages multiple readers well, but it serializes writes. Ensuring one writer at a time keeps everything running smoothly.

Transactions for bulk operations

Grouping large insert or update batches into a single transaction reduces overhead and speeds up mass data changes.

Index selectively

Indexing the right columns boosts query speed. Still, over-indexing can slow inserts and waste space, so use them with caution.

Use EXPLAIN for query insight

Running EXPLAIN or EXPLAIN QUERY PLAN shows how SQLite processes queries, helping you refine indexing and structure.

Adjust via PRAGMA

Statements like PRAGMA foreign_keys = ON; ensure consistent data relationships. Other pragmas let you tweak journaling modes or cache size to align with your needs.

Keep it clean with VACUUM

After large data changes, VACUUM helps reclaim space and maintain snappy reads.

Rely on built-In functions

Native date/time operations, core functions, json utilities and math functions can reduce application-level complexity.

Straightforward backups

Since the database lives in a single file, copying it directly makes backups and transfers simple.

Readable diffs with git

Since a SQLite database is binary, a standard git diff won't provide much insight. However, you can configure git to run sqlite3 .dump whenever it needs to show changes. This way, git will produce a text-based representation of the schema and data differences rather than an unreadable binary diff.

How to set it up:

In your .gitattributes file, associate .sqlite files with a custom diff driver:

*.sqlite diff=sqlite

In .gitconfig or .git/config, specify that for the sqlite diff driver, git should run sqlite3 .dump on each version of the database it's comparing:

[diff "sqlite"]
    textconv = "sqlite3 $1 .dump"

After these steps, git diff on .sqlite files will show differences in terms of actual SQL statements rather than binary gibberish. This makes browsing the history of your database's structure and even contents, if pushed, much easier.

RTFM

SQLite documentation seems very light, but it includes all you need to know about this magic tool.

Have you some other tips about SQLite ? Share them on the Fediverse