Automating database releases
Published 11 September 2019
Version Control your database changes
Redgate Change Control helps your team start using standardized processes to capture database changes in development. These changes are scripted to a folder on your workstation. This folder should be associated with your version control system so you can commit, push, and pull changes and use your normal version control processes like branching, code reviews, pull requests, etc. Use your version control system client to perform these operations.
Redgate Change Control generates scripts that are compatible with Flyway, a leading Open Source migrations framework for understanding what scripts have been applied to a target database, what scripts are pending, and executing those pending scripts and the corresponding metadata. We are in the process of building a wrapper around Flyway to improve the CI/CD experience and give you more confidence in your releases. Please see below for more information and to sign up for the Early Access Program. This will be part of the Deployment Suite for Oracle when it's available. For now, users need to use Flyway to integrate their database changes into their existing CI/CD release processes. The following sections explain how to do this.
Once your database changes are pushed to your version control system, your Continuous Integration server can be triggered to automatically build the changes. The primary task of a build is to validate the contents of the project by checking that the scripts and the configuration file in the project can be used to build a database from scratch. This validates that the SQL is valid, that all dependencies are correct, etc. If this fails, this gives you fast feedback to review and fix any of the changes in the build before they are deployed to your other downstream environments (Test, Staging, Production).
For the build, you should use the same configuration file from your project that was checked into your version control system. This is giving you a repeatable, consistent process so that any settings you use in development are used in your other environments as well. When using this configuration file, you will need to override the following settings:
- url = the jdbc connection to the database that you're using for Continuous Integration
- user = the username with permission to the CI database
- password = the password for the CI database
We recommend that these settings are set up as variables in your CI system and passed in as parameters. Most CI systems enable your variables to be encrypted so this information can remain secure.
To build your database changes, simply use your Continuous Integration system to call a cmd line task that runs the following:
flyway migrate -url=<jdbc connection string to your CI db (parameter)> -user=<username (parameter)> -password=<password (parameter)> -cleanOnValidationError
If you are using Oracle 10.1 - 12.1, then you will also need to use the following parameters:
-licenseKey=<your Flyway license key (parameter)>
More on -cleanOnValidationError
If your CI database is a permanent database, then only pending migrations will be applied to it during the CI process. Every time you run flyway migrate, by default it tries to validate the database first. This means that the checksum for each versioned migration script is checked to make sure that the versioned migration scripts haven't changed since they were executed on the database. This ensures a consistent process. If you're not worried about this in your CI environment, then you can use the -cleanOnValidationError option, which will clean the CI database if the checksums don't match, dropping all the objects from it and then applying all the migration scripts.
Warning: Never use this option on production databases!
If you want to always build your CI database from scratch, then use flyway clean -url=<> -user=<> -password=<> before calling flway migrate to give you a fresh CI db environment.
Automating your releases
Once your database build is successful, then you can release this to your other environments. Use your release automation tool to set up environment-specific variables that are encrypted and then pass these to the command line steps.
Optionally, some users start with a cmdline step to capture some information about the release. Flyway info will tell you what scripts have already been applied to your target database (eg., Test, Staging, Production) and which scripts are pending that will be applied during this release. You can do this by running the following command. The results will appear in the logs or you may want to script this to a file and then store this as a release artifact.
flyway info -url=<jdbc connection string to your Test/Staging/Prod db (parameter)> -user=<username(parameter)> -password=<password (parameter)>
Use the following command to actually update your target database. Make sure that your testing is complete and you have plans in place to recover if needed.
flyway migrate -url=<jdbc connection string to your CI Test/Staging/Prod db (parameter)> -user=<username (parameter)> -password=<password (parameter)>
You may also need the following parameters:
-schema=<target db schema name> - This is needed if your schema name is different between your environments
-locations=<migrations folder for specific targets> - In some cases, additional scripts might need to be ran on some environments, but not all (eg., a script to insert some test data on a test environment). The location of these additional scripts that are environment-specific can be listed here.
If you are using Oracle 10.1 - 12.1, then you will also need to use the following parameters for both of these commands:
-licenseKey=<your Flyway license key (parameter)>
If you are using Octopus Deploy for your release management system, there is a Flyway Migrate Template available at https://library.octopus.com/step-template/actiontemplate-flyway-migrate. Note: The ShadowURL is completely different to the shadow database used by Redgate Change Control. This ShadowURL is used for the purposes of detecting drift (unexpected changes to the target database).
Improve this CI/CD experience
We are in the process of building a wrapper around Flyway to improve your CI/CD, which will make setup easier and give you more confidence in your releases. This will include things like:
- Improvements for optional manual reviews before going to production and an easier way to audit changes
- Providing the actual SQL script that will be ran for the release
- Providing a side-by-side differences report of what objects will be changed and how
- Improvements for automated releases
- Warnings if there's a chance of data loss and more so the release can be reviewed more thoroughly
- Integrated static code analysis to automatically check to make sure best practices and naming conventions are being followed. These can be set as informational warnings or errors that stop the release. → Currently, you can do this via the cmdline in the Code Analysis for Oracle preview release.
- Integrated drift analysis that provides additional checks and reports to see if your production environment has drifted from your last release or from your dry-run in a staging environment. This can help to ensure that your release processes are being followed, understand if any production hotfixes were made, and give you more confidence that your deployment will work since it's the same schema that was tested in your staging environment. → Currently, you can do this manually via the Schema Compare for Oracle cmdline.
How else can we improve database releases? If you would like to participate in our Early Access Program so we can understand more about your current release process, how we can improve it, and for you to try out early access releases so we can get feedback, please get in touch with the Product Manager.