2026-09-15·4 min read·Simon

Why SQLite refuses your ALTER TABLE, and the twelve-step rebuild it wants instead

SQLite supports a fraction of ALTER TABLE and fails the rest at run time, in every environment. What it does and does not allow, why DROP COLUMN rewrites the file, and the documented rebuild procedure that replaces the missing statements.

sqlite

migrations

Coming from Postgres, SQLite's ALTER TABLE feels broken. It is not. It is small on purpose, and the rules for what it refuses are consistent once you know them. The trouble is that the refusals happen at run time, and a migration that fails at run time fails in production too.

What ALTER TABLE can do

Rename a table. Rename a column. Add a column, with restrictions. Drop a column, since 3.35, with more restrictions. That is the complete list. There is no ALTER COLUMN, no changing a type, no adding or dropping a constraint, no adding a primary key to an existing table.

The add-column restrictions

ADD COLUMN is fast in SQLite because it does not touch existing rows: the new column is appended to the schema and existing rows read as the default. That is exactly why the restrictions exist. Anything that would require a value per existing row that SQLite can not derive from a constant is refused.

NOT NULL needs a non-null default. Without one the statement fails with Cannot add a NOT NULL column with default value NULL. On every table, including an empty one, so unlike the Postgres version this fails in development too. That is SL001.
PRIMARY KEY and UNIQUE are refused outright. A uniqueness constraint would need to be checked across all rows, and existing rows would all have the same default. SL002.
A default must be a constant. DEFAULT CURRENT_TIMESTAMP is refused, because the stored-default trick only works for a value that is the same for every row.
A STORED generated column can not be added; only VIRTUAL.

The add-column fixes

-- wrong: fails at run time, everywhere
ALTER TABLE users ADD COLUMN tier TEXT NOT NULL;
-- right: a constant default satisfies every existing row without touching it
ALTER TABLE users ADD COLUMN tier TEXT NOT NULL DEFAULT 'free';

-- wrong: refused outright
ALTER TABLE users ADD COLUMN public_id TEXT UNIQUE;
-- right: add the column, then enforce uniqueness with an index
ALTER TABLE users ADD COLUMN public_id TEXT;
CREATE UNIQUE INDEX users_public_id ON users (public_id);

-- wrong: a non-constant default is refused
ALTER TABLE users ADD COLUMN created_at TEXT DEFAULT CURRENT_TIMESTAMP;
-- right: nullable now, populate on insert from the application, or rebuild the table

Why DROP COLUMN rewrites the file

SQLite stores each row as a record with the columns in order. Dropping a column means rewriting every record without it, so DROP COLUMN is a full table rewrite, holding the write lock for the duration. On a phone that is fine. On a server with a multi-gigabyte database file behind a web app it is an outage that looks like a one-line migration. SL006 flags it for that reason. And DROP COLUMN is refused entirely when the column is indexed, part of a constraint, or referenced by a view or trigger.

The twelve-step rebuild

For everything ALTER TABLE will not do, the SQLite documentation prescribes one procedure: create the table you wanted, copy the rows into it, drop the old one, rename. Written out properly it is twelve steps, and the order matters, because most of the steps exist to keep foreign keys, indexes, triggers and views from breaking half way through.

PRAGMA foreign_keys = OFF;          -- 1. outside the transaction
BEGIN;                              -- 2
-- 3. remember indexes, triggers and views on the table
CREATE TABLE users_new (...);       -- 4. the schema you actually want
INSERT INTO users_new SELECT ...    -- 5. copy the rows
  FROM users;
DROP TABLE users;                   -- 6
ALTER TABLE users_new RENAME TO users; -- 7
-- 8. recreate indexes and triggers
-- 9. recreate views
PRAGMA foreign_key_check;           -- 10. must return no rows
COMMIT;                             -- 11
PRAGMA foreign_keys = ON;           -- 12. outside the transaction

The steps people skip are the ones that hurt. Dropping the old table before copying loses the data, SL019. Leaving foreign keys enforced during the rebuild makes step six fail or, worse, cascade. Forgetting the foreign_key_check commits a database with dangling references. And a PRAGMA foreign_keys inside the transaction is silently a no-op, so steps one and twelve have to be outside it, which is the kind of detail that is easy to get wrong and impossible to see in a diff.

Checking it

The SQLite corpus is thirty-five rules and every "SQLite refuses this" claim in it is proven against a real SQLite in the test suite. It runs locally in the free CLI with --engine=sqlite, and it is static only for now: no live schema, no hosted checks. For a migration that is going to fail at run time in every enviroment, static is enough to save the deploy.

The rules behind this post

critical

SQLite

free in the CLI

SL001 ADD COLUMN NOT NULL without a default

SQLite refuses to add a NOT NULL column unless it has a default other than NULL — the statement fails with "Cannot add a NOT NULL column with default value NULL", so the migration stops here in every environment, including the one that matters.

What is wrong

ALTER TABLE orders ADD COLUMN region TEXT NOT NULL;

Do this instead

Give the column a non-NULL default, or add it nullable, backfill, and enforce NOT NULL by rebuilding the table later. In SQLite a default on ADD COLUMN is free: existing rows are not rewritten, the default is read from the schema.

ALTER TABLE orders ADD COLUMN region TEXT NOT NULL DEFAULT 'eu';

Read the rule

Catch this before it ships: npx bolvrk check migration.sql --engine=sqlite runs it locally, no account needed. Sign in for the full corpus, pull-request comments and live-schema checks.

critical

SQLite

free in the CLI

SL002 ADD COLUMN with PRIMARY KEY or UNIQUE

ALTER TABLE ADD COLUMN cannot carry a PRIMARY KEY or UNIQUE constraint — SQLite rejects the statement outright ("Cannot add a PRIMARY KEY column", "Cannot add a UNIQUE column"). The migration fails, and the constraint it wanted still does not exist.

What is wrong

ALTER TABLE orders ADD COLUMN external_id TEXT UNIQUE;

Do this instead

Add the column plain, then create the unique index as its own statement — that is what UNIQUE would have built anyway. A new primary key needs the table rebuilt: create the new table, copy, drop, rename.

ALTER TABLE orders ADD COLUMN external_id TEXT;
CREATE UNIQUE INDEX orders_external_id ON orders (external_id);

Read the rule

Catch this before it ships: npx bolvrk check migration.sql --engine=sqlite runs it locally, no account needed. Sign in for the full corpus, pull-request comments and live-schema checks.

warning

SQLite

free in the CLI

SL006 DROP COLUMN rewrites the table

DROP COLUMN edits the schema and then rewrites every row of the table to purge the column's values — one write transaction that holds the database's single write lock for the length of the copy. It also fails outright if anything else in the schema names the column (an index, a foreign key, a view, a trigger, a generated column) and is unsupported before SQLite 3.35.

What is wrong

ALTER TABLE orders DROP COLUMN legacy_flag;

Do this instead

Stop reading the column in code first, let a release soak, and drop it in a scheduled migration — SQLite's write lock means every other writer waits for the rewrite. If the column is indexed or referenced, drop those objects first or the statement fails.

-- release N: remove every code reference to legacy_flag
-- release N+1, scheduled:
--    DROP INDEX IF EXISTS orders_legacy_flag;
--    ALTER TABLE orders DROP COLUMN legacy_flag;

Read the rule

Catch this before it ships: npx bolvrk check migration.sql --engine=sqlite runs it locally, no account needed. Sign in for the full corpus, pull-request comments and live-schema checks.

critical

SQLite

free in the CLI

SL019 Table rebuild that drops the old table before copying its rows

The rebuild recipe is create, copy, drop, rename. This migration creates the new table, drops the old one and renames the new one onto its name, but never copies the rows across before the DROP. DROP TABLE discards them; the rename brings back the name with an empty table, and nothing in SQLite fails to say so.

What is wrong

CREATE TABLE orders_new (id INTEGER PRIMARY KEY, qty INTEGER NOT NULL);
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;

Do this instead

Copy before dropping: INSERT INTO the new table SELECT from the old, inside the same transaction, so a failed copy rolls the whole rebuild back. If the table is meant to come back empty, suppress the rule with a reason that says so.

BEGIN;
CREATE TABLE orders_new (id INTEGER PRIMARY KEY, qty INTEGER NOT NULL);
INSERT INTO orders_new SELECT id, qty FROM orders;
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;
COMMIT;

Read the rule

Catch this before it ships: npx bolvrk check migration.sql --engine=sqlite runs it locally, no account needed. Sign in for the full corpus, pull-request comments and live-schema checks.