Interesting idea - I like seeing a list of pet-peeves followed by a proposal for a straightforward way to have a set of 'alternative defaults' that remains backwards compatible. If you don't want to opt in, don't run the new PRAGMA edition = 2026.
Too often it's just a list of issues and a wish that everyone else will change.
In (mild) defense of SQLITE_BUSY - busy_timeout just tells sqlite to sleep and retry up to the timeout when it receives SQLITE_BUSY. It seems like a sensible default for a library to leave that up the calling code - which may have something else it could do while it waits. However, that logic often gets missed!
This isn't so much a list of pet peeves as it is the almost universal way people that work seriously with SQLite configure the database. It's reasonable to suggest that the alternative settings for each of these suggestions is probably the wrong default for 2026.
I’d say these are reasonable settings for most uses. Though do you know of surveys that back this up? I don’t mean to nit pick too much, I’d just like to see common uses and the data.
SQLite is used in a lot of unconventional settings (for SQL databases) where these settings don't make as much sense. But that's what makes the "edition" useful; it captures the use case we all mean when we're thinking of the "database" lego in an application stack.
While that's true, editions are more about leaving legacy decisions behind while keeping the backward compatibility promise.
Even if you're in one of those unconventional settings (say, a bare-metal microcontroller or something), you'd probably still start from edition 2026 and mutate your settings accordingly, rather than using the defaults that are 26 years old.
Yeah, that's important. Rust's 2015 edition is worse, not just different from what you'd write today with 2024 edition. There's a clear direction of travel.
You are probably correct, but I imagine the SQLite team's dedication to backwards compatibility has things the way they are so that existing systems can user later versions a swap without worrying about changing the SQL using it.
The entire point of "SQLite should have editions" is so that projects can opt into a set of modern defaults for 2026 and not get all of those backwards compatible decisions from 20 years ago.
GPP's wording suggests that the defaults simply be changed to what is being discussed as more generally sensible in current times, rather than being opt-in.
Given how many projects are potentially out there effectively relying on the current settings, and SQLite's general attitude to backwards compatibility, that would likely not be considered a good idea. Opting in with an edition flag for new (or updating) projects does seem like a good solution to this to serve all of old, active, and new projects, but it would increase potential bug surface area and therefor testing requirements, and the existing setting do allow all that to be opted in/out to/from already (and it is only four settings we are talking about here).
That FKs being enforced is set per-connection rather than at the database level is something that surprised me a lot when I found out. A way of setting that at DB creation (or via ALTER DATABASE after) seems like quite an omission because if you have multiple potential routes that can update the same DB any one of them could cause serious the others will encounter.
> It's reasonable to suggest that the alternative settings for each of these suggestions is probably the wrong default for 2026.
That's the key concept here. When tightening up the defaults, an "edition" mechanism is a good solution.
Now we need this for C/C++, which have much legacy stuff which ought to go away for new code. This is more feasible than it used to be, because "Convert this Edition 4 code to Edition 5" is something LLMs can do now.
I'd never seen all the rules for SQLite soft typing written out before. Those are more complicated than strong typing.
P1881 Epochs proposed to WG21 (the C++ standards committee) in 2019 by Vittorio Romeo
The committee found plenty of problems with this, and made it clear that if Vittorio did all the hard work to resolve those problems they would find more, P1881 was abandoned.
So, you're not the first person to notice that this is a good idea, P1881 was written after Rust's 2018 Edition, but before 2021 Edition with its even more significant improvements. I firmly believe Rust's Editions unlock not only technical possibilities (though it does certainly do that) it unlocks an appetite from users which is good for your ecosystem.
The key concept of an "edition" is that you can deprecate stuff. "gets()" should have been pulled from the standard library around 1990 or so, along with "strcat" and its frenemies.
Yep. The whole locking database thing is this persistent myth about SQLite. All databases lock on write, it’s a question of the granularity of the lock. Multiple writers simply take turns.
yes but no... most database allow for at least as many parallel and concurrent writes as there are tables at a minimum.
The "lock on write" problem is that in MySQL i could run a OLAP pipeline for a few hours and have a fully functioning database with degraded perfomance, on SQLite the same pipeline would lock the database for the full hour. (there are surely ways to solve this (eg using the main db as read-only and a secondary db for writes or splitting the writes in incremental transaction), but it is not a "myth".
The “myth” is you can’t use it for a website because if you have multiple requests that need to write they can’t do it concurrently. (They just take turns of course for the milliseconds of write time.)
If you run a transaction with writes for an hour on any database, the data you update will literally be locked. So your example only works if results are independent of the data other programs want to use.
Of course more granularity of locking is better and enables more designs that would not otherwise work. But somewhere you run into the same problem of writers taking turns.
After read, it hit me that because sqlite is a DB, "editions" as-is not work.
Because it not tied to the data but to the code.
Instead, what I think should be is that the PRAGMAs become "data" that is always checked in full with "if manually set" and then on next "open" THEY GET APPLIED.
That is.
(and in the command line when open interactively they show up).
RE: SQLITE_BUSY: I would replace "often" with "nearly always." On top of that, it's often not fixed even when pointed out. "This software only has one writer, so we don't need to handle SQLITE_BUSY" translates to me sending SIGSTOP to a process any time I want to run some queries against its database.
busy_timeout is often sidestepped (ignored) when a transaction attempts to upgrade from a read to a write producing SQLITE_BUSY.
By default, SQLite transactions start in DEFERRED mode, acting as read transactions until an actual write operation occurs.
If another connection begins writing to the database while your transaction is in this read state, an immediate SQLITE_BUSY error is triggered regardless of what you set busy_timeout to
Exactly. In cases where I expect long-running parallel connections from separate processes to the same sqlite file, I make sure that all read transactions do `BEGIN DEFERRED` so `COMMIT` releases the read locks, and all write transactions do `BEGIN IMMEDIATE` so that `SQLITE_BUSY` timeout is not side-stepped.
There was one case where all transactions were implemented using nested `SAVEPOINT bla` so `BEGIN IMMEDIATE` could not be used without more hassle, so this ended all “I know I'm going to write” transactions to instantly update a single-row table so that their lock would not begin as DEFERRED and eventually switch to `IMMEDIATE`; this way almost all `SQLITE_BUSY` side-steppings disappeared. (timeout was set to 30 seconds but all read/write transactions were instrumented to have less than 5 seconds duration).
lol, I briefly thought of such a technique, but in the end, found it simpler to just use a synchronization primitive at the application level to serialize db access on transactions where I knew I was going to write. it amounts to about the same honor code.
SQLite is slightly different from Rust in that it is a data container. It’s somewhat more common for people to move SQLite database files from one machine to another and then inspect using the command line tool. And it is often the case that the embedded SQLite version in your app is a newer version than whatever version /usr/bin/sqlite3 happens to be. Adding editions to your SQLite file will probably break this use case of using an older version to read a database written by a newer version because it does not know what has changed in a new edition.
Not a big deal though. Probably just need better ops to bundle the command-line utility that’s the same version as what’s used in your app.
For some of these pragmas you have the same issue with or without "editions", right? Busy timeout is per-connection. And then: if you're running in WAL mode, you, the user, have to know that, or risk messing up the database by copying just the .db file rather than vacuuming-into.
Editions make the problem worse by requiring the version not only to support the underlying pragmas but also to understand the edition mapping.
Example: PRAGMA foo=1 is introduced in 2027. PRAGMA edition=2030 implies this foo pragma. Now you unnecessarily lock out three years worth of releases.
There’s no need to store the literal edition in the DB file. Instead the edition could be a library construct that sets appropriate flags. So if you set edition 2026 you get WAL, etc.
It seems SQLite could be evolve to solve this by just bundling itself entirely in the data files? After all, the binary is less than 1MB, anywhere you’re putting a database surely has at least that much overhead, for most applications it’s less than a drop in the bucket.
I’d be interested to learn if there are any db implementations that take this approach, or reasons this wouldn’t work.
Well you'd have the problem that an sqlite database file created on a Linux AMD64 box could be copied to an AArch64 macOS machine to be read there. And quite a lot of (cross platform) software build their file formats on top of SQLite.
I think my silly and unserious naive response would be linear scaling bundled binaries with number of platforms doesn’t seem to be that materially different in terms of total size. But I see your point, didn’t consider the architectures
The issue then is that instead of just bundling a tiny 1MB sqlite library with your program, you have to bundle a huge WASM runtime. I looked at the size of wasm on my system just to get an idea and it's 46MB; that's huge.
For context, I have a game I'm working on where I build stand-alone builds with all dependencies bundled. The app bundle is 14MB right now. If I added sqlite, it would grow to 15MB. If I added sqlite-but-with-wasm, it'd more than quadruple in size to 60MB.
% curl -LO 'https://cosmo.zip/pub/cosmos/bin/sqlite3'
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 4845k 100 4845k 0 0 1384k 0 0:00:03 0:00:03 --:--:-- 1384k
% chmod +x sqlite3
% file sqlite3
sqlite3: DOS/MBR boot sector; partition 1 : ID=0x7f, active, start-CHS (0x0,0,1), end-CHS (0x3ff,255,63), startsector 0, 4294967295 sectors
% ./sqlite3
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>:
There is a way to do this with sqlite in readonly mode using the append vfs [0] which allows you to put your DB appended to the end of the executable, and then the executable can read it (like a zip file the header is then at the end of the db rather than the front). Or you could embed a normal db as a binary blob with the linker. Allowing writes is more tricky because most OSes don't typically allow executables to edit their own executable memory (on unix I believe executables always load as r/x or r/o depending on the region, maybe with some minor exceptions).
And a lot of users of sqlite statically link sqlite within their executable, they actually recommend that instead of dynamically linking.
editions could be self-describing as to certain semantic changes, and you could embed that with the file. older versions could safely ignore it and newer versions parse and run it. you could also force things like "editions must be declared early", "editions are one way only" etc to get some level of security in the adoption of the change
I think you can maintain full backward compatibility with all these changes. SQLite has a bunch of meta-data that you can use to see that no earlier editions have modified your database since you last wrote. You make the new editions follow these strict rules, but if an old edition modifies the DB in the interim, you fall back to the original rules, until it’s verified compliant again.
> SQLite is slightly different from Rust in that it is a data container.
I think this is the key.
From sqlite.org [1]:
> [Since 2004], the file format has been fully backwards compatible.
> By "backwards compatible" we mean that newer versions of SQLite can always read and write database files created by older versions of SQLite. It is often also the case that SQLite is "forwards compatible", that older versions of SQLite can read and write database files created by newer versions of SQLite. But there are sometimes forward compatibility breaks. Sometimes new features are added to the file format
---
Given editions (A) and (B), what does backwards compatibility look like? Must (B) be backwards compatible with (A)?
If yes -> editions are backwards compatible but not necessarily forwards compatible, which is the current status quo:
-------(A)----(B)--
If no -> editions are not backwards compatible, the edition space is bifurcated:
---+----(A)--------
\
\--(B)--------
Now you may have to worry about backwards compatibility with (A)..(Z). What happens when you import a file from edition (Y)?
The answer to the question "What happens when you import a file from edition (Y)" should, ideally, be exactly the same as the answer to the question "What happens when you import a file created with parameters foo, bar and baz set to values a, b and c". There's really nothing new here.
Some parameters are properties of the database file itself, such as (I believe) journal_mode. These parameters probably result in issues with earlier versions of sqlite; if you create a file with journal_mode WAL, you're not gonna be able to open it in a version of sqlite without WAL support. Same with strict tables; if you have a database which contains strict tables, I assume you're going to encounter issues if you try to open it in a version of sqlite too old to support strict tables. But anything new enough to support the individual database file features shouldn't have any trouble. This is true whether we're talking about an edition pragma or individual parameters set the old way.
Some parameters are properties of the connection, like the foreign_keys parameter. If you're trying to open a database file in a version of sqlite which doesn't support foreign key validation at all, just don't set the foreign_keys parameter.
The "use strict" thing is interesting. I often hear people say, well we can't fix absurd behavior in JS because backwards compatibility! Well, we already did, and we can do it again!
I became hopeful for a moment, then saw it's from 2015. Ouch! (Also, I love the name. I had a similar idea I called "use sane", but obviously that one wouldn't have gotten far...)
The strong typing thing is really interesting. After using JavaScript for a while, I developed PTSD around dynamic types. I became convinced that static typing was the only way to avoid hell.
Then I used Python for a while, and... experienced approximately none of my previous pain. I found that quite odd. Turns out what I was actually after was a sane type system, not a static one. In other words, strong types rather than weak ones.
I do think there are additional benefits to static typing, especially for larger projects and serious work. But I was surprised that most of the pain-delta was in this first jump:
Brilliant. I have a similar idea for a language I've been envisioning.
So, my approach to dynamic features, global mutable state, etc... There's this big list of features which have caused me tremendous pain over the years. I am increasingly weary.
Sometimes you want or need them, though. You don't want the same level of strictness for every project (e.g. throwaway scripts or game jams), nor at every stage of a project -- e.g. being forced to specify invariants is something I'd love to be able to enable, but I wouldn't want that on while I'm still figuring out the basic structure of things.
So for game jams I'd put the language into #JAMMODE (which would be short for a bunch of other flags). But the point here is that jam mode should be opt-in, rather than opt-out. You should have to go out of your way to enable the footguns. And a file with #JAMMODE should be a little smelly. You should think, OK I'm actually shipping this thing now, let's get it out of #JAMMODE. (And strict files can't talk to jam files, and we probably want different strictness levels beyond Debug and Release, etc... someone told me with the strictness "zones" that I'm reinventing half of Ada, hahah.)
Counterpoint: each new edition will add bloat to SQLite as future edition will need to keep each past edition pragma set for backward compatibility.
As SQLite is often used embedded, bloat matters.
So I suggest that "PRAGMA edition" to be only be a shortcut to a list of PRAGMA commands, that would be expanded at the library level: PRAGMA edition would never appear in the DB file. As such, the build of the library would just support a limited set of editions, with a removal policy in default builds. Maybe editions could even be defined at runtime (a system table?) as a way to load them dynamically if old editions are needed beyond builtin support (think about the state of SQLite in 2046).
The developer still needs to ensure they apply their set of pragmas, whether that’s a single edition pragma or a set of pragmas. And they still need to understand and carefully choose the pragmas/options they use (an edition really makes this a little harder by abstracting/hiding something that needs to be directly understood and visible).
And these proposed new default pragmas are more incremental improvements rather than complete solutions, more convenience than critical. E.g., while the default affinity is goofy, strict tables don’t come close to a comprehensive validation mechanism — so if you need strong validation you’re likely going to need to implement that at a higher level anyway. (Also, there’s a decent separation-of-concerns argument that you should handle it separately.)
Likewise the busy timeout. The pragma is convenient but you still need to handle busy timeouts. (The author’s problem, “I didn’t realize busy timeouts could happen so the app didn’t handle them correctly”, is not solved by the pragma.)
Suggestion: post this on https://sqlite.org/forum/forum - the SQLite team monitor that forum closely and I've had some really great answers from them to questions or suggestions in the past.
I haven't really ever participated in that forum before, but it's an interesting idea. I don't know if I'm going to do it, I might. Though I also wouldn't mind if someone else posted about it there. I might even make a forum account and participate in the discussion.
Found what appears to be a minor mistake you may want to fix: "This means that a dangling reference easily results in a reference to the wrong column" should probably be "... a reference to the wrong row". (In the paragraph about SQLite's tendency to re-use ROWIDs).
That's correct but SQLite was never designed to be a production database in the first place. It can be used as a production database but only if you know what you're doing, and presumably anyone who knows what they're doing knows about the AUTOINCREMENT keyword because it's one of the first things you learn about SQLite.
I disagree, SQLite is a production embedded database, the extensive test¹ suite is a testament to that. It's just has different default and behavior than a database designed for being served to multiples simultaneous writers.
> The normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDs as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID. If you ever delete rows or if you ever create a row with the maximum possible ROWID, then ROWIDs from previously deleted rows might be reused when creating new rows and newly created ROWIDs might not be in strictly ascending order.
I don’t think people are criticizing the MAXINT thing. The problem is that IDs are already reused when you create 10 rows, delete 5 and then make a new one. Normal DB engines just keep counting afaik so the new ID will still be one that has never been used before.
Oh I really like this! The one counterargument that comes to mind is when I think about the likes of C++, where there are many editions and they can be confusing to keep track of.
I'm not sure if you'd want to set one edition in stone every year. Perhaps every 3 years? Or 5 years? Especially for a long-term project like SQLite, that sounds perfectly acceptable!
> I don't think I need to explain why it's a bad idea for a database to be so careless about data validation.
Well, loose typing can be extremely useful, and having a type of "ANY" would not replace it.
I have built recently an accounting reconciliation system to find discrepancies in data coming from a large variety of sources: some from proper database engines (MySQL MariaDB), but most from proprietary systems that export to CSV. It's amazing how corrupt data can become: dates that are invalid, numbers that aren't numbers, strings strings strings everywhere.
Being able to store the data into tables that have types, but can accept anything, is simply great.
"Loose typing" enforced in a strict typing system can be useful in certain scenarios, but it is regrettable that it instead replaced the strict typing discipline for some time in software. Strict typing should be the default, because it is the most accurate description of data in the vast majority of cases.
Are you saying that you have an application where you want the loose-typing-with-integer-affinity semantics for a column (or some other particular affinity)? It would be entirely reasonable to have a specific type for each loose-with-affinity variant. But I don’t think those should be the default.
You're using CSV, what did you expect? CSV was never meant for data exchange between two systems that do not know of each other's existence. Basically every CSV file is its own dialect.
DuckDB is a OLAP database. Its optimized for batch analytics, not for individual transactions. Its great for data science work, but I wouldn't use it for a production system.
> I once had to clean up a project where some code had accidentally been writing the strings '1' and '0' to a column which was intended to store booleans (1 and 0). That was not a fun debugging story.
So this was a write to a column that did not have INTEGER affinity. If it was intended to be used as a boolean, then it should have INTEGER affinity. I know because I've tried hard to enter integer- and float-like strings as strings in INTEGER affinity columns, and I haven't managed to; I could only insert them as BLOBs, or prefix the string with say '\' and check/remove at the application level. (That was for an ontology-like database, where table EAttribute.eatvalue could have any type.)
It might be worth bringing this up on the forum [1]. The developers are quite active there, and it's possible they've never considered this option, or they have considered it and have reasons to not go for it. The original design followed Postel's Law (see my comment from the other say [2]), it would (theoretically) be nice if that mess could be avoided by specifying an edition.
Today I noticed I could do `pragma foreign_key = ON`, and despite the pragma being wrong (it should be foreign_keys, plural), it reported nothing. In fact, it reports nothing with the correct pragma either. So check your pragmas!
I scarcely go a week without encountering issues caused by their huge CMake 4 backward compatibility break. I think CMake has one of the worst solutions out there.
Hasn't been an issue at all for me, but in any case that's a separate issue. They removed compatibility with CMake earlier than 3.5, which is 10 years old at this point. Support length is orthogonal to default behaviour evolution systems.
I never encounter things which need CMake <3.5 features. I constantly encounter things which declare their "minimum supported version" to be below 3.5.
Which makes a lot of sense for those projects! They can be built with CMake 3.2 because they don't need anything introduced in a newer version, so why not declare 3.2 to be the earliest supported version? It's not their fault that CMake interprets "minimum supported version" weirdly?
It's really weird to me how the SQLite author is clearly a very smart guy and talented developer and then his argument against type safety effectively just boils down to
> But I do not recall a single instance where the bugs might have been caught by a rigid type system.
Which is a shame. Of course the author writes more than this, but this is IMO largely the gist of the argument. At this point it's beginning to feel like this is mostly a sort of stubborn sunken cost fallacy, where they've been arguing this for so long they can't take the "hit" of agreeing to change the defaults.
Sadly, the ORM layer lags here: Drizzle has no way to declare STRICT tables. The request has been open since March 2023 (issue #202, now discussion #2435) and didn't make the v1.0 beta either. The only workaround is hand-appending STRICT to generated migration SQL, which doesn't work at all if you use `drizzle-kit push`.
But push is not for use in production? It's for development. You generate a single custom migration that sets strict, apply it, and then you can use push as you want (during development, not for deploying actual changes to your database)
I don't have a problem with setting a few relevant flags for any new SQLite database. It's a good encouragement to familiarize yourself with the settings.
I think the solution is for author to use PostgreSQL
Those choices were made for specific reasons that make sense in embedded environment and when backward compatibility is no.1 concern.
But I wouldn't mind feature-sets. Editions are too wide of a concept and tell you nothing at glance what a given code is doing, "enable 2026 set of features" tells me nothing on what is actually enabled.
What are the specific reasons for why it makes sense in embedded systems to not enforce foreign key constraints or to let me insert a blob into an integer column? Because I work with embedded systems and have never found those defaults to make sense.
My proposal does not harm backwards compatibility in the slightest.
> Bad default #3: SQLITE_BUSY errors with concurrent writers
This is a weird complaint to me. When I use SQLite, I always make sure there is a single writer thread. Any thread can submit a write request in a thread-safe queue. If you follow this pattern, you never need to worry about SQLITE_BUSY errors.
And your scripts for rare tasks you haven't gotten around to making a GUI for also go through that queue? And your manual interactions with the database to fix problems? Or do you just risk crashing the program when you do those things?
You know, 10 years ago one might have remarked ... changing these defaults means C programmers would have to correctly implement error handling and retries.
The reaction they're likely to have to that can probably be best described in megatons, like any other nuclear explosion ...
It sounds like you're also advocating for some form of permanent pragma that gets stored in the SQLite file and that the CLI will read and apply on startup?
That way somebody can snag a copy of your data and be subject to the same constraints by default.
This changes one default that "everyone agrees about" and which you can change with a compile time option: SQLITE_DEFAULT_FOREIGN_KEYS
Then it argues for STRICT tables, recognizing that there are drawbacks without introducing a new feature (custom type aliases, CREATE TYPE alias = base).
If also doesn't even considering what it means for existing data to make tables strict, which is precisely why “there is no pragma to globally make all tables strict”.
Then it argues for setting a busy timeout, and picks 5s. Why? Why 5s and not 1 or 60s? SQLite doesn't decide, which makes perfect sense. Your OS or programming language also doesn't offer you locks with a default timeout: it's either indefinite, or an instant "try lock".
Finally: WAL mode is a different file format, unsupported on many platforms, in more danger of silent corruption. Why should it be the default?
The section "The solution: editions?" in the article addresses directly the point of existing data.
The way I read it, this article does not advocate at any point to change the defaults for existing databases, but rather to start with better defaults for new databases.
Also, regarding the timeout of 5 seconds, I disagree with your premise "SQLite doesn't decide, which makes perfect sense". As the article explains, SQLite decides on the value zero (ie. instant error), which is arguably an inconvenient default.
> The section "The solution: editions?" in the article addresses directly the point of existing data.
I'm sorry, where?
SQLite schema is stored as text. If you change the default interpretation of CREATE TABLE with a PRAGMA, your existing tables become STRICT, but (1) they might now have columns with invalid types (which means you have an invalid schema, and your database fails to open), (2) they may have invalid data for their strict types (which you can only figure out with a full table scan, PRAGMA integrity_check will complain).
Regarding busy_timeout, why is 5s specifically a better default? You did not engage with my argument: that 5s is no different from 1s or 60s. How do you decide?
I think the minimum is considering the arguments by the people who promise to maintain the software for the next 25 years.
PS: I actually really like the idea of `CREATE TYPE alias = type` for use with STRICT tables. I would champion that feature request on the forum. Given how schema is saved, I disagree with making it the default. Having to mark your tables STRICT is not such a burden, IMO.
> > The section "The solution: editions?" in the article addresses directly the point of existing data.
> I'm sorry, where?
Here:
quote
[...]This should be a nice middle ground </i>which avoids breaking backwards compatibility, but lets the database engine move forwards and not be bogged down by its own history.
</i>end quote
> Regarding busy_timeout, why is 5s specifically a better default?
According to the post we are discussing, any number greater than zero is better than the current default:
quote
The default behavior [no timeout] has lead me to writing real-world bugs, where systems would sometimes just crash. I've manually written retry loops to fix it.
> This should be a nice middle ground which avoids breaking backwards compatibility, but lets the database engine move forwards and not be bogged down by its own history.
Does nothing to address my criticism. Handwaving "this is a nice middle ground" does not address the issues. How does the feature work when enabled on an existing database, and on a new database?
Please understand that when you write `CREATE TABLE …` this statement is copied pretty much verbatim to the schema table. And that's the metadata that's saved for the table: a verbatim copy of the DDL.
If you don't mark it STRICT any current version of SQLite will consider it not to be.
So if you make assuming tables are STRICT the default (even with a PRAGMA), you'll have to deal with tables that you assume are STRICT, but aren't: they have invalid types, or invalid data.
The STRICT feature, as it was added, is backwards compatible in the sense that: (1) all old databases work with new versions of the library, and (2) all new databases fail fast (before corrupting data) in old versions of it, as they'll refuse to parse STRICT tables.
To fail fast for existing database files, you'd need to integrity check the entire databases.
So how do you implement it, what's the alternative, exactly? Add STRICT to every table you create since enabling the PRAGMA? You could do that but, to my knowledge, it'd be the first time you'd do that (modifying the schema before writing it) in 25 years of SQLite.
Whatever you come up with must be backwards compatible, as that is the promise SQLite developers have made.
SQLite gets so much praise here but when you start using it, you realize quickly how bad it is, the type system is by default very limited and dangerous.
It's like comparing old php with a strongly typed language.
It’s not as bad since you can always use a powerful programming language with a good type system that avoids type errors at the SQL level. You can build good abstractions in your programming language.
It’s curious how many people don’t understand what SQLite is and its intended feature set. They get huffy that it’s not a full client server model with multimaster clustering across 8 data centers on 12 continents plus New Zealand with realtime synchronous replication.
It’s a product that allows you to do sql like things without a database server. If you need to have database server behavior, you’re using the wrong product.
Well, it goes both ways. You'll see articles saying essentially "you don't need Postgres or any other fancy database, SQlite is enough" while ignoring the fact that some use-cases warrant a more conventional DB server.
I agree with you. There are 2 dozen foot-guns to be kept in mind. And discovered a new footgun regarding multi-byte strings and NUL handling today on HN. SQLite became popular because it was the only free and open-source choice 2 decades ago. Now there are other type-safe and robust choices.
It is very simple. Which means fast to setup in dev environment for local testing. Which makes first version very easy. And then people just keep fixing that one.
Still I quite a lot of question the use on servers if you have decided that I need a database.
Not that there isn't more valid use cases like local storage or self-contained information transfer for specific use.
From what I can tell, DuckDB is more focused on huge-scale data analysis than simple data persistence, so I’m not sure if it fits my use cases. Otherwise, it looks good.
Thanks for the suggestions. I did some brief research on those two and DuckDB in particular looks enticing. I'll have to remember to give it a try on my next side project.
been working on a new implementation entirely of a local sqlite like database. It's from scratch in rust and data is made up of TSV's so the data is human (and agent) readable. sql querying is more expensive for an llm
A system-wide config file in /etc that changes defaults for every program would break any program that assumes the old defaults.
It also wouldn't solve the problem of having to manually find out what the current recommended defaults are. With editions, you can simply enable the latest one and know you've got the right defaults.
I should have been more clear, I meant a sqlite.conf can configure a program rather than have it apply globally. For example, a config file placed in same directory as its .wal file to tune it for specific instances. That way you don’t need to lookup what “editions” apply which pragmas or settings. With sqlite.conf you can tune your specific database connections by uncommenting the default settings to enable current features/best practices
SQLite isn't typically a global one-per-system database, and even if it was how would that solve this problem? The problem isn't that you can't set all these settings to the right values - it's that they don't have the right values by default.
All it does is fragment a ecosystem, bloat a source tree and makes maintenance a painful task only to please people who think maintainers should cater to their poor tech hygiene
Editions do not fragment the ecosystem at all. A crate written in rust 2015 can depend on a crate written in rust 2024 and vice versa. There are no forced upgrades.
The only maintainers it causes a burden for are the compiler developers (and tech debt within the compiler). But this is pretty much unavoidable if the language is to evolve while remaining backwards compatible -- e.g. it's not like the c++ compiler has a lower rate of tech debt accrual.
The "3" refers to the file format (or rather, represents a breaking file format change vs 2), which the devs have committed to keep backward-compatible until 2050. https://sqlite.org/lts.html
Oooh I'd forgotten about that! I'm keen on the real (non-rowid) primary keys and covering indexes. I'm not sure about defaulting to decimal math, but I suppose the reasoning makes sense
HN… for the love of god… please please stop trying to make SQLite be something it isn’t. Leave this poor project alone.
It’s a great tool if you want to give a local app its own database. If you need concurrent writes and full ACID guarantees of an industrial strength database, use an industrial strength database.
Yes, other databases will require you to read more manual pages and configure a service. Higher up front cost. Not “lightweight.” But given enough operating time there is a certain unarguable lightness to using the right tool for the job.
Just by its testing and its number of installations (real world testing) you could consider SQLite being more "industrial strength" than any other DB on the market.
Like you want me to ship postgres with my app? I mean sure I could, but that seems very over complicated when sqlite works just fine except for some unfortunate defaults
I have: SQLite is great, it does everything I need, it would've been even better if it had better defaults or this edition system. In lieu of that, I will just keep setting these 4 pragmas and add strict to my tables.
I'm not critiquing your opinion, but could you please elaborate? I don't know Rust, but I just had a conversation on why I think it might have a niche in the enterprise.
Interesting idea - I like seeing a list of pet-peeves followed by a proposal for a straightforward way to have a set of 'alternative defaults' that remains backwards compatible. If you don't want to opt in, don't run the new PRAGMA edition = 2026.
Too often it's just a list of issues and a wish that everyone else will change.
In (mild) defense of SQLITE_BUSY - busy_timeout just tells sqlite to sleep and retry up to the timeout when it receives SQLITE_BUSY. It seems like a sensible default for a library to leave that up the calling code - which may have something else it could do while it waits. However, that logic often gets missed!
This isn't so much a list of pet peeves as it is the almost universal way people that work seriously with SQLite configure the database. It's reasonable to suggest that the alternative settings for each of these suggestions is probably the wrong default for 2026.
Yes, agree. These are very sane defaults and match what I use..
I’d say these are reasonable settings for most uses. Though do you know of surveys that back this up? I don’t mean to nit pick too much, I’d just like to see common uses and the data.
SQLite is used in a lot of unconventional settings (for SQL databases) where these settings don't make as much sense. But that's what makes the "edition" useful; it captures the use case we all mean when we're thinking of the "database" lego in an application stack.
While that's true, editions are more about leaving legacy decisions behind while keeping the backward compatibility promise.
Even if you're in one of those unconventional settings (say, a bare-metal microcontroller or something), you'd probably still start from edition 2026 and mutate your settings accordingly, rather than using the defaults that are 26 years old.
Yeah, that's important. Rust's 2015 edition is worse, not just different from what you'd write today with 2024 edition. There's a clear direction of travel.
So you’re saying there’s no data to group these settings by editions.
You are probably correct, but I imagine the SQLite team's dedication to backwards compatibility has things the way they are so that existing systems can user later versions a swap without worrying about changing the SQL using it.
The entire point of "SQLite should have editions" is so that projects can opt into a set of modern defaults for 2026 and not get all of those backwards compatible decisions from 20 years ago.
If they’re opt-in, how could the new defaults be a problem for backwards compatibility?
GPP's wording suggests that the defaults simply be changed to what is being discussed as more generally sensible in current times, rather than being opt-in.
Given how many projects are potentially out there effectively relying on the current settings, and SQLite's general attitude to backwards compatibility, that would likely not be considered a good idea. Opting in with an edition flag for new (or updating) projects does seem like a good solution to this to serve all of old, active, and new projects, but it would increase potential bug surface area and therefor testing requirements, and the existing setting do allow all that to be opted in/out to/from already (and it is only four settings we are talking about here).
That FKs being enforced is set per-connection rather than at the database level is something that surprised me a lot when I found out. A way of setting that at DB creation (or via ALTER DATABASE after) seems like quite an omission because if you have multiple potential routes that can update the same DB any one of them could cause serious the others will encounter.
> It's reasonable to suggest that the alternative settings for each of these suggestions is probably the wrong default for 2026.
That's the key concept here. When tightening up the defaults, an "edition" mechanism is a good solution.
Now we need this for C/C++, which have much legacy stuff which ought to go away for new code. This is more feasible than it used to be, because "Convert this Edition 4 code to Edition 5" is something LLMs can do now.
I'd never seen all the rules for SQLite soft typing written out before. Those are more complicated than strong typing.
> Now we need this for C/C++
P1881 Epochs proposed to WG21 (the C++ standards committee) in 2019 by Vittorio Romeo
The committee found plenty of problems with this, and made it clear that if Vittorio did all the hard work to resolve those problems they would find more, P1881 was abandoned.
There was a Reddit thread https://www.reddit.com/r/cpp/comments/1tja9zr/c_profiles_a_c... which suggested that the "Profiles" idea Bjarne is pushing for C++ 29 could be used to deliver this.
So, you're not the first person to notice that this is a good idea, P1881 was written after Rust's 2018 Edition, but before 2021 Edition with its even more significant improvements. I firmly believe Rust's Editions unlock not only technical possibilities (though it does certainly do that) it unlocks an appetite from users which is good for your ecosystem.
> Now we need this for C/C++, which have much legacy stuff which ought to go away for new code.
In C++, certainly. In C, though, what do you not do in C23 that you was doing in C99?
IIRC gets() ?
I don't think that counts - fgets (the safer replacement) was already in C89.
"Modern C" by 1999 already included "Don't use gets()"
The key concept of an "edition" is that you can deprecate stuff. "gets()" should have been pulled from the standard library around 1990 or so, along with "strcat" and its frenemies.
I hope we're getting it for C++ with CppFront, but I'm not hopeful it'll ever become mainstream
https://github.com/hsutter/cppfront
Yep. The whole locking database thing is this persistent myth about SQLite. All databases lock on write, it’s a question of the granularity of the lock. Multiple writers simply take turns.
They don’t know by now, honestly they don’t want to know.
yes but no... most database allow for at least as many parallel and concurrent writes as there are tables at a minimum.
The "lock on write" problem is that in MySQL i could run a OLAP pipeline for a few hours and have a fully functioning database with degraded perfomance, on SQLite the same pipeline would lock the database for the full hour. (there are surely ways to solve this (eg using the main db as read-only and a secondary db for writes or splitting the writes in incremental transaction), but it is not a "myth".
The “myth” is you can’t use it for a website because if you have multiple requests that need to write they can’t do it concurrently. (They just take turns of course for the milliseconds of write time.)
If you run a transaction with writes for an hour on any database, the data you update will literally be locked. So your example only works if results are independent of the data other programs want to use.
Of course more granularity of locking is better and enables more designs that would not otherwise work. But somewhere you run into the same problem of writers taking turns.
After read, it hit me that because sqlite is a DB, "editions" as-is not work.
Because it not tied to the data but to the code.
Instead, what I think should be is that the PRAGMAs become "data" that is always checked in full with "if manually set" and then on next "open" THEY GET APPLIED.
That is.
(and in the command line when open interactively they show up).
RE: SQLITE_BUSY: I would replace "often" with "nearly always." On top of that, it's often not fixed even when pointed out. "This software only has one writer, so we don't need to handle SQLITE_BUSY" translates to me sending SIGSTOP to a process any time I want to run some queries against its database.
busy_timeout is often sidestepped (ignored) when a transaction attempts to upgrade from a read to a write producing SQLITE_BUSY.
By default, SQLite transactions start in DEFERRED mode, acting as read transactions until an actual write operation occurs.
If another connection begins writing to the database while your transaction is in this read state, an immediate SQLITE_BUSY error is triggered regardless of what you set busy_timeout to
Exactly. In cases where I expect long-running parallel connections from separate processes to the same sqlite file, I make sure that all read transactions do `BEGIN DEFERRED` so `COMMIT` releases the read locks, and all write transactions do `BEGIN IMMEDIATE` so that `SQLITE_BUSY` timeout is not side-stepped.
There was one case where all transactions were implemented using nested `SAVEPOINT bla` so `BEGIN IMMEDIATE` could not be used without more hassle, so this ended all “I know I'm going to write” transactions to instantly update a single-row table so that their lock would not begin as DEFERRED and eventually switch to `IMMEDIATE`; this way almost all `SQLITE_BUSY` side-steppings disappeared. (timeout was set to 30 seconds but all read/write transactions were instrumented to have less than 5 seconds duration).
lol, I briefly thought of such a technique, but in the end, found it simpler to just use a synchronization primitive at the application level to serialize db access on transactions where I knew I was going to write. it amounts to about the same honor code.
SQLite is slightly different from Rust in that it is a data container. It’s somewhat more common for people to move SQLite database files from one machine to another and then inspect using the command line tool. And it is often the case that the embedded SQLite version in your app is a newer version than whatever version /usr/bin/sqlite3 happens to be. Adding editions to your SQLite file will probably break this use case of using an older version to read a database written by a newer version because it does not know what has changed in a new edition.
Not a big deal though. Probably just need better ops to bundle the command-line utility that’s the same version as what’s used in your app.
For some of these pragmas you have the same issue with or without "editions", right? Busy timeout is per-connection. And then: if you're running in WAL mode, you, the user, have to know that, or risk messing up the database by copying just the .db file rather than vacuuming-into.
Editions make the problem worse by requiring the version not only to support the underlying pragmas but also to understand the edition mapping.
Example: PRAGMA foo=1 is introduced in 2027. PRAGMA edition=2030 implies this foo pragma. Now you unnecessarily lock out three years worth of releases.
I don't see how you don't have the pragma compatibility problem either way. The edition proposal captures a bunch of behaviors that already exist.
Yes, the problem exists either way. Editions just exacerbate the problem.
feature-set would be a better idea, because it is more explicit on what you are enabling
There’s no need to store the literal edition in the DB file. Instead the edition could be a library construct that sets appropriate flags. So if you set edition 2026 you get WAL, etc.
It seems SQLite could be evolve to solve this by just bundling itself entirely in the data files? After all, the binary is less than 1MB, anywhere you’re putting a database surely has at least that much overhead, for most applications it’s less than a drop in the bucket.
I’d be interested to learn if there are any db implementations that take this approach, or reasons this wouldn’t work.
Well you'd have the problem that an sqlite database file created on a Linux AMD64 box could be copied to an AArch64 macOS machine to be read there. And quite a lot of (cross platform) software build their file formats on top of SQLite.
I think my silly and unserious naive response would be linear scaling bundled binaries with number of platforms doesn’t seem to be that materially different in terms of total size. But I see your point, didn’t consider the architectures
it would be sorta enough to bundle the wasm binary
That's the idea behind the future file format:
https://github.com/future-file-format/F3
Bundle a wasm decoder as a fallback when native decoder isn't available.
The issue then is that instead of just bundling a tiny 1MB sqlite library with your program, you have to bundle a huge WASM runtime. I looked at the size of wasm on my system just to get an idea and it's 46MB; that's huge.
For context, I have a game I'm working on where I build stand-alone builds with all dependencies bundled. The app bundle is 14MB right now. If I added sqlite, it would grow to 15MB. If I added sqlite-but-with-wasm, it'd more than quadruple in size to 60MB.
Ugh I can't edit now but just noticed I should've said I looked at the size of wasmtime, not "the size of wasm".
Perhaps it could be an "actually portable executable"?
https://news.ycombinator.com/item?id=26273960
FWIW, that's existed for a while. Source at https://github.com/jart/cosmopolitan/tree/master/third_party... with downloadable binary at https://cosmo.zip/pub/cosmos/bin/sqlite3 .
So for every new SQLite db you want to access would have to run a new untrusted executable? That seems… hilariously bad for security.
Fair point! Was mostly an intrusive technical thought
I mean.... it does sound like a fun project
There is a way to do this with sqlite in readonly mode using the append vfs [0] which allows you to put your DB appended to the end of the executable, and then the executable can read it (like a zip file the header is then at the end of the db rather than the front). Or you could embed a normal db as a binary blob with the linker. Allowing writes is more tricky because most OSes don't typically allow executables to edit their own executable memory (on unix I believe executables always load as r/x or r/o depending on the region, maybe with some minor exceptions).
And a lot of users of sqlite statically link sqlite within their executable, they actually recommend that instead of dynamically linking.
[0] https://sqlite.org/vfs.html ctrl-f for 'append'
Edit: I got confused between the sqlar command and the append vfs so fixed it.
editions could be self-describing as to certain semantic changes, and you could embed that with the file. older versions could safely ignore it and newer versions parse and run it. you could also force things like "editions must be declared early", "editions are one way only" etc to get some level of security in the adoption of the change
I think you can maintain full backward compatibility with all these changes. SQLite has a bunch of meta-data that you can use to see that no earlier editions have modified your database since you last wrote. You make the new editions follow these strict rules, but if an old edition modifies the DB in the interim, you fall back to the original rules, until it’s verified compliant again.
> SQLite is slightly different from Rust in that it is a data container.
I think this is the key.
From sqlite.org [1]:
> [Since 2004], the file format has been fully backwards compatible.
> By "backwards compatible" we mean that newer versions of SQLite can always read and write database files created by older versions of SQLite. It is often also the case that SQLite is "forwards compatible", that older versions of SQLite can read and write database files created by newer versions of SQLite. But there are sometimes forward compatibility breaks. Sometimes new features are added to the file format
---
Given editions (A) and (B), what does backwards compatibility look like? Must (B) be backwards compatible with (A)?
If yes -> editions are backwards compatible but not necessarily forwards compatible, which is the current status quo:
If no -> editions are not backwards compatible, the edition space is bifurcated:
Now you may have to worry about backwards compatibility with (A)..(Z). What happens when you import a file from edition (Y)?
1. https://www.sqlite.org/formatchng.html
---
Interesting PS, grepping sqlite.org for "backwards compat": https://pastebin.com/Q7b7h4eM
The answer to the question "What happens when you import a file from edition (Y)" should, ideally, be exactly the same as the answer to the question "What happens when you import a file created with parameters foo, bar and baz set to values a, b and c". There's really nothing new here.
Some parameters are properties of the database file itself, such as (I believe) journal_mode. These parameters probably result in issues with earlier versions of sqlite; if you create a file with journal_mode WAL, you're not gonna be able to open it in a version of sqlite without WAL support. Same with strict tables; if you have a database which contains strict tables, I assume you're going to encounter issues if you try to open it in a version of sqlite too old to support strict tables. But anything new enough to support the individual database file features shouldn't have any trouble. This is true whether we're talking about an edition pragma or individual parameters set the old way.
Some parameters are properties of the connection, like the foreign_keys parameter. If you're trying to open a database file in a version of sqlite which doesn't support foreign key validation at all, just don't set the foreign_keys parameter.
A lot of this discussion mirrors the SQLite forum's discussion on the never-implemented "PRAGMA strict":
https://sqlite.org/forum/forumpost/1b9d073a37ca5998
I personally find this idea interesting and would like if SQLite meaningfully moved away from Postel's Law.
The "use strict" thing is interesting. I often hear people say, well we can't fix absurd behavior in JS because backwards compatibility! Well, we already did, and we can do it again!
"use stricter"
"use loose; footloose; kick off your Sunday shoes"
“hold my beverage”;
"use strong" was a proposal from Google
https://docs.google.com/document/d/1Qk0qC4s_XNCLemj42FqfsRLp...
I became hopeful for a moment, then saw it's from 2015. Ouch! (Also, I love the name. I had a similar idea I called "use sane", but obviously that one wouldn't have gotten far...)
The strong typing thing is really interesting. After using JavaScript for a while, I developed PTSD around dynamic types. I became convinced that static typing was the only way to avoid hell.
Then I used Python for a while, and... experienced approximately none of my previous pain. I found that quite odd. Turns out what I was actually after was a sane type system, not a static one. In other words, strong types rather than weak ones.
I do think there are additional benefits to static typing, especially for larger projects and serious work. But I was surprised that most of the pain-delta was in this first jump:
Weak -> Strong -> Static
"use strict;" is a perl thing. You also may turn experimental features on, or demand feature set from a certain version.
It was (just a perl thing last millennium).. https://developer.mozilla.org/en-US/docs/Web/JavaScript/Refe...
Brilliant. I have a similar idea for a language I've been envisioning.
So, my approach to dynamic features, global mutable state, etc... There's this big list of features which have caused me tremendous pain over the years. I am increasingly weary.
Sometimes you want or need them, though. You don't want the same level of strictness for every project (e.g. throwaway scripts or game jams), nor at every stage of a project -- e.g. being forced to specify invariants is something I'd love to be able to enable, but I wouldn't want that on while I'm still figuring out the basic structure of things.
So for game jams I'd put the language into #JAMMODE (which would be short for a bunch of other flags). But the point here is that jam mode should be opt-in, rather than opt-out. You should have to go out of your way to enable the footguns. And a file with #JAMMODE should be a little smelly. You should think, OK I'm actually shipping this thing now, let's get it out of #JAMMODE. (And strict files can't talk to jam files, and we probably want different strictness levels beyond Debug and Release, etc... someone told me with the strictness "zones" that I'm reinventing half of Ada, hahah.)
Counterpoint: each new edition will add bloat to SQLite as future edition will need to keep each past edition pragma set for backward compatibility.
As SQLite is often used embedded, bloat matters.
So I suggest that "PRAGMA edition" to be only be a shortcut to a list of PRAGMA commands, that would be expanded at the library level: PRAGMA edition would never appear in the DB file. As such, the build of the library would just support a limited set of editions, with a removal policy in default builds. Maybe editions could even be defined at runtime (a system table?) as a way to load them dynamically if old editions are needed beyond builtin support (think about the state of SQLite in 2046).
Isn’t that exactly what the author suggests? Editions as a set of config options that are already there.
The config options are already implemented, so the heavy lifting is done. Editions would amount to a few hundred bytes.
That's certainly not the case for making STRICT tables the default.
But figuring that out requires understanding how they are implemented, or reading the forum, which the author admittedly didn't do.
The rest are assumptions about best practices (also not shared by the developers of SQLite).
It's always really funny to me when commenters don't read the article and then phrase the article's point like they invented it.
I actually think this wouldn’t be that helpful.
The developer still needs to ensure they apply their set of pragmas, whether that’s a single edition pragma or a set of pragmas. And they still need to understand and carefully choose the pragmas/options they use (an edition really makes this a little harder by abstracting/hiding something that needs to be directly understood and visible).
And these proposed new default pragmas are more incremental improvements rather than complete solutions, more convenience than critical. E.g., while the default affinity is goofy, strict tables don’t come close to a comprehensive validation mechanism — so if you need strong validation you’re likely going to need to implement that at a higher level anyway. (Also, there’s a decent separation-of-concerns argument that you should handle it separately.)
Likewise the busy timeout. The pragma is convenient but you still need to handle busy timeouts. (The author’s problem, “I didn’t realize busy timeouts could happen so the app didn’t handle them correctly”, is not solved by the pragma.)
Oh hi, author here. Fun to see this make it to HN.
Suggestion: post this on https://sqlite.org/forum/forum - the SQLite team monitor that forum closely and I've had some really great answers from them to questions or suggestions in the past.
https://lobste.rs/c/kzln1c
I haven't really ever participated in that forum before, but it's an interesting idea. I don't know if I'm going to do it, I might. Though I also wouldn't mind if someone else posted about it there. I might even make a forum account and participate in the discussion.
Found what appears to be a minor mistake you may want to fix: "This means that a dangling reference easily results in a reference to the wrong column" should probably be "... a reference to the wrong row". (In the paragraph about SQLite's tendency to re-use ROWIDs).
You're right, thanks. Fixed
In the first example, there's a a second thing that surprised me: you delete an entity and it's unique ID gets reused? Is that a good idea?
I guess if foreign keys are handled properly then that's not a problem by definition? But it sounds wrong somehow.
I think that's a security vulnerability.
If a parent table ID gets reused, then it's a potential to expose data to a wrong user -- security broked.
That's correct but SQLite was never designed to be a production database in the first place. It can be used as a production database but only if you know what you're doing, and presumably anyone who knows what they're doing knows about the AUTOINCREMENT keyword because it's one of the first things you learn about SQLite.
I disagree, SQLite is a production embedded database, the extensive test¹ suite is a testament to that. It's just has different default and behavior than a database designed for being served to multiples simultaneous writers.
1- https://sqlite.org/testing.html
>> you delete an entity and it's unique ID gets reused? Is that a good idea?
That's default behavior, but it can be altered when creating a table. See;
https://sqlite.org/autoinc.html
Where do you see that they get reused from that link?
> The normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDs as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID. If you ever delete rows or if you ever create a row with the maximum possible ROWID, then ROWIDs from previously deleted rows might be reused when creating new rows and newly created ROWIDs might not be in strictly ascending order.
What should it do once you hit MAXINT? Honest question…
I don’t think people are criticizing the MAXINT thing. The problem is that IDs are already reused when you create 10 rows, delete 5 and then make a new one. Normal DB engines just keep counting afaik so the new ID will still be one that has never been used before.
In SQLite the max int is 64 bit. So a rather large number (about 9 billion billion, aka 19 digits). That's a lot of rows to add.
I would suggest if you are storing that much data, SQLite may not be the correct engine. (And you probably shouldn't be using an Int primary key.)
It's a good question to ask, but probably not a concern for most of us.
Oh I really like this! The one counterargument that comes to mind is when I think about the likes of C++, where there are many editions and they can be confusing to keep track of.
I'm not sure if you'd want to set one edition in stone every year. Perhaps every 3 years? Or 5 years? Especially for a long-term project like SQLite, that sounds perfectly acceptable!
An alternative is to use wrapper libraries that set sane defaults, e.g. https://rogerbinns.github.io/apsw/bestpractice.html
But I suppose it would be nice to have a standard way to refer to those defaults, in a cross-runtime way.
> I don't think I need to explain why it's a bad idea for a database to be so careless about data validation.
Well, loose typing can be extremely useful, and having a type of "ANY" would not replace it.
I have built recently an accounting reconciliation system to find discrepancies in data coming from a large variety of sources: some from proper database engines (MySQL MariaDB), but most from proprietary systems that export to CSV. It's amazing how corrupt data can become: dates that are invalid, numbers that aren't numbers, strings strings strings everywhere.
Being able to store the data into tables that have types, but can accept anything, is simply great.
> loose typing can be extremely useful
"Loose typing" enforced in a strict typing system can be useful in certain scenarios, but it is regrettable that it instead replaced the strict typing discipline for some time in software. Strict typing should be the default, because it is the most accurate description of data in the vast majority of cases.
> I don't think I need to explain why it's a bad idea for a database to be so careless about data validation.
...Meanwhile MongoDB being successful for years with no sign of decline.
Your... solution to bad ETL data is to go "let's keep it this way"?
You can already "store whatever you want" in a serious database that respects types by default. It's called a blob or if you must, a text/varchar.
Yes! keeping it that way helps with traceability. The point is not to fix the data, it is to understand where corruption happens.
Are you saying that you have an application where you want the loose-typing-with-integer-affinity semantics for a column (or some other particular affinity)? It would be entirely reasonable to have a specific type for each loose-with-affinity variant. But I don’t think those should be the default.
You are implicitly dismissing any future in which the auditors actually do their job. Is that quite safe?
You're using CSV, what did you expect? CSV was never meant for data exchange between two systems that do not know of each other's existence. Basically every CSV file is its own dialect.
I am not "using" CSV, I am forced to use it.
I think the OP wants duckdb.
The first two points are deliberate SQLite design decisions so they're unlikely to change.
No, I actually like SQLite with the settings tweaked a tiny bit. Disagreement with the defaults isn't enough to make me switch databases completely
What are the defaults for duckdb? Is it the pragma 2026 equivalent?
I can't speak for speed, but foreign keys and data type checking are on by default.
DuckDB is a OLAP database. Its optimized for batch analytics, not for individual transactions. Its great for data science work, but I wouldn't use it for a production system.
> I once had to clean up a project where some code had accidentally been writing the strings '1' and '0' to a column which was intended to store booleans (1 and 0). That was not a fun debugging story.
So this was a write to a column that did not have INTEGER affinity. If it was intended to be used as a boolean, then it should have INTEGER affinity. I know because I've tried hard to enter integer- and float-like strings as strings in INTEGER affinity columns, and I haven't managed to; I could only insert them as BLOBs, or prefix the string with say '\' and check/remove at the application level. (That was for an ontology-like database, where table EAttribute.eatvalue could have any type.)
It might be worth bringing this up on the forum [1]. The developers are quite active there, and it's possible they've never considered this option, or they have considered it and have reasons to not go for it. The original design followed Postel's Law (see my comment from the other say [2]), it would (theoretically) be nice if that mess could be avoided by specifying an edition.
Today I noticed I could do `pragma foreign_key = ON`, and despite the pragma being wrong (it should be foreign_keys, plural), it reported nothing. In fact, it reports nothing with the correct pragma either. So check your pragmas!
1. https://sqlite.org/forum/forum
2. https://news.ycombinator.com/item?id=48900625
The Postfix mailer has allowed recommended default behavior to evolve using its "compatibility_level" parameter:
https://www.postfix.org/postconf.5.html#compatibility_level
https://www.postfix.org/COMPATIBILITY_README.html
You get a warning whenever you depend on the deprecated old default until you either move forward or specifically commit to the old behavior.
I think CMake actually has the best default evolution system out there (a bit surprising give how awful the actual language is).
Each "policy" they change can be manually set to old or new, and there's a global config to set them all at once based on the version of CMake.
https://cmake.org/cmake/help/latest/command/cmake_minimum_re...
I scarcely go a week without encountering issues caused by their huge CMake 4 backward compatibility break. I think CMake has one of the worst solutions out there.
Hasn't been an issue at all for me, but in any case that's a separate issue. They removed compatibility with CMake earlier than 3.5, which is 10 years old at this point. Support length is orthogonal to default behaviour evolution systems.
I never encounter things which need CMake <3.5 features. I constantly encounter things which declare their "minimum supported version" to be below 3.5.
Which makes a lot of sense for those projects! They can be built with CMake 3.2 because they don't need anything introduced in a newer version, so why not declare 3.2 to be the earliest supported version? It's not their fault that CMake interprets "minimum supported version" weirdly?
It's really weird to me how the SQLite author is clearly a very smart guy and talented developer and then his argument against type safety effectively just boils down to
> But I do not recall a single instance where the bugs might have been caught by a rigid type system.
Which is a shame. Of course the author writes more than this, but this is IMO largely the gist of the argument. At this point it's beginning to feel like this is mostly a sort of stubborn sunken cost fallacy, where they've been arguing this for so long they can't take the "hit" of agreeing to change the defaults.
Sadly, the ORM layer lags here: Drizzle has no way to declare STRICT tables. The request has been open since March 2023 (issue #202, now discussion #2435) and didn't make the v1.0 beta either. The only workaround is hand-appending STRICT to generated migration SQL, which doesn't work at all if you use `drizzle-kit push`.
https://github.com/drizzle-team/drizzle-orm/discussions/2435
But push is not for use in production? It's for development. You generate a single custom migration that sets strict, apply it, and then you can use push as you want (during development, not for deploying actual changes to your database)
I don't have a problem with setting a few relevant flags for any new SQLite database. It's a good encouragement to familiarize yourself with the settings.
I think the solution is for author to use PostgreSQL
Those choices were made for specific reasons that make sense in embedded environment and when backward compatibility is no.1 concern.
But I wouldn't mind feature-sets. Editions are too wide of a concept and tell you nothing at glance what a given code is doing, "enable 2026 set of features" tells me nothing on what is actually enabled.
What are the specific reasons for why it makes sense in embedded systems to not enforce foreign key constraints or to let me insert a blob into an integer column? Because I work with embedded systems and have never found those defaults to make sense.
My proposal does not harm backwards compatibility in the slightest.
This is a weird complaint to me. When I use SQLite, I always make sure there is a single writer thread. Any thread can submit a write request in a thread-safe queue. If you follow this pattern, you never need to worry about SQLITE_BUSY errors.
And your scripts for rare tasks you haven't gotten around to making a GUI for also go through that queue? And your manual interactions with the database to fix problems? Or do you just risk crashing the program when you do those things?
The oldest `editions` idea I know of, is with Perl. For 20 years it was already common to write `use 5.18` to activate multiple options.
https://perldoc.perl.org/functions/use
You know, 10 years ago one might have remarked ... changing these defaults means C programmers would have to correctly implement error handling and retries.
The reaction they're likely to have to that can probably be best described in megatons, like any other nuclear explosion ...
On iOS most apps link Apple's prebuilt libsqlite3, so compile-time defaults are out of reach.
It sounds like you're also advocating for some form of permanent pragma that gets stored in the SQLite file and that the CLI will read and apply on startup?
That way somebody can snag a copy of your data and be subject to the same constraints by default.
This changes one default that "everyone agrees about" and which you can change with a compile time option: SQLITE_DEFAULT_FOREIGN_KEYS
Then it argues for STRICT tables, recognizing that there are drawbacks without introducing a new feature (custom type aliases, CREATE TYPE alias = base).
If also doesn't even considering what it means for existing data to make tables strict, which is precisely why “there is no pragma to globally make all tables strict”.
Then it argues for setting a busy timeout, and picks 5s. Why? Why 5s and not 1 or 60s? SQLite doesn't decide, which makes perfect sense. Your OS or programming language also doesn't offer you locks with a default timeout: it's either indefinite, or an instant "try lock".
Finally: WAL mode is a different file format, unsupported on many platforms, in more danger of silent corruption. Why should it be the default?
The section "The solution: editions?" in the article addresses directly the point of existing data.
The way I read it, this article does not advocate at any point to change the defaults for existing databases, but rather to start with better defaults for new databases.
Also, regarding the timeout of 5 seconds, I disagree with your premise "SQLite doesn't decide, which makes perfect sense". As the article explains, SQLite decides on the value zero (ie. instant error), which is arguably an inconvenient default.
> The section "The solution: editions?" in the article addresses directly the point of existing data.
I'm sorry, where?
SQLite schema is stored as text. If you change the default interpretation of CREATE TABLE with a PRAGMA, your existing tables become STRICT, but (1) they might now have columns with invalid types (which means you have an invalid schema, and your database fails to open), (2) they may have invalid data for their strict types (which you can only figure out with a full table scan, PRAGMA integrity_check will complain).
This was discussed previously on the SQLite forum, you can read the team's position there: https://sqlite.org/forum/forumpost/0248dcf7f0ece9fb
Regarding busy_timeout, why is 5s specifically a better default? You did not engage with my argument: that 5s is no different from 1s or 60s. How do you decide?
Also discussed in the forum, with the team laying out the rational; https://sqlite.org/forum/forumpost/f0da30efa661bd9c
I think the minimum is considering the arguments by the people who promise to maintain the software for the next 25 years.
PS: I actually really like the idea of `CREATE TYPE alias = type` for use with STRICT tables. I would champion that feature request on the forum. Given how schema is saved, I disagree with making it the default. Having to mark your tables STRICT is not such a burden, IMO.
> > The section "The solution: editions?" in the article addresses directly the point of existing data.
> I'm sorry, where?
Here:
quote
[...]This should be a nice middle ground </i>which avoids breaking backwards compatibility, but lets the database engine move forwards and not be bogged down by its own history.
</i>end quote
> Regarding busy_timeout, why is 5s specifically a better default?
According to the post we are discussing, any number greater than zero is better than the current default:
quote
The default behavior [no timeout] has lead me to writing real-world bugs, where systems would sometimes just crash. I've manually written retry loops to fix it.
</i>end quote
Your forum links may be quite useful as a response to this comment: https://news.ycombinator.com/item?id=48928441
OK, but this:
> This should be a nice middle ground which avoids breaking backwards compatibility, but lets the database engine move forwards and not be bogged down by its own history.
Does nothing to address my criticism. Handwaving "this is a nice middle ground" does not address the issues. How does the feature work when enabled on an existing database, and on a new database?
Please understand that when you write `CREATE TABLE …` this statement is copied pretty much verbatim to the schema table. And that's the metadata that's saved for the table: a verbatim copy of the DDL.
If you don't mark it STRICT any current version of SQLite will consider it not to be.
So if you make assuming tables are STRICT the default (even with a PRAGMA), you'll have to deal with tables that you assume are STRICT, but aren't: they have invalid types, or invalid data.
The STRICT feature, as it was added, is backwards compatible in the sense that: (1) all old databases work with new versions of the library, and (2) all new databases fail fast (before corrupting data) in old versions of it, as they'll refuse to parse STRICT tables.
To fail fast for existing database files, you'd need to integrity check the entire databases.
So how do you implement it, what's the alternative, exactly? Add STRICT to every table you create since enabling the PRAGMA? You could do that but, to my knowledge, it'd be the first time you'd do that (modifying the schema before writing it) in 25 years of SQLite.
Whatever you come up with must be backwards compatible, as that is the promise SQLite developers have made.
Not the author of the article, but I think you are overthinking the issue.
New database ⇒ use the proposed new magic PRAGMA, start with sane defaults.
Existing database ⇒ don't touch anything, keep legacy "suboptimal" defaults.
The forum post I linked to has a 3 line example:
You (or the author) can replace the non-existent `PRAGMA strict` with whatever editions thingy you're proposing.
Just, you know, explain what should happens when you do this, because the creator of SQLite doesn't know.
When doing so consider the file format, how schema is stored, and what happens when other versions of SQLite touch your file.
Should be simple, given that I'm overthinking it. Thanks.
I would go with something like "Syntax error: pragma strict must appear before any table creation. " and consider that db file unusable.
SQLite gets so much praise here but when you start using it, you realize quickly how bad it is, the type system is by default very limited and dangerous.
It's like comparing old php with a strongly typed language.
There is not even a date type...
It’s not as bad since you can always use a powerful programming language with a good type system that avoids type errors at the SQL level. You can build good abstractions in your programming language.
SQLite competes with fopen. Not Postgres
It’s curious how many people don’t understand what SQLite is and its intended feature set. They get huffy that it’s not a full client server model with multimaster clustering across 8 data centers on 12 continents plus New Zealand with realtime synchronous replication.
It’s a product that allows you to do sql like things without a database server. If you need to have database server behavior, you’re using the wrong product.
Well, it goes both ways. You'll see articles saying essentially "you don't need Postgres or any other fancy database, SQlite is enough" while ignoring the fact that some use-cases warrant a more conventional DB server.
Different tools for different situations!
I think this critique was traditionally about the LAMP stack. Imagine how many engineering years would have been saved if Wordpress ran on SQLite,
- no db user configuration - no installing multiple tenants in the same db - no phpmyadmin (ftp db files) - no remote database hacks - no backup tools
I agree with you. There are 2 dozen foot-guns to be kept in mind. And discovered a new footgun regarding multi-byte strings and NUL handling today on HN. SQLite became popular because it was the only free and open-source choice 2 decades ago. Now there are other type-safe and robust choices.
It is very simple. Which means fast to setup in dev environment for local testing. Which makes first version very easy. And then people just keep fixing that one.
Still I quite a lot of question the use on servers if you have decided that I need a database.
Not that there isn't more valid use cases like local storage or self-contained information transfer for specific use.
What are other choices for FOSS serverless relational databases? I’ve been looking everywhere and couldn’t find anything.
DuckDB ? Strict by default and excellent for logs, telemetry, dashboard apps, etc.
From what I can tell, DuckDB is more focused on huge-scale data analysis than simple data persistence, so I’m not sure if it fits my use cases. Otherwise, it looks good.
Firebird can be embedded, although neither the database itself, nor the embedded mode are as popular as they once were.
It's a fully featured database though, with everything you expect from one, including actually working ALTER TABLEs.
Nah, all those defaults are features. Of course, there are contexts where those defaults are unsuitable which means: Use a Different RDBMS!
Change RDBMS instead of changing configs from their default value? They're configurable for a reason.
Are there any other serverless SQL DBMSes?
Lots. Firebird and DuckDB come immediately to mind. Wikipedia lists several more, though not all of them are relational: https://en.wikipedia.org/wiki/Embedded_database
Thanks for the suggestions. I did some brief research on those two and DuckDB in particular looks enticing. I'll have to remember to give it a try on my next side project.
Assuming the author is here (they link to the HN submission from the post), you have a typo: “laudbile” instead of “laudable”.
you can also set those defaults with compile time flags, that's what I have been doing.
Off-topic: TIL wal2 mode in the works. Wow. That's a no-brainer feature, IMHO.
been working on a new implementation entirely of a local sqlite like database. It's from scratch in rust and data is made up of TSV's so the data is human (and agent) readable. sql querying is more expensive for an llm
Why not a .conf file like everything in /etc or postgresql.conf?
These proposed editions are per connection.
A system-wide config file in /etc that changes defaults for every program would break any program that assumes the old defaults.
It also wouldn't solve the problem of having to manually find out what the current recommended defaults are. With editions, you can simply enable the latest one and know you've got the right defaults.
I should have been more clear, I meant a sqlite.conf can configure a program rather than have it apply globally. For example, a config file placed in same directory as its .wal file to tune it for specific instances. That way you don’t need to lookup what “editions” apply which pragmas or settings. With sqlite.conf you can tune your specific database connections by uncommenting the default settings to enable current features/best practices
SQLite isn't typically a global one-per-system database, and even if it was how would that solve this problem? The problem isn't that you can't set all these settings to the right values - it's that they don't have the right values by default.
Who are these tourists?
D will get caught into that edition trap too
All it does is fragment a ecosystem, bloat a source tree and makes maintenance a painful task only to please people who think maintainers should cater to their poor tech hygiene
Editions do not fragment the ecosystem at all. A crate written in rust 2015 can depend on a crate written in rust 2024 and vice versa. There are no forced upgrades.
The only maintainers it causes a burden for are the compiler developers (and tech debt within the compiler). But this is pretty much unavoidable if the language is to evolve while remaining backwards compatible -- e.g. it's not like the c++ compiler has a lower rate of tech debt accrual.
It is sqlite3. Emphasis on the 3 - it already has 'editions'.
The "3" refers to the file format (or rather, represents a breaking file format change vs 2), which the devs have committed to keep backward-compatible until 2050. https://sqlite.org/lts.html
It also refers to what the binary on my PATH is called, also what the library name I need to pass to link against it.
They even had an sqlite4:
https://sqlite.org/src4/doc/trunk/www/design.wiki
Oooh I'd forgotten about that! I'm keen on the real (non-rowid) primary keys and covering indexes. I'm not sure about defaulting to decimal math, but I suppose the reasoning makes sense
HN… for the love of god… please please stop trying to make SQLite be something it isn’t. Leave this poor project alone.
It’s a great tool if you want to give a local app its own database. If you need concurrent writes and full ACID guarantees of an industrial strength database, use an industrial strength database.
Yes, other databases will require you to read more manual pages and configure a service. Higher up front cost. Not “lightweight.” But given enough operating time there is a certain unarguable lightness to using the right tool for the job.
Just by its testing and its number of installations (real world testing) you could consider SQLite being more "industrial strength" than any other DB on the market.
- https://sqlite.org/testing.html
- https://sqlite.org/mostdeployed.html
EDIT: added links
Exactly. There is no reason to complain about it. It is successful. People who find it lacking can use something else, there are many options.
I don’t understand why a local app database shouldn’t still have the same basic functionality and data guarantees as the full-sized ones.
You can just run postgresql locally like akonadi does if that's what you need
Like you want me to ship postgres with my app? I mean sure I could, but that seems very over complicated when sqlite works just fine except for some unfortunate defaults
Make up your mind :D
I have: SQLite is great, it does everything I need, it would've been even better if it had better defaults or this edition system. In lieu of that, I will just keep setting these 4 pragmas and add strict to my tables.
Rust is a cancer
it's widespread in rewriting CLI tools, so it's terminal
I'm not critiquing your opinion, but could you please elaborate? I don't know Rust, but I just had a conversation on why I think it might have a niche in the enterprise.