🔮 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!🥳

• SQLite: Strict Tables

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