Why I Prefer Strict Tables in SQLite for Better Data Integrity
I advocate for using strict tables in SQLite to enforce rigid typing and prevent common mistakes like inserting text into integer columns. By adding the STRICT keyword, you can catch type mismatches early and avoid bogus column definitions. While this feature requires SQLite 3.37.0+ and cannot be applied to existing tables easily, I believe the benefits of catching errors loudly far outweigh the minor limitations.
I don't want SQLite to let me make this error!
- simonw
> Unfortunately, I don’t think there’s a way to ALTER a table to make it strict. I think you have to copy the data out of the non-strict table into the strict one.
This inspired me to add a feature to my sqlite-utils Python library and CLI tool, so you can now use it to transform non-strict tables to strict (and vice-versa) like this:
uvx sqlite-utils transform data.db mytable --strict
Or in Python:
import sqlite_utils
db = sqlite_utils.Database("data.db")
db.table("mytable").transform(
strict=True
)
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...
- dfabulich
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.
- jll29
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!
- ezekiel68
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.
- chrismorgan
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.
- petilon
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.
- Cyberdog
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.
- blixt
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.