SQLite Koans
SQLite Koans
SQLite is a very useful trustworthy database for a number of tasks.
Full disclosure I grok SQL far better than Excel - to that end I’m frequently dumping CSVs into SQLite for further analysis. 😳
Over the course of building many tools using SQLite, I frequently find myself flummoxed when my ingestion code either violates data types and foreign keys, or worse yet when I want to tail the database to monitor its progress I’m met with a rude database locked error 🙁
A quick RTFM provides enlightenment! (keeping with our koan theme here ☯️). As of sqlite3 does NOT by default:
- ❌ enforce foreign keys
- ❌ defaults to lock-on-write (to the exclusion of readers)
- ❌ enforce type safety
Let’s treat each PRAGMA as a koan that solves a facet of our problems on the way to SQLitenlightenment!
PRAGMA foreign_keys = ON;
docs: PRAGMA foreign_keys
Foreign keys are oft used to enforce data integrity. Without foreign key enforcement, relying on tables as enums + no foreign keys can violate your expected enums. Surprising in the worst way!
PRAGMA journal_mode = 'WAL';
docs: PRAGMA journal_mode
WAL or Write Ahead Logging is ideal for replicating SQLite to durable storage such as S3 using Litestream. When running locally it allows tailing of the database by other reader threads. There are experimental efforts to make SQLite multi-writer but for now SQLite remains single-writer by default. Postgres users may be familiar with using WAL to replicate from the primary node to read replica instances. A similar concept applies in SQLite.
You can read more about the SQLite implementation of WAL here
PRAGMA synchronous = NORMAL;
docs: PRAGMA synchronous
When WAL is enabled, per the sqlite docs, we want to enable synchronous = NORMAL as it defaults to OFF otherwise.
PRAGMA encoding = 'UTF-8';
docs: PRAGMA encoding
By default many databases adopt surprising encodings such as latin1. These days UTF-8 is a good sane default that supports emoji, CJK.
I would argue never assume a database’s default encoding and explicitly define it - your future self will thank you.
PRAGMA busy_timeout=30000;
docs: PRAGMA busy_timeout
Given SQLite allows one writer at a time, by default SQLite immediately throws an error if another thread attempts to write at the same time as another thread. This is a common situation when using multiple Goroutines to ingest data into your db. To resolve this contention its useful to add a busy timeout (in milliseconds) where SQLite will optimistically try to write to the database before erroring. App side retries could also be added as well but this si a cheap and simple way to add resilience to multi-threaded apps.
Why not PRAGMA cache_size?
Every database is different! The cache size is very dependant on how your reads and writes impact SQLite, as well as the hardware you’re running your code. Presumably the SQLite DB itself resides on the same host as your app code. Thus I leave the tuning of this property to the reader.
Type Safety
You might have noticed that I called out SQLite for not enforcing ❌ type safety by default. This is configured when we create tables like this:
CREATE TABLE test(id TEXT) STRICT;
If we have questions about the data integrity of our database we can run:
PRAGMA integrity_check;
to validate indexes, constraints, as well as datatypes.
Note per the docs, foreign keys are not vetted and that needs to be run via PRAGMA foreign_key_check;
Sqlite + Golang
So you’ve setup a project in Go and want to use SQLite as your backend. Great choice!
If you are using SQLite with Golang I would hazard mattn/go-sqlite3 is the defacto standard.
You might be wondering how do we validate that SQLite is configured as we expect?
tl;dr you can find the repo with the setup and tests here: mikeblum/sqlite-koans
run the tests with make test