Application Integration & Schema Evolution

Schema Migrations and Database Testing

Schema evolution is a compatibility rollout across old and new application versions, data, replicas, and operational tooling. Versioned, observable expand–migrate–contract steps plus real-engine tests make that rollout reviewable and recoverable.

Migrations are immutable ordered history.
Expand before contract.
Online DDL is conditional.
Roll-forward is often safer than rollback.
Tests need layers.
Fixtures are explicit valid states.

Define migration metadata and the expanded object

CREATE TABLE schema_migrations (
  version VARCHAR(80) PRIMARY KEY,
  checksum VARCHAR(128) NOT NULL,
  applied_at TIMESTAMP NOT NULL,
  applied_by VARCHAR(120) NOT NULL
);

-- V20260711_01: expand; nullable keeps old writers compatible.
ALTER TABLE customers ADD COLUMN email_normalized VARCHAR(320);
CREATE INDEX customers_email_normalized_idx ON customers (email_normalized);
-- Product-specific online/concurrent index syntax has different transaction rules;
-- choose it only after verifying the deployed engine and version.

Expand–migrate–contract deployment

Backfill and validate the explicitly added column

-- Repeat bounded primary-key windows; checkpoint the last customer_id.
UPDATE customers
SET email_normalized = LOWER(email)
WHERE customer_id >= :first_id
  AND customer_id < :next_id
  AND email_normalized IS NULL;

SELECT COUNT(*) AS missing FROM customers WHERE email_normalized IS NULL;
SELECT email_normalized, COUNT(*) AS duplicates
FROM customers
WHERE email_normalized IS NOT NULL
GROUP BY email_normalized HAVING COUNT(*) > 1;
-- Resolve collisions before a later migration adds NOT NULL / UNIQUE semantics.

Database test portfolio
Layer	Proves	Must include
query/unit	mapping and boundary logic	null/empty/error cases
real-engine integration	actual types, constraints, SQL, transactions	production engine/version where feasible
migration path	every supported old schema reaches expected new schema	data-preservation and rerun/failure checks
property/model	invariants across generated operations	sequences, concurrency, shrinking/replay
representative performance	operationally viable rollout/query	scale, skew, locks, log and replica lag