Prefer strict tables in SQLite

Post by e2le » Sat, Jul 11, 2026, 7:34 PM UTC

petilon wrote: post #16

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 applicati…

Post by 3eb7988a1663 » Sat, Jul 11, 2026, 7:35 PM UTC

mort96 wrote: post #11

jll29 wrote: post #9

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!

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…

Post by [deleted] » Sat, Jul 11, 2026, 7:40 PM UTC

rogerbinns wrote: post #13

tuvix wrote: post #4

Earlier quoted context omitted.

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…

Post by mb7733 » Sat, Jul 11, 2026, 7:44 PM UTC

tuvix wrote: post #4

tehlike wrote: post #2

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

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)

Post by formerly_proven » Sat, Jul 11, 2026, 7:49 PM UTC

ahartmetz wrote: post #24

jll29 wrote: post #9

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!

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.