SQLite should have (Rust-style) editions
127 points by mort
127 points by mort
I wrote this post after yesterday's news that lobste.rs is now running on SQLite: https://lobste.rs/s/ko1ji1/lobste_rs_is_now_running_on_sqlite. That conversation made me think of all the minor annoyances I have with SQLite's defaults, so I wrote a blog post about it.
I agree and came to the same conclusion reading that conversation.
It seems like it might be worth sharing your post here: https://sqlite.org/forum/forum
if you have the energy.
That would be interesting, but it feels like it would be a faux pas to jump in there with a 0 day old account to do some self promotion so I'm probably not gonna do it. I wouldn't mind if someone else posted it there though.
Sorry. I meant the content of the post. I agree with the optics of your first post being a link to your own blog.
It'd be nice for the authors to get to see and consider the idea, and there's no such thing as a pull/merge request over there.
I may have the energy to create an account and share there later this week, if nobody beats me to it.
If we could write something like:
CREATE TYPE KEY_VALUE_SET = TEXT;
In SQL, CREATE TYPE is for creating composites, and Postgres extends it with range, enum, array, and the fairly complex base types.
What you are looking for (and could / should request) is CREATE DOMAIN: these are essentially newtypes (a type created from a base concrete type), and can furthermore carry defaults and constraints, which would really cut down on the verbosity of some schema definitions, especially when the same CHECK constraints get used in multiple tables. e.g.
create domain mything as text;
or
create domain mything as integer not null check (VALUE < 5);
Oh awesome, I should've checked if there's an existing way to do type aliases. I'll add a note to the post.
I hate the fact that normal people can’t access the actual SQL standard so much. I’m so used to being able to read specs that it really grates when I have to interact with ISO nonsense.
I’ve assumed for some years that CREATE TYPE was what you needed, because (a) it sounds right, (b) it’s what I found when I searched once, and (c) SQL Server does use CREATE TYPE type_name FROM base_type to create an alias, and doesn’t mention CREATE DOMAIN at all.
Sigh.
How much does the SQL standard matter for users?
Depending on my application I either use PostgreSQL or SQLite so I only need its documentation, and I don't care if a particular feature is standard or a vendor extension. So I don't think I would use it much even if it was public.
I suspect the lack of access to the standard has contributed to the wild implementation inconsistency.
I think it's worth mentioning a rather nasty gotcha, busy_timeout is not sufficient to prevent immediate SQLITE_BUSY errors.
The case where they can still occur is when starting a transaction with a read-only query, and then trying to upgrade the lock the transaction holds by running a write query. This fails immediately regardless of what busy_timeout is set to, if there's another writer.
The solution is to use begin immediate.
SELECT ... FOR UPDATE should also make it a write transaction, right?
Yes, by default (when using plain "begin") the transaction is created as deferred. The first statement determines whether a read or rw lock is acquired. If the first statement is an update or "select for update" you'll have a rw lock straight away
I thought this was a great suggestion when I saw it in the previous thread, and I’m happy that you turned it into a full post. One thing that occurs to me is that some of the things bundled into your proposed edition are (in SQLite’s model) properties of the connection, while others are properties of the file, or of the individual tables.
Off the top of my head, the PRAGMA journal_mode = WAL setting is “sticky” in the database file, but e.g. PRAGMA foreign_keys = ON is a property of the connection, and you have to set it every single time (why?!). And STRICT is a property of an individual table; you can mix and match strict and non-strict tables within a database. So you would need to define whether this super pragma is a property of the database file or of the connection. (I wonder if there’s a built-in mechanism for such files to flag to older versions of the library, “hey, there’s stuff in here that you don’t know about and if you try to edit it you might screw it up!”)
i went in disagreeing with that headline but came out agreeing with the post. the best kind of lobsters post. :)
to me, sqlite is like lua in that their development processes are ... a tiny bit eccentric, but they work unbelievably, almost suspiciously, well and the idea of tinkering with that in any way makes my eyes widen. i think the pragma approach is a really good idea. xmpp could take a page from that book with its xep mess.
Thanks for writing this blog post. Editions seems like a good compromise between the authors wanting to maintain backwards compatibility by default and less confusing defaults for those that want the latest recommendations.
I recently fell in love with RocksDB. It is conceptually similar to SQLite (an embedded database library) but without SQL, which I think is a great advantage. In my opinion, having to express all operations in SQL only gets in the way of working with the database.
RocksDB advertises itself as a Key-Value Database, and SQLite is a Relational Database. In my mind those are 2 orthogonal concepts built for different purposes.
How would you approach converting something that uses SQL (like lobste.rs) to RocksDB (or any KV DB)? It's hard for me to imagine it.
A lot of applications don't use relations or joins very much in their model (e.g., cache, job queue, document search). They can use a KV database directly, and it's more straightforward than using relational.
If you only need a "little bit" of relational, you can implement that on top of KV with mechanical transformations. Basically, you concatenate the primary key values as a key, and store the serialized row as its value. You can then make up your own indexes by using more keys whose value is the primary-key key, and you can do joins yourself. (And there are other ways to structure this, of course.)
But if your application really is deeply relational, that's probably a bad match for a KV DB.
Note that it's not uncommon to use a KV store as an invisible implementation detail of a SQL-compatible database (e.g., CockroachDB), or a document store (e.g., DocDB), but that's not really the same question.
I don't think that SQLite is relational per se. It can become relational if you enable and use foreign keys, which is essentially just a convenience feature to, for example, automatically clean up "orphaned" rows or the like. You could accomplish the same thing by simply implementing that in your application.
Conceptually, I don't think that they are orthogonal. I think they are parallel. I think that what you mean by SQLite being "relational" is its "build-up" of higher level features like SQL, foreign keys, joins, and so on, all of which can be implemented in RocksDB.
Tables and views are relations in relational database. You might call references between tables relations, but it's not really why they're called relational.
I still haven't found a satisfactory fix for the strict datetime problem; that is: if you have STRICT turned off on a table, it allows you to declare DATETIME columns which while they are stored as numeric values in the DB, can be turned into datetimes by the driver. But once you do turn your table STRICT (which there's very good reason to do!) you can't do this any more. (The same problem exists for booleans too.)
Support for user-defined domains (types) as advocated by the post would do it. You could define a DATETIME domain (over an integer, float, or text depending on your wants and needs) then assuming SQLite’s API would reflect the domain you could bind on that.
But if course they would need to be supported first, and that means the interaction between domains and non-strict tables would have to be defined (some sort of edition or strict mode would be useful there, as you could have a strict mode with both strict tables and domains, and a non-strict mode with loose tables, opt-in strict tables, and no domains, although that seems very unlikely to happen given the sqlite team doesn’t generally seem enthused by strictness)
The fix is to set busy_timeout with a pragma
I don't agree here. The performance of SQLite is great, as long as you have a single writer thread and batch unrelated "logical" transactions as described here: https://andersmurphy.com/2025/12/02/100000-tps-over-a-billion-rows-the-unreasonable-effectiveness-of-sqlite.html
I agree that for typical workloads, you should have a single writer, and not build an architecture where you typically have multiple writers.
However, it's pretty common to occasionally interact with the database manually to clean up some things, or via a script to perform the occasional administrative task that you haven't found the need to write a front-end for. It kinda sucks that this has the chance to randomly crash your server or application software if you're unlucky with the timing.