SQLite type system is the wooorst
Tags: sqlite, rant, funny, sql
Published: 2023-03-11 14:05:16

🔮 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 ⛵️

![SQLite types are the wooooorst](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ot6rgpdvroaypfp7jmtc.png)

🕗 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

![SQLite schema example](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6up2oibs631t1w8053g2.png)

🍶 Because software is not doomed (yet), this actually works! Good. It means we can continue with StackOverflow-Driven Development.

![SQLite usage example](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0affv4avy834diufl46x.png)

🔞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 🎂

![SQLite types](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d1xt89rdyb7be27pln7h.png)

🧩 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🍀

![Another SQLite example](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xivumucivi09794ya3ei.png)

> 📜 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???

![Don't store times in SQLite](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/orzxpa45nrw73vcmqvz4.png)

👷 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`?"🤔

![Is this meme](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4bnv4pqd70iwp31ply71.png)

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

{% twitter 1361022421021696003 %}

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 🤡

![Another SQLite example](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/920fvcqzu69nmr9624rq.png)

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

![Stick meme](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tejlwt0y7pppcgqbb1kj.png)

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 🟡🟣🔴🟠🔵🟢

![Stones of eternity](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/le9ods6fcygd7bcovcmp.png)

🚲🏏 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!

![Let's see who this really is](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kaokvmx0auhtrm15p7q1.png)

<hr>

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](https://www.sqlite.org/stricttables.html)

<hr>

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 👋

![Skeletor disturbing facts](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qywtl3td0sbwpevzbiqj.png)


> This post is a copy of my Twitter thread from May 24, 2022 with a few changes
>
> {% twitter 1529010669642727424 %}