Deploying to Oracle with Octopus Deploy and Liquibase
Database deployments are often the hardest part of deployments. In this post, I show you how to deploy an Oracle database with Liquibase using Octopus Deploy.
Liquibase is a migrations-based database deployment tool similar to Flyway, DbUp, and RoundhousE. The differentiating feature of Liquibase is how you define your migrations.
Flyway, DbUp, and RoundhousE rely on the user writing the SQL scripts themselves, while Liquibase allows the user to define migrations in XML, JSON, YAML, as well as SQL in what is called a
changelog. Liquibase will convert the changelog to SQL statements during the deployment process.
Sample project: Sakila
Sakila is a sample database originally developed for MySQL. This project contains a number of different database objects, including tables, views, stored procedures, and functions. This makes it a well-rounded example.
To support other database technologies, I created a repo in our Samples BitBucket instance, which contains the Sakila project in a number of different database technologies and methods.
The step template for Liquibase in Octopus Deploy works in one of two ways:
- With Liquibase included in the package.
- With only the changelog file.
For this post, we package the changelog file:
Using Octopus Deploy, you can include the Liquibase step template to automate your deployments to Oracle. This post assumes you're already familiar with creating a project in Octopus Deploy and will cover the steps specific to Liquibase.
Liquibase step template
To add the Liquibase template, click the ADD STEP button in the Process tab of your Octopus project:
Then filter by
liquibase. The template will appear in the Community Contributed Step Templates section. Click INSTALL AND ADD (or ADD if it's already installed):
The Liquibase template is specifically designed to support running on a Worker with the capability to download everything it needs to run, including Java. With the template added, fill in the fields for the template.
Select the database type that you're deploying to. The currently supported types are:
Liquibase itself supports more types, but they have not been included in the template yet.
Changelog file name
This is the name and or relative location within the package of the changelog file. For example:
Name, DNS, or IP address of the server to deploy to.
Port number the database server is listening on.
Name of the database to deploy to.
Username of the account with permissions to the database.
Password of the account with permissions to the database.
Connection query string parameters
Specify any additional query string parameters for the connection string. For example:
Database driver path
Path to the database driver jar file within the package to use for connecting to the database server. Not used when the Download Liquibase option is checked.
Executable file path
Path to the Liquibase executable within the package. Not used when Download Liquibase option is checked.
This checkbox is used when you only want to see the SQL that is going to be used for the deployment.
The step template will automatically attach the .sql file as an artifact of the deployment. This is useful when you require someone to review the changes prior to deployment.
Use this option if you're not including Liquibase in your deployment package.
This option will download the community version of Liquibase, Java, and the database type .jar file necessary to perform the deployment. This post uses this option.
This option is only used with the Download Liquibase option.
This specifies the version of Liquibase to download. Leave blank to use the latest.
This is the package selector for deployment.
When done, it should look something like this:
For this post, I added a few other steps to the process to mimic a real-world deployment:
- Create user: Creates the database user if it doesn't exist.
- Add connect role: Adds the CONNECT role to the above user.
- Add resource role: Adds the RESOURCE role to the above user.
- Add DBA role: Adds the DBA role to the above user.
- Generate change script: Liquibase step with Report Only checked to generate what will be run.
- DBA Approval: Requests DBA approval only in production.
- Apply change set: Liquibase step that applies the changes.
When executed, the deployment results will look something like this:
As you can see, the Report Only step attached the
ChangeSet.sql file as an artifact, which can be reviewed prior to approval. Expanding the
Apply changeset step shows:
If we log into Oracle, we can see that our database updates have been applied:
Octopus Deploy supports deploying to many database technologies (Microsoft SQL Server, MariaDB, MySQL, Oracle, PostgreSQL, etc.) as well as many different deployment methods (DACPAC, DbUp, Flyway, Liquibase, RoundhousE, etc.). I hope this post helps you understand how you can use Octopus to deploy to an Oracle database using Liquibase.