Is it safe to rename a column or table in SQLite during a deploy?

RENAME COLUMN or RENAME TABLE during a deploy

Warning: it works, but holds the single write lock or breaks the previous release.

What happens

A rename is instant in SQLite, and that is the problem: the moment it commits, every process still running the previous release fails on the old name. Views and triggers are rewritten to the new name, but application SQL is not.

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 RENAME COLUMN qty TO quantity;

The safe pattern

ALTER TABLE orders ADD COLUMN quantity INTEGER;
-- dual-write from the application; backfill in batches
-- later release, after readers moved:
--    ALTER TABLE orders DROP COLUMN qty;

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.

ALTER TABLE orders RENAME COLUMN qty TO quantity;

DROP TABLE orders;
ALTER TABLE orders_staging RENAME TO orders;

ALTER TABLE orders RENAME TO purchases;

PRAGMA foreign_keys = OFF;
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;
PRAGMA foreign_key_check;
COMMIT;
PRAGMA foreign_keys = ON;

CREATE TABLE tmp_orders (id INTEGER PRIMARY KEY);
ALTER TABLE tmp_orders RENAME COLUMN id TO order_id;

How to check locally

npx bolvrk check migration.sql --engine=sqlite