tl;dr zero-downtime database migrations in SQLite using triggers

_README.md

• Zero-downtime: Multiple releases of the code that understand different database schemas can simultaneously write to the database.

• Triggers: Triggers are used to keep old and new schemas in sync while the application undergoes a rolling upgrade.

• Expand-migrate-contract: The migration is performed in three phases. The expand phase creates new columns (and possibly tables), and triggers. The migrate phase moves data from the old column to the new column. The contract phase removes old columns (and possible tables), and triggers.

To demonstrate, run:

sh demo.sh

check.sql

/* Check in on the state of the data. */

SELECT * FROM demo;

contract.sql

/* Drop old tables, columns, indexes, and triggers. */

DROP TRIGGER insert_original_true;
DROP TRIGGER update_original_true;
DROP TRIGGER insert_original_false;
DROP TRIGGER update_original_false;
DROP TRIGGER insert_fixed_true;
DROP TRIGGER update_fixed_true;
DROP TRIGGER insert_fixed_false;
DROP TRIGGER update_fixed_false;

/* This an ALTER TABLE DROP COLUMN implemented for the sake of SQLite */
CREATE TABLE demo_contracted (id INTEGER PRIMARY KEY AUTOINCREMENT, fixed BOOLEAN NULL DEFAULT NULL);
INSERT INTO demo_contracted (id, fixed) SELECT id, fixed from demo;
DROP TABLE demo;
ALTER TABLE demo_contracted RENAME TO demo;

demo.sh

#!/bin/bash
set -e
rm -rf test.db
sqlite3 -echo -bail test.db < init.sql
read -n1 -r -p "Press space to continue..." key
echo
sqlite3 -echo -bail test.db < release-v1.sql
sqlite3 -echo -bail test.db < check.sql
read -n1 -r -p "Press space to continue..." key
echo
sqlite3 -echo -bail test.db < expand.sql
read -n1 -r -p "Press space to continue..." key
echo
sqlite3 -echo -bail test.db < migrate.sql
read -n1 -r -p "Press space to continue..." key
echo
sqlite3 -echo -bail test.db < release-v1.sql
sqlite3 -echo -bail test.db < release-v2.sql
sqlite3 -echo -bail test.db < check.sql
read -n1 -r -p "Press space to continue..." key
echo
sqlite3 -echo -bail test.db < contract.sql
read -n1 -r -p "Press space to continue..." key
echo
sqlite3 -echo -bail test.db < release-v2.sql
sqlite3 -echo -bail test.db < check.sql

expand.sql

/* Create new tables, columns, indexes, and triggers. */

ALTER TABLE demo ADD COLUMN fixed BOOLEAN NULL DEFAULT NULL;

CREATE TRIGGER insert_original_true
    AFTER INSERT
    ON demo
    FOR EACH ROW WHEN NEW.original='true'
BEGIN
    UPDATE demo SET fixed=1 WHERE id=NEW.id;
END;

CREATE TRIGGER update_original_true
    AFTER UPDATE
    ON demo
    FOR EACH ROW WHEN NEW.original='true'
BEGIN
    UPDATE demo SET fixed=1 WHERE id=OLD.id;
END;

CREATE TRIGGER insert_original_false
    AFTER INSERT
    ON demo
    FOR EACH ROW WHEN NEW.original='false'
BEGIN
    UPDATE demo SET fixed=0 WHERE id=NEW.id;
END;

CREATE TRIGGER update_original_false
    AFTER UPDATE
    ON demo
    FOR EACH ROW WHEN NEW.original='false'
BEGIN
    UPDATE demo SET fixed=0 WHERE id=OLD.id;
END;

CREATE TRIGGER insert_fixed_true
    AFTER INSERT
    ON demo
    FOR EACH ROW WHEN NEW.fixed=1
BEGIN
    UPDATE demo SET original='true' WHERE id=NEW.id;
END;

CREATE TRIGGER update_fixed_true
    AFTER UPDATE
    ON demo
    FOR EACH ROW WHEN NEW.fixed=1
BEGIN
    UPDATE demo SET original='true' WHERE id=OLD.id;
END;

CREATE TRIGGER insert_fixed_false
    AFTER INSERT
    ON demo
    FOR EACH ROW WHEN NEW.fixed=0
BEGIN
    UPDATE demo SET original='false' WHERE id=NEW.id;
END;

CREATE TRIGGER update_fixed_false
    AFTER UPDATE
    ON demo
    FOR EACH ROW WHEN NEW.fixed=0
BEGIN
    UPDATE demo SET original='false' WHERE id=OLD.id;
END;

init.sql

/* Initialize the database with tables and indexes. */

CREATE TABLE demo (id INTEGER PRIMARY KEY AUTOINCREMENT, original TEXT NULL);

migrate.sql

/* Migrate data from the old tables and old columns to the new tables and new
 * columns. */

UPDATE demo SET fixed=1 WHERE original='true';
UPDATE demo SET fixed=0 WHERE original='false';

release-v1.sql

/* Version 1 of our app, which reads and writes to it's own schema. */

INSERT INTO demo (original) VALUES ('true');
INSERT INTO demo (original) VALUES ('false');
UPDATE demo SET original='false' WHERE id=1;
UPDATE demo SET original='true' WHERE id=2;

release-v2.sql

/* Version 2 of our app, which reads and writes to it's own schema. It knows
 * nothing of the schema used by version 1. */

INSERT INTO demo (fixed) VALUES (0);
INSERT INTO demo (fixed) VALUES (1);
UPDATE demo SET fixed=0 WHERE id=2;
UPDATE demo SET fixed=1 WHERE id=1;

This was super useful. But I don't think it's actually zero-downtime the way its written?

/* This an ALTER TABLE DROP COLUMN implemented for the sake of SQLite */
CREATE TABLE demo_contracted (id INTEGER PRIMARY KEY AUTOINCREMENT, fixed BOOLEAN NULL DEFAULT NULL);
INSERT INTO demo_contracted (id, fixed) SELECT id, fixed from demo;
DROP TABLE demo;
ALTER TABLE demo_contracted RENAME TO demo;

If you keep receiving simultaneous requests while this is running, somethings probably going to break? Either/or the triggers no longer exist so you'll just get wrong data migrated.

Does sqlite use snapshot isolation consistency level for

INSERT INTO demo_contracted (id, fixed) SELECT id, fixed from demo;

?