Zero-downtime Database Migrations in SQLite

This is a full example about how to perform zero-downtime database migrations in SQLite. In this example, the migration is performed in three phases: expand, migrate, and contract.

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.

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

Demonstrate
Demo.sh
Init.sql
Release-v1.sql
Expand.sql
Migrate.sql
Release-v2.sql
Contract.sql
Check.sql

Demonstrate

To demonstrate, run:

sh demo.sh

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

Init.sql

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

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

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;

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;

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-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;

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;

Check.sql

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

SELECT * FROM demo;

Table of Contents