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.
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!
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
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!
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!
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.
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)
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.
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.
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
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!
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 ...
> 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.
47 comments
[ 4.9 ms ] story [ 72.5 ms ] threadNot 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.
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!
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
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
I guess if foreign keys are handled properly then that's not a problem by definition? But it sounds wrong somehow.
It's like comparing old php with a strongly typed language.
There is not even a date type...
https://github.com/drizzle-team/drizzle-orm/discussions/2435
But I suppose it would be nice to have a standard way to refer to those defaults, in a cross-runtime way.
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.
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!
The reaction they're likely to have to that can probably be best described in megatons, like any other nuclear explosion ...
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.