Prefer strict tables in SQLite(evanhahn.com)
evanhahn.com
Prefer strict tables in SQLite
https://evanhahn.com/prefer-strict-tables-in-sqlite/
80 comments
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.
OTOH I don't see a similar superpower arising from handcrafted data type enforcement over (non-strict) SQLite.
> I never took SQLite seriously for the very reason that field types were not enforced by default.
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.
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.
Even foreign keys weren't available before 2009, and are still not enabled by default:
https://sqlite.org/foreignkeys.html
https://sqlite.org/foreignkeys.html
If I'm interested in a Jeep or Bronco, I don't go to a car reviewer. They say it is noisy and handles poorly. They act like their use case is what matters for something obviously targeting a different use case.
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.
> 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.
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!
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. It was considered but not implemented, see this forum thread [2].
1. https://sqlite.org/foreignkeys.html
2. https://sqlite.org/forum/forumpost/1b9d073a37ca5998
1. https://sqlite.org/foreignkeys.html
2. https://sqlite.org/forum/forumpost/1b9d073a37ca5998
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.
The need ‘default sets’ so that as the very first command I can say ‘use 2026.1 defaults’
Which seems reasonable. And those who care deeply will have no problem configuring it the specific way they want on their own project. Win-win.
Unless you don't know it exists of course
Is it better to learn that it exists through surprise, by way of an engine upgrade suddenly changing the behavior of the software you wrote while in the bliss of your ignorance?
... than by surprise, when you find your integer column contains "[object Object]"? Jokes aside, breaking backwards compatibility is obviously a non-starter.
Developing with sqlite isn't quite like developing software that generically works against an ODBC or JDBC interface with minimal tweaking. You probably will want to peruse the documentation.
Even a pure AI naysayer can use it to find obscure SQLite options
Well no, the software should be configured in its best state by default. Otherwise you run into the case where the user has to read the documentation like a legal contract to find all the footguns that need disabling. If STRICT is strictly better, than that should be the default. The correct approach here would be for the user to pass along a "compatibility version" tag when first connecting, which would set the defaults to whatever was default in that version. That should be something you force each user to set in their source and it should never ever have a "latest" value. It may be too late for sqlite, but if I were designing an API that had to remain stable for decades now, I would put an enum with possible versions in a header and require the user to pick one.
I didn’t get the sense from TFA that STRICT is strictly better, only that it’s how this person prefers to operate in their time and context and experience.
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)?
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)?
They already had this concern with WITHOUT ROWID. They recommend using WITHOUT ROWID whenever possible, but can't make it the default.
The SQLite documentation doesn't say that. WITH and WITHOUT ROWID tables are also different data structures (ordinary tables are B+ trees, without rowid B*). In particular, without rowid tends to be detrimental for tables consisting of wide rows, while being an advantage for narrow rows with a non-integer key.
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.
> This document describes the support for SQL foreign key constraints introduced in SQLite version 3.6.19 (2009-10-14).
That quote leaves open whether SQLite "pretended" to support foreign keys by allowing to create tables with them, but didn't implement them. Otherwise, I don't see the compatibility problem.
Yes.
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)
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)
Well, I would also like a proper datetime/timestamp datatype that isn't just a string.
Please keep your Haskell, Ada, etc. out of my SQL.
Some of us have work to do without wrangling an over complicated type system. I don't need logical invariants embedded into my type system and I certainly don't need it to be Turing complete.
Some of us have work to do without wrangling an over complicated type system. I don't need logical invariants embedded into my type system and I certainly don't need it to be Turing complete.
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.
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.
> Instead, you're expected to just use a text column and store a textual timestamp.
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.
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.
Only if you're not compiling yourself, otherwise, there's SQLITE_DEFAULT_FOREIGN_KEYS=1
'0' and '1', while not ideal, seems fine to maintain? There is not even a true SQLite boolean type.
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
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
the SQLite team explains their preference for dynamic types here: https://sqlite.org/flextypegood.html
(please note that I personally strongly prefer static types, but I still found this an interesting read).
(please note that I personally strongly prefer static types, but I still found this an interesting read).
If every time the SQLite team added a better default for something they changed it, we'd be at SQLite 11 by now and every application would contain at least six incompatible versions of SQLite.
If you are using SQLite as an embedded database, which seems to be SQLite's primary use-case, why wouldn't you prove statically that you are not accidentally inserting the wrong type? Runtime checks are unnecessary overhead.
Runtime validation is there to enable when using SQLite in other ways.
Runtime validation is there to enable when using SQLite in other ways.
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.
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.
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.
> The downside of strict tables is that some data types are not available, such as Date.
There are only 5 datatypes in sqlite. INTEGER, TEXT, BLOB, REAL, and NUMERIC.
https://sqlite.org/datatype3.html
There are only 5 datatypes in sqlite. INTEGER, TEXT, BLOB, REAL, and NUMERIC.
https://sqlite.org/datatype3.html
Right, and that's the problem. There should be DATE and BOOL as well, especially in strict mode.
numeric is not a type it’s an affinity, the underlying types are real and integer. That is why numeric is not valid on strict tables.
The downside is that you loose the place to store the metadata that a column is supposed to store a date.
Which is why I prefer not to use them.
Which is why I prefer not to use them.
SQLite has no date data type. Also SQLite has no way to call EXPLAIN on query and get the dummy type name either for arbitrary SELECT query, so you can't even infer it, if you were to use the dummy type name "DATE" or "DATETIME".
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.
> https://sqlite.org/flextypegood.html
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.
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.
> Yeah my DB is the one place I want strict types. Well also RPCs.
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.
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.
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.
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.
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.
That was your language's driver "helping" you. There is no SQLite octal type.
I'll see if i can find the case - my description was a bit hand-wavy because it was a while ago, and just drawing on memory -- not that your explanation couldn't be true, but I thought I exercised that possibility. For your part, you'll remain sceptical it wasn't the driving language when I tell you it was Tcl, which is indeed the origin of this "manifest type" we're discussing :)
thanks! i have seen the sqlite quirks page before but just added this to my queries thanks to you
I would’ve thought this was the default.
It really should be default, but it isn't due to backward compatibility (i assume).
It’s a stated [0] goal of the project:
> SQLite strives to be flexible regarding the datatype of the content that it stores.
[0]: https://sqlite.org/stricttables.html
> SQLite strives to be flexible regarding the datatype of the content that it stores.
[0]: https://sqlite.org/stricttables.html
You can be flexible with strict tables, type every column as ANY and you pretty much get back the original behaviour.
Sure, but you lose the representation of the developer’s intention that way. I would be pretty pissed off if I inherited a project and the schema was all ANYs.
The developer's intention is that anything can go in there.
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.
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.
The intent of ANY is obviously that the values be flexible. That’s why it’s there if you need it.
“I intended this to be an integer but it could really be anything” is not very useful.
Sure it is. If you encounter something that’s not an int, that could be a signal you have a bug in your writers. Or in the source of the data. That’s useful information compared to “oh, I have some ints and some strings, that’s ANY, everything is ok.”
I’m kind of curious why the decision to have implicit casting like this was made in the first place. I can’t think of a single upside other than not having to type out cast(foo as bar)
SQLite was originally started as a local database library for use during development for times when the main networked database was not available. It used dbm as the underlying storage mechanism, with the dbm API roughly being string keys with string values. ie all underlying values were actually stored as strings. The SQLite code would automatically do conversions - eg the plus operator would convert the strings to int or float, add them, and generate a stringified number as a result. The vast majority of implementation code did not have to care about types, and very local decisions could be made such as in the addition example.
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!
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!
> SQLite was originally started as a local database library for use during development for times when the main networked database was not available.
hipp developed sqlite to replace informix, not to assist during development. He considered the network reliability issues to be a concern for a system supposed to help diagnose problems.
hipp developed sqlite to replace informix, not to assist during development. He considered the network reliability issues to be a concern for a system supposed to help diagnose problems.
SQLite docs: The Advantages Of Flexible Typing: https://sqlite.org/flextypegood.html
It's even worse than implicit casting, if the value can't be cast to the the column's type, it's just inserted without casting. Eg. into an integer column, '10' -> 10 and '1O' -> '1O'
That is documented behaviour - think of it as making a best effort, and not losing the 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!
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!
IIRC, the project started out as TCL code and it carried that vibe through to what it is today.
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.
"Everything should be built top down, except for the first time", as the saying goes %) The problem is often that new things are built with tools that allow for flexibility, because the builder hasn't decided on the shape of what needs to be built. In 1990s it was Perl, in 2020s it's vibe-coding, but in either case it's lax and "dwim".
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.
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.
What's the best way to get booleans in strict tables, since those aren't supported? I don't think people applaud 0 and 1 being boolean values in languages.
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.
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.
https://sqlite.org/lang_createtable.html#ckconst
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.
https://sqlite.org/lang_createtable.html#ckconst
the only thing that sucks about SQLite is migrations.
Yes, the process at https://www.sqlite.org/lang_altertable.html is super risky - 12 steps and a giant CAUTION sidebar about the data loss possible if you do it incorrectly.
They DO include a nice section at the bottom about why these limitations exist, but I wish they would make the process easier.
They DO include a nice section at the bottom about why these limitations exist, but I wish they would make the process easier.
We use this for migrations:
https://david.rothlis.net/declarative-schema-migration-for-s...
Discussed here: https://news.ycombinator.com/item?id=31249823
But it would be a lot better if it were built in.
Discussed here: https://news.ycombinator.com/item?id=31249823
But it would be a lot better if it were built in.
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.
really interesting, thanks
about the use of ANY, that's perfect for tracking changes on an audit table per field
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.