Prefer STRICT tables in SQLite
In short: I prefer strict tables in SQLite because they avoid some datatype problems, such as putting text in number columns. SQLite has a feature that I think is underrated: strict tables . Strict tables help enforce rigid typing, preventing mistakes like putting text into integer columns. I like them, and wrote this post to promote their use! To make a strict table, add to the end of its definition. Like this: That’s it! But what does it do? Broadly, strict tables help enforce rigid types, like other SQL engines do. Most significantly, strict tables keep you from inserting the wrong type into a column. For example, SQLite normally lets you put text into an column, but not with strict tables. Personally, I think it’s a mistake to try to put text in an integer column, or vice-versa. I don’t want SQLite to let me make this error! The same validation happens for s, too. Notably, if a value can be losslessly converted, it will still be accepted. For example, the string can be perfectly converted to an integer, so it’s allowed. These two lines are equivalent, even for a strict table: By default, you can create columns with bogus types. For example, all of these work even though they aren’t valid SQLite datatypes: I think these aren’t what the developer intended. Some of these are typos, some of them are misunderstandings of which datatypes SQLite supports , and some are egregious mistakes. Appending to any of these statements makes them error. In my opinion, that’s the correct behavior! Only , , , , , and are allowed. Strict tables also require a column type, so you can’t do . If you still need a column to be flexible, you can use the datatype. As the name suggests, it allows anything—even in a strict table. I haven’t found a use for this, but maybe you will! I prefer strict tables but I must share a few cons. Not everything is better! I think it’s best to use strictness from the start, but that’s not always possible. Unfortunately, I don’t think there’s a way to a table to make it strict. I think you have to copy the data out of the non-strict table into the strict one. Something like this: Note that this could be tricky if the non-strict table has invalid data! For example, if the old data accidentally contains text in an integer column, you’ll get errors when doing the migration. You’ll probably need to clean the data or cast it . You could make a rule for your codebase that all new tables are strict. That might be useful—at least some of your tables are valid! But it might also mean you have inconsistent validation across your tables, which might be more surprising than having weak validation on all tables. It’s up to you to decide whether this is a good fit for you. SQLite has a whole page called “The Advantages Of Flexible Typing” , where they argue that SQLite’s flexible behavior is good, actually. I hesitate to wade into the controversy of static-versus-dynamic, but I disagree in most cases. I’ve personally encountered many bugs where an unexpected data type caused subtle headaches. I’d much rather these mistakes explode loudly. But it’s worth noting that SQLite’s developers seem not to share my preference for strict tables! They point out a few good uses for flexible tables, such as “a pure key-value store” or “a place to store miscellaneous attributes” of different types. They also mention that you might want to keep the invalid data in some cases, like if you’re directly importing a messy CSV and don’t want to lose any data. I still prefer strict tables, but acknowledge there are some reasonable cases for non-strict ones. (There’s also at least one comment in the SQLite source that calls non-strict tables “legacy” , but I trust that less than the official documentation.) SQLite introduced strict tables in version 3.37.0 , released November 2021. If you’re on an older version of SQLite, you can’t use strict tables. It’s worth noting that old versions of SQLite can’t read databases with strict tables. For example, if you create a strict table in the newest version of SQLite and then try to read that database in SQLite 3.36.0 (before strict tables were added), you’ll get an error—even if the strict table is already in the database. Strict tables are theoretically slower because they have to do a little extra work. For example, they check datatypes when doing an insert or update . But in practice, I don’t think this is an issue. I wrote a hacky script that inserted millions of rows into a table with 100 columns, and there was no obvious difference on multiple machines I tried. The file size on disk was also the same. I didn’t test this thoroughly, so maybe there’s something I missed, but I don’t think strict tables present a performance problem. In fact, one might expect better performance because you won’t be accidentally mismatching SQLite’s column affinities. But again, I haven’t tested this. Personally, I think the pros of strict tables outweigh the cons. I generally prefer when types are rigidly enforced. It squashes a class of mistakes, and help enforce good data integrity. They’re not a panacea, but they’re usually easy to add and go a long way. If there’s a SQLite feature you think is underrated, please tell me .
Database
AI
Open Source
Web Development
Go
Programming
Culture
JavaScript
Performance
DevOps
JSON
Python
Writing
Gaming
Tutorial
Books
Business
TypeScript
Rust