SQLite Is Dynamically Typed

A small, cool fact about SQLite is that its columns are flexibly typed. You can store any value in any column! Try it out:

sqlite> create table t1 (v1 int, v2 varchar(10));
sqlite> insert into t1 values ("abc", "this string is longer than 10 characters!");
sqlite> select * from t1;
abc|this string is longer than 10 characters!
sqlite> select typeof(v1), typeof(v2) from t1;
text|text

The null type is still special:

sqlite> create table t1 (v1 int, v2 varchar(10) NOT NULL);
sqlite> insert into t1 values ("abc", NULL);
Error: NOT NULL constraint failed: t1.v2

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:

sqlite> insert into t1 values("123", "");
sqlite> select typeof(v1) from t1;
integer

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.