June 14, 2011 at 06:55Versioned Migration of Database Structures: Key Approaches
The problems of controlling versions of databases and migrations between versions have repeatedly been raised both on Habré ( 1 , 2 , 3 , etc.) and on the Internet (mainly English). In the first section of this article, I consider the main problems that arise in teams of programmers when making any changes to the database structure. In the second section, I tried to highlight the main general approaches to the form in which changes in the database structure can be stored and maintained during development.
Terminology
Database - a set of all database objects (tables, procedures, triggers, etc.), static data (immutable data stored in lookup tables) and user data (which change during the work with the application).Database structure - a set of all database objects and static data. User data is not included in the concept of database structure.
Database version - a specific state of the database structure. Typically, the version has a number associated with the version number of the application.
Migration , in this context, is updating the database structure from one version to another (usually a newer one).
In this sense, the term migration seems to be used in many sources (especially migration contributed to thisfrom the Active Record gem included with Ruby on Rails). However, when using this term, ambiguity arises: a person who does not know the context will rather think that it is a question of transferring a database from one DBMS to another (MySQL => Oracle), or even migration of processes / data between cluster nodes. Therefore, I propose, in cases where the context is not obvious, to use a more precise term: versioned database migration .
Why is this needed?
Developers who have already encountered the problem of unsynchronizing versions of the database and application can skip this section. Here I will remind you why it is necessary to maintain parity of versions of the application and the database and what a common problem arises.The database version must match the application version
So, imagine the following situation: a team of several programmers is developing an application that actively uses the database. From time to time the application is delivered to production - for example, this is a website that is deployed to a web server.
In the process of writing application code, any programmer may need to change the structure of the database, as well as the data that is stored in it. Let me give you a simple example: let's say there is a non-nullable string field in one of the tables. This field does not always have data, in which case an empty string is stored there. At some point, you decided that storing empty lines is semantically incorrect in some cases (see 1 , 2), but it’s right to store NULLs. In order to implement this, you will need the following steps:
1. Change the type of the field to nullable:
ALTER myTable CHANGE COLUMN myField myField VARCHAR(255) NULL DEFAULT NULL;2. Since there are probably already blank lines in the production database in this table, you make a strong-willed decision and treat them as a lack of information. Therefore, you will need to replace them with NULL:
UPDATE myTable SET myField = NULL WHERE myField = '';3. Change the application code so that when receiving data stored in this field from the database, it will adequately respond to NULLs. Writing in this field also now requires NULLs instead of empty lines.
From point 3 you can see that the application and the database are inextricable parts of one whole. This means that when a new version of the application is delivered to production, the database version must also be updated, otherwise the application simply will not be able to work correctly. In this example, if only the application is updated to the new version, then at some point NULL will be inserted into a non-nullable field, and this is an obvious error.
Thus, updating the application version requires correct versioned database migration .
Is it that simple?
Having realized that the parity of the database and application versions is necessary, you need to make sure that the database migrations to the desired version will always be performed correctly. But what is the problem here? After all, at first glance, there is nothing complicated!
Here we turn again to a living example. For example, during the development process, programmers write their changes in the structure and data of a database into a separate file in the form of SQL queries (both DDL and DML queries). And with each deployment of the latest version of the application, you simultaneously update the database to the latest version, executing queries from the same SQL file ... But hey, which versionAre you updating the database to the latest version? “From the past”? But do you remember so well what exactly was the previous version (it was released 2 months ago)? If not, how are you going to update it? Indeed, without accurate information about the state of the structure and data, it is impossible to correctly migrate: if you inadvertently execute queries that have already been executed, this can lead to loss of data or a violation of its integrity.
A simple example is replacing passwords with their MD5 sums. If you re-execute such a request, then the data can be restored only from backup. Anyway, any
UPDATE's, DELETE' s, and even INSERT's, executed repeatedly, can lead to extremely undesirable consequences. Not to mention untimely TRUNCATE'ah andDROP'ah (although such cases are much less likely). By the way, from this point of view, underfulfillment is no less a danger to the health of an application than overfulfillment.
Thus, we can conclude that in the process of versioned migration all requests should be executed only once and, moreover, in the correct sequence . Consistency is important because some changes may depend on others (as in the example of a nullable field).
General principles of versioned migration
In the previous section, we highlighted important criteria that show what is required of the versioned migration process. It:- one-time execution of each change (SQL query);
- strictly predefined order of changes.
- so that any version of the database can be updated to any (usually the latest) version;
- so that a set of SQL queries that implement migration between any two versions can be received as quickly and easily as possible;
- so that you can always create from scratch a database with the structure of the latest version. This is very useful both in the development and testing process, and when deploying a new production server;
- so that, in the case of work on different branches, during their subsequent merger, manual editing of database files is minimized;
- to roll back a database to an earlier version was as simple as updating to a newer one.
Base of migration
As it turned out, most approaches have a common principle: they must base (baseline) - a reference state of the database from which to draw on. This concept is pretty well described in Scott Allen 's Versioning Databases - The Baseline .
Simply put, a foundation is a dump of the database structure for a version that is accepted as the base. Having the foundation in hand , subsequently, you can always create a database from scratch. After applying all the migrations created during the development process to this database, we get a database with the structure of the latest version.
Next, we will consider three approaches to organizing versioned database migration.
Incremental change method
This method is well described in the article “Versioning Databases - Change Scripts” of the same Scott Allen. A similar approach is also described in the article “Managing SQL scripts and continuous integration” by Michael Baylon.File structure
An example of how a folder with migration files might look in this case:
Database
|- Baseline.sql
|- 0001.03.01.sql
|- 0002.03.01.sql
|- 0003.03.01.sql
|- 0004.03.02.sql
|- 0005.03.02.sql
|- 0006.03.02.sql
'- 0007.03.02.sqlIn this example, the folder stores all the files created during the development of version 03 . However, the folder may be common to all versions of the application.
In any case, the very first file that appears in such a folder is the base (Baseline.sql). After that, any change in the database is submitted to the repository as a new migration file with the name of the view
[номер файла].[версия].[подверсия].sql. In fact, in this example, the file name contains the full version number of the database. That is, after the migration file with the name is executed,
0006.03.02.sqlthe database will be updated from the state corresponding to the version to version .03.02.000503.02.0006Storing Version History
The next step is to add a special table to the database, which will store the history of all changes in the database.
CREATE TABLE MigrationHistory
(
Id INT,
MajorVersion VARCHAR(2),
MinorVersion VARCHAR(2),
FileNumber VARCHAR(4),
Comment VARCHAR(255),
DateApplied DATETIME,
PRIMARY KEY(Id)
)
This is just an example of how a table might look. If necessary, it can be both simplified and supplemented.
In the file
Baseline.sql, you will need to add the first record to this table:INSERT INTO
MigrationHistory ( MajorVersion, MinorVersion, FileNumber, Comment, DateApplied )
VALUES ( '03', '01', '0000', 'Baseline', NOW() )
After each migration file is completed, an entry with all migration data will be entered into this table.
The current version of the database can be obtained from the record with the maximum date.
Migrate automatically
The final touch in this approach is a program / script that will update the database from the current version to the latest.
Performing database migration automatically is quite simple, because the number of the last completed migration can be obtained from the MigrationHistory table, and after that it remains only to apply all files with higher numbers. You can sort files by number, so there will be no problems with the order of migrations.
Such a script also has the task of adding records of completed migrations to the MigrationHistory table.
As additional conveniences, such a script may be able to create the current version of the database from scratch, first rolling the base onto the database , and then performing a standard migration operation to the latest version.
Pros, Cons, Conclusions
Quick and easy migration to the latest version; Version numbering mechanism. The current version number is stored directly in the database; 
For maximum convenience, you need automation tools for migrations; 
It’s inconvenient to add comments to the database structure. If you add them to Baseline.sql, then in the next version they will disappear, because the base will be generated from scratch again, as a dump of the new version of the structure. In addition, such comments will quickly become obsolete;There are problems during the parallel development process in several branches of the repository. Since the numbering of migration files is sequential, files with different DDL / DML queries may appear under the same numbers in different branches. As a result, when merging branches, you will either have to manually edit the files and their sequence, or in a new, “merged” branch, start with the new Baseline.sql, which takes into account changes from both branches. This method in various forms is quite widespread. In addition, it is easy to simplify and modify to the needs of the project.
On the Internet, you can find ready-made script options for incremental migration and embed in your project.
Idempotent Change Method
This method is described in Phil Hack 's Bulletproof Sql Change Scripts Using INFORMATION_SCHEMA Views . A similar approach is also described in the answer to this question on StackOverflow.Under idempotency understood property of the object remain unchanged after repeated attempt to change it.
A funny scene from “Friends” is recalled to the topic :)
The main idea of this approach is to write migration files so that they can be executed on the database more than once. At the first attempt to execute any of the SQL-commands, the changes will be applied; with all subsequent attempts nothing will happen.
This idea is easiest to understand with an example. Suppose you need to add a new table to the database. If you want to ensure that if it already exists, there is no error when executing the query, MySQL has a short syntax for these purposes:
CREATE TABLE IF NOT EXISTS myTable
(
id INT(10) NOT NULL,
myField VARCHAR(255) NULL,
PRIMARY KEY(id)
);Thanks to the passphrase
IF NOT EXISTS, MySQL will only try to create a table if a table with that name does not exist yet. However, this syntax is not available in all DBMSs; besides, even in MySQL it can not be used for all commands. Therefore, we consider a more universal way:IF NOT EXISTS
(
SELECT *
FROM information_schema.tables
WHERE table_name = 'myTable'
AND table_schema = 'myDb'
)
THEN
CREATE TABLE myTable
(
id INT(10) NOT NULL,
myField VARCHAR(255) NULL,
PRIMARY KEY(id)
);
END IF;In the last example, the role of the conditional expression parameter is played by a query that checks if a table exists
myTablein the database with the name myDb. And only if the table is missing, will it actually be created. Thus, the above query is idempotent. It is worth noting that in MySQL, for some reason, it is forbidden to execute DDL queries inside conditional expressions. But this prohibition is easy to circumvent - just include all such requests in the body of the stored procedure:
DELIMITER $$
CREATE PROCEDURE sp_tmp() BEGIN
IF NOT EXISTS
(
--
-- Условие.
--
)
THEN
--
-- Запрос, изменяющий структуру БД.
--
END IF;
END;
$$
DELIMITER;
CALL sp_tmp();
DROP PROCEDURE sp_tmp;What kind of bird is information_schema?
Full information about the database structure can be obtained from special system tables located in the database with the name
information_schema. This database and its tables are part of the SQL-92 standard, so this method can be used on any of the modern DBMSs. The previous example uses a table information_schema.tablesthat stores data about all the tables. In a similar way, you can check the existence and metadata of table fields, stored procedures, triggers, schemas, and, in fact, any other objects in the database structure. A complete list of tables with detailed information about their purpose can be found in the text of the standard . A short list can be seen in the Phil Hack article already mentioned.. But the easiest way, of course, is to simply open this database on any working database server and see how it works.
Usage example
So, you know how to create idempotent SQL queries. Now we will consider how this approach can be used in practice.
An example of how a folder with sql files might look in this case:
Database
|- 3.01
| |- Baseline.sql
| '- Changes.sql
|
'- 3.02
|- Baseline.sql
'- Changes.sqlIn this example, a separate folder is created for each minor version of the database. When you create each new folder, a base is generated and recorded in Baseline.sql. Then, during the development process, all necessary changes are written to the Changes.sql file in the form of idempotent queries.
Suppose, during development at different times, programmers needed the following changes to the database:
a) create the table myTable;
b) add a newfield field to it;
c) add some data to the myTable table.
All three changes are written so as not to be repeated. As a result, no matter which of the intermediate states the database is in, when the Changes.sql file is executed, it will always be migrated to the latest version.
For example, one of the developers created the myTable table on his local copy of the database, recorded the change a) in the Changes.sql file stored in the general code repository, and forgot about it for some time. Now, if he executes this file on his local database, change a) will be ignored, and changes b) and c) will be applied.
Pros, Cons, Conclusions
Very convenient migration from any intermediate version to the latest - you just need to execute one file on the database (Changes.sql); Potentially possible situations in which data will be lost, this will have to be monitored. An example is deleting a table, and then creating another table with the same name. If only the name is checked during deletion, then both operations (deletion and creation) will occur each time the script is executed, despite the fact that they were once performed; In order for the changes to be idempotent, you need to spend more time (and code) to write them.Due to the fact that updating the database to the latest version is very simple, and you can do it manually, this method shows itself in a good light if you have many production servers and you need to update them often.
A method for comparing the database structure to the source code
Unfortunately, I did not find separate articles devoted to this approach. I would be grateful for links to existing articles, if any. UPD: In his article Absent talks about his experience implementing a similar approach using samopisnaya diff-tool.The main idea of this method is reflected in the header: the database structure is the same source code as PHP, C # or HTML code. Therefore, instead of storing migration files (with queries that change the database structure) in the code repository, you only need to store the current database structure - in a declarative form.
Implementation example
For simplicity of the example, we assume that in each revision of the repository there will always be only one SQL-file: CreateDatabase.sql. In parentheses, I note that in analogy with the source code, you can go even further and store the structure of each database object in a separate file. Also, the structure can be stored in the form of XML or other formats that are supported by your DBMS.
Teams will be stored in CreateDatabase.sql file
CREATE TABLE, CREATE PROCEDUREand so on that create the entire database from scratch. If necessary, changes to the structure of tables, these changes are made directly to existing DDL-queries to create tables. The same goes for changes to stored procedures, triggers, etc. For example, the current version of the repository already has a myTable table, and in the CreateDatabase.sql file it looks like this:
CREATE TABLE myTable
(
id INT(10) NOT NULL,
myField VARCHAR(255) NULL,
PRIMARY KEY(id)
);If you need to add a new field to this table, you simply add it to the existing DDL query:
CREATE TABLE myTable
(
id INT(10) NOT NULL,
myField VARCHAR(255) NULL,
newfield INT(4) NOT NULL,
PRIMARY KEY(id)
);After that, the modified sql file is submitted to the code repository.
Performing migrations between versions
In this method, the procedure for updating the database to a newer version is not as straightforward as in other methods. Since only a declarative description of the structure is stored for each version, for each migration it is necessary to generate a difference in the form of
ALTER-, DROP- and CREATE-queries. Automated diff utilities will help you with this, such as the Schema Synchronization Tool, which is part of SQLyog , TOAD , which is available for many DBMSs, Dklab_pgmigrator for PostgreSQL by DmitryKoterov , as well as SQL Comparison SDK from RedGate.In order to migrate from one version of the database to another, you will have to restore the structure of the original and final versions on two temporary databases, and then generate a migration script. However, this procedure can be automated and should not take a lot of time.
What about data changes?
From time to time, when updating the database version on production servers, it is necessary to update not only the database structure, but also the data stored in it. An example is the transfer of data from a table with an old structure to new tables - in order to normalize. Since data on production servers already exists and is used, it is not enough just to create new tables and delete old ones, you also need to transfer the existing data.
In previous methods, in the context of storing and executing migrations, the data did not differ much from the structure of the database. But in this method, changes in the data are special, because it is impossible to store them in a code repository in a declarative form: the data on all servers is different. And it’s also impossible to automatically generate such queries for changing data: it requires human intervention.
This problem has several more or less acceptable solutions:
- store data changes according to the incremental changes method (possibly in a simplified form) and add them to the resulting diff script after its generation, manually;
- nowhere to store data change requests, and when the diff script is generated, analyze it and add in place all the necessary DML requests. I bring this decision here only because my colleague insists that it is working and has no shortcomings; I find it too dangerous, because the generation of the diff script can potentially occur several months after working on the application-related part of the change, and the details necessary for the correct data migration may already be forgotten.
Pros, Cons, Conclusions
It is convenient to observe changes in the structure between versions using the tools of the version control system; Like any source code, it is convenient to comment on the database structure; In order to create a clean database of the latest version from scratch, you need to run only one file; Migration scripts are more reliable than in other methods, as they are generated automatically; Migrating from new versions to old is almost as easy as migrating from old to new ones (problems can arise only with the notorious data changes); In case of merging two branches of the repository, merge of the database structure is simpler than when using other approaches; Data changes will have to be stored separately, and then manually inserted into the generated migration scripts;Manually performing migrations is very inconvenient; automated tools are needed. This method has many positive qualities. If you are not afraid of the described problems with data changes, and if updates to production servers are rare, I recommend using this method.
Ready-made solutions for versioned database migration
The methods described above can be used without third-party solutions, however, there are also ready-to-use products, each with its own ideology and original approach, worthy of a separate article. When choosing a versioned migration solution, be sure to consider such products as well.Some of them are discussed in a recent article, "Approaches for Versioning Databases," by Denis Gladkikh .
Only a small portion of the version-ready migration systems that are ready to use are listed below:
- Migrator.NET ;
- ECM7.Migrator - fork of Migrator.NET by dima117 . His article on Habré;
- Active Record Migrations included in Ruby on Rails;
- SQL Source Control from RedGate;
- DotNetMigrations;
- Fluent Migrator. Статья на Хабре за авторством tabushi, создавшего форк проекта;
- DbDeploy.NET;
- Tarantino;
- Mygrate;
- DBUpdater;
- Wizardby за авторством ostapbender. Его статья на Хабре
