Migrating to SQLite STRICT tables: mostly boring, sometimes not

SQLite’s famous flexibility with types is both its charm and its curse. A column declared INTEGER will accept a string. A NUMERIC column will happily take "not a number" and store it as text. For years this was a feature we used to survive schema evolution without migrations. Last month we decided it was time to grow up.

STRICT tables, introduced in SQLite 3.37 (2021), opt into actual type enforcement. I migrated a schema that’s been in production for 9 years. Here are the surprises.

The plan

Run through every table, add STRICT to the end of the CREATE, rebuild the table with a copy-rename pattern. SQLite doesn’t have ALTER TABLE ... SET STRICT, so you:

CREATE TABLE users_new (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  is_active INTEGER NOT NULL CHECK (is_active IN (0, 1))
) STRICT;

INSERT INTO users_new SELECT * FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

Boring. Works great. Except when it doesn’t.

Surprise 1: storing bools as “true”/“false” strings

One table had a column declared BOOLEAN. SQLite treats that as NUMERIC affinity. Over the years, at least four different code paths had inserted values:

1 / 0 (correct)
"true" / "false" (strings)
"1" / "0" (numeric strings)
true / false (Python booleans, which got converted to 1/0 by psycopg… wait, this is SQLite, that’s sqlite3 module in Python which does the right thing but)

The STRICT rebuild failed on rows with actual string values. I had to clean them up first:

UPDATE users
SET is_active = CASE
  WHEN is_active IN ('1', 'true', 'True', 1) THEN 1
  WHEN is_active IN ('0', 'false', 'False', 0) THEN 0
  ELSE 0
END;

Fun data archaeology. This is the kind of thing you don’t know until you try.

Surprise 2: integer columns with floats

A usage_bytes INTEGER column had, over time, collected some floating-point values. Our billing code had been computing usage * rate and storing the result, and at some point the product of two integers became a float because of a division rounding error. INTEGER in non-STRICT happily stored 4.0. STRICT rejects it.

Cleanup:

UPDATE billing_records
SET usage_bytes = CAST(usage_bytes AS INTEGER);

Also caught a bug while doing this — a few rows had fractional usage that were being truncated silently. Probably dollars of impact total, but the fact that we didn’t know was uncomfortable.

Surprise 3: ANY in STRICT tables

STRICT tables introduce a new type: ANY. It’s the one escape hatch. A column declared ANY in a STRICT table stores whatever you put into it, no coercion. We have exactly one column like this — an event_payload that genuinely can be any JSON-or-binary blob. We migrated it to ANY rather than trying to force it into TEXT or BLOB.

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  kind TEXT NOT NULL,
  payload ANY,
  recorded_at INTEGER NOT NULL
) STRICT;

This is weirder than it looks. An ANY column still returns what you put in (no coercion), but it also doesn’t have a type affinity, which affects indexing and comparisons in subtle ways. Don’t use ANY unless you actually need it.

Surprise 4: rebuild is slow on big tables

The copy-rename rebuild on a 4GB table took 18 minutes and held a write lock the whole time. For production systems with online traffic, that’s not OK. We took downtime for the rebuild — it was a weekend job on a maintenance window.

If you can’t take downtime, you can dual-write to a new table for a while and switch over once caught up. More engineering, more risk. We opted for the window.

What we got out of it

Two real bugs found and fixed (the float in usage_bytes, the boolean string drift).
A schema that matches the types the application actually expects.
Downstream tools (analytics exports, BI) have clean types now. No more “is this an integer or a string?” in the consumer.

Was it worth it for a 9-year-old schema? Probably yes, but marginally. If I were starting a new SQLite project today I would use STRICT from day one. Migrating legacy schemas is the kind of task that’s valuable but not urgent — it sits on your list for years.

Reflection

The value of typed columns isn’t “my code will crash if I insert the wrong type.” The value is “my code can’t accidentally store the wrong type and then forget about it for 3 years.” STRICT is the right default. It’s mildly embarrassing that SQLite made it opt-in. But here we are, and it’s here now, and it’s worth turning on.