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.
Even though most schema migration tools aim to minimise the impact of schema changes on any existing data in the database undergoing migration, preservation of data in general is not guaranteed because schema changes such as the deletion a database column can destroy data (i.e. all values stored under that column for all rows in that table are deleted). Therefore, schema migration is typically only used when the data held in the database is not real nor valuable, such as in software development, where developers work only with (possibly generated) test data. Programmatic schema migrations are almost never performed in production for the same reason.
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 is 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 practise, 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.
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.
- Flyway - data migration framework for Java where migrations are written in SQL or Java
- LiquiBase - cross platform tool where migrations are written in XML, YAML, JSON or SQL.
- Active Record (Migrations) - schema migration tool for Ruby on Rails projects based on Active Record.
- Ruckusing-migrations - schema migration tool for PHP projects.