Why must the new table be filled before the old one is dropped in a SQLite rebuild?

Table rebuild that drops the old table before copying its rows

Critical: SQLite refuses the statement, so the migration fails here.

What happens

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.

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

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

The safe pattern

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;

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.

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

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

PRAGMA foreign_keys = OFF;
BEGIN;
CREATE TABLE orders_new (id INTEGER PRIMARY KEY, qty INTEGER NOT NULL);
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;
PRAGMA foreign_key_check;
COMMIT;
PRAGMA foreign_keys = ON;

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;

CREATE TABLE orders_new (id INTEGER PRIMARY KEY, qty INTEGER NOT NULL, region TEXT);
INSERT INTO orders_new (id, qty, region) SELECT o.id, o.qty, w.region FROM orders AS o JOIN warehouses AS w ON w.id = o.warehouse_id;
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;

DROP TABLE orders;
ALTER TABLE orders_staging RENAME TO orders;

How to check locally

npx bolvrk check migration.sql --engine=sqlite