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

161–170 of 188 posts

Re: Prefer strict tables in SQLite

#161

Post by HexDecOctBin »

dfabulich wrote: post #79

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

SQLite did come from TCL everything-is-a-string world, so this attitude is not surprising. TCL makes for a very good shell language (much better than Bash or Batch), but a rigid systems language it is not.

Re: Prefer strict tables in SQLite

#162

Post by masklinn »

mort96 wrote: post #139
masklinn wrote: post #18

Earlier quoted context omitted.

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

Of course you can store timestamps as integers, but then you lose sqlite's built-in date and time functions.

You do not. SQLite date/time functions work with time-values which can be ISO timestamp strings, Julian fractional day numbers, or Unix timestamps. See https://sqlite.org/lang_datefunc.html for the details.

Re: Prefer strict tables in SQLite

#163

Post by cowboylowrez »

bhaak wrote: post #152
lenkite wrote: post #138

Earlier quoted context omitted.

SQLite has a LOT of footguns that one only discovers over time. Dynamically typed by default, Off-by-Default foreign keys, ID re-use in AUTOINCREMENT, WAL Mode needing explicit enabling to ensure readers are not blocked, double-quote/single-quote issues, positional placeholders & named parameters issues, no TIMESTAMP type in 2026 despite being a CORE feature in SQL-92 standard, etc.

I knew most of he issues that you mentioned ... but 'ID re-use in AUTOINCREMENT'? What the ...?

"Enforce authoritarian type-checking when inserting new content into tables" lol you can really get into the mindset by reading these threads. I think having the strict defaultable at the database level seems like a good option, maybe they think everyone will pile into the new default and leave the old databases in a state of decaying disrepair?

My only experience with versionitus type things is with microsoft sql and other "enterprisey" things that used it and its hard enough without huge upgrade blockers like text in an integer field lol still they're willing to add the default to the table create statement. I'm thinking that sqlite folks feel that things like type safety, database consistency are disliked authoritarian attributes, and I'm sure thats a consideration in letting old code touch databases that old code doesn't understand.

Its wierd that they didn't like having a pragma for any new table creation even at the database level but they allow this problem factory:

  "Because of a quirk in the SQL language parser, versions of SQLite prior to 3.37.0 can still read and write STRICT tables if they set "PRAGMA writable_schema=ON" immediately after opening the database file, prior to doing anything else that requires knowing the schema."
https://github.com/simonw/sqlite-utils/issues/344#issuecomme...

I guess for ID reuse a rationalization could be that theres only so many integer values that can fit into a certain number of bytes haha

Re: Prefer strict tables in SQLite

#164

Post by cowboylowrez »

frollogaston wrote: post #106
coldtea wrote: post #93

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

SQLite has to be one of the most stubborn software projects around, in a good way. Everything about it breaks what you learn in school and ignores trends, but it thrives. First time I used it was in high school, when I was a newbie to C and didn't know how to link in libraries, and SQLite was the only thing that offered all the code as a single .c file https://sqlite.org/amalgamation.html

I do understand the mindset, in college, math was pretty authoritarian in nature.

Re: Prefer strict tables in SQLite

#165

Post by cowboylowrez »

wvenable wrote: post #132
sgarland wrote: post #102

Wait until you read about its quirks [0]. My favorite: “NUL characters (ASCII code 0x00 and Unicode \u0000) may appear in the middle of strings in SQLite. This can lead to unexpected behavior.” 0: https://sqlite.org/quirks.html

I was going to say that really shouldn't be a problem but then I read further: https://sqlite.org/nulinstr.html

Yeah the fact that length() can't count null containing strings accurately is interesting.

Re: Prefer strict tables in SQLite

#166

Post by fauigerzigerk »

ivanjermakov wrote: post #142
dfabulich wrote: post #79

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

Wow, SQLite not believing in fail-fast systems is disappointng. Never knew SQLite was JS of SQL!

That doesn't do justice to some of the arguments that SQLite authors are making.

In Postgres, if you insert a real number into an int column, the data gets rounded and stored as int. In SQLite, the data is inserted as real.

Neither approach is fail-fast.

Re: Prefer strict tables in SQLite

#167

Post by ncruces »

chrismorgan wrote: post #125

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

Precisely, this is my experience as well.

If you use strict tables with my Go SQLite driver, you'll get worse support for bool/date/time columns than otherwise.

It's still unfortunate though that typing a column DECIMAL triggers numeric affinity, which destroys decimal numbers stored as strings.

Re: Prefer strict tables in SQLite

#169

Post by throwlifeaway »

simonw wrote: post #95

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

> This inspired me to add a feature to my sqlite-utils Python library

"me" == "ChatGPT", apparently:

> Can the .transform() internal Python method turn a non-strict table into a strict table?

> No. Table.transform() preserves the table’s existing strictness; it cannot change it. Its signature has no strict= parameter

> add an optional strict= boolean parameter to the transform() method - if it is None (the default) then the strict is not changed, otherwise True means change to strict and False means change to non strict. Implement with red/green TDD and uv run pytest -k

https://gist.github.com/simonw/ab8256b81646ad967a601975e206d...

I appreciate the transparency, at least.

Re: Prefer strict tables in SQLite

#170

Post by pettijohn »

eternauta3k wrote: post #135
pettijohn wrote: post #86

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

Why without Rowid? Are you using non-integer primary keys?

I always define a primary key, integer or otherwise, and prefer the be explicit and consistent.
Post reply on HN