Skip to main content

Database change management: evolutionary design in regulated environments

Database changes are often the most feared deployments in banking. An organisation may deploy application code twenty times a day yet change its schema once a quarter. That quarterly release then becomes a multi-week exercise involving a Change Advisory Board, a five-page impact assessment, three reviewers, and a 2am Sunday maintenance window. The concern is rational: a bad migration can corrupt data, break referential integrity, or produce incorrect calculations that flow into regulatory reports. The usual response is counterproductive. Making database changes rare and large makes each one more dangerous.

The experience of leading engineering teams at a Tier-1 bank reinforced that the path to safe database changes is the same path that makes application deployments safe: make them small, frequent, automated, and reversible. Scott Ambler and Pramod Sadalage articulated this in Refactoring Databases (2006), arguing that database schemas should evolve incrementally through small, well-tested migrations rather than through large, infrequent releases. Martin Fowler reinforced this with his essay on Evolutionary Database Design. The principle is straightforward: treat your database schema as code, version it, test it, and deploy it through the same CI/CD pipeline as your application. In regulated environments this approach strengthens your controls, because every change is versioned, traceable, and automatically validated.

Why database change management matters​

Database changes must be safe, repeatable, and recoverable. Financial services adds four hard constraints:

  • Data integrity: financial data must be accurate to the cent. A migration that silently truncates a decimal column can produce incorrect balances across millions of accounts.
  • Regulatory compliance: regulators require audit trails for data changes. You must be able to demonstrate who changed the schema, when, why, and what review process was followed.
  • System availability: in a 24/7 banking environment, downtime for database changes is not an option. Migrations must be performed online, without service interruption.
  • Rollback capability: when a migration fails, you need the ability to roll back safely. In banking, "roll forward and fix it later" is often not an acceptable strategy.

Benefits​

  • Small, well-tested migrations are far less likely to corrupt or lose data than large, manually executed schema changes.
  • Automated validation ensures that migrations maintain referential integrity and data constraints.
  • When database changes are versioned, tested, and automated, deployments become routine rather than high-risk events.
  • Every migration is a versioned artefact with a clear author, timestamp, and approval record, giving you an auditable change history.

Evolutionary database design​

Martin Fowler's concept of Evolutionary Database Design challenges the traditional approach of designing the database schema upfront and then treating it as immutable. Instead, the schema evolves alongside the application through a series of small, incremental migrations.

The core principles of evolutionary database design are:

  1. All schema changes are migrations. No manual DDL execution against production databases. Every change is expressed as a versioned migration script.
  2. Migrations are applied in order. Each migration has a version number, and the migration tool ensures they are applied in sequence. This guarantees that any environment can be reconstructed from scratch by running all migrations in order.
  3. Migrations are idempotent or versioned. Either migrations can be safely re-run (idempotent) or the tooling tracks which migrations have been applied (versioned). Flyway uses the versioned approach; some teams prefer idempotent scripts for operational flexibility.
  4. Schema and data changes are separated. Structural changes (adding columns, creating indexes) and data changes (backfilling values, migrating data formats) should be in separate migrations for clarity and reversibility.
  5. Backward compatibility is maintained during transition periods. When renaming a column, the old name and new name coexist during a transition period, allowing the application to be deployed independently of the migration.

The expand-contract pattern​

The expand-contract pattern (sometimes called parallel change) is essential for zero-downtime database migrations:

  1. Expand: add the new column/table alongside the existing one. Write to both. Read from the old.
  2. Migrate: backfill the new column/table with data from the old one.
  3. Transition: update the application to read from the new column/table. Continue writing to both.
  4. Contract: once all consumers are reading from the new column/table, remove the old one.

This pattern allows database changes to be deployed independently of application changes, eliminating the need for coordinated "big bang" releases.

Example: renaming a column in a live banking system​

Renaming a column in a production banking database sounds trivial. It is not. If you rename acct_bal to account_balance in a single migration, every application, report, ETL job, and downstream system that references acct_bal will break simultaneously.

Using the expand-contract pattern:

-- Migration V1: Expand - Add new column
ALTER TABLE accounts ADD COLUMN account_balance DECIMAL(19,4);

-- Migration V2: Backfill - Copy data
UPDATE accounts SET account_balance = acct_bal WHERE account_balance IS NULL;

-- Migration V3: Sync - Add trigger to keep both columns in sync
CREATE TRIGGER sync_account_balance
BEFORE INSERT OR UPDATE ON accounts
FOR EACH ROW EXECUTE FUNCTION sync_balance_columns();

-- Migration V4: Contract - Remove old column (after all consumers migrated)
ALTER TABLE accounts DROP COLUMN acct_bal;

Each migration is deployed independently, tested independently, and can be rolled back independently. At no point is the system in an inconsistent state.

Version control for database schemas​

Version control is the practice of tracking and managing changes to database schemas. It allows multiple developers to collaborate on database changes, maintain a history of changes, and revert to previous versions if needed.

  • Git for migration scripts: all migration scripts live in the same repository as the application code, so application changes and their corresponding database changes are reviewed, approved, and deployed together.
  • Liquibase: a schema change management tool that uses XML, YAML, JSON, or SQL changelog files to define database changes. Liquibase tracks which changesets have been applied and supports rollback generation.
  • Flyway: a migration tool that uses versioned SQL scripts (V1__create_accounts.sql, V2__add_balance_column.sql). Flyway is simple, convention-driven, and integrates cleanly with CI/CD pipelines.

Example: using Flyway for version control​

A typical Flyway migration directory structure:

db/migration/
  V1__create_accounts_table.sql
  V2__add_customer_reference.sql
  V3__create_transactions_table.sql
  V4__add_index_on_transaction_date.sql
  V5__add_account_balance_column.sql

Flyway maintains a flyway_schema_history table that records which migrations have been applied, when, by whom, and the checksum of each script. This table is the audit trail. In banking, this table is evidence that can be presented to auditors demonstrating the complete history of schema evolution.

Automated testing for database changes​

Automated testing involves writing tests that run automatically to verify the correctness of database changes. In financial services, database testing is a control.

  • Unit testing: testing individual database functions, stored procedures, or triggers in isolation. Tools like pgTAP (for PostgreSQL) or tSQLt (for SQL Server) provide test frameworks specifically for database code.
  • Integration testing: testing the interaction between the application and the database, ensuring that migrations do not break existing queries, ORM mappings, or API contracts.
  • Migration testing: running the full migration sequence from scratch in a disposable environment (using Docker containers or cloud-provisioned databases) to verify that all migrations apply cleanly and in order.
  • Data validation testing: after a migration that modifies data, running validation queries to confirm that the data transformation was correct. For financial data, this typically means verifying that aggregate balances match before and after the migration.

Example: migration testing in CI/CD​

In our CI/CD pipeline at the bank, every pull request that included database migration scripts triggered the following automated checks:

  1. Clean build: spin up a fresh PostgreSQL container and apply all migrations from V1 to the latest. Verify there are no errors.
  2. Incremental build: apply only the new migrations against a database that already has all previous migrations applied. Verify there are no errors.
  3. Rollback test: apply the new migrations, then roll them back. Verify the database returns to its previous state.
  4. Application compatibility: run the full application test suite against the migrated database. Verify there are no regressions.
  5. Data integrity check: for data migrations, run assertion queries that verify row counts, aggregate sums, and referential integrity constraints.

This pipeline gave us confidence that any migration that reached production had already been validated in four environments (developer machine, CI, staging, pre-production).

Example: Claude Code as a migration reviewer​

The five automated checks above catch whether a migration runs. They do not catch whether it is safe to run against a live, large table: an unguarded ALTER TABLE ... ADD COLUMN NOT NULL that locks for the duration of a rewrite, a DROP COLUMN with no read path removed first, an index added without CONCURRENTLY. Those are exactly the kind of pattern-matching problems a repository-aware agent is good at, because the answer depends on reading the migration alongside the schema history and the application code that queries the table it touches, not on the migration file in isolation. A Claude Code review step, scoped with read-only tools and given the expand-contract pattern above as the standard to check against, flags migrations that skip a step (a drop with no prior deprecation window, a rename with no dual-write period) before a human reviewer even opens the PR. It does not replace the automated checks above; it catches the class of mistake those checks are structurally unable to see.

Continuous integration for database changes​

Continuous integration involves merging database changes frequently to detect issues early. The key principle is: database changes should flow through the same CI/CD pipeline as application code.

  • Automated builds: the build process includes database migration execution, which catches syntax errors, constraint violations, and compatibility issues before they reach production.
  • Database migrations in the pipeline: tools like Liquibase and Flyway run as pipeline stages that execute automatically during deployment. Nobody executes DDL manually against any environment.
  • Continuous feedback: developers get immediate feedback on database changes and issues. If a migration breaks the build, the developer knows within minutes, not days.

Example: database CI/CD in a regulated environment​

In a regulated banking environment, the CI/CD pipeline for database changes included additional controls:

  • Four-eyes review: every migration script required approval from at least one other engineer before merging, enforced by branch protection rules in Git.
  • Change classification: migrations were automatically classified as "structural" (DDL) or "data" (DML). Data migrations on tables containing personally identifiable information (PII) triggered additional review by the data governance team.
  • Execution logging: the pipeline recorded the exact SQL executed, the execution time, the number of rows affected, and the before/after state of affected objects. This log was immutable and retained for seven years.
  • Segregation of duties: the person who wrote the migration could not approve it, and the pipeline service account that executed it was separate from all human accounts.

Audit trails and regulatory compliance​

In regulated environments, database change management is a compliance obligation as well as an engineering practice. Regulators expect:

  • Traceability: the ability to trace any data value back to the code change, migration script, and approval that produced it.
  • Immutability: evidence that the audit trail cannot be tampered with after the fact.
  • Segregation of duties: proof that the person who authored a change is not the same person who approved or deployed it.
  • Retention: audit records retained for the period specified by regulation (typically 5-7 years in banking).

The combination of Git (for change authoring and review), Flyway/Liquibase (for execution tracking), and CI/CD pipeline logs (for deployment evidence) creates a comprehensive audit trail that satisfies these requirements without requiring manual documentation.

Tools for database change management​

  • Liquibase: a schema change management tool that works with version control systems. Supports rollback generation, diff reports, and multiple changelog formats.
  • Flyway: a migration tool that integrates with version control systems, using a convention-over-configuration approach with versioned SQL scripts.
  • DBmaestro: a database DevOps platform that automates change management with built-in governance and compliance features.
  • Redgate SQL Change Automation: a tool for automating database deployments and version control, particularly strong in the SQL Server ecosystem.
  • SchemaHero: a Kubernetes-native schema management tool that uses declarative schema definitions.
  • Atlas: a schema management tool that supports declarative and versioned migrations with built-in linting.

References​

  1. Ambler, S.W. & Sadalage, P.J. (2006). Refactoring Databases: Evolutionary Database Design. Addison-Wesley. The foundational text on treating database schemas as evolvable artefacts, with a comprehensive catalogue of database refactoring patterns.

  2. Fowler, M. (2016). "Evolutionary Database Design." martinfowler.com. Available at martinfowler.com/articles/evodb.html. An accessible introduction to the principles of evolutionary database design, including the expand-contract pattern.

  3. Sadalage, P.J. & Fowler, M. (2012). NoSQL Distilled: A Brief Guide to the Emerging World of Polyglot Persistence. Addison-Wesley. Extends the evolutionary design thinking to non-relational databases and polyglot persistence architectures.

  4. Flyway Documentation. Available at documentation.red-gate.com/flyway. Official documentation for Flyway, including migration conventions, configuration options, and CI/CD integration patterns.

  5. Liquibase Documentation. Available at docs.liquibase.com. Official documentation for Liquibase, including changelog formats, rollback strategies, and enterprise governance features.

  6. Humble, J. & Farley, D. (2010). Continuous Delivery: Reliable Software Releases through Build, Test, and Deployment Automation. Addison-Wesley. Chapter 12 ("Managing Data") provides essential guidance on integrating database changes into continuous delivery pipelines.