Modern SQLite: STRICT tables • Michael Uloth

Excerpts from Modern SQLite: STRICT tables by Anton Zhiyanov:

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;

Edit

They check types the same way other DBMS do:

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

Edit

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

Edit

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

Edit

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');

Edit

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.