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 NULLneeds a non-null default. Without one the statement fails withCannot 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 KEYandUNIQUEare 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_TIMESTAMPis refused, because the stored-default trick only works for a value that is the same for every row. - A
STOREDgenerated column can not be added; onlyVIRTUAL.
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.
Originally published at bolvrk.com. Bolvrk is a deterministic checker for database migrations and committed credentials: free CLI, GitHub App and Action.
