▲ 362 points
back
177 comments
> Unfortunately, I don’t think there’s a way to ALTER a table to make it strict. I think you have to copy the data out of the non-strict table into the strict one.
This inspired me to add a feature to my sqlite-utils Python library and CLI tool, so you can now use it to transform non-strict tables to strict (and vice-versa) like this:
uvx sqlite-utils transform data.db mytable --strict
import sqlite_utils
db = sqlite_utils.Database("data.db")
db.table("mytable").transform(
strict=True
)
Here are the relevant docs:
- Using table.transform(strict=True): https://sqlite-utils.datasette.io/en/stable/python-api.html#...
- The sqlite-utils transform command: https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...
> This inspired me to add a feature to my sqlite-utils Python library
"me" == "ChatGPT", apparently:
> Can the .transform() internal Python method turn a non-strict table into a strict table?
> No. Table.transform() preserves the table’s existing strictness; it cannot change it. Its signature has no strict= parameter
> add an optional strict= boolean parameter to the transform() method - if it is None (the default) then the strict is not changed, otherwise True means change to strict and False means change to non strict. Implement with red/green TDD and uv run pytest -k
https://gist.github.com/simonw/ab8256b81646ad967a601975e206d...
I appreciate the transparency, at least.
Does this trick work with foreign keys? Like, if you have an ON CASCADE DELETE, does it delete a bunch of rows in other tables when converting the table to strict?
https://sqlite.org/flextypegood.html explains why this isn't the default (and probably will never be the default).
> rigid type enforcement can successfully prevent the customer name (text) from being inserted into the integer Customer.creditScore column. On the other hand, if that mistake occurs, it is very easy to spot the problem and find all affected rows.
That doesn't line up with my experience. (In particular, it may not be easy to fix those corrupted rows; the data may be entirely lost.)
> By suppressing easy-to-detect errors and passing through only the hard-to-detect errors, rigid type enforcement can actually make it more difficult to find and fix bugs.
This doesn't line up with my experience at all.
These are similar to arguments that people made about MongoDB. You can store anything! And then most people who used it realized that this is actually terrible, in most cases.
It looks like this is an artifact of when SQLite was written and the strong opinion of its author, less so a rigorous engineering principle. Reading this, it sounds like the author has been criticized a lot on this, is digging in their heels no matter what, and will find any supposed justification.
On the other hand, datatypes like JSON or HSTORE (in postgres) can handle what they are advocating for. But opt-in to YOLO typing is nearly always better than opt-out.
The safety and trust that comes out of a very reliable database setup is, apparently, a misplaced feeling that makes data bugs harder to fix. I really don’t understand their take.
My experience is the opposite: add as many checks and safety rails to <DB> (Postgres, in my case), and you don’t have to go looking for this sort of mistake, which shouldn’t happen in the first place.
Yeah that doesn't make any sense at all. It sounds like a post-hoc justification to me.
> If you find a real-world case where STRICT tables prevented or would have prevented a bug in an application, please post a message to the SQLite Forum so that we can add your story to this document.
Kind of wild that they don't believe this happens.
Also they totally drew the wrong conclusions from their example in Appendix A. The data type was CHECK'd for a column and they are like "oh if only we hadn't enforced checks of this data type, we would have had to verify it when we opened the database!" instead of "thank goodness we have this CHECK'd this data type, it means we are forced to robustly verify it in one place, instead of using unreliable checks in the application code".
SQLite did come from TCL everything-is-a-string world, so this attitude is not surprising. TCL makes for a very good shell language (much better than Bash or Batch), but a rigid systems language it is not.
I was actually expecting that article to say something about performance. In the 90s telecom DBs removed all constraints including primary key from their ingestion tables for speed, and in general constraints are for OLAP not OLTP.
Having said that, given sqlite's tiny type universe I can't imagine TC would be at all slow.
Wow, SQLite not believing in fail-fast systems is disappointng. Never knew SQLite was JS of SQL!
I'd like to see STRICT as the default.
That's pretty much the only disagreement with the SQLite developer, who is an amazing guy that wrote an amazing tool!
Even foreign keys aren't enabled by default, you have to use `PRAGMA foreign_keys = ON;` [1]. The bigger issue with strict tables is that there is no equivalent pragma, and you're forced to use the non-standard STRICT on each CREATE TABLE. A global STRICT pragma was considered but not implemented, see this forum thread [2].
SQLite very rarely changes defaults because of their commitment to backwards compatibility. They don't want software written against SQLite 3.53 to start throwing errors when upgraded to 3.54 because suddenly `CREATE TABLE` is creating strict tables and the rest of the software breaks as a result.
SQLite has a LOT of footguns that one only discovers over time. Dynamically typed by default, Off-by-Default foreign keys, ID re-use in AUTOINCREMENT, WAL Mode needing explicit enabling to ensure readers are not blocked, double-quote/single-quote issues, positional placeholders & named parameters issues, no TIMESTAMP type in 2026 despite being a CORE feature in SQL-92 standard, etc.
There are more similar issues, like disabling foreign key constraints by default "for compatibility reasons". Makes me wonder if there was a time when SQLite supported foreign key syntax, but didn't actually implement the functionality.
Well, I would also like a proper datetime/timestamp datatype that isn't just a string.
Yeah it's a really weird design decision. Why would I want the database to let me accidentally insert the wrong type? SQLite is mostly great but its philosophy towards type safety leaves something to be desired. I once had to clean up in a project where someone had accidentally stored the strings '1' and '0' in a Boolean column in code deployed to thousands of devices; not fun.
Another thing I dislike is the lack of timestamp types. Instead, you're expected to just use a text column and store a textual timestamp. Even worse, instead of using ISO, the standard date time functions produce strings on the form "yyyy-mm-dd HH:MM:SS" which you're just supposed to assume are in UTC. Why not at least give us "yyyy-mm-ddTHH:MM:SSZ"? Or, you know, a proper space efficient timestamp data type.
A truly great project, with some truly baffling design decisions.
Yes. I always considered that a downside of SQLite. You have to validate numeric fields on the read side or risk the application blowing up on bad data.
lmao default okay buddy
I agree with you. I'd go one step further and let it be the only mode available starting with new versions of the library.
Coming from the enterprise SQL world, I never took SQLite seriously for the very reason that field types were not enforced by default. (Yes, I was agog when it became the backbone for app metadata on smartphones.) Anyway, reading this reminds me of the old chestnut from networking about choosing UDP over TCP for its low-latency and simplicity and then eventually adding nearly all the reliability facilities of TCP to the app (automatic retry, etc) by hand.
The difference is that when you add all these mechanisms yourself, you can do it differently than TCP does, sometimes to a great effect: see QUIC and HTTP/3.
OTOH I don't see a similar superpower arising from handcrafted data type enforcement over (non-strict) SQLite.
I don’t like strict mode because it quite unnecessarily thwarts better strict types in the application layer: by restricting the spellings of column types, it stops you from using more meaningful names and prevents code from using those names when mapping database and application types:
https://hn.algolia.com/?query=chrismorgan+strict+sqlite&type...
If you’re going to work with a database through something like the Rust sqlx crate, I think you’re better to eschew strict mode.
The downside of strict tables is that some data types are not available, such as Date.
Strict should really be the default. If a database is shared by multiple applications then you should be able to rely on the declared data type. If one application stores a string into a numeric column that breaks everyone else.
On the other hand, the main use case for SQLite is embedded databases. And that means only one application is using the database. In that scenario being able to evolve the schema (as opposed to creating a new database and copying the data over) can be seen as an advantage. The application's code knows what to expect in each column--including mixed data types.
If you're stuck with an older version of SQLite and/or want to enforce order on an existing table without creating a new table with STRICT and then copying all your rows over and/or also want to do things like enforce signedness, int size, or char/varchar length on a field like you can in other DBs, you can use CHECK constraints.
CREATE TABLE users (
user_id CHAR(36) NOT NULL PRIMARY KEY CONSTRAINT user_id_length CHECK (LENGTH(user_id) = 36),
email_address VARCHAR(255) UNIQUE CONSTRAINT email_address_length CHECK (email_address IS NULL OR LENGTH(email_address) < 256),
role UNSIGNED TINYINT(1) NOT NULL CONSTRAINT role_valid CHECK (role >= 0 AND role <= 9)
)
It's a little frustrating that all this extra cruft is necessary to get the world's most popular RDBMS to take data correctness seriously. I hope that some SQLite fork that behaves more like other RDBMSes when it comes to this stuff catches on some day, but the fact that that hasn't happened yet makes me think that the demand isn't there, somehow, unfortunately.
I think I can see how dynamic data types make sense (eg flat key/value store), but my question would be:
What is least surprising? That INTEGER implicity accepts 'hello world' without error, or that you can't insert such a value unless you use a keyword like NONSTRICT or a type like ANY?
I would wager the vast majority of SQLite users if asked would probably not expect it to work.
Yeah my DB is the one place I want strict types. Well also RPCs. But SQLite is a somewhat different set of use cases, so maybe I'd understand https://sqlite.org/flextypegood.html more if I were using it. Like there's a point about random scripts not made for SQLite happening to work with it, which isn't normally a consideration for other DBMSes.
I had a UUID (partly?) mis-converted to a number if the UUID started w (from memory) something like 08123… which was parsed as octal. Confusing, annoying, fixed w “strict” and a complete table rebuild.
It's good advice to "Prefer strict X in Y" for almost any value of X and Y. Lax DWIM stuff always comes back and bites you in the end.
I would’ve thought this was the default.
CREATE TABLE ... STRICT WITHOUT ROWID is my default, I don't know why I'd ever do otherwise.
It really should be default, but it isn't due to backward compatibility (i assume).
the only thing that sucks about SQLite is migrations.
Using Entity Framework, this doesn't come up as a particular issue, but I still wish it were strict by default because I expect there could be some performance optimizations made for de/serialization.
Wait until you read about its quirks [0]. My favorite:
“NUL characters (ASCII code 0x00 and Unicode \u0000) may appear in the middle of strings in SQLite. This can lead to unexpected behavior.”
thanks! i have seen the sqlite quirks page before but just added this to my queries thanks to you
about the use of ANY, that's perfect for tracking changes on an audit table per field
I was inspired by this blog post to write "SQLite is all you need" [1]
I love HN's (healthy) obsession with SQLite. It's brilliant.
I want a better alternative for SQLite
really interesting, thanks
Too many people missing the point entirely and wanting to make SQLite Postgres or Oracle.
i think braindead developers (most people in this thread) have become way too typescript pilled and as such think that types are something you can’t live without. grow up. use another of the 4000 databases out there or stop fucking bitching that you can’t manage your data without going peepee in your pampers
I really hate this trend of turning every piece of software into this kafkaesque monstrosity that demands you jump through 100 hurdles to do the simplest thing. I mean yeah its good for LLMs but as a human it gets kind of annoying. I honestly love that if you hand SQLite garbage it will do its best.