Does DROP COLUMN rewrite the table in SQLite?
DROP COLUMN rewrites the table
Warning: it works, but holds the single write lock or breaks the previous release.
What happens
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.
Why it is dangerous on a populated table
SQLite holds one write lock for the whole database, so anything that rebuilds a table blocks every writer for as long as the copy takes, and that time grows with the table.
Fires on
ALTER TABLE orders DROP COLUMN legacy_flag;The safe pattern
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;Fixtures
The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.
Fires (1)
ALTER TABLE orders DROP COLUMN legacy_flag;Stays silent (2)
ALTER TABLE orders ADD COLUMN legacy_flag INTEGER;CREATE TABLE orders (id INTEGER PRIMARY KEY, legacy_flag INTEGER);
ALTER TABLE orders DROP COLUMN legacy_flag;How to check locally
SQLite support is in beta: this rule runs locally in the free CLI, static only, and not in the hosted service yet. No install, nothing leaves your machine:
npx bolvrk check migration.sql --engine=sqlite