Prefer strict tables in SQLite
evanhahn.com
evanhahn.com
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...
That's the pattern recommended by SQLite here: https://www.sqlite.org/lang_altertable.html#otheralter
"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.
As far as I can tell I've shared more prompt transcripts than anyone else. Happy to be proven wrong about that.
Look through my commit history on https://github.com/simonw/tools/commits and https://github.com/simonw/sqlite-utils/commits and https://github.com/simonw/datasette/commits and you'll see that commits that were AI-assisted almost all link to a transcript or include a prompt or both.
> 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.
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.
Flexible, yes. You can store anything. The downside is, that I've also found "anything". Stuff attached to the wrong "class", wrong datatypes, missing "obligatory" fields, etc, etc.
It's a PITA to work with. If I could design it from scratch, it'd be a single table with JSON payload.
Isn't plain JSON even worse? At least the design you're criticising has a dynamic schema definition separate from code.
You could of course have a JSON schema somewhere, but in my experience the whole point of representing the schema as data in the database is to support (limited) end-user driven schema changes.
I would use JSON to store data that complies with a schema that can be modified by third parties outside of my control.
The point is that "dynamism" isn't needed in this case. It's used to define fixed parameter sets for different components, so those components could just as well define a class and use json to (de)serialize it.
IOW: Defining new fields, classes and values is worthless unless the underlying component can interpret them. So code changes are needed anyway, and then class is way better and safer to use.
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.
In Postgres, if you insert a real number into an int column, the data gets rounded and stored as int. In SQLite, the data is inserted as real.
Neither approach is fail-fast.
> 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".
Having said that, given sqlite's tiny type universe I can't imagine TC would be at all slow.
That's pretty much the only disagreement with the SQLite developer, who is an amazing guy that wrote an amazing tool!
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.
You can actually use an integer column and store Unix timestamps (or floats for subsecond accuracy).
But yes, sqlite has very little types support and its default behaviour is very much unityped / dynamically typed which I also dislike. Same with having to enable foreign keys every time you open a connection.
Then again, I have been subjected to Oracle nonsense for too long and have had to accept all of the boolean alternatives: 0,1,'0','1',Y,N,y,n,YES,NO,T,F, etc
I have done this many times. Function-based indexes are necessary if they must be searched.
That's one 64-bit number, or 50 different columns.
If you are greatly concerned, SQL Server and the Sybase database from which it emerged have a native boolean data type.
Another benefit of this scheme is that adding another boolean means using the next power of 2 in the existing integer, assuming room remains. No new column necessary.
Runtime validation is there to enable when using SQLite in other ways.
(please note that I personally strongly prefer static types, but I still found this an interesting read).
The premise of your question doesn't work. If one person is going to learn about the option and a second one isn't, the better system for that to occur in is the one in which more people end up with correct behavior.
In this case, that means surprising the guy with existing (buggy) software; he'll have to fix the bugs. It means not bothering the guy who's starting a greenfield project. Neither of them will be subject to this bug.
The alternative, which you seem to be saying you prefer, is that the guy with buggy software should continue to have bugs, and the greenfield guy should also have bugs.
At some level, shouldn’t choices where one option is strictly better not surface as configurable choices at all?
If I have to memorize sets of behaviors by “compatibility version,” don’t I now have to remember lots of sets of particular footguns, across time and across systems that I work on (or parachute into)?
I read an essay about XML once where the author noted that he got a warm feeling inside whenever he saw the opening tag:
<?xml version="1.0">
def create_table(name, cols): return f"CREATE TABLE {name} ({cols}) STRICT;"
From the release notes:
2002-06-17 (2.5.0), "Parse (but do not implement) foreign keys."
At one point there was also a tool which would generate trigger rules to enforce foreign key constraints. (2008 Oct 15 (3.6.4), Added the source code and documentation for the genfkey program for automatically generating triggers to enforce foreign key constraints)
Strict type all the things.
Why did they do that? Is it owned by a private company?
By the time TH3 was added in 2008, SQLite had gained a fair bit of traction across multiple industries. Though I totally agree that the comprehensive coverage is a leading reason why they've been so stable over the last ~20 years.
So indirectly, TH3 is why they (continue to) exist and (are able to) make money, but it isn't a direct line as one might assume.
What the ...?
My only experience with versionitus type things is with microsoft sql and other "enterprisey" things that used it and its hard enough without huge upgrade blockers like text in an integer field lol still they're willing to add the default to the table create statement. I'm thinking that sqlite folks feel that things like type safety, database consistency are disliked authoritarian attributes, and I'm sure thats a consideration in letting old code touch databases that old code doesn't understand.
Its wierd that they didn't like having a pragma for any new table creation even at the database level but they allow this problem factory:
"Because of a quirk in the SQL language parser, versions of SQLite prior to 3.37.0 can still read and write STRICT tables if they set "PRAGMA writable_schema=ON" immediately after opening the database file, prior to doing anything else that requires knowing the schema."
I guess for ID reuse a rationalization could be that theres only so many integer values that can fit into a certain number of bytes haha
OTOH I don't see a similar superpower arising from handcrafted data type enforcement over (non-strict) SQLite.
Datagram is also quite often more fit for applications than streams, because many applications are message oriented. The Websocket protocol acknowledges that even though over TCP. But that's more a bonus point than a strong reason to choose UDP over TCP, one can always recreate packets/messages on top of TCP. It's a bit goofy though, because TCP uses IP packets.
A lot of online games with significant real-time constrains and many-to-many connections gladly use UDP - and similarly, video conference services also use it. Smaller protocols like DNS and NTP as well.
There are other arguments beside real-time streaming with acceptable data loss, see [1] and the "end-to-end argument" paper it links in particular.
Choosing UDP and ending up recreating some of its reliability and flow control features is not a "Uh, Oh..." moment. It's normally a deliberate choice. Sometimes you do need custom wheels [2].
I can understand not taking it seriously if it was completely unsupported but I'm pretty sure most databases don't have perfect default configs. Even PostgreSQL needs configuration for optimal performance because the defaults are for low (minimum) spec systems.
> then eventually adding nearly all the reliability facilities of TCP to the app (automatic retry, etc) by hand.
Depending on what you're doing you're still probably doing better than TCP after all that work. TCP is a stream based protocol which is not ideal for many applications due to head of line blocking. If you built your own reliability layer over UDP you likely avoid that issue entirely.
The SQLite documentation is very up front about these things [1] and more. It should only be an issue if you're the type of person who never reads any documentation.
> The original implementation of SQLite sought to follow Postel's Law which states in part "Be liberal in what you accept". This used to be considered good design - that a system would accept dodgy inputs and try to do the best it could without complaining too much. More recently, people have come to prefer software that is strict in what it accepts, so as to more easily find errors.
> There are now millions of applications that take advantage of SQLite's flexible and forgiving design choices. We cannot change SQLite to follow the current preference toward strict and dogmatic behavior without breaking those legacy applications.
Sometimes you can build a successful business out of doing so: https://aeron.io/
Or, at least, so says the lore.
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.
If you use strict tables with my Go SQLite driver, you'll get worse support for bool/date/time columns than otherwise.
It's still unfortunate though that typing a column DECIMAL triggers numeric affinity, which destroys decimal numbers stored as strings.
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.
That’s not a type, you just get a numeric-affinity column.
You can use comments to preserve intent in strict mode, and that’s strictly more useful than fuzzy mode: it is richer, it is clearer, and it is no less reliable.
There are only 5 datatypes in sqlite. INTEGER, TEXT, BLOB, REAL, and NUMERIC.
Which is why I prefer not to use them.
Domain types would be the best fix, but I do not think legacy tables are the second best.
I can use either API to trigger bool and time handling in my Go driver.
https://github.com/ncruces/go-sqlite3/blob/main/driver/drive...
sqlite> create table test (
id integer primary key,
created_at text default current_timestamp,
flag integer not null default 0 check (flag in (0, 1))
) strict;
sqlite> insert into test (flag) values (1);
sqlite> select * from test;
╭────┬─────────────────────┬──────╮
│ id │ created_at │ flag │
╞════╪═════════════════════╪══════╡
│ 1 │ 2026-07-12 04:03:22 │ 1 │
╰────┴─────────────────────┴──────╯ 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.
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.
Otherwise, yeah, it's very surprising to explicitly put INTEGER and still be able to insert text. It's not like the user left the type out.
Which is why in most cases you are going to show at compile time that your code adheres to the typed structure. The SQLite schema you are developing alongside provides the type information for static analysis. There is no real benefit in also double checking again at runtime. Your code isn't going to magically mutate in a way that it starts inserting integers where your static analysis showed that it inserts strings.
SQLite is not like Postgres, which is designed for many different applications all sharing the same data, where you have to place trust in third-parties to also do the right thing. Runtime validation is critical in that environment. SQLite is designed for one application, one database. While it technically can support multiple applications sharing the same file, support is poor and it is not really designed for that. In the typical case, the only trust you need is your code, which you can evaluate at compile time. For the atypical cases you can enable strict tables.
That is common today, but remember that Postgres is now 40 years old. It is so old that it was originally based on QUEL rather than SQL. Back then database servers were designed to be what we now think of as the "API server". That necessitates runtime input validation same as your "API server" needs input validation today. If you were designing Postgres from scratch now you would do a lot of things differently, but it was built for its time.
> Guess if your code enforces types at DB insert time
It would be unusual for your programming language to magically turn strings into ints, or the like, so you can prove statically that your code won't insert the wrong thing. Duplicating the same thing at runtime doesn't buy you anything. Maybe if you are still trying to futz around with Javascript, but SQLite was designed for statically-typed programming languages.
Schema:
CREATE TABLE foo (
bar INTEGER
);
fn main() {
stmt := "INSERT INTO foo (bar) VALUES (?)"
if (now() % 2 == 0) {
db.exec(stmt, [1]);
} else {
db.exec(stmt, ["Baz"]); // Static analysis fails here. Input is not an integer.
}
}
Schema:
CREATE TABLE foo (
bar TEXT
);
fn main() {
stmt := "INSERT INTO foo (bar) VALUES (?)"
if (now() % 2 == 0) {
db.exec(stmt, [1]); // Static analysis fails here. Input is not text.
} else {
db.exec(stmt, ["Baz"]);
}
}
CREATE TABLE foo (
bar INTEGER
);
sqlite3_stmt *stmt;
const char *sql = "INSERT INTO foo (bar) VALUES (?);";
...
sqlite3_bind_text(stmt, 1, "4.5", -1, SQLITE_STATIC);
sqlite3_stmt *stmt;
const char *sql = "SELECT bar FROM foo;";
...
int bar_int = sqlite3_column_int(stmt, 0); // bar_int = 4Why does that matter? You don't have to rely on human memory. You went to all the trouble to define the types so you don't have to remember. Your static analysis will tell you that the types are incompatible.
> money_adder.c inserts string
This fails analysis. `bar` is defined as an integer. money_adder.c will not ever get the point of inserting a string as your infrastructure will halt the build pipeline long before you ever get to the point of running the program.
You must have accidentally replied to the wrong comment at some point? It is technically true that you can write software without type analysis, but that's clearly not applicable to our discussion about using type analysis.
> You're also updating the code while keeping the same SQLite file
In the real world where migrations are necessary it is prudent to validate that any already persisted data is structured as expected, applying any necessary migrations if there is a mismatch, but when used as SQLite was primarily designed you only need to do that once at initialization, not every single time you touch the data. Once you have validated that the file's schema matches the schema defined at compile time then the static truths hold.
Both the advantages and disadvantages section is missing key arguments.
Key argument in favor of flexible typing: Easy to evolve schema. When you are using SQLite to store data in your embedded database and your requirements change, do you want to create a new database and migrate data each time? Evolving the schema in-place is much easier, and if your application is the only one reading/writing data into the database you will not be surprised by the fact the column that previously stored integers now contains strings as well.
Key argument against flexible typing: The schema is a contract, and when multiple applications read and write to a single database, and these applications are updated on their own schedules, storing the wrong type of data in a column will break the other applications. Strict adherence to the contract is necessary for applications to collaboratively read and write data. When a table is created by one application and used by another, the data types must be what you agreed to.
Additionally, how often are there multiple apps with different release schedules written using Sqlite? I would expect overwhelmingly large number of its use cases would be a single application working on it.
As the developer attains a much better understanding of the product, strictness becomes more and more beneficial, but the spectre of backwards compatibility haunts the interfaces and defaults.
> SQLite strives to be flexible regarding the datatype of the content that it stores.
Which, is something you could have caught before it got written at all if you had your db enforcing your types.
You would only inherit a project where everything was ANY if anything could go anywhere.
With SQLite's default behavior, anything can always go anywhere, so the type definitions are at best semi-accidentally observed by the code, and at worst completely misleading. You have no idea which of the two the developer intended.
I get the impression that this SQLite behavior is a historical oddity caused by the original use case for the tool, rather than something that was intentionally planned and thought through, and was later retconned to be intentional and benign. To me, it makes no sense, even after reading the explanation on sqlite's website.
Implies documentation is crucial. Fortunately SQLite's documentation is among the best out there.
TCL was used as a dev wrapper language at the time, and it functioned the same way.
It was only in mid-2004 that SQLite 3 was released which used its own storage backend, and that allowed for the 5 supported storage types (int64, string, bytes, float, null). It was API compatible (with minor adjustments) with the earlier SQLite 2, so the lack of static typing continued, otherwise everyone would have to rewrite their code. You do get dynamic typing, which hasn't been a problem for the vast majority of SQLite users.
Do remember that SQLite is competition for fopen, not Oracle / Postgres etc. It is trying to make things as effective as possible in that scenario. If you don't want numbers in your string column, then don't do that!
I used to sort of dismiss berkeleydb(why so simple?), but a disk backed b-tree indexed key value store is not trivial to get right and having a prebuilt library to do it provides a huge value.
As of January 2006 you could add CHECK constraints using the TYPEOF function to reject that at the SQL level. And it is your own code - there is no server - doing the insertions. As was common back then, protecting you from your own bugs was not a high priority for APIs!
They DO include a nice section at the bottom about why these limitations exist, but I wish they would make the process easier.
Discussed here: https://news.ycombinator.com/item?id=31249823
But it would be a lot better if it were built in.
“NUL characters (ASCII code 0x00 and Unicode \u0000) may appear in the middle of strings in SQLite. This can lead to unexpected behavior.”
I love HN's (healthy) obsession with SQLite. It's brilliant.
First time I used it was in high school, when I was a newbie to C and didn't know how to link in libraries, and SQLite was the only thing that offered all the code as a single .c file https://sqlite.org/amalgamation.html