Article URL: https://mort.coffee/home/sqlite-editions/ Comments URL: https://news.ycombinator.com/item?id=48928135 Points: 122 # Comments: 49

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. 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. 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. This is made even worse by SQLite's tendency to re-use ROWID. You see, in this example, those INTEGER PRIMARY KEY rows become aliases for the table's ROWID, which is a unique integer ID assigned to every row of a table in SQLite. The algorithm for assigning ROWID is a bit complicated (more details in the SQLite documentation), but it results in ID re-use in some cases. This means that a dangling reference easily results in a reference to the wrong column, which is even worse than a dangling reference because everything will seem fine. You don't even get an error during lookup. Just look at this hypothetical sequence of operations in our toy database schema: If we had done this in the beginning, the buggy DELETE would have produced an error: