Prefer strict tables in SQLite

tehlike

It really should be default, but it isn't due to backward compatibility (i assume).

cdmckay

I would’ve thought this was the default.

itsthecourier

about the use of ANY, that's perfect for tracking changes on an audit table per field

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!

Eswo

really interesting, thanks

moron4hire

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.

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.

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. https://sqlite.org/lang_createtable.html#ckconst

dzonga

the only thing that sucks about SQLite is migrations.

bch

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.

sherburt3

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.

frollogaston

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.

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.

quotemstr

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.

nektro

thanks! i have seen the sqlite quirks page before but just added this to my queries thanks to you

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.

pettijohn

CREATE TABLE ... STRICT WITHOUT ROWID is my default, I don't know why I'd ever do otherwise.

coldtea

Too many people missing the point entirely and wanting to make SQLite Postgres or Oracle.

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...