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 Next revision Both sides next revision | ||
|
development:database_migration_management [2015/04/23 20:28] lee [How the OHDSI WebAPI services use Flyway] |
development:database_migration_management [2015/04/24 13:08] lee [The basics of how Flyway works] |
||
|---|---|---|---|
| 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 of this approach are: | ||
| + | |||
| + | * It provides a timestamped, checksum verified audit trail of all applied database schema changes | ||
| + | * All database schema changes are performed via SQL DDL scripts that are version controlled | ||
| + | * Initial creation of tables and all subsequent database migrations are automatically executed dynamically in new and existing databases | ||
| ==== 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 30: | Line 37: | ||
| e.g. The next heracles related database migration script after V1.0.0.1_heracles_schema_migation.sql would be called V1.0.0.1.1_heracles_schema_migration.sql. | e.g. The next heracles related database migration script after V1.0.0.1_heracles_schema_migation.sql would be called V1.0.0.1.1_heracles_schema_migration.sql. | ||
| - | Note. The OHDSI WebAPI build has the Flyway "out of order execution" option enabled so that the rules around database script execution order are relaxed. This provides needed flexibility when we have typically one developer assigned to each set of WebAPI services that are working independently and avoids the more complex alternatives of requiring separate forks for WebAPI database migrations. | + | Note. The OHDSI WebAPI build has the Flyway "out of order execution" option enabled so that the rules around database script execution order are relaxed. This provides needed flexibility when we have typically one developer working on each subset of WebAPI services. Since we want to enable each developer to be able to work independently in parallel, this approach avoids the more complex alternative of requiring separate forks for WebAPI database migrations. |
| ==== Testing Flyway migration scripts prior to a pull request ==== | ==== Testing Flyway migration scripts prior to a pull request ==== | ||
| Ideally you will need access to a local test environment with all three DBMS systems - SQL Server, Postgresql and Oracle. Build a copy of the WebAPI locally with whichever database profile you are most familiar with using. Flyway will then automatically execute the database migration script according to your maven build settings and you will see the tables are automatically created in your database. You can then manually test the database scripts for the other DBMSs by manually running them in a matching local DBMS to ensure they execute as expected. | Ideally you will need access to a local test environment with all three DBMS systems - SQL Server, Postgresql and Oracle. Build a copy of the WebAPI locally with whichever database profile you are most familiar with using. Flyway will then automatically execute the database migration script according to your maven build settings and you will see the tables are automatically created in your database. You can then manually test the database scripts for the other DBMSs by manually running them in a matching local DBMS to ensure they execute as expected. | ||
development/database_migration_management.txt · Last modified: 2015/04/24 13:18 by lee
Page Tools
