Selectively apply Changes to Database Environments using Liquibase

In our previous post, we learned how to use Liquibase to export and compare databases. That brings us to another important question: how do we deploy separate changes on separate database environments like dev, qa, prod etc. Often times, developers would want to push certain changes in the dev environment more frequently and often and not all of them necessarily make it into the production. For example, dev database may have a special ERRORLOG table which stores the debugging information, but there is no requirement of that in the QA or production environment. Similarly, QA team would like to insert some data and modify certain values to see how the application is behaving into a particular scenario and again those will not make it to the production environment in general. So we need to be able to define different database environment and deploy the changes according to the type of the environment.

Using Database Contexts to Differentiate Changes

To minimize the command that we need to type, we have stored all required information inside file:

classpath: ..\\Program Files\\Microsoft JDBC Driver 6.0 for SQL Server\\sqljdbc_6.0\\enu\\jre8\\sqljdbc42.jar
url: jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks2017;integratedSecurity=false;
changeLogFile: C:\\Source\\changelog-01.json
username: liquibase
password: liquibase@123
logLevel: info

view raw
hosted with ❤ by GitHub

Now, we’ll apply the below changelog for creation of table ErrorLog:

liquibase formatted sql
changeset mohitgoyal:20190304153300 context:development
CREATE TABLE [dbo].[ErrorLog](
[ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
[ErrorTime] [datetime] NOT NULL,
[UserName] [sysname] NOT NULL,
[ErrorNumber] [int] NOT NULL,
[ErrorSeverity] [int] NULL,
[ErrorState] [int] NULL,
[ErrorProcedure] [nvarchar](126) NULL,
[ErrorLine] [int] NULL,
[ErrorMessage] [nvarchar](4000) NOT NULL,
[ErrorLogID] ASC
changeset mohitgoyal:20190304153400 context:development
ALTER TABLE [dbo].[ErrorLog] ADD CONSTRAINT [DF_ErrorLog_ErrorTime] DEFAULT (getdate()) FOR [ErrorTime]

Note that now when we are mentioning changeset details, we are specifying an extra option named ‘contexts’. For our development environment, we have set this to ‘development’. This signifies that we want to create the ErrorLog table only in the development context. Now, when running Liquibase command, we add an extra option: ––contexts and set it to the value ‘development’:

liquibase --contexts=development update

running liquibase update on development context

If all goes well, we should be able to see the table and constraint created fine while browsing the schema:

checking development db schema to verify table creation

Same can be confirmed from output of DatabaseChangeLog table:

checking databasechangelog table in the dev database

Now we do not want to apply this change in the production environment. Let’s say that for production environment, changes are specified by setting ‘contexts’ to value of ‘production’. Now, while running the liquibase command, we can apply changes as below:

liquibase --contexts=production update

running liquibase update on production context

Again, we can browse the production schema to verify ErrorLog table has not been created:

checking production db schema to verify table is not created

Common Gotcha

It is mandatory to specify a value for ––contexts option to make relevant changesets applicable. If this value is not specified, Liquibase will indiscriminately apply all contexts when running.

Using different files for environment segregation

Another convenient bit that we forgot above is about database connection information which we have mentioned inside the file. Before applying the change in the production, we have modified the properties file to contain the details for the production database. However, this can be a hassle and not a recommended method when using CI/CD pipelines. To overcome this, we can define a properties file for each environment, say for dev environment and for production environment and mention the same using ––defaultsFile option:

When running liquibase command, we need to tell the relevant properties file to use:

liquibase --contexts=development update
liquibase --contexts=production update

using separate file for liquibase update

We can go one step further and remove contexts from command-line to inside properties file.

Again, this presents an interesting situation where you can point to different changelog files since you are using properties file. However, I would not recommend that in my personal opinion.

What about the DML Changes Segregation?

The concept of contexts is relevant to the DML Changes as well. So if you are managing your test data with Liquibase, the best way to include it is in-line with all your other changeSets, but marked with a ‘test-data’ context. That way, when you want your test data inserted you can run the migrator with the ‘test-data’ context. When it comes time to migrate your production database, don’t include the ‘test-data’ context, and your test data not be included. Same goes for reference data and we can have context ‘reference-data’ which will be applicable for relevant environments.

2 thoughts on “Selectively apply Changes to Database Environments using Liquibase

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s