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 liquibase.properties file:
|classpath: ..\\Program Files\\Microsoft JDBC Driver 6.0 for SQL Server\\sqljdbc_6.0\\enu\\jre8\\sqljdbc42.jar|
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,|
|CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED|
|)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]|
|) ON [PRIMARY]|
|—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
If all goes well, we should be able to see the table and constraint created fine while browsing the schema:
Same can be confirmed from output of DatabaseChangeLog table:
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
Again, we can browse the production schema to verify ErrorLog table has not been created:
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 liquibase.properties files for environment segregation
Another convenient bit that we forgot above is about database connection information which we have mentioned inside the liquibase.properties 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 liquibase_development.properties for dev environment and liquibase_development.properties 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 --defaultsFile=liquibase_development.properties --contexts=development update liquibase --defaultsFile=liquibase_production.properties --contexts=production 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.