Back to the notes

Someone built a Go library to stop you shooting yourself in the foot with SQLite

A new Go wrapper enforces WAL mode, busy timeouts, and foreign keys by default. Turns out most SQLite footguns are configuration problems, not the database itself.

Automatic fan controller for server racks (38933511880)
Dilshan Jayakody from Maharagama, Sri Lanka / Wikimedia Commons. Resized and converted to WebP. CC BY-SA 2.0

Someone on Hacker News released litesql, a Go library that wraps the standard SQLite driver with opinionated defaults. The pitch is simple: most people configure SQLite wrong, then blame the database when things break under load. The library forces WAL mode instead of the default rollback journal. WAL lets readers and writers work concurrently without blocking each other. The default journal mode serialises everything, which is fine for single-threaded scripts but terrible for a web server handling concurrent requests. It also sets a 5-second busy timeout. Without this, SQLite throws a “database is locked” error the moment two connections try to write at the same time. With the timeout, the second writer waits a few seconds before giving up. Most write contention resolves in milliseconds, so a short timeout turns a crash into a success. Foreign key constraints are off by default in SQLite. The library turns them on. This is a reasonable default for anyone who expects their database to enforce referential integrity, which is most people. The interesting bit is that these are not exotic settings. They are all standard SQLite pragmas. The problem is that nobody reads the documentation, copies the database/sql quickstart, and ships it. Then production hits and the “database is locked” errors start rolling in. I have seen this pattern in other tools. Elasticsearch defaults to a 10,000 result window, which is fine until you paginate past it and your logs fill with exceptions. Postgres defaults work_mem to 4MB, which is absurdly low for analytical queries. The footgun is not the tool, it is the gap between the conservative default and the production workload. The library is 200 lines of Go. It does not add features. It just changes the defaults to what most web applications actually need. That is a smaller surface area than learning all the SQLite pragmas yourself.


Source: Show HN: I created a library for correctly using SQLite in Go programs

Back to all notes

Behind the notes

Vikrant
Sharma.

Artificial Intelligence Engineer intern at Voxon Photonics in Adelaide. Studying a Master of Information and Communications Technology at UniSC, with a focus on data, machine learning and security.

Meet the person behind the work