SQLite should have (Rust-style) editions

Date: 2026-07-15

Git: https://gitlab.com/mort96/blog/blob/published/content/00000-home/00017-sqlite-editions.md

SQLite is an amazing database engine. I use it as a database for plenty of embedded projects, and I don't think it's an exaggeration to call it the industry standard for local data storage. Some server software even uses it; for example, lobste.rs is now running on SQLite.

Unlike traditional RDBMSes (Relational DataBase Management Systems), SQLite is not a separate process; it's an RDBMS as a library, meaning your software remains self contained. Unlike traditional file formats, you don't need to write custom serializers and parsers. In some ways, it's the best of both worlds.

There's just one huge problem though. Its defaults are all wrong.

Bad default #1: Foreign key constraints are ignored by default

You read that right. Foreign key constraints are arguably the primary tool we have to ensure that a database remains consistent and don't have dangling references.

As a quick primer, this is how an SQL foreign key constraint looks:

CREATE TABLE users ( id INTEGER PRIMARY KEY, display_name TEXT ); CREATE TABLE posts ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, content TEXT NOT NULL, FOREIGN KEY(user_id) REFERENCES users(id) );

The typical behavior for all other RDBMSes would be that the user_id column of a post must always reference the ID of a valid user. You can't create a new post without providing a valid user ID, you can't delete a user without also deleting its posts, lest you get a foreign key constraint violation error.

... continue reading