Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Prefer strict tables in SQLite

evanhahn.com

Post reply on HN
Print view View original

41–50 of 188 posts

Re: Prefer strict tables in SQLite

#41

Post by ncruces »

masklinn wrote: post #18
mort96 wrote: post #11

Earlier quoted context omitted.

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…

> Instead, you're expected to just use a text column and store a textual timestamp. You can actually use an integer column and store Unix timestamps (or floats for subsecond accuracy). But yes, sqlite has very little types support and its default behaviour is very much unityped / dynamically typed which I also dislike. Same with having to enable foreign keys every time you open a connection.

Only if you're not compiling yourself, otherwise, there's SQLITE_DEFAULT_FOREIGN_KEYS=1

Re: Prefer strict tables in SQLite

#42

Post by ncruces »

e2le wrote: post #21
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…

> The downside of strict tables is that some data types are not available, such as Date. There are only 5 datatypes in sqlite. INTEGER, TEXT, BLOB, REAL, and NUMERIC. https://sqlite.org/datatype3.html

The downside is that you loose the place to store the metadata that a column is supposed to store a date.

Which is why I prefer not to use them.

Re: Prefer strict tables in SQLite

#43

Post by sinfulprogeny »

ezst wrote: post #31
simonw wrote: post #15

Earlier quoted context omitted.

SQLite very rarely changes defaults because of their commitment to backwards compatibility. They don't want software written against SQLite 3.53 to start throwing errors when upgraded to 3.54 because suddenly `CREATE TABLE` is creating strict tables and the rest of the software breaks as a result.

Which seems reasonable. And those who care deeply will have no problem configuring it the specific way they want on their own project. Win-win.

Unless you don't know it exists of course

Re: Prefer strict tables in SQLite

#44

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

Re: Prefer strict tables in SQLite

#45

Post by walthamstow »

sinfulprogeny wrote: post #43
ezst wrote: post #31

Earlier quoted context omitted.

Which seems reasonable. And those who care deeply will have no problem configuring it the specific way they want on their own project. Win-win.

Unless you don't know it exists of course

Even a pure AI naysayer can use it to find obscure SQLite options

Re: Prefer strict tables in SQLite

#46

Post by Asooka »

ezst wrote: post #31
simonw wrote: post #15

Earlier quoted context omitted.

SQLite very rarely changes defaults because of their commitment to backwards compatibility. They don't want software written against SQLite 3.53 to start throwing errors when upgraded to 3.54 because suddenly `CREATE TABLE` is creating strict tables and the rest of the software breaks as a result.

Which seems reasonable. And those who care deeply will have no problem configuring it the specific way they want on their own project. Win-win.

Well no, the software should be configured in its best state by default. Otherwise you run into the case where the user has to read the documentation like a legal contract to find all the footguns that need disabling. If STRICT is strictly better, than that should be the default. The correct approach here would be for the user to pass along a "compatibility version" tag when first connecting, which would set the defaults to whatever was default in that version. That should be something you force each user to set in their source and it should never ever have a "latest" value. It may be too late for sqlite, but if I were designing an API that had to remain stable for decades now, I would put an enum with possible versions in a header and require the user to pick one.

Re: Prefer strict tables in SQLite

#47

Post by boredatoms »

simonw wrote: post #15
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!

SQLite very rarely changes defaults because of their commitment to backwards compatibility. They don't want software written against SQLite 3.53 to start throwing errors when upgraded to 3.54 because suddenly `CREATE TABLE` is creating strict tables and the rest of the software breaks as a result.

The need ‘default sets’ so that as the very first command I can say ‘use 2026.1 defaults’

Re: Prefer strict tables in SQLite

#48

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

Re: Prefer strict tables in SQLite

#49

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

Re: Prefer strict tables in SQLite

#50

Post by panzi »

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!

Well, I would also like a proper datetime/timestamp datatype that isn't just a string.
Post reply on HN