development:database_migration_management
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision Last revision Both sides next revision | ||
|
development:database_migration_management [2015/04/23 20:37] lee [Background] |
development:database_migration_management [2015/04/24 13:08] lee [Create idempotent database migration scripts] |
||
|---|---|---|---|
| Line 8: | Line 8: | ||
| The OHDSI WebAPI executable has a dependency on a java framework called "Flyway" to manage the execution of the database schema migration scripts that perform the database migrations. | The OHDSI WebAPI executable has a dependency on a java framework called "Flyway" to manage the execution of the database schema migration scripts that perform the database migrations. | ||
| - | The advantages with this approach are: | + | The advantages of this approach are: |
| * It provides a timestamped, checksum verified audit trail of all applied database schema changes | * It provides a timestamped, checksum verified audit trail of all applied database schema changes | ||
| Line 15: | Line 15: | ||
| ==== The basics of how Flyway works ==== | ==== The basics of how Flyway works ==== | ||
| - | The Flyway data migration framework will execute database migration scripts that are provided in a known directory (or sub-directory) in the source code tree and each script name must begin with a database migration version number. Each script is a SQL source code file containing Database Definition Language (DDL) statements. | + | The Flyway data migration framework will execute database migration scripts that are provided in a known directory (or sub-directory) in the source code tree and each script name must begin with a database migration version number. |
| + | |||
| + | Each database migration script is a SQL source code file containing only the set of Database Definition Language (DDL) changes that need to be applied to the existing database schema in order to migrate it to the latest version. | ||
| The scripts are executed in the order of the database migration version number in the script names. | The scripts are executed in the order of the database migration version number in the script names. | ||
| Line 41: | Line 43: | ||
| ==== Create idempotent database migration scripts ==== | ==== Create idempotent database migration scripts ==== | ||
| If a script fails half way through then you don't want to have to manually drop the objects that were created. | If a script fails half way through then you don't want to have to manually drop the objects that were created. | ||
| - | This can be avoided by including DDL statements to drop objects if they already exist. That way a script will clean-up previously created objects automatically when it is re-run. | + | This can be avoided by including DDL statements to drop objects if they already exist. That way a script will clean-up previously created objects automatically if it is re-run. |
| ==== Manually rolling back a Flyway migration ==== | ==== Manually rolling back a Flyway migration ==== | ||
| It is not encouraged but it is possible to edit a database migration script and re-run it. You would need to manually delete the row containing the record of the previous execution of the script from the SCHEMA_VERSION table (and if necessary also drop any database objects that had been created). Care must be taken not to delete any other rows from the SCHEMA_VERSION table. | It is not encouraged but it is possible to edit a database migration script and re-run it. You would need to manually delete the row containing the record of the previous execution of the script from the SCHEMA_VERSION table (and if necessary also drop any database objects that had been created). Care must be taken not to delete any other rows from the SCHEMA_VERSION table. | ||
development/database_migration_management.txt · Last modified: 2015/04/24 13:18 by lee
Page Tools
