Start Here: Preparing for Database Automation with Liquibase

If you’re wondering where to start with automating your database process, you’re not alone. Most teams are delivering database changes manually or with some limited automation. Automatically delivering changes to target databases is a relatively new thing. As your organization shifts to a CICD pipeline flow, it can feel like a struggle to figure out how to evolve your database change deployment process. Handily, Liquibase has a lot of capabilities to help you on your journey.

Drama-free deployments

An important thing to keep in mind when it comes to CICD is that it considers a lot more than just a result of ‘the change was deployed automatically’. CICD also concerns itself with using the automation to ensure the safety, reliability, quality, and repeatability of the deployment. The idea is that deployments in lower environments are testing the delivery process as well as the actual changes — that way anything that makes it to Production will be as drama-free as possible when it’s time to deploy.

How to get started

That sounds great, but where to start? You’ve downloaded Liquibase and installed it, but what does a good pattern look like? Let’s walk through a simple example using this Microsoft Azure DevOps (ADO) pipeline definition to illustrate some concepts.

This is a basic YAML pipeline with a parameter to help us select our target environment when triggering the pipeline. There are three variables defined. One is a variable for selecting which changeset labels we want to deploy (defaulting to all of them). The other two variables serve as placeholders for redacting credentials in the command line. (Note: While this is not a security-focused post, it is worth a reminder at this point that everyone should be using a password vault of some kind to manage secrets in their automation flows). The rest of the pipeline runs a series of Liquibase commands in the shell of a demo Windows machine with an ADO agent and SQL Server installed on it. 

Breaking down the steps and the logic behind them, there are four main phases:

1. Basic readiness
2. Test deploy
3. Test rollback
4. Finalize

Phase 1 - Basic Readiness

This phase does some basic checks to make sure that everything is working and that the changes are ready to deploy. We could enhance this in the future with other quality checks, but for this basic example, we are just going to do three things:

1. Use liquibase validate to make sure that the changelog is syntactically correct
2. Use liquibase tag to set a marker in the target database to indicate where this batch of changes started and to set a rollback point for this specific batch of changes
3. Use liquibase status to put a list of what changes are pending to be applied to the target database into the automation system’s log for this job. This helps us establish history, traceability, and may help to troubleshoot if there is a problem.
   

- script: |
   call c:\apps\liquibase\liquibase.bat --username=$(LBMSSQLUSERNAME) --password=$(LBMSSQLPASSWORD)  --url jdbc:sqlserver://localhost:1433;database=cyclopsbi_${{ parameters.env }}; --labels=$(labels) validate
   call c:\apps\liquibase\liquibase.bat --username=$(LBMSSQLUSERNAME) --password=$(LBMSSQLPASSWORD)  --url jdbc:sqlserver://localhost:1433;database=cyclopsbi_${{ parameters.env }}; tag JOBID_$(Build.BuildId)
   call c:\apps\liquibase\liquibase.bat --username=$(LBMSSQLUSERNAME) --password=$(LBMSSQLPASSWORD)  --url jdbc:sqlserver://localhost:1433;database=cyclopsbi_${{ parameters.env }}; --labels=$(labels) status --verbose
  workingDirectory: Liquibase
  displayName: 'Verify Changes'

Phase 2 - Test Deploy

The goal of this phase is to validate that the changes actually deploy. Again, we could do other tests as well, but the most basic question to answer: Do the changes actually work?

1. Use liquibase updateSQL to generate a ‘dry run’ list of all the SQL that will be executed when deploying this batch. This puts clear information in the automation log as to precisely what will be done to the database and can be used for troubleshooting.
2. Use liquibase update to actually do the deployment for real and make sure it works.

- script: |
   call c:\apps\liquibase\liquibase.bat --username=$(LBMSSQLUSERNAME) --password=$(LBMSSQLPASSWORD)  --url jdbc:sqlserver://localhost:1433;database=cyclopsbi_${{ parameters.env }}; --labels=$(labels) updateSQL
   call c:\apps\liquibase\liquibase.bat --username=$(LBMSSQLUSERNAME) --password=$(LBMSSQLPASSWORD)  --url jdbc:sqlserver://localhost:1433;database=cyclopsbi_${{ parameters.env }}; --labels=$(labels) update
  workingDirectory: Liquibase
  displayName: 'Deploy Changes'

Phase 3 - Test Rollback

This phase ensures that the change delivery process has safety. The most basic test for process safety is to know that if we put the changes in, we can reliably take them back out if needed.

1. Use liquibase rollbackSQL to generate a ‘dry run’ list of all the SQL that will be executed to roll this batch of changes back to the tag we set in Phase 1. This provides explicit traceability information of what happens and eases troubleshooting should it be necessary.
2. Use liquibase rollback to actually test the rollback for the batch. It is much better to know that this works in a lower environment before having to use an untested rollback in a critical situation on Production.

- script: |
   call c:\apps\liquibase\liquibase.bat --username=$(LBMSSQLUSERNAME) --password=$(LBMSSQLPASSWORD)  --url jdbc:sqlserver://localhost:1433;database=cyclopsbi_${{ parameters.env }}; --labels=$(labels) rollbackSQL JOBID_$(Build.BuildId)
   call c:\apps\liquibase\liquibase.bat --username=$(LBMSSQLUSERNAME) --password=$(LBMSSQLPASSWORD)  --url jdbc:sqlserver://localhost:1433;database=cyclopsbi_${{ parameters.env }}; --labels=$(labels) rollback JOBID_$(Build.BuildId)
  workingDirectory: Liquibase
  displayName: 'Verify Deployment'

Phase 4 - Finalize

This phase redeploys the changes we removed in Phase 3. If we successfully reach this phase, then we know what quality gates we have passed, the safety of what we are doing, and therefore have a sufficient degree of confidence in the changes to do a final deployment. 

Use liquibase update to do one final deployment into the environment and terminate the process.

- script: |
   call c:\apps\liquibase\liquibase.bat --username=$(LBMSSQLUSERNAME) --password=$(LBMSSQLPASSWORD)  --url jdbc:sqlserver://localhost:1433;database=cyclopsbi_${{ parameters.env }}; --labels=$(labels) update
  workingDirectory: Liquibase
  displayName: 'Finalize'

Summing it up

This example serves as a guide and inspiration. I want to help new Liquibase users adopt good CICD patterns in their use of the tool so that they get the most out of it. There are plenty of other Liquibase commands and even some adjacent tools that you can wrap into the process to make it more robust and optimize your processes. All it takes is a little creativity!

Want to learn more Liquibase commands and ways to implement them? I highly recommend taking some free courses through Liquibase University. If you’re interested in our expert support implementing Liquibase at your organization, contact us. We’re happy to help.