Rendered at 06:30:37 GMT+0000 (Coordinated Universal Time) with Cloudflare Workers.
sethev 7 hours ago [-]
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!
tptacek 7 hours ago [-]
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.
Animats 3 hours ago [-]
> 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.
doc_ick 5 hours ago [-]
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.
tptacek 4 hours ago [-]
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.
mikepurvis 3 hours ago [-]
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.
nullsanity 4 hours ago [-]
[dead]
sethev 7 hours ago [-]
Yes, agree. These are very sane defaults and match what I use..
pstuart 5 hours ago [-]
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.
usefulcat 4 hours ago [-]
If they’re opt-in, how could the new defaults be a problem for backwards compatibility?
justinsaccount 4 hours ago [-]
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.
groundzeros2015 6 hours ago [-]
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.
DANmode 5 hours ago [-]
They don’t know by now, honestly they don’t want to know.
mamcx 5 hours ago [-]
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).
aidenn0 4 hours ago [-]
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.
kccqzy 7 hours ago [-]
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.
tptacek 7 hours ago [-]
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.
kccqzy 7 hours ago [-]
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.
tptacek 7 hours ago [-]
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.
kccqzy 7 hours ago [-]
Yes, the problem exists either way. Editions just exacerbate the problem.
ijustlovemath 7 hours ago [-]
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
arijun 7 hours ago [-]
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.
appplication 7 hours ago [-]
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.
anitil 1 hours ago [-]
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.
Edit: I got confused between the sqlar command and the append vfs so fixed it.
mort96 7 hours ago [-]
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.
appplication 7 hours ago [-]
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
andai 7 hours ago [-]
Perhaps it could be an "actually portable executable"?
So for every new SQLite db you want to access would have to run a new untrusted executable? That seems… hilariously bad for security.
appplication 7 hours ago [-]
Fair point! Was mostly an intrusive technical thought
anitil 1 hours ago [-]
I mean.... it does sound like a fun project
Rendello 6 hours ago [-]
> 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.
Rendello 6 hours ago [-]
A lot of this discussion mirrors the SQLite forum's discussion on the never-implemented "PRAGMA strict":
I personally find this idea interesting and would like if SQLite meaningfully moved away from Postel's Law.
andai 7 hours ago [-]
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!
postepowanieadm 2 hours ago [-]
"use strict;" is a perl thing. You also may turn experimental features on, or demand feature set from a certain version.
"use loose; footloose; kick off your Sunday shoes"
henryoman 28 minutes ago [-]
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
vanyaland 31 minutes ago [-]
On iOS most apps link Apple's prebuilt libsqlite3, so compile-time defaults are out of reach.
mort96 8 hours ago [-]
Oh hi, author here. Fun to see this make it to HN.
rmunn 5 hours ago [-]
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).
mort96 2 hours ago [-]
You're right, thanks. Fixed
simonw 7 hours ago [-]
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.
mort96 7 hours ago [-]
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.
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.
deepsun 6 hours ago [-]
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.
zarzavat 8 minutes ago [-]
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.
bruce511 4 hours ago [-]
>> 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;
Where do you see that they get reused from that link?
echoangle 8 minutes ago [-]
> 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.
But I suppose it would be nice to have a standard way to refer to those defaults, in a cross-runtime way.
4 hours ago [-]
souvlakee 6 hours ago [-]
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`.
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...
groundzeros2015 6 hours ago [-]
SQLite competes with fopen. Not Postgres
wang_li 5 hours ago [-]
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.
lbourdages 4 hours ago [-]
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!
lenkite 1 hours ago [-]
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.
kccqzy 6 hours ago [-]
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.
tmpfile 4 hours ago [-]
Why not a .conf file like everything in /etc or postgresql.conf?
hmry 3 hours ago [-]
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.
tmpfile 2 hours ago [-]
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
Rendello 7 hours ago [-]
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!
You get a warning whenever you depend on the deprecated old default until you either move forward or specifically commit to the old behavior.
andrewchambers 5 hours ago [-]
It is sqlite3. Emphasis on the 3 - it already has 'editions'.
Retr0id 5 hours ago [-]
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
andrewchambers 5 hours ago [-]
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.
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
GianFabien 4 hours ago [-]
Nah, all those defaults are features. Of course, there are contexts where those defaults are unsuitable which means: Use a Different RDBMS!
Rohansi 4 hours ago [-]
Change RDBMS instead of changing configs from their default value? They're configurable for a reason.
Cyberdog 3 hours ago [-]
Are there any other serverless SQL DBMSes?
chillfox 6 hours ago [-]
you can also set those defaults with compile time flags, that's what I have been doing.
eduction 4 hours ago [-]
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.
yomismoaqui 4 hours ago [-]
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.
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!
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.
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.
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).
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.
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’d be interested to learn if there are any db implementations that take this approach, or reasons this wouldn’t work.
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.
https://news.ycombinator.com/item?id=26273960
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
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.
https://sqlite.org/forum/forumpost/1b9d073a37ca5998
I personally find this idea interesting and would like if SQLite meaningfully moved away from Postel's Law.
https://docs.google.com/document/d/1Qk0qC4s_XNCLemj42FqfsRLp...
I guess if foreign keys are handled properly then that's not a problem by definition? But it sounds wrong somehow.
If a parent table ID gets reused, then it's a potential to expose data to a wrong user -- security broked.
That's default behavior, but it can be altered when creating a table. See;
https://sqlite.org/autoinc.html
But I suppose it would be nice to have a standard way to refer to those defaults, in a cross-runtime way.
https://github.com/drizzle-team/drizzle-orm/discussions/2435
It's like comparing old php with a strongly typed language.
There is not even a date type...
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.
Different tools for different situations!
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.
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
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.
They even had an sqlite4:
https://sqlite.org/src4/doc/trunk/www/design.wiki
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.
- https://sqlite.org/testing.html
- https://sqlite.org/mostdeployed.html
EDIT: added links