Code Shipping - Change and Deployment Pipeline - Development Lifecycle / Workflow with SQL database.
Database migration is the process of changing the structure of the database in an other with mostly DDL statement.
The flow sometimes will block any change that risks data-loss.
See also: Data - Migration
Their is several methods:
see also: RoundhousE-Script-Order
Type:
On databases that support transactions with statements that change the schema, migrations are wrapped in a transaction. If the database does not support this then when a migration fails the parts of it that succeeded will not be rolled back. You will have to rollback the changes that were made by hand.
Prefix of the migration script: The only benefit of a timestamp over a number is that it almost eliminates the risk of “collision” and makes unnecessary to “reserve” a number over chat before creating the migration.
By creating a script for each modification, if you want to retrieve the state of a database for a certain version, it can add a lot of file.
The list of alter statement can be so long that when you want to speed up the process of building a database, you are merging them in a create statement. With an automatic schema compare, every hundred or two alter file, the initial creation script is recreated (not concatenate all the script, an actual clean creation).
Each migration itself is written to be idempotent, e.g. “don’t try to add the column if it’s already there”. Therefore, a specific order isn't necessary needed in the database migration script.
A multi-step transition is
Conceptually it's straightforward but it can take a long time in calendar days depending on the deployment schedule, it can be tough to keep track of what's been migrated, and the data migration will cause performance issues if you don't plan it properly (e.g. trying to do a migration that locks an important table).
You just have do it in a way where each individual change is backward compatible and you don't move on to the next change until the previous one is rolled out everywhere.
Most of the migration script are done in a transaction because nobody want a migration to partially succeed but some database transaction are automatically committing any change. Ex: CREATE INDEX CONCURRENTLY in Postgres databases can't run in a transaction.
Flyway do everything in a transaction but in the non transnational cases, the migration must be done manually and update Flyway's schema_version table, which is annoyingly complex (11 columns, some with no obvious purpose).
By deploying small and often, rollback are barely needed. It’s often quicker to fix code and deploy than reverse a migration (especially across hundreds of databases). If we needed to reverse something, we could just push another migration negating whatever we did that went boom.
Why roll back when you can roll forward?
The truth is always in the release. The trunk has always errors. Example
Error(2739,15): PL/SQL: ORA-00904: "WAT_EVER_COLUMN": invalid identifier
Framework | Script | Language | Desc |
---|---|---|---|
My Batis | Native | Java | Version management in the script name (undo management and prod script creation) |
Flyway 2013 | Native | Java | Version management in the script name |
LiquiBase | Xml | Java | Database agnostic through the use of an XML file the databaseChangeLogFile |
Ghost from Github | gh-ost is a triggerless online schema migration solution for MySQL using the binary log. Must see doc | ||
DbDeploy | Native script | Java, C# | SQL delta scripts management with versioning in the file name |
DBMaintain | Native | Java | Script with version in the file name |
Roundhouse | Native | C# |
Change Log is generally saved in a table within the target database.
Example for DbDeploy:
Column | Data Type | Description |
---|---|---|
ChangeId | INT | Auto-incrementing unique ID for each change entry |
Folder | VARCHAR(256) | Versioned folder name (Example: v1.0). |
ScriptNumber | SMALLINT | Sequential script number within folder. |
ScriptName | VARCHAR(512) | File name including extension. |
StartDate | DATETIME | Date and time script started. |
CompleteDate | DATETIME | Date and time script ended even if it failed. |
AppliedBy | VARCHAR(128) | User account that ran the script. |
ScriptStatus | TINYINT | 0 = Failure, 1 = Success, 2 = Problem Resolved, 3 = Started |
ScriptOutput | VARCHAR(MAX) | Full output of the script execution. |
The rules that I will search in a database migration tool: