Modern SQLite: STRICT tables

I'm starting a series of short notes about handy SQLite features you might not have heard about. The first one is about "strict" tables.

As you probably know, SQLite's type system is very flexible (some people even call SQLite the JavaScript of databases) — you can store any value in any column type (e.g. create an INTEGER column and store text values there, or a REAL with blob values).

Some people love SQLite for its flexibility, others hate it for the same reason. So at some point the SQLite authors introduced STRICT tables:

create table people (
  id integer primary key,
  name   text,
  salary real
) strict;

ok

They check types the same way other DBMS do:

insert into people (name, salary)
values ('Alice', 100);

ok

insert into people (name, salary)
values ('Bob', 90.5);

ok

insert into people (name, salary)
values ('Cindy', 'decent');

Runtime error: cannot store TEXT value
in REAL column people.salary

Even with strict tables, you can still explicitly declare a column as ANY — such columns can hold values of any type. So you can have the best of both worlds — strict type checking and type flexibility:

create table people (
    id integer primary key,
    name   text,
    salary any
) strict;

insert into people (name, salary)
values ('Alice', 100);

insert into people (name, salary)
values ('Bob', randomblob(10));

insert into people (name, salary)
values ('Cindy', 'decent');

ok

Available since SQLite 3.37 (Nov 2021).

Documentation

──

Interactive examples in this post are powered by codapi — an open source tool I'm building. Use it to embed live code snippets into your product docs, online course or blog.

★ Subscribe to keep up with new posts.