A small, cool fact about SQLite is that its columns are flexibly typed. You can store any value in any column! Try it out:
The null type is still special:
The longer story is that column definitions express a "type affinity" and SQLite will try to do implicit conversions to match type affinity e.g. converting strings of numeric characters to integers:
Lots of databases support the type conversion behavior, but SQLite can't do the conversion it powers through and writes the bytes anyways.
I'd be curious to hear more about how this type system came to be. It seems trickier to implement, but potentially useful? And also potentially anti-useful? Are there users who totally eschew column types and actually rely on this behavior for correctness?
As always, the insanely great SQLite docs have a lot more detail.