Using the Microsoft sqlcmd Utility with Liquibase

Liquibase does a great job of helping developers get database code into source control to enable continuous integration. The system works extremely well when developers don’t have to worry about forming workarounds outside of Liquibase to accommodate using variables or constants in their SQL script that can be reused by other statements.

Users have told us that they want to be able to take advantage of Microsoft’s sqlcmd Utility to execute commands for SQL Server within their Liquibase workflow. Good news! Our latest release of Liquibase v4.8 allows you to do just that.

Supporting SQLCMD

sqlcmd Utility is a popular tool that lets SQL Server users enter Transact-SQL statements, system procedures, and script files. An example of something that sqlcmd Utility does that Liquibase doesn’t do is outputting results from a query. The sqlcmd Utility has ways to set up your tabular output so that a select statement outputs nicely. Since Liquibase relies on JDBC connections to execute changelogs, we needed to add a way to execute via other tools, like sqlcmd Utility. Enter the runwith attribute.

Executing the sqlcmd Utility with Liquibase

The runWith="<executor>" changeset attribute allows you to specify a custom executor to run your SQL. In Liquibase v4.8, we added a specific way you can add and run your changesets with the sqlcmd Utility. Once you add SQLCMD to the PATH environment variable, you can use the new attributes with any type of Liquibase changelog:

SQL changelog runWith:sqlcmd
XML changelog runWith=”sqlcmd”JSON changelog“ runWith”:”sqlcmd”
YAML changelog runWith: sqlcmd

Using Liquibase with sqlcmd Utility

Let’s look at an example. Let’s say you need to execute a script to conditionally set up a new variable-named table with primary key and foreign key constraints, as well as indexes in T-SQL. This type of multi-statement, variable-driven script is not possible using Liquibase alone. With the sqlcmd integration, it is pretty straightforward!

First, save your SQL script into a file. In this case, let’s call it “createMytable.sql”:

set quoted_identifier on
go
 
if exists (select * from sysobjects where id = object_id('dbo.$(MY_TABLE)') and sysstat & 0xf = 3)
   drop table "dbo".$(MY_TABLE)
GO
 
CREATE TABLE "$(MY_TABLE)" (
   "EmployeeID" "int" IDENTITY (1, 1) NOT NULL ,
   "LastName" nvarchar (20) NOT NULL ,
   "FirstName" nvarchar (10) NOT NULL ,
   "Title" nvarchar (30) NULL ,
   "TitleOfCourtesy" nvarchar (25) NULL ,
   "BirthDate" "datetime" NULL ,
   "HireDate" "datetime" NULL ,
   "Address" nvarchar (60) NULL ,
   "City" nvarchar (15) NULL ,
   "Region" nvarchar (15) NULL ,
   "PostalCode" nvarchar (10) NULL ,
   "Country" nvarchar (15) NULL ,
   "HomePhone" nvarchar (24) NULL ,
   "Extension" nvarchar (4) NULL ,
   "Photo" "image" NULL ,
   "Notes" "ntext" NULL ,
   "ReportsTo" "int" NULL ,
   "PhotoPath" nvarchar (255) NULL ,
   CONSTRAINT "PK_$(MY_TABLE)" PRIMARY KEY  CLUSTERED
   (
       "EmployeeID"
   ),
   CONSTRAINT "FK_$(MY_TABLE)_$(MY_TABLE)" FOREIGN KEY
   (
       "ReportsTo"
   ) REFERENCES "dbo"."$(MY_TABLE)" (
       "EmployeeID"
   ),
   CONSTRAINT "CK_$(MY_TABLE)_Birthdate" CHECK (BirthDate < getdate())
)
GO
CREATE  INDEX "LastName" ON "dbo"."$(MY_TABLE)"("LastName")
GO
CREATE  INDEX "PostalCode" ON "dbo"."$(MY_TABLE)"("PostalCode")
GO

Then, in order to make sure this SQL file is included in your Liquibase workflow, add the following runWith="sqlcmd" attribute to your changeset which points to the SQL script to your changelog. (This example is an XML changelog, but you could just as easily use SQL, JSON, or YAML changelogs.)

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 
...<snipped>...
http://www.liquibase.org/xml/ns/pro/liquibase-pro-4.8.xsd">
 
   <changeSet runWith="sqlcmd" author="Liquibase-Pro-User" id="1a::country" objectQuotingStrategy="QUOTE_ALL_OBJECTS" labels="createobjects">
     <sqlFile path="sql/createMyTable.sql" />
   </changeSet>        
</databaseChangeLog>

Finally, run liquibase update as you would for any other deployment job and Liquibase will hand the runwith=sqlcmd changeset to the native tool so it can handle the variables and TSQL. 

Want more information and examples? Visit Liquibase Documentation.

Try Using the sqlcmd Utility with Liquibase

Give the new sqlcmd Utility integration with Liquibase a try! It’s free for you to use during a trial of Liquibase Pro. You’ll get a trial key to unlock this advanced feature and many others to help your team easily manage your SQL Server database change deployments.