Customize generated SQL from an XML changeset

Sometimes you need to modify SQL in specific changesets and you still want to use the abstracting power of XML to make your change database agnostic. Although Liquibase supports most standard SQL statements with change types, there are situations when the generated SQL needs to be modified for your particular needs. To accommodate this, Liquibase offers a very useful tag: <modifySql>.

Scenario

You need to create a table using an XML changeset for a specific filegroup on SQL Server.

Problem: The XML changeset does not offer a filegroup attribute.
Solution: Use the handy <modifySql> tag!

Example

Let’s use the <modifySql> tag to append the string: “ON FileGroup_Name”.

<changeSet author="SteveZ" id="159-CreateTable_salesTable2">
    <createTable tableName="salesTable2">
        <column name="ID" type="int">
                       <constraints nullable="false"/>
        </column>
               <column name="NAME" type="varchar(20)"/>
                  <column name="REGION" type="varchar(20)"/>
               <column name="MARKET" type="varchar(20)"/>
        </createTable>
        <modifySql  dbms="mssql">  
                 <append value=" ON FileGroupName"/>  
        </modifySql>            
</changeSet>

Notice that the dbms=mssql attribute ensures that this <modifySql> tag will only apply to the SQL Server database platform.

Now you have generated a SQL statement that includes the table within the desired filegroup!

Simply run the liquibase updateSQL command to verify the generated SQL and conduct a quick code review.

-- Changeset changeLog.xml::159-CreateTable_salesTable2::SteveZ
CREATE TABLE salesTable2 (ID int NOT NULL, NAME varchar(20), REGION varchar(20), MARKET varchar(20)) ON FileGroupName
GO

Once the generated SQL is confirmed and is ready to be deployed, run the liquibase update command to apply the changes