Lab: deploy a backward-compatible database change

Outcome

Release a training application through an expand–migrate–deploy–contract sequence. You will create a SQLite database, add a nullable column that old and new code can both tolerate, verify the migration before traffic moves, deliberately attempt an unsafe ordering, and recover without relying on a destructive rollback.

Before you begin

Use a disposable repository and training environment. SQLite keeps the data local; the release rule generalizes to managed databases: test backup/restore and the provider’s migration semantics separately before production. Do not run destructive SQL against shared or production data.

Create migrations/001_create.sql:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_name TEXT NOT NULL);
INSERT INTO orders (customer_name) VALUES ('Ada');

Create migrations/002_expand.sql:

ALTER TABLE orders ADD COLUMN delivery_note TEXT NULL;

Create scripts/migrate.py:

import pathlib, sqlite3, sys
database = sys.argv[1]
connection = sqlite3.connect(database)
for migration in sorted(pathlib.Path('migrations').glob('*.sql')):
    connection.executescript(migration.read_text())
connection.commit()
print(connection.execute('PRAGMA table_info(orders)').fetchall())

Do

1. Create and inspect the v1 training database:

   mkdir -p training-release && cd training-release
   python3 ../scripts/migrate.py app.db
   sqlite3 app.db 'SELECT id, customer_name FROM orders;'

2. Treat 002_expand.sql as an expand migration. It adds a nullable column, so old application code can still insert orders and new code can populate delivery_note. Apply it, then verify the schema and both reads:

   python3 ../scripts/migrate.py app.db
   sqlite3 app.db "INSERT INTO orders(customer_name) VALUES ('Grace');"
   sqlite3 app.db "UPDATE orders SET delivery_note='side door' WHERE customer_name='Ada';"
   sqlite3 app.db 'SELECT id, customer_name, delivery_note FROM orders;'

3. Place the migration before application deployment in a protected deployment job. It must stop when schema verification fails and record deployment history against a training environment:

   - deployment: training_database_release
     environment: training-database
     pool:
       vmImage: ubuntu-latest
     strategy:
       runOnce:
         deploy:
           steps:
           - checkout: self
           - bash: python3 scripts/migrate.py training-release/app.db
             displayName: Expand schema
           - bash: sqlite3 training-release/app.db "SELECT delivery_note FROM orders LIMIT 1;"
             displayName: Verify new schema before application deploy
           - bash: ./scripts/deploy-training-app.sh
             displayName: Deploy compatible application

4. Run the job only in the training environment. Capture the migration output, schema query, application health check, environment approval/history, and release identifier. Keep a backup/export created before the migration according to the database provider’s tested recovery procedure.

Verify

Before deploying the compatible application, confirm all four observations:

1. PRAGMA table_info(orders) shows delivery_note and its nullable state.
2. The old insert (customer_name only) succeeds after the expand migration.
3. A new-version write and read of delivery_note succeeds.
4. The deployment history shows that schema verification completed before the application deployment step.

You should now understand: an expand migration is safe only when both old and new application versions can operate against the observed schema; a successful SQL command alone is not enough release evidence.

Break and fix

1. In a copy of the database, try a contract change first: make the new field required or remove a field old code still reads. Run the old-code insert/read check. It should fail or show incompatibility. Discard that copy; do not try to hide the failure with a broad exception.
2. Introduce a syntax error into a copied migration. The job must stop at Expand schema, before deploy-training-app.sh. Correct the migration, rebuild the disposable database from the known baseline, then rerun. Never assume a failed migration left a database in a known state without provider-specific evidence.
3. Simulate an application failure after a successful expand migration. Roll back the application artifact or disable the feature path; keep the additive column. Contract only after every running version no longer depends on the old schema and you have a tested recovery plan.

Scenario practice

1. Configure: A new column needs a value for all existing rows. Which staged steps add it, backfill it, validate it, and only later enforce a constraint?
2. Diagnose: A deployment fails after schema migration but before traffic switches. Which evidence distinguishes a migration failure, a deployment failure, and a health-check failure?
3. Distinguish: Contrast application rollback, feature-flag rollback, database restore, and schema contract. Which action avoids data loss after an additive migration?

Clean up

Remove the disposable training-release directory and database. Delete the training environment and pipeline artifacts only when no other training lab uses them. Keep no database dump containing real data.

Sources and re-audit

• Azure Pipelines deployment jobs, checked 2026-08-22.
• Azure Pipelines environments, checked 2026-08-22.

Re-audit when deployment-job, environment approval, or the chosen database provider’s migration/recovery behavior changes.

Related

Workload · Feature flags · Release path · All labs