Prefer strict tables in SQLite

Article URL: https://evanhahn.com/prefer-strict-tables-in-sqlite/

Comments URL: https://news.ycombinator.com/item?id=48873940

Points: 303

# Comments: 148

Hacker News 讨论

305 points · 150 comments · 查看原帖

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