Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Database Change Management with Liquibase - Advanced Features


By:   |   Last Updated: 2016-07-05   |   Comments   |   Related Tips: More > Database Administration

Problem

In the first part of this tip series, we introduced Liquibase, an open source database version and change management tool. We learned how to install it and how to use it to rollout simple database changes and reverse engineer existing databases. In this tip we will introduce some advanced features of the product.

Solution

In part 1 of this series, we were introduced to the Liquibase update and generate ChangeLog commands. We will now see how to selectively apply database changes.

Contexts

Typically, databases exist in different environments (DEV, TST, QA, PROD etc.) or different versions (v 1.0, v 2.0 etc.). There are times you want to apply changes to only one environment and not others. For example, the DEV database may have a log table for debugging purposes which you don't want in your PROD database. Instead of creating two changelogs for two environments, you can define a context for the changeset that creates the table. When running Liquibase, the context is specified for the DEV database and not for the PROD one.

As an example, the following script shows our Liquibase formatted SQL changelog with two changesets. The changesets create a table and adds a constraint:

--liquibase formatted sql
--changeset sadequl_hussain:1 context:development
CREATE TABLE [dbo].[ErrorLog] ( [ErrorID] [int] IDENTITY(1,1) NOT NULL, [ErrorTime] [datetime] NOT NULL DEFAULT GETDATE(), [UserName] [sysname] NOT NULL, [ErrorSeverity] [tinyint] NULL, [ErrorMessage] [nvarchar](2000) NOT NULL )
--changeset sadequl_hussain:2 context:development
ALTER TABLE [dbo].[ErrorLog] ADD CONSTRAINT [PK_ErrorID] PRIMARY KEY CLUSTERED (ErrorID)

If you look closely, both changesets have a new attribute: context. You can use any value for context, in our example, we are using a value of "development". We want this changeset to be applied only in the development database, so "development" seemed more appropriate.

To test our changelog, we created two databases: one named context_dev and the other one as context_prd:

DEV and PRD Versions of context Database

The changelog file was then applied to the context_dev database:

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" 
--url="jdbc:sqlserver://<SQL Server name or IP>:1433;databaseName=context_dev;integratedSecurity=false;" 
--changeLogFile="D:\Liquibase\databaseChangeLog\contextExampleChangeLog.sql" 
--username=liquibase 
--password=liquibase
--contexts=development 
update

The extra option added here is contexts. It has the same context as defined in the changesets. If successful, we should see the table created in the database along with the DATABASECHANGELOG and DATABASECHANGELOGLOCK tables:

Context DEV Database with Object Created

The DATABASECHANGELOG table shows the two changesets have been applied:

DATABASECHANGELOG Table of context_dev Database

Next the same changelog file was applied against the context_prd database, with a different context. We cannot omit the contexts option, otherwise Liquibase would run the development context anyway, so we used a fake context name: "production":

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" 
--url="jdbc:sqlserver://<SQL Server name or IP>:1433;databaseName=context_prd;integratedSecurity=false;" 
--changeLogFile="D:\Liquibase\databaseChangeLog\contextExampleChangeLog.sql" 
--username=liquibase 
--password=liquibase 
--contexts=production 
update

This time the Errorlog table would not be created, but the DATABASECHANGELOG and DATABASECHANGELOGLOCK would:

Context PRD Database with No Object Created

This is how Liquibase lets you create the same codebase for different databases. Not only objects, you can also ingest particular rows of data based on contexts. For example, version 1.0 of your database may need a different set of configuration metadata than version 2.0. You can write two different INSERT changesets with different contexts. Running Liquibase with the intended context will ensure the correct metadata is inserted.

Database Diff

There are many tools available today which can compare both database schemas and data. Liquibase is no different. With the Liquibase diff command, it's possible to do a schema comparison and - to a limited extent - data comparison between two databases. Taking our previous example of the context databases, let's say we want to compare the PROD version with the DEV version. Running the following command compares the reference database (context_dev) with the comparison database (context_prd):

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" 
--url="jdbc:sqlserver://<SQL Server name or IP>:1433;databaseName=context_prd;integratedSecurity=false;" 
--username=liquibase 
--password=liquibase 
diff 
--referenceUrl="jdbc:sqlserver://<SQL Server name or IP>:1433;databaseName=context_dev;integratedSecurity=false;" 
--referenceUsername=liquibase 
--referencePassword=liquibase 

The referenceUrl, referenceUsername and referencePassword parameters are command parameters for the diff command. The output will look like this:

Diff Results:
Reference Database: liquibase @ jdbc:sqlserver://<SQL Server name or IP>:1433;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=context_dev;applicationName=Microsoft JDBC Driver for SQL Server;applicationIntent=readwrite; (Default Schema: dbo)
Comparison Database: liquibase @ jdbc:sqlserver://<SQL Server name or IP>:1433;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=context_prd;applicationName=Microsoft JDBC Driver for SQL Server;applicationIntent=readwrite; (Default Schema: dbo)
Product Name: EQUAL
Product Version: EQUAL
Missing Catalog(s): NONE
Unexpected Catalog(s): NONE
Changed Catalog(s):
context_dev
name changed from 'context_dev' to 'context_prd'
Missing Column(s):
ErrorLog.ErrorID
ErrorLog.ErrorMessage
ErrorLog.ErrorSeverity
ErrorLog.ErrorTime
ErrorLog.UserName
Unexpected Column(s): NONE
Changed Column(s): NONE
Missing Foreign Key(s): NONE
Unexpected Foreign Key(s): NONE
Changed Foreign Key(s): NONE
Missing Index(s):
PK_ErrorID unique on ErrorLog(ErrorID)
Unexpected Index(s): NONE
Changed Index(s): NONE
Missing Primary Key(s):
PK_ErrorID on ErrorLog(ErrorID)
Unexpected Primary Key(s): NONE
Changed Primary Key(s): NONE
Missing Schema(s): NONE
Unexpected Schema(s): NONE
Changed Schema(s): NONE
Missing Sequence(s): NONE
Unexpected Sequence(s): NONE
Changed Sequence(s): NONE
Missing Stored Procedure(s): NONE
Unexpected Stored Procedure(s): NONE
Changed Stored Procedure(s): NONE
Missing Table(s):
ErrorLog
Unexpected Table(s): NONE
Changed Table(s): NONE
Missing Unique Constraint(s): NONE
Unexpected Unique Constraint(s): NONE
Changed Unique Constraint(s): NONE
Missing View(s): NONE
Unexpected View(s): NONE
Changed View(s): NONE
Liquibase 'diff' Successful

And like other database diff tools, Liquibase can also generate a changelog for the missing objects. This is done with the diffChangeLog command:

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" 
--url="jdbc:sqlserver://<SQL Server name or IP>:1433;databaseName=context_prd;integratedSecurity=false;" 
--username=liquibase 
--password=liquibase 
diffChangeLog 
--referenceUrl="jdbc:sqlserver://<SQL Server name or IP>:1433;databaseName=context_dev;integratedSecurity=false;" 
--referenceUsername=liquibase 
--referencePassword=liquibase 

The command output will be an XML document as shown below:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd">
 <changeSet author="sadequl_hussain (generated)" id="1461404109198-1">
  <createTable tableName="ErrorLog">
   <column autoIncrement="true" name="ErrorID" type="int">
    <constraints primaryKey="true" primaryKeyName="PK_ErrorID"/>
   </column>
   <column defaultValueComputed="getdate()" name="ErrorTime" type="datetime">
    <constraints nullable="false"/>
   </column>
   <column name="UserName" type="sysname">
    <constraints nullable="false"/>
   </column>
   <column name="ErrorSeverity" type="tinyint"/>
   <column name="ErrorMessage" type="nvarchar(2000)">
    <constraints nullable="false"/>
   </column>
  </createTable>
 </changeSet>
</databaseChangeLog>

We can easily pipe the command output to a file which then becomes our reconciliation script.

Preconditions

Another method of conditionally running Liquibase changesets is via preconditions. Preconditions can be defined either at the changelog level, or inside a changeset. When defined at changelog level, none of the changesets will run if the precondition fails. When defined inside a changeset, the changeset will not run if the precondition fails. Within the precondition, we can check a number of things:

Precondition Name Checks Performed
dbms Checks if the script is running against a specific DBMS (e.g. SQL Server or Oracle)
runningAs Checks if the script is running as a specific database user
changesetExecuted Checks if the changeset has already been executed
tableExists Checks if a table exists in the database
columnExists Checks if a column exists in a table in the database
viewExists Checks if a view exists in the database
indexExists Checks if a index exists in the database
foreignkKeyConstraintExists Checks if a foreign key exists in a table
primaryKeyExists Checks if a table has the specified primary key
sequenceExists Checks if a sequence exists in the database
sqlCheck Runs a SQL command and checks the output returned. The SQL command must return a single value (one row and column) like the output of COUNT, SUM, MIN, MAX or AVG.

Each of these preconditions can again have one more attributes. For example, when specifying the viewExists precondition, only two attributes are necessary: schemaName and viewName. When specifying the primaryKeyExists precondition, we have to specify more attributes: schemaName, tableName and primaryKeyName.

Naturally, you would want to take some actions when a precondition fails. There can be two scenarios:

  • Failure: When the precondition check fails
  • Error: When the check itself cannot be performed and an exception is thrown

Both the failure situations can be handled by a number of attributes. Some of these include:

  • onFail: What to do when the precondition check fails
  • onFailMessage: Custom message to output when the precondition check fails
  • onError: What to do when the precondition itself fails
  • onErrorMessage: Custom message to output when the precondition itself fails

There are four possible actions for onFail and onError attributes:

  • HALT: The entire changelog script stops running and Liquibase exits. This is the default behavior
  • WARN: Shows a warning and continues executing the changelog / changeset
  • CONTINUE: Skips running the current changeset and moves to the next changeset for execution, if one exists. The changeset will be attempted the next Liquibase run
  • MARK_RAN: Skips running the current changeset, but marks it as run in the DATABASECHANGELOG table

Let's see how this works with an example. In part 1 of this series, we created a table called "customer" in the liquibase_test database:

Liquibase_Test Database with Customer Table

In the following code snippet, we have added a new changeset to create a table: "products":

<?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" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
<changeSet id="1" author="sadequl_hussain"> <createTable tableName="customer"> <column name="id" type="int"> <constraints primaryKey="true" nullable="false"/> </column> <column name="customer_name" type="varchar(50)"> <constraints nullable="false"/> </column> <column name="active" type="boolean" defaultValueBoolean="true"/> </createTable> </changeSet> <changeSet id="2" author="sadequl_hussain"> <addColumn tableName="customer"> <column name="join_date" type="datetime"/> </addColumn> </changeSet> <changeSet id="3" author="sadequl_hussain"> <createTable tableName="products"> <column name="product_id" type="int"> <constraints primaryKey="true" nullable="false"/> </column> <column name="product_name" type="varchar(50)"> <constraints nullable="false"/> </column> <column name="active" type="boolean" defaultValueBoolean="true"/> </createTable> </changeSet> </databaseChangeLog>

We would now like to add another new table called "orders". But for that, the script needs to ensure the "customer" and "products" tables are already in place. This is a good candidate for precondition. Also, we would like to ensure the changelog is always run under the liquibase user account. The modified changelog file now looks like this:

<?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" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd"> <preConditions onFail="HALT"> <runningAs username="liquibase"/> </preConditions> <changeSet id="1" author="sadequl_hussain"> <createTable tableName="customer"> <column name="id" type="int"> <constraints primaryKey="true" nullable="false"/> </column> <column name="customer_name" type="varchar(50)"> <constraints nullable="false"/> </column> <column name="active" type="boolean" defaultValueBoolean="true"/> </createTable> </changeSet> <changeSet id="2" author="sadequl_hussain"> <addColumn tableName="customer"> <column name="join_date" type="datetime"/> </addColumn> </changeSet> <changeSet id="3" author="sadequl_hussain"> <createTable tableName="products"> <column name="product_id" type="int"> <constraints primaryKey="true" nullable="false"/> </column> <column name="product_name" type="varchar(50)"> <constraints nullable="false"/> </column> <column name="active" type="boolean" defaultValueBoolean="true"/> </createTable> </changeSet> <changeSet id="4" author="sadequl_hussain" failOnError="true"> <preConditions onFail="HALT"> <tableExists schemaName="dbo" tableName="customer"/> <tableExists schemaName="dbo" tableName="products"/> </preConditions> <comment> The orders table should be created after the customer and products table. </comment> <createTable tableName="orders"> <column name="order_id" type="int"> <constraints primaryKey="true" nullable="false"/> </column> <column name="customer_id" type="int"> <constraints nullable="false"/> </column> <column name="product_id" type="int"> <constraints nullable="false"/> </column> <column name="order_date" type="datetime"> <constraints nullable="false"/> </column> </createTable> </changeSet> <changeSet id="5" author="sadequl_hussain" failOnError="true"> <preConditions onFail="HALT"> <primaryKeyExists tableName="customer" primaryKeyName="PK_CUSTOMER"/> <primaryKeyExists tableName="products" primaryKeyName="PK_PRODUCTS"/> </preConditions> <comment> The customer and products table should have primary key constraints already created. </comment> <addForeignKeyConstraint baseColumnNames="customer_id" baseTableName="orders" constraintName="FK_CUSTOMER_ID" referencedColumnNames="id" referencedTableName="customer"/> <addForeignKeyConstraint baseColumnNames="product_id" baseTableName="orders" constraintName="FK_PRODUCT_ID" referencedColumnNames="product_id" referencedTableName="products"/> </changeSet> </databaseChangeLog>

Note how we are building our precondition here.

  • First, we want to ensure the script runs as the liquibase user which has db_owner privileges to the database. If the user account for the command is different, the script exits
  • Next, we are creating the orders table and stipulating two preconditions: the products and customer tables must exist. We are also specifying that if these preconditions fail then Liquibase should stop running the changelog file. We have also added a comment to this changeset for future reference and for clarity. Comments should always come after precondition tags.
  • Finally, we are adding a foreign key to the orders table. However, this means primary keys need to exist in both the referenced tables. Another set of preconditions are specified at the beginning of the changeset and a comment shows why we are doing this check.

Running Liquibase to apply the changelog shows it is successful:

liquibase --defaultsFile="C:\Liquibase\liquibase.properties" --logLevel=info update
INFO 25/04/16 8:17 PM: liquibase: Successfully acquired change log lock INFO 25/04/16 8:17 PM: liquibase: Reading from [dbo].[DATABASECHANGELOG] INFO 25/04/16 8:17 PM: liquibase: D:/Liquibase/databaseChangeLog/dbchangelog.xml: D:/Liquibase/databaseChangeLog/dbchangelog.xml::4::sadequl_hussain: Table orders created INFO 25/04/16 8:17 PM: liquibase: D:/Liquibase/databaseChangeLog/dbchangelog.xml: D:/Liquibase/databaseChangeLog/dbchangelog.xml::4::sadequl_hussain: ChangeSet D:/Liquibase/databaseChangeLog/dbchangelog.xml::4::sadequl_hussain ran successfully in 1223ms INFO 25/04/16 8:18 PM: liquibase: D:/Liquibase/databaseChangeLog/dbchangelog.xml: D:/Liquibase/databaseChangeLog/dbchangelog.xml::5::sadequl_hussain: Foreign key constraint added to orders (customer_id) INFO 25/04/16 8:18 PM: liquibase: D:/Liquibase/databaseChangeLog/dbchangelog.xml: D:/Liquibase/databaseChangeLog/dbchangelog.xml::5::sadequl_hussain: Foreign key constraint added to orders (product_id) INFO 25/04/16 8:18 PM: liquibase: D:/Liquibase/databaseChangeLog/dbchangelog.xml: D:/Liquibase/databaseChangeLog/dbchangelog.xml::5::sadequl_hussain: ChangeSet D:/Liquibase/databaseChangeLog/dbchangelog.xml::5::sadequl_hussain ran successfully in 2646ms INFO 25/04/16 8:18 PM: liquibase: Successfully released change log lock Liquibase Update Successful

Now let's suppose this was our development system and everything ran fine here. It's now time for us to port theses new changes to production. However, the changelog file was modified for some reason and the product table creation was either omitted or moved after the orders table creation. A Liquibase run would produce an error like this:

Unexpected error running Liquibase: Preconditions Failed

And this is how Liquibase can maintain schema integrity between two environments or two versions of the same database.

So far we added only three tables and a few constraints in our database and already the XML changelog file looks quite long. If you are a SQL developer, this may seem a bit cumbersome to write or even read through. In the first part of this series we mentioned Liquibase can run changelog files written in any of the four formats: XML, YAML, JSON or SQL. As a SQL developer, you can write the same file in Liquibase formatted SQL.

More Liquibase Commands

Here are few more Liquibase commands:

validate

This command checks if your changelog file conforms to Liquibase's syntax. If the file is not valid, Liquibase will print an error message. Here is an example where we have deliberately created a duplicate changeset id in our changelog file.

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" 
 --url="jdbc:sqlserver://<SQL Server name or IP>:1433;databaseName=AdventureWorks2012;integratedSecurity=false;" 
 --changeLogFile="D:\Liquibase\databaseChangeLog\AdventureWorks2012ChangeLog.xml" 
 --username=liquibase 
 --password=liquibase 
 --logLevel=info 
 validate
Validation Error: 1 change sets had duplicate identifiers D:/Liquibase/databaseChangeLog/AdventureWorks2012_SalesSchema_ChangeLog.xml::1463574109733-2::sadequl_hussain (generated) Liquibase 'validate' Successful

dropAll

This command drops all the database objects owned by the specified user. This is useful when you are developing a database and halfway through you find the changelog has become too complex or it's best to drop all objects and start with an empty database. However, this command does not drop stored procedures, functions, triggers etc. 

updateSQL

This command prints out the SQL commands it would have executed instead of applying the un-run changesets. The screen output can easily be piped to a file. Running updateSQL has a few advantages:

  • It can be used to generate SQL scripts that a DBA can examine before applying to production database.
  • It can be used by developers for debugging and troubleshooting purposes.
  • It can be used to generate SQL script files that need to be preserved for regulatory compliance.

In the code snippet below, we are running updateSQL against a new database for the script we developed before. We are sending the screen output to a file. This file can be viewed here.

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" 
 --url="jdbc:sqlserver://<SQL Server name or IP>:1433;databaseName=liquibase_qa;integratedSecurity=false;" 
 --changeLogFile="D:\Liquibase\databaseChangeLog\dbchangelog.xml" 
 --username=liquibase 
 --password=liquibase 
 --logLevel=info 
 updateSQL > D:\Liquibase\liquibase_qa_script.sql

Rolling Back Database Changes

Before we finish today, we will see how Liquibase can rollback the changes it made to the database. Having a rollback capability makes Liquibase a powerful deployment tool. Rollbacks can be done if:

  • The rollback command is already included in the changeset
  • The rollback is performed
    • to change the database state back to a previous date/time
    • to change the database to a previous state marked by a "tag"
    • to change the database to a state that existed a number of changesets before

For some statements, you don't need to specify rollbacks. These rollbacks are automatic and implicit because of the ACID principle followed by most RDBMS. For example, if you are inserting a row and it fails a table constraint, the INSERT statement will be automatically rolled back. However, if you are inserting a series of rows in your changeset and one of them fails halfway through, none of the already inserted rows will be rolled back. Then there are changes which may need explicit rollbacks to be defined. DDL statements are one example. Some databases like PostgreSQL allows automatic DDL statement rollback while others like SQL Server does not allow this.

To illustrate, let's say we are creating another changeset in our database. This changeset is to roll out a very simple stored procedure. But before that, we will tag our database in case we need to roll it back. A tag is nothing but a marker for the database at any particular point in time. A tag is given so when rolling back, we can always go back to that tag. The command below shows how we are creating the tag. All we have to specify is the tag command along with a tag name:

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" 
 --url="jdbc:sqlserver://<SQL Server name or IP>:1433;databaseName=liquibase_test;integratedSecurity=false;" 
 --changeLogFile="D:\Liquibase\databaseChangeLog\dbchangelog.xml" 
 --username=liquibase 
 --password=liquibase 
 --logLevel=info 
 tag rollback_sp

The output will be like this:

...
Successfully tagged [email protected]:sqlserver://<SQL Server name or IP>:1433;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=liquibase_test;applicationName=Microsoft 
JDBC Driver for SQL Server;applicationIntent=readwrite;
Liquibase 'tag' Successful

The DATABASECHANGELOG table now shows we have a tag applied to the last changeset applied. This was the changeset where we created a foreign key constraint:

Rollback Tag in DATABASECHANGELOG Table

Next we are creating a new changelog file for rolling out a stored procedure:

--liquibase formatted sql
--changeset sadequl_hussain:1 runOnChange:true failOnError:true
IF EXISTS (SELECT * FROM sys.objects WHERE name='sp_GetCustomers' AND type='P') DROP PROCEDURE sp_GetCustomers;
CREATE PROCEDURE sp_GetCustomers AS SELECT * FROM dbo.customer
--rollback DROP PROCEDURE sp_GetCustomers

This is an extremely simple stored procedure rolled out by a fairly simple changelog. However, there are three things to note here:

  • We have instructed Liquibase to run this changeset if it changes. Usually Liquibase will not run a changeset that has already been applied. If the changeset text changes, the MD5 checksums will not match and Liquibase will throw an error. We are overriding the default behavior here because stored procedures are often changed for debugging, testing and enhancement purposes. Instead of creating a new changeset for each change, we have asked Liquibase to run the changeset if its contents change.
  • We have instructed Liquibase to fail the run if there is an error when applying the changeset
  • We have included a rollback command for drooping the stored procedure

Running the changelog file will create the stored procedure in the database:

Successfull Rollout of Stored Procedure by Liquibase

Now let's say for some reason the stored procedure was something that we did not want in our database and we would like to roll it back. Remember we had tagged our database before we ran this changeset. We can roll our database back to that tag using the following command:

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" 
 --url="jdbc:sqlserver://<SQL Server name or IP>:1433;databaseName=liquibase_test;integratedSecurity=false;" 
 --changeLogFile="D:\Liquibase\databaseChangeLog\dbchangelog_sp.sql" 
 --username=liquibase 
 --password=liquibase 
 --logLevel=info 
 rollback rollback_sp

It's as simple as that: all we had to do was to use the rollback command with the tag name. The stored procedure would be gone now, and so would be the record from the DATABASECHANGELOG table:

Successfull Rollback of Stored Procedure by Liquibase

What happens if we forgot to tag the database before the stored procedure was created? We could always use the rollbackToDate command and use the date time of the changeset we wanted to roll back to:

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" 
 --url="jdbc:sqlserver://<SQL Server name or IP>:1433;databaseName=liquibase_test;integratedSecurity=false;" 
 --changeLogFile="D:\Liquibase\databaseChangeLog\dbchangelog_sp.sql" 
 --username=liquibase 
 --password=liquibase 
 --logLevel=info 
 rollback rollbackToDate 2016-04-25 10:19:15.880

There's more to it. Suppose we did not want Liquibase to actually rollback the already committed changes, but rather generate a rollback script. We could use the rollbackSQL or rollbackToDateSQL commands. The output would be a series of SQL commands to rollback the database. We can then send the screen output to a file.

If we wanted to go one step further and generate the rollback SQL script without actually applying the change, we could use the futureRollbackSQL command.

Finally, Liquibase also allows the updateTestingRollback command. This command actually updates the database with the changeset, then tests its rollback and if successful, applies the change again.

Conclusion

We have covered some core functionality of Liquibase here. As you can see, it's neither a diff tool, nor a simple schema generator. Instead, Liquibase can be used to selectively and intelligently rollout or rollback database changes. In the next and final part, we will talk about some advanced features like integrating Liquibase with source control systems and deployment tools.

Next Steps
  • Use Liquibase to reverse engineer a locally restored version of a development or test database. Tweak the script, see if you can rollout the structure and data to a new test database.
  • Make use of pre-conditions and contexts in your changelog and see how you can selectively apply some of the changes to a different database.


Last Updated: 2016-07-05


next webcast button


next tip button



About the author
MSSQLTips author Sadequl Hussain Sadequl Hussain has been working with SQL Server since version 6.5 and his life as a DBA has seen him managing mission critical systems.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools