๐ฎ It's not a mystery that SQLite's type system is.. not its strong side. But you probably don't realise the depth of this ocean of shit ๐ฉ๐
โ๏ธ Grab a cup of tea and join me on this exciting journey about some "wonderful" design decisions in software engineering โต๏ธ
๐ So, you want to store timestamps in your database.
Nobody has the time to read manuals. So, after browsing StackOverflow answers for about 5 minutes, you'll come up with a schema like the one below
๐ถ Because software is not doomed (yet), this actually works! Good. It means we can continue with StackOverflow-Driven Development.
๐You may actually find some time to read SQLite manuals in your life. You know, during one of those days while you're waiting for the code compilation to finish and all tweets are already written and sent.
You'll learn that SQLite doesn't actually have the TIMESTAMP
type ๐คฏ
๐ You've read the previous tweet correctly. That's right. There's no TIMESTAMP
type. SQLite only can store:
NULL
INTEGER
REAL
TEXT
BLOB
You see โ no TIMESTAMP
๐
โโ๏ธ Your entire life was a lie ๐
๐งฉ And yet, things somehow work. If you like puzzles, you may guess correctly that time is stored as a value of type TEXT
. And, again, this would be a lucky guess๐
๐ A sidenote. Those strings are in the ISO8601 format. The neat thing about this format is that you can use natural text comparison aka lexicographical sorting to order your data by timestamp even though it's just text.
Aren't computers cool???
๐ท If you haven't got burnout or depression after working in tech, it means that you will continue compiling code and having even more free time to read SQLite manuals.
You'll be asking uncomfortable questions like "How does SQLite decide that TIMESTAMP
should be TEXT
?"๐ค
๐ด Remember kids. Don't ask questions if you're not ready for answers. Especially in tech. ๐
Turns out, SQLite contains many surprises ๐ฉ
The resulting storage type (called affinity) is defined by checking if the type name contains one of the specified strings as substrings ๐คฆโโ๏ธ
I have no words, only emotions (and they are not pleasant).
The Hindley-Milner type system? Don't laugh my socks off! ๐งฆ๐คฃ
How about the Knuth-Morris-Pratt type system?? ๐ค๐ช
๐ง ๐ Once you started to piece together your brain after it had been shattered, you may even try to make sense of this world.
You may even think that TIMESTAMP
must have TEXT affinity obviously!
Prepare to get f*cked so hard, you might even regret becoming a Software Engineer๐จ
๐Turns out, if you read the affinity rules carefully, TIMESTAMP
is not TEXT
. It has the NUMERIC
affinity in fact!
If you're still alive after learning that a TIMESTAMP
is actually NUMERIC
and stores TEXT
inside, congratulations ๐
But that's not the end of the story.
Listen, the thing is... A while ago, some genius decided that a column of the NUMERIC
affinity can store values of any type inside.
What a piece of joke ๐คก
SQLite has 5 different affinities:
๐กTEXT
๐ฃNUMERIC
๐ดINTEGER
๐ REAL
๐ตBLOB
And no, NUMERIC
and INTEGER
are not the same. NUMERIC
is a synonym for ANY (because why not?).
Legend says, if you collect all 5 storage classes under the NUMERIC
affinity in the same column, you'll be able to erase half of your data in an SQLite database with a snap of a finger ๐ก๐ฃ๐ด๐ ๐ต๐ข
๐ฒ๐ If you're missing good old CastFailedException
, SQLite is your database of choice ๐
To summarise, any column of type TIMESTAMP
, TIME
, DATE
, DATETIME
, etc. actually has the NUMERIC affinity and can store anything inside. So you better be careful!
But let's end on a positive note๐ถ
People slowly but surely see the value of strong static typing even if some are still trying to slow down the progress.
Since SQLite version 3.37 or higher, you can define tables in the STRICT mode to live happily!๐ฅณ
That's all for me! Hope you enjoyed this post and had a good laugh ๐ค SQLite is actually amazing despite some design decisions!
Till the next time when I share a story with you about how I learned that it's impossible to have an in-memory DB in SQLite with multiple concurrent read-only queries ๐
This post is a copy of my Twitter thread from May 24, 2022 with a few changes
Top comments (0)