First contact — brandur.org

I’ve been playing around with SQLite the last couple of days. I thought I knew a little about SQLite, but I didn’t, and am getting my remedial education through an accelerated gauntlet. Some of what I’ve learned of its quirks has left me reeling.

SQLite doesn’t have data types on columns. Data types (and there are only five) are on values only, so anything can go anywhere.

If you ask for a column with an unsupported/non-existent type, it happily does the wrong thing without warning or error. I was raising a schema like CREATE TABLE my_table (id bigserial, messages jsonb[]), which seemed to be working, so I mistakenly thought for the first day that SQLite supported serials and arrays. It does not.

There was a lot of recent fanfare about SQLite’s new support for jsonb. Unlike in Postgres, jsonb is not actually a data type, but rather a format that’s input and output to and from built-in jsonb* functions. When persisted, it’s one of the big five: blob.

Other fairly critical types are also missing. e.g. There’s nothing like timestamptz. If you want a date/time, you store is as a Unix timestamp integer or ISO8601-formatted string, and a number of built-in functions are provided to work with those.

Leave a Comment