DEV Community

Dmitrii Kovanikov
Dmitrii Kovanikov

Posted on

SQLite type system is the wooorst

๐Ÿ”ฎ 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

๐Ÿ•— 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

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

SQLite usage example

๐Ÿ”ž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

๐Ÿงฉ 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

๐Ÿ“œ 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

๐Ÿ‘ท 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

๐Ÿ‘ด 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 ๐Ÿคก

Another SQLite example

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

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

๐Ÿšฒ๐Ÿ 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


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 ๐Ÿ‘‹

Skeletor disturbing facts

This post is a copy of my Twitter thread from May 24, 2022 with a few changes

Top comments (0)