> 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:
> 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
What are you trying to achieve with this comment? Does everything need to turn into a sniping contest about whether or not something is ai-produced? This is the world we live in now.
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?
> 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.
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.
I've seen a set of SQL tables designed to mimic "flexible classes". There's a table for the "class", another table defining its "fields", and two other tables defining class instances and related field values (all as varchar).
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.
>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 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".
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.
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.
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.
Or even better just SET STRICT_TABLES, or whatever other opt-in feature you want. A couple of such statements in the beginning of your migration script (refactored in a way to be reused across all migration scripts) and you are done. I wouldn't want to learn WTF "2026.1 defaults" are.
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.
> 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 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">
He noted that there were no higher versions. (Though they're now up to 1.1.) But he considered that the inclusion of a version number from the beginning of the standard was a shining example of why they hadn't needed a new version.
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)?
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?
> 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?
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.
Which is the worse surprise, when you update your dependency during development and get a fault, or when a user somehow ends up with a fault because of the previous default?
... 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.
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.
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.
"Enforce authoritarian type-checking when inserting new content into tables" lol you can really get into the mindset by reading these threads. I think having the strict defaultable at the database level seems like a good option, maybe they think everyone will pile into the new default and leave the old databases in a state of decaying disrepair?
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."
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.
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.
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)
Forcing all dates to be cast to a single time zone (UTC) complicates workflows when you’re on the other side of the globe. Many DBs have added a timestamp offset type for this to keep the data in the offset it occurred in.
In a 2021 podcast interview[0], Dr. Hipp noted they've sold zero copies of the TH3 (extensive, proprietary) test suite in SQLite's history.
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.
They sell several niche things, for hopefully hundreds of thousands of dollars, to just a few people each. TH3 is just one instance of the general pattern.
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.
> 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 do not. SQLite date/time functions work with time-values which can be ISO timestamp strings, Julian fractional day numbers, or Unix timestamps. See https://sqlite.org/lang_datefunc.html for the details.
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.
'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
But if you do the function based index, you actually increase the stored size, right?
Seems like a microoptimization to me that I would only do if I really have to.
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.
"SQLite began as a TCL extension that later escaped into the wild." case closed, everything else is a rationalization but who doesn't like a good rationalization every now and then?
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.
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.
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.
Yep. More generally the correct reason to prefer UDP over TCP is the fine-grained control you gain. When you want that and have to use TCP, you're in for a deadly fight against the OS and its TCP/IP stack.
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].
Thanks, I've been using SQLite for a while and I did not realise that FKs are disabled by default! Fortunately not on anything critical, still... Yikes.
The SQLite docs have a great "quirks" page [1], which contains this telling quote:
> 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.
>
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
Sometimes you can build a successful business out of doing so: https://aeron.io/
For sure -- but these are the 'exceptions that prove the rule'. Someone else mentioned HTTP3 and QUIC. For all of these, there are thousands of projects (including multiplayer games and even enterprises) that didn't quite get it right and suffered for it.
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.
> 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.
It's for backwards compatibility, as always. You also need to enable foreign keys or they aren't enforced. You also need to tune for performance (enable WAL, adjust cache size, etc.).
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.
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:
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.
I don't understand what this has to do with strict mode? Yes those types should exist, but the workaround is to use an integer column for bool and a text column for date, whether or not you're using strict mode.
The point of strict mode is that the RDBMS performs data type validation when inserting/updating data. If you use a text column for storing dates, you can store any string in the column, not just valid dates. That's not strict.
A comment will do more than a nonsensical type which is not enforced: it won’t have the wrong affinity, it will spell out what the concrete type is, and there’s no limit to what it can specify.
Domain types would be the best fix, but I do not think legacy tables are the second best.
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".
The serious limitation is that you create a column as date, you don’t understand what sqlite does with it, and you start storing strings in there, at which point everything is confused.
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.
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)
)
Note that the column types here are just to describe to the user what the field should be doing and it's the constraints that actually enforce it. Behind the scenes SQLite still creates two "text (supposedly but whatever)" and one "integer (supposedly but whatever)" columns.
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.
It's probably because SQLite intends to be untyped but also wants the statements to look like standard SQL. This matches their other note about wanting code designed to work with other DBMSes to accidentally work with SQLite too.
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.
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.
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.
I can see that it would be difficult to make changes to db schema types, if there are multiple separate applications with their own release schedule. But this affects schema structure as well, not just its types. I can't even describe how difficult it would be to write apps that work in that scenario—and test it against data that could be written into the db by any previous version of the app. If you truly have this kind of situation at hand, perhaps a document database would be a better fit.
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.
Can't say I've done a SQLite migration before. Changing int col to text in Postgres could be done with a couple of ALTER statements, or more safely, add a new col with a different name and switch over.
> 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.
But one DB one app is fairly common with Postgres too, particularly if you're adhering to a services deliniation. Guess if your code enforces types at DB insert time, the DB doesn't need to, but one of those is more likely to change than the other.
> But one DB one app is fairly common with Postgres too
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.
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.
}
}
If a different code version comes along and, say, changes the schema then:
Schema:
CREATE TABLE foo (
bar TEXT
);
Program:
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"]);
}
}
Perhaps what you are imagining is when the data is provided externally, where the target schema isn't known at compile time? That is a possible use-case, but not what SQLite was primarily designed for and not what we are talking about. If that is what you need that is what strict tables are there for. Different tools for different jobs.
One schema, defined by you, but your codebase is big and you forget what type something is. You're also updating the code while keeping the same SQLite file, as many apps would do, so compatibility across versions is important.
> but your codebase is big and you forget what type something is.
Why 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.
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.
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 :)
I can't replicate atm, but I now think it had to do w scientific notation, not octal - so "1e234..." into a text column. And istr "strict" solving my problem, but...
"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.
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.
IIRC SQLite originated as a Tcl extension. In Tcl at the user level "everything is a string" or a number. So it's logical SQLite would accept values as a string or number. Interestingly a Tcl function defines its own semantics, an input value means whatever the function says it means, perhaps a timestamp. SQLite inherited these attributes, and as many commenters observe, SQLite largely continues to work that way.
Implies documentation is crucial. Fortunately SQLite's documentation is among the best out there.
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!
If I remember correctly mysql also started with a berkeleydb(dbm) storage layer. before myisam then later innodb.
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.
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!
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.
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.
SQLite has to be one of the most stubborn software projects around, in a good way. Everything about it breaks what you learn in school and ignores trends, but it thrives.
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
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.
> 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:
Or in Python: Release notes for 4.1 here: https://sqlite-utils.datasette.io/en/stable/changelog.html#v...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.
Right, the transcript is linked from the issue: https://github.com/simonw/sqlite-utils/issues/787#issuecomme...
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.
What are you trying to achieve with this comment? Does everything need to turn into a sniping contest about whether or not something is ai-produced? This is the world we live in now.
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?
It uses "PRAGMA foreign_keys=0" and "PRAGMA defer_foreign_keys= ON" before running the transformation, then resets those settings afterwards: https://github.com/simonw/sqlite-utils/blob/3f0471701b5f8c7d...
That's the pattern recommended by SQLite here: https://www.sqlite.org/lang_altertable.html#otheralter
Thanks for the nudge, I just added a new test explicitly covering this: https://github.com/simonw/sqlite-utils/commit/d71420065903ff...
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.
I've seen a set of SQL tables designed to mimic "flexible classes". There's a table for the "class", another table defining its "fields", and two other tables defining class instances and related field values (all as varchar).
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.
>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 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".
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.
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.
Wow, SQLite not believing in fail-fast systems is disappointng. Never knew SQLite was JS of SQL!
That doesn't do justice to some of the arguments that SQLite authors are making.
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.
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].
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’
Or even better just SET STRICT_TABLES, or whatever other opt-in feature you want. A couple of such statements in the beginning of your migration script (refactored in a way to be reused across all migration scripts) and you are done. I wouldn't want to learn WTF "2026.1 defaults" are.
I just do it in my code like
def create_table(name, cols): return f"CREATE TABLE {name} ({cols}) STRICT;"
That relies on you knowing about what good settings are though
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.
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.
> 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 read an essay about XML once where the author noted that he got a warm feeling inside whenever he saw the opening tag:
He noted that there were no higher versions. (Though they're now up to 1.1.) But he considered that the inclusion of a version number from the beginning of the standard was a shining example of why they hadn't needed a new version.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)?
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?
> 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?
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.
How do you feel about Apple breaking all the apps on every major update?
Which is the worse surprise, when you update your dependency during development and get a fault, or when a user somehow ends up with a fault because of the previous default?
... 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
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.
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.
I knew most of he issues that you mentioned ... but 'ID re-use in AUTOINCREMENT'?
What the ...?
"Enforce authoritarian type-checking when inserting new content into tables" lol you can really get into the mindset by reading these threads. I think having the strict defaultable at the database level seems like a good option, maybe they think everyone will pile into the new default and leave the old databases in a state of decaying disrepair?
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:
https://github.com/simonw/sqlite-utils/issues/344#issuecomme...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
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)
Thank you. Strange decisions, but not completely baffling then.
Well, I would also like a proper datetime/timestamp datatype that isn't just a string.
Why not store them as integers? Representing dates is a UI responsibility
Forcing all dates to be cast to a single time zone (UTC) complicates workflows when you’re on the other side of the globe. Many DBs have added a timestamp offset type for this to keep the data in the offset it occurred in.
I also want to be able to debug the database in an SQL console with nice looking date-times.
You can already do that: https://sqlite.org/lang_datefunc.html
Just to state the obvious, you can create a view which uses these functions to make your integer date/times readable, and then debugging is easy.
Someone should fork it in Rust using Claude and rename it SQRite.
Strict type all the things.
A large portion of the tests are closed source unfortunately which would make it tough to create a port.
What the hell? This is the first I'm learning of this.
Why did they do that? Is it owned by a private company?
https://www.hwaci.com/
See https://news.ycombinator.com/item?id=23511151
That's one way they make money. That's the reason sqlite exists.
In a 2021 podcast interview[0], Dr. Hipp noted they've sold zero copies of the TH3 (extensive, proprietary) test suite in SQLite's history.
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.
[0] https://corecursive.com/066-sqlite-with-richard-hipp/
[1] https://youtu.be/5zQdYx-fqJg?t=300
They sell several niche things, for hopefully hundreds of thousands of dollars, to just a few people each. TH3 is just one instance of the general pattern.
https://github.com/tursodatabase/turso
Why not ~Zoidberg~ Zig?
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.
> 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.
Only if you're not compiling yourself, otherwise, there's SQLITE_DEFAULT_FOREIGN_KEYS=1
Of course you can store timestamps as integers, but then you lose sqlite's built-in date and time functions.
You do not. SQLite date/time functions work with time-values which can be ISO timestamp strings, Julian fractional day numbers, or Unix timestamps. See https://sqlite.org/lang_datefunc.html for the details.
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.
'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
When you need many booleans on a table, use an integer with bitwise and/or to record them as powers of 2.
I have done this many times. Function-based indexes are necessary if they must be searched.
Why would you do that? To save some bytes in storage?
The largest table where I've done this had over 50 of these booleans.
That's one 64-bit number, or 50 different columns.
But if you do the function based index, you actually increase the stored size, right? Seems like a microoptimization to me that I would only do if I really have to.
For the searched column only?
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.
When the column is an integer column, you don't want strings in it.
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).
"SQLite began as a TCL extension that later escaped into the wild." case closed, everything else is a rationalization but who doesn't like a good rationalization every now and then?
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.
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.
Yep. More generally the correct reason to prefer UDP over TCP is the fine-grained control you gain. When you want that and have to use TCP, you're in for a deadly fight against the OS and its TCP/IP stack.
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].
[1] https://deepplum.com/post-b/
[2] https://en.wikipedia.org/wiki/Mecanum_wheel
Even foreign keys weren't available before 2009, and are still not enabled by default:
https://sqlite.org/foreignkeys.html
Thanks, I've been using SQLite for a while and I did not realise that FKs are disabled by default! Fortunately not on anything critical, still... Yikes.
The SQLite docs have a great "quirks" page [1], which contains this telling quote:
> 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.
1. https://sqlite.org/quirks.html
> 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
Sometimes you can build a successful business out of doing so: https://aeron.io/
For sure -- but these are the 'exceptions that prove the rule'. Someone else mentioned HTTP3 and QUIC. For all of these, there are thousands of projects (including multiplayer games and even enterprises) that didn't quite get it right and suffered for it.
Or, at least, so says the lore.
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.
> 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.
tuning defaults for performance feels much more acceptable than tuning defaults for correctness
It's for backwards compatibility, as always. You also need to enable foreign keys or they aren't enforced. You also need to tune for performance (enable WAL, adjust cache size, etc.).
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.
[1] https://sqlite.org/quirks.html
It wasn't option for the first 20 years of SQLite's existence.
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.
Precisely, this is my experience as well.
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.
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.
> 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
Right, and that's the problem. There should be DATE and BOOL as well, especially in strict mode.
I don't understand what this has to do with strict mode? Yes those types should exist, but the workaround is to use an integer column for bool and a text column for date, whether or not you're using strict mode.
The point of strict mode is that the RDBMS performs data type validation when inserting/updating data. If you use a text column for storing dates, you can store any string in the column, not just valid dates. That's not strict.
Yeah the parse rules for strict tables are annoying but it doesn't change the underlying semantics:
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.
A comment will do more than a nonsensical type which is not enforced: it won’t have the wrong affinity, it will spell out what the concrete type is, and there’s no limit to what it can specify.
Domain types would be the best fix, but I do not think legacy tables are the second best.
A comment is not something I can get programmatically through `sqlite3_column_decltype` or `sqlite3_table_column_metadata`.
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 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".
> some data types are not available, such as Date.
That’s not a type, you just get a numeric-affinity column.
Right, and that's a serious limitation when in strict mode.
The serious limitation is that you create a column as date, you don’t understand what sqlite does with it, and you start storing strings in there, at which point everything is confused.
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.
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.
Note that the column types here are just to describe to the user what the field should be doing and it's the constraints that actually enforce it. Behind the scenes SQLite still creates two "text (supposedly but whatever)" and one "integer (supposedly but whatever)" columns.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
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.
It's probably because SQLite intends to be untyped but also wants the statements to look like standard SQL. This matches their other note about wanting code designed to work with other DBMSes to accidentally work with SQLite too.
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.
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.
I can see that it would be difficult to make changes to db schema types, if there are multiple separate applications with their own release schedule. But this affects schema structure as well, not just its types. I can't even describe how difficult it would be to write apps that work in that scenario—and test it against data that could be written into the db by any previous version of the app. If you truly have this kind of situation at hand, perhaps a document database would be a better fit.
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.
Can't say I've done a SQLite migration before. Changing int col to text in Postgres could be done with a couple of ALTER statements, or more safely, add a new col with a different name and switch over.
> 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.
But one DB one app is fairly common with Postgres too, particularly if you're adhering to a services deliniation. Guess if your code enforces types at DB insert time, the DB doesn't need to, but one of those is more likely to change than the other.
> But one DB one app is fairly common with Postgres too
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.
Even with a statically typed language, two separate codepaths or code versions could write to the same tables and disagree on the types.
How? Consider the following pseudocode:
Schema:
Program: If a different code version comes along and, say, changes the schema then:Schema:
Program: Perhaps what you are imagining is when the data is provided externally, where the target schema isn't known at compile time? That is a possible use-case, but not what SQLite was primarily designed for and not what we are talking about. If that is what you need that is what strict tables are there for. Different tools for different jobs.One schema, defined by you, but your codebase is big and you forget what type something is. You're also updating the code while keeping the same SQLite file, as many apps would do, so compatibility across versions is important.
money_adder.c inserts string: money_viewer.c selects int:> but your codebase is big and you forget what type something is.
Why 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.
2004 called and they want their ignorant bad takes back
They also want this type of joke and misunderstanding SQLite purpose to get "macho programmer" points back.
Reddit called and said they're missing a guy
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 :)
I can't replicate atm, but I now think it had to do w scientific notation, not octal - so "1e234..." into a text column. And istr "strict" solving my problem, but...
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.
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.
Why without Rowid? Are you using non-integer primary keys?
I always define a primary key, integer or otherwise, and prefer the be explicit and consistent.
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
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.
IIRC SQLite originated as a Tcl extension. In Tcl at the user level "everything is a string" or a number. So it's logical SQLite would accept values as a string or number. Interestingly a Tcl function defines its own semantics, an input value means whatever the function says it means, perhaps a timestamp. SQLite inherited these attributes, and as many commenters observe, SQLite largely continues to work that way.
Implies documentation is crucial. Fortunately SQLite's documentation is among the best out there.
“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.”
> If you encounter something that’s not an int, that could be a signal you have a bug in your writers
Which, is something you could have caught before it got written at all if you had your db enforcing your types.
i have no idea why this would be preferable to failing immediately on write
The intent of ANY is obviously that the values be flexible. That’s why it’s there if you need it.
But that is still better than getting a table with typed columns that have been used like ANY columns.
But why aren't you pissed at a database where the columns have types that aren't enforced?
Postgres is flexible, too. Sometimes design choices are just bad, and it's 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!
If I remember correctly mysql also started with a berkeleydb(dbm) storage layer. before myisam then later innodb.
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.
OpenLDAP also originally used BerkeleyDB
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!
IIRC, the project started out as TCL code and it carried that vibe through to what it is today.
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.
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.
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.
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.
[1] https://www.dbpro.app/blog/sqlite-is-all-you-need
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.”
0: https://sqlite.org/quirks.html
I was going to say that really shouldn't be a problem but then I read further:
https://sqlite.org/nulinstr.html
Yeah the fact that length() can't count null containing strings accurately is interesting.
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 want a better alternative for SQLite
really interesting, thanks
[flagged]
[dead]
[flagged]
[dead]
Too many people missing the point entirely and wanting to make SQLite Postgres or Oracle.
SQLite has to be one of the most stubborn software projects around, in a good way. Everything about it breaks what you learn in school and ignores trends, but it thrives.
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
I do understand the mindset, in college, math was pretty authoritarian in nature.
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.
this 100%