Database Migration Guide

Database Migration Guide

references:

• Grails Database Migration Plugin
• Liquibase
• Rundeck Docker Zoo (Useful for easily developing with different databases)

How to use Grails Database Migration plugin with Rundeck

The Database Migration plugin lets us define migration scripts for the DB schema using Liquibase. It can also automatically generate scripts for you by comparing the Grails Domain Classes with what the existing schema of a database is.

So a typical workflow would be:

1. Start current version of Rundeck pointing at the target database.
2. Shut down Rundeck.
3. Make changes to domain class definitions in the Rundeck codebase.
4. In your development Rundeck Base, change your dataSource configuration to point at the target database.
5. If present, delete the "bootstrap.yml" file from your Rundeck Base
6. Run the "dbmGormDiff" task to generate migration scripts from the target database to the current Domain Class definitions.
7. Hand-edit the generated db migration scripts to fix them.
8. Test the changes

Running dbmGormDiff

Run:

./gradlew :rundeckapp:dbmGormDiff -Pargs="my-changes.groovy --add"

Hand-editing the scripts

The generated scripts will be somewhat specific to the database type of the target database (e.g. Mysql). It will use specific datatypes. These need to be changed to use property substitution. The file "rundeckapp/grails-app/migrations/changelog.groovy" is the root migration script, and defines properties for all the necessary datatypes, which should be used. E.g. use ${text.type} instead of a long text type. Refer to the file for the specific type definitions.

In some cases, you may want to create some additional changeSets to migrate data or run specific SQL queries to determine if the change should be made. Often these SQL statements may have to be different for different database types. You can create different changeSets for different databases. In the changeSet(...) definition, add a dbms: "type" section. E.g. dbms:"mysql,mariadb".

The generated scripts may also have spurious entries dropping tables or indexes, you should remove those.

Testing the changes

Now that you have written your changelog scripts, you should test them on different databases.

You can use the Rundeck Docker Zoo to quickly spin up different databases, and review the configuration values for Rundeck to connect to them.

A workflow here would be:

1. Generate the db migration scripts as described above
2. Set up a docker-zoo configuration, e.g change the base image for Rundeck to use the version you have generated migration scripts from. Then spin up the containers using docker-compose, e.g. docker compose up -d. This will spin up a rundeck container and a database. Access the Rundeck instance that is running, and if necessary perform actions to generate database rows (e.g. run jobs, define nodes, etc). Maybe you have a Rundeck Project archive you can upload here to make it simpler.
3. At this point the database should have data relevant to the changes you want to test, such as migrating one table's datatypes.
4. Now shut down just the Rundeck container, e.g. docker stop <containerID>, leaving the DB container running.
5. Update your development Rundeck configuration to point to the new database. Refer to the docker-zoo docker-compose.yml for relevant connection parameters.
6. Start up your Rundeck development environment (e.g. using IDEA run configuration, or gradle bootRun task)
7. Observe the server startup as it runs the migration scripts, (hopefully no errors occur)
8. Now you can introspect the actual database schema, or Rundeck application behavior to verify your migration scripts worked. (note: IntelliJ IDEA can connect to the database to view the schema, perform queries, etc.)

If necessary (migration script errors, etc) you may have to go back, edit your scripts, and try again.

Finally, commit your migration script changes, domain class and other app changes, and submit a PR.