Usually, database changes go through a pipeline of environments. For example, they are developed at developers' laptops, checked into a repository, deployed to the development environment, pushed to the test environment and finally propagated to the production. Ideally, the changes should be applied to all these environments in the same way but in real life there are some exceptions.
You may have a change or a script that should be executed only on one environment. Liquibase authors did their best to make the tool practical so they implemented a context attribute of a changeset.
If you need an introduction to Liquibase, check Database versioning - Liquibase - how to use.
Context attribute
A changeset has a context attribute which tells Liquibase to which context a particular changeset is bound. Sample usage:
<changeSet id="test data" author="DBA presents" context="dev">...
Multiple context names are also allowed:
<changeSet id="Adding a test user (dev,test)" author="DBA presents" context="dev,test">
As it is an optional attribute, you can skip it:
<changeSet id="Adding a test user" author="DBA presents">
It would not be useful without a possibility to choose a context during deployment. If you use command line, you can specify the context:
liquibase --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver --classpath="c:\Program Files\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\sqljdbc4.jar" --changeLogFile=databaseChangeLog.xml --url="jdbc:sqlserver://localhost\DEVSQL01;databaseName=MyDatabase" --username=sa --password=xyz --contexts="prod" update
Before we go to examples, see what are the rules of interpreting the context attribute:
- Changesets without the context attribute are executed regardless of a context chosen during deployment.
- A changeset which at least one value from the context attribute matches at least one context value chosen during deployment is executed.
Practical examples
I have three environments: dev, test and prod. Majority of changes go to all these environments with small exceptions:
- USERS table has to be populated with a test entry on the dev and the test environment
- Test transactions have to be deleted from the production
To achieve it, I have created the following XML file with changesets for Liquibase.
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd
http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
<changeSet id="Table creation" author="DBA presents">
<createTable tableName="USERS">
<column name="ID" type="int" />
<column name="NAME" type="varchar(100)" />
</createTable>
</changeSet>
<changeSet id="New column" author="DBA presents">
<addColumn tableName="USERS">
<column name="BIRTH_DATE" type="date" />
</addColumn>
</changeSet>
<changeSet id="Adding a test user (dev and test environment)" author="DBA presents" context="dev,test">
<sql>
insert into USERS (ID, NAME)
values (1, 'DBA')
</sql>
</changeSet>
<changeSet id="Column extension" author="DBA presents">
<modifyDataType tableName="USERS" columnName="NAME" newDataType="nvarchar(255)" />
</changeSet>
<changeSet id="Remove test transactions (prod only)" author="DBA presents" context="prod">
<sql>
delete from TRANSACTIONS where TRN_IS_TEST = 1
</sql>
</changeSet>
</databaseChangeLog>
I use a command line to deploy the changes. To show you which changes would be taken for deployment in each context I use a status command instead of update as the former does not modify the database so I can run it multiple times.
Apply all changes regardless of context - all changes qualify for the deployment:
liquibase --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver --classpath="c:\Program Files\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\sqljdbc4.jar" --changeLogFile=databaseChangeLog.xml --url="jdbc:sqlserver://localhost\DEVSQL01;databaseName=MyDatabase" --username=sa --password=xyz status --verbose
5 change sets have not been applied to sa@jdbc:sqlserver://localhost\devsql01:60245;authenticationScheme=nativeAuthentication;xopenStates=false;sendTimeAsDatetime=true;trustServerCertificate=false;sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;packetSize=8000;multiSubnetFailover=false;loginTimeout=15;lockTimeout=-1;lastUpdateCount=true;encrypt=false;disableStatementPooling=true;databaseName=MyDatabase;applicationName=Microsoft JDBC Driver for SQL Server;applicationIntent=readwrite;databaseChangeLog.xml::Table creation::DBA presents
databaseChangeLog.xml::New column::DBA presents
databaseChangeLog.xml::Adding a test user (dev and test environment)::DBA presents
databaseChangeLog.xml::Column extension::DBA presents
databaseChangeLog.xml::Remove test transactions (prod only)::DBA presents
Liquibase 'status' Successful
Apply changes for dev environment - each change qualifies that have no context or have dev in the context attribute:
liquibase --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver --classpath="c:\Program Files\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\sqljdbc4.jar" --changeLogFile=databaseChangeLog.xml --url="jdbc:sqlserver://localhost\DEVSQL01;databaseName=MyDatabase" --username=sa --password=xyz --contexts="dev" status --verbose
4 change sets have not been applied to sa@jdbc:sqlserver://localhost\devsql01:60245;authenticationScheme=nativeAuthentication;xopenStates=false;sendTimeAsDatetime=true;trustServerCertificate=false;sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;packetSize=8000;multiSubnetFailover=false;loginTimeout=15;lockTimeout=-1;lastUpdateCount=true;encrypt=false;disableStatementPooling=true;databaseName=MyDatabase;applicationName=Microsoft JDBC Driver for SQL Server;applicationIntent=readwrite;
databaseChangeLog.xml::Table creation::DBA presents
databaseChangeLog.xml::New column::DBA presents
databaseChangeLog.xml::Adding a test user (dev and test environment)::DBA presents
databaseChangeLog.xml::Column extension::DBA presents
Liquibase 'status' Successful
Apply changes for prod environment - each change qualifies that have no context or have prod in the context attribute:
liquibase --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver --classpath="c:\Program Files\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\sqljdbc4.jar" --changeLogFile=databaseChangeLog.xml --url="jdbc:sqlserver://localhost\DEVSQL01;databaseName=MyDatabase" --username=sa --password=xyz --contexts="prod" status --verbose
4 change sets have not been applied to sa@jdbc:sqlserver://localhost\devsql01:60245;authenticationScheme=nativeAuthentication;xopenStates=false;sendTimeAsDatetime=true;trustServerCertificate=false;sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;packetSize=8000;multiSubnetFailover=false;loginTimeout=15;lockTimeout=-1;lastUpdateCount=true;encrypt=false;disableStatementPooling=true;databaseName=MyDatabase;applicationName=Microsoft JDBC Driver for SQL Server;applicationIntent=readwrite;
databaseChangeLog.xml::Table creation::DBA presents
databaseChangeLog.xml::New column::DBA presents
databaseChangeLog.xml::Column extension::DBA presents
databaseChangeLog.xml::Remove test transactions (prod only)::DBA presents
Liquibase 'status' Successful
Apply changes for an environment that has no special changesets - each change that have no context qualifies:
liquibase --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver --classpath="c:\Program Files\Microsoft JDBC Driver 4.0 for SQL Server\sqljdbc_4.0\enu\sqljdbc4.jar" --changeLogFile=databaseChangeLog.xml --url="jdbc:sqlserver://localhost\DEVSQL01;databaseName=MyDatabase" --username=sa --password=xyz --contexts="test2" status --verbose
3 change sets have not been applied to sa@jdbc:sqlserver://localhost\devsql01:60245;authenticationScheme=nativeAuthentication;xopenStates=false;sendTimeAsDatetime=true;trustServerCertificate=false;sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;packetSize=8000;multiSubnetFailover=false;loginTimeout=15;lockTimeout=-1;lastUpdateCount=true;encrypt=false;disableStatementPooling=true;databaseName=MyDatabase;applicationName=Microsoft JDBC Driver for SQL Server;applicationIntent=readwrite;
databaseChangeLog.xml::Table creation::DBA presents
databaseChangeLog.xml::New column::DBA presents
databaseChangeLog.xml::Column extension::DBA presents
Liquibase 'status' Successful
What's next
You can also tell Liquibase that a changeset should be executed only on a particular database engine - read or watch Database independent script in Liquibase.
