SQLite should have (Rust-style) editions - Mort's Ramblings

home

projects

blurbs

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.<br>I use it as a database for plenty of embedded projects,<br>and I don't think it's an exaggeration to call it the industry standard<br>for local data storage.<br>Some server software even uses it; for example,<br>lobste.rs is now running on SQLite.

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

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

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

You read that right.<br>Foreign key constraints are arguably the primary tool we have to ensure<br>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 (<br>id INTEGER PRIMARY KEY,<br>display_name TEXT<br>);

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

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

The only RDBMS I'm aware of which doesn't enforce this by default is SQLite.

This is made even worse by SQLite's tendency to re-use ROWID.<br>You see, in this example, those INTEGER PRIMARY KEY rows become aliases<br>for the table's ROWID, which is a unique integer ID assigned to every row<br>of a table in SQLite.<br>The algorithm for assigning ROWID is a bit complicated<br>(more details in the SQLite documentation),<br>but it results in ID re-use in some cases.<br>This means that a dangling reference easily results in a reference to<br>the wrong column, which is even worse than a dangling reference because<br>everything will seem fine.<br>You don't even get an error during lookup.

Just look at this hypothetical sequence of operations<br>in our toy database schema:

-- Bob creates a user account<br>INSERT INTO users (display_name) VALUES ('Bob');<br>SELECT * FROM users;<br>-- id | display_name<br>-- 1 | Bob

-- Bob posts an introduction post<br>INSERT INTO posts (user_id, content) VALUES (1, 'Hello, I am Bob');<br>SELECT u.display_name, p.content FROM users as u, posts as p WHERE u.id = p.user_id;<br>-- display_name | content<br>-- Bob | Hello, I am Bob

-- Bob deletes his account,<br>-- but we forgot to delete the posts.<br>-- SQLite doesn't produce an error because it ignores our foreign key.<br>DELETE FROM users WHERE id = 1;

-- Alice creates an account.<br>-- Alice gets the same ID that Bob had due to the ROWID algorithm.<br>INSERT INTO users (display_name) VALUES ('Alice');<br>SELECT * FROM users;<br>-- id | display_name<br>-- 1 | Alice

-- Alice has now inherited Bob's old post!<br>SELECT u.display_name, p.content FROM users as u, posts as p WHERE u.id = p.user_id;<br>-- display_name | content<br>-- Alice | Hello, I am Bob

The fix is to enable foreign_keys with a pragma:

PRAGMA foreign_keys = ON;

If we had done this in the beginning,<br>the buggy DELETE would have produced an error:

DELETE FROM users WHERE id = 1;<br>-- Runtime error: FOREIGN KEY constraint failed (19)

Bad default #2: Columns can store the wrong data type

SQLite has a simple type system: a value can be NULL, an INTEGER,<br>a REAL (aka a double precision float), TEXT, or a BLOB (aka binary data).<br>Consequently, a column can be defined to hold values of any of those types.

However, a column defined as an INTEGER column isn't restricted to only integers;<br>instead, SQLite considers it to "use INTEGER affinity".<br>What this means is essentially:

If you try to insert a TEXT value,<br>and it is a valid string representation of an integer,<br>it is converted to an integer and stored as such.

If you try to insert a TEXT value,<br>and it is a valid string representation of a real number,<br>it is converted to a real (aka double precision float) and stored as such.

Otherwise, the value is stored as-is.

Other affinities have different but simpler rules:

Columns with BLOB affinity store values as-is.

Columns with TEXT affinity store BLOB, TEXT and NULL values as-is,<br>but convert numeric values to TEXT.

Columns with REAL affinity work like columns with INTEGER affinity<br>except that integer values are converted to REAL.

Here's how this looks in practice:

CREATE TABLE music (<br>id INTEGER PRIMARY KEY,<br>name TEXT,<br>duration_sec INTEGER<br>);

INSERT INTO music (name, duration_sec) VALUES ('Lost In Hollywood', 321);<br>INSERT INTO music (name, duration_sec) VALUES ('Comfortably...