Modern SQLite: 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

──

P.S. 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.