By: Sadequl Hussain | Updated: 2016-07-05 | Comments | Related: 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:
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:
The DATABASECHANGELOG table shows the two changesets have been applied:
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:
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:
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 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=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:
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:
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:
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.
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2016-07-05
