Prefer STRICT tables in SQLiteIn short: I prefer strict tables in SQLite because they avoid some datatype problems, such as putting text in number columns.<br>SQLite has a feature that I think is underrated: strict tables. Strict tables help enforce rigid typing, preventing mistakes like putting text into integer columns. I like them, and wrote this post to promote their use!<br>To make a strict table, add STRICT to the end of its definition. Like this:<br>-CREATE TABLE people (name TEXT);<br>+CREATE TABLE people (name TEXT) STRICT;

That&rsquo;s it! But what does it do?<br>Advantages of strict tables<br>Broadly, strict tables help enforce rigid types, like other SQL engines do.<br>Prevents type mismatches on insert/update<br>Most significantly, strict tables keep you from inserting the wrong type into a column. For example, SQLite normally lets you put text into an INTEGER column, but not with strict tables.<br>-- Non-strict tables let you put anything anywhere.<br>CREATE TABLE people_nonstrict (age INTEGER);<br>INSERT INTO people_nonstrict (age) VALUES ('garbage');<br>-- => works fine

-- Strict tables don't allow that, which I prefer.<br>CREATE TABLE people_strict (age INTEGER) STRICT;<br>INSERT INTO people_strict (age) VALUES ('garbage');<br>-- => error: cannot store TEXT value in INTEGER column

Personally, I think it&rsquo;s a mistake to try to put text in an integer column, or vice-versa. I don&rsquo;t want SQLite to let me make this error!<br>The same validation happens for UPDATEs, too.<br>Notably, if a value can be losslessly converted, it will still be accepted. For example, the string '123' can be perfectly converted to an integer, so it&rsquo;s allowed. These two lines are equivalent, even for a strict table:<br>INSERT INTO people_strict (age) VALUES ('123');<br>INSERT INTO people_strict (age) VALUES (123);

Prevents bogus column types on table creation<br>By default, you can create columns with bogus types. For example, all of these work even though they aren&rsquo;t valid SQLite datatypes:<br>-- SQLite doesn't support these types, but this is all accepted.<br>CREATE TABLE tbl (name GARBAGE);<br>CREATE TABLE tbl (name DATETIME);<br>CREATE TABLE tbl (name JSON);<br>CREATE TABLE tbl (name UUID);<br>CREATE TABLE tbl (name BLOBB);

I think these aren&rsquo;t what the developer intended. Some of these are typos, some of them are misunderstandings of which datatypes SQLite supports, and some are egregious mistakes.<br>Appending STRICT to any of these statements makes them error. In my opinion, that&rsquo;s the correct behavior!<br>-- All of these give errors, which I prefer.<br>CREATE TABLE tbl (name GARBAGE) STRICT;<br>CREATE TABLE tbl (name DATETIME) STRICT;<br>CREATE TABLE tbl (name JSON) STRICT;<br>CREATE TABLE tbl (name UUID) STRICT;<br>CREATE TABLE tbl (name BLOBB) STRICT;

Only INT, INTEGER, REAL, TEXT, BLOB, and ANY are allowed.<br>Strict tables also require a column type, so you can&rsquo;t do CREATE TABLE tbl (name).<br>Still allows flexibility with ANY<br>If you still need a column to be flexible, you can use the ANY datatype. As the name suggests, it allows anything—even in a strict table.<br>CREATE TABLE tbl (value ANY) STRICT;

-- All of these are valid because the column is ANY:<br>INSERT INTO tbl (value) VALUES (123);<br>INSERT INTO tbl (value) VALUES ('text');<br>INSERT INTO tbl (value) VALUES (12.34);<br>INSERT INTO tbl (value) VALUES (X'8647');

I haven&rsquo;t found a use for this, but maybe you will!<br>Disadvantages of strict tables<br>I prefer strict tables but I must share a few cons. Not everything is better!<br>Can&rsquo;t strict-ify an existing table<br>I think it&rsquo;s best to use strictness from the start, but that&rsquo;s not always possible.<br>Unfortunately, I don&rsquo;t think there&rsquo;s a way to ALTER a table to make it strict. I think you have to copy the data out of the non-strict table into the strict one. Something like this:<br>-- 1. Create a new strict table with the same schema<br>CREATE TABLE new_people (name TEXT) STRICT;

-- 2. Copy data (risky if types are wrong!)<br>INSERT INTO new_people SELECT * FROM people;

-- 3. Replace the old table<br>DROP TABLE people;<br>ALTER TABLE new_people RENAME TO people;

Note that this could be tricky if the non-strict table has invalid data! For example, if the old data accidentally contains text in an integer column, you&rsquo;ll get errors when doing the migration. You&rsquo;ll probably need to clean the data or cast it.<br>You could make a rule for your codebase that all new tables are strict. That might be useful—at least some of your tables are valid! But it might also mean you have inconsistent validation across your tables, which might be more surprising than having weak validation on all tables. It&rsquo;s up to you to decide whether this is a good fit for you.<br>The SQLite developers disagree with me<br>SQLite has a whole page called &ldquo;The Advantages Of Flexible Typing&rdquo;, where they argue that SQLite&rsquo;s flexible behavior is good, actually.<br>I hesitate to wade into the controversy of static-versus-dynamic, but I disagree in most cases. I&rsquo;ve...