Schema migration

Not to be confused with Data migration.

In software engineering, schema migration (also database migration, database change management) refers to the management of incremental, reversible changes to relational database schemas. A schema migration is performed on a database whenever it is necessary to update or revert that database's schema to some newer or older version.

Migrations are performed programmatically by using a schema migration tool. When invoked with a specified desired schema version, the tool automates the successive application or reversal of an appropriate sequence of schema changes until it is brought to the desired state.

Most schema migration tools aim to minimise the impact of schema changes on any existing data in the database. Despite this, preservation of data in general is not guaranteed because schema changes such as the deletion of a database column can destroy data (i.e. all values stored under that column for all rows in that table are deleted). Instead, the tools help to preserve the meaning of the data or to reorganize existing data to meet new requirements. Since meaning of the data often cannot be encoded, the configuration of the tools usually needs manual intervention.

Risks and Benefits

Schema migration allows for fixing mistakes and adapting the data as requirements change. They are an essential part of software evolution, especially in agile environments (see below).

Applying a schema migration to a production database is always a risk. Development and test databases tend to be smaller and cleaner. The data in them is better understood or, if everything else fails, the amount of data is small enough for a human to process. Production databases are usually huge, old and full of surprises. The surprises can come from many sources:

• Corrupt data that was written by old versions of the software and not cleaned properly
• Implied dependencies in the data which no one knows about anymore
• People directly changing the database without using the designated tools
• Bugs in the schema migration tools
• Mistakes in assumptions how data should be migrated

For these reasons, the migration process needs a high level of discipline, thorough testing and a sound backup strategy.

Schema migration in agile software development

When developing software applications backed by a database, developers typically develop the application source code in tandem with an evolving database schema. The code typically has rigid expectations of what columns, tables and constraints are present in the database schema whenever it needs to interact with one, so only the version of database schema against which the code was developed is considered fully compatible with that version of source code.

In software testing, while developers may mock the presence of a compatible database system for unit testing, any level of testing higher than this (e.g. integration testing or system testing) it is common for developers to test their application against a local or remote test database schematically compatible with the version of source code under test. In advanced applications, the migration itself can be subject to migration testing.

With schema migration technology, data models no longer need to be fully designed up-front, and are more capable of being adapted with changing project requirements throughout the software development lifecycle.

Relation to revision control systems

Teams of software developers usually use version control systems to manage and collaborate on changes made to versions of source code. Different developers can develop on divergent, relatively older or newer branches of the same source code to make changes and additions during development.

Supposing that the software under development interacts with a database, every version of the source code can be associated with at least one database schema with which it is compatible.

Under good software testing practise, schema migrations can be performed on test databases to ensure that their schema is compatible to the source code. To streamline this process, a schema migration tool is usually invoked as a part of an automated software build as a prerequisite of the automated testing phase.

Schema migration tools can be said to solve versioning problems for database schemas just as version control systems solve versioning problems for source code. In practice, many schema migration tools actually rely on a textual representation of schema changes (such as files containing SQL statements) such that the version history of schema changes can effectively be stored alongside program source code within VCS. This approach ensures that the information necessary to recover a compatible database schema for a particular code branch is recoverable from the source tree itself. Another benefit of this approach is the handling of concurrent conflicting schema changes; developers may simply use their usual text-based conflict resolution tools to reconcile differences.

Relation to schema evolution

Schema migration tooling could be seen as a facility to track the history of an evolving schema.

Advantages

Developers no longer need to remove the entire test database in order to create a new test database from scratch (e.g. using schema creation scripts from DDL generation tools). Further, if generation of test data costs a lot of time, developers can avoid regenerating test data for small, non-destructive changes to the schema.

Available Tools

• Flyway_(software) - database migration tool (for Windows, OSX, Linux, Android and the JVM) where migrations are written in SQL or Java
• LiquiBase - cross platform tool where migrations are written in XML, YAML, JSON or SQL.
• Datical - Enterprise commercial version of Liquibase.
• Redgate SQL Compare - a schema comparison and deployment tool for SQL Server and Oracle.
• ReadyRoll - a migrations-based Visual Studio extension for SQL Server development and deployment.
• Active Record (Migrations) - schema migration tool for Ruby on Rails projects based on Active Record.
• Ruckusing-migrations - schema migration tool for PHP projects.
• Phinx - another framework-independent PHP migration tool.
• MyBatis Migrations - seeks to be the best migration tool of its kind.
• Ragtime - a SQL database schema migration library written in Clojure
• Lobos - a SQL database schema manipulation and migration library written in Clojure.
• Alembic - a lightweight database migration tool for usage with the SQLAlchemy Database Toolkit for Python.
• RoundhousE - a SQL database versioning and change management tool written in C#.
• XMigra - a SQL database evolution management tool written in Ruby that generates scripts without communicating with the database.
• DBmaestro - a database version control and schema migration solution for SQL Server and Oracle.
• DB Change Manager - Commercial Change Management Software by Embarcadero.
• Sqitch - Sqitch by Theory.
• Goose - database migration tool where migrations are written in SQL or Go