Hacker News Reader

Comment by mort96 | Hacker News Reader

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 timestamp types. Instead, you're expected to just use a text column and store a textual timestamp. Even worse, instead of using ISO, the standard date time functions produce strings on the form "yyyy-mm-dd HH:MM:SS" which you're just supposed to assume are in UTC. Why not at least give us "yyyy-mm-ddTHH:MM:SSZ"? Or, you know, a proper space efficient timestamp data type.

A truly great project, with some truly baffling design decisions.

View on HN

> 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

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.

If every time the SQLite team added a better default for something they changed it, we'd be at SQLite 11 by now and every application would contain at least six incompatible versions of SQLite.

'0' and '1', while not ideal, seems fine to maintain? There is not even a true SQLite boolean type.

Then again, I have been subjected to Oracle nonsense for too long and have had to accept all of the boolean alternatives: 0,1,'0','1',Y,N,y,n,YES,NO,T,F, etc

When you need many booleans on a table, use an integer with bitwise and/or to record them as powers of 2.

I have done this many times. Function-based indexes are necessary if they must be searched.

Why would you do that? To save some bytes in storage?

The largest table where I've done this had over 50 of these booleans.

That's one 64-bit number, or 50 different columns.

But if you do the function based index, you actually increase the stored size, right? Seems like a microoptimization to me that I would only do if I really have to.

For the searched column only?

If you are greatly concerned, SQL Server and the Sybase database from which it emerged have a native boolean data type.

Another benefit of this scheme is that adding another boolean means using the next power of 2 in the existing integer, assuming room remains. No new column necessary.

When the column is an integer column, you don't want strings in it.

the SQLite team explains their preference for dynamic types here: https://sqlite.org/flextypegood.html

(please note that I personally strongly prefer static types, but I still found this an interesting read).

"SQLite began as a TCL extension that later escaped into the wild." case closed, everything else is a rationalization but who doesn't like a good rationalization every now and then?

If you are using SQLite as an embedded database, which seems to be SQLite's primary use-case, why wouldn't you prove statically that you are not accidentally inserting the wrong type? Runtime checks are unnecessary overhead.

Runtime validation is there to enable when using SQLite in other ways.

Reply on news.ycombinator.com