Database-independent script in Liquibase

Liquibase

xmlToSqlA need of writing database-independent SQL scripts may seem abstract to developers that implement systems for only one database engine. But there are people, who develop software that must be able to work with two or more. Imagine an application for accounting that your customer can install with Oracle or SQL Server - when it requires high reliability and availability or even with a community version of MySQL - when the cost matters the most.

Despite of the fact that SQL is not the same across different database engines, especially DDL statements, Liquibase can help achieving this.

More information about database vendor-independent software can be found in Database-agnostic applications article. If you do not have experience with Liquibase, check Liquibase - how to use article.

Liquibase offers four formats of changelog files: XML, YAML, JSON, SQL. If portability to different database engines is important for you, you will probably appreciate XML format. An empty file (with no changesets) looks as below:

<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">

// changesets go here

</databaseChangeLog>

The whole content of the file is wrapped by databaseChangeLog tag. When Liquibase applies changesets to a database, it recognizes what database engine it is connected to and uses this information to produce a proper SQL script that is valid in this particular case. Some examples and different options of customizing the script can be found below.

 

Differences covered by Liquibase

Changing database structure or data can be done in this file using XML tags. The Liquibase author did his best to make those tags as much database-independent as possible. Thanks to that, in many cases a developer can write one changeset using XML and Liquibase will take care of database differences. For example, the below script creates a table and extends one column.

<?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="create MESSAGE table" author="dba-presents.com">
        <createTable tableName="MESSAGE">
            <column name="MSG_ID" type="int" />
            <column name="MSG_TITLE" type="varchar(100)" />
        </createTable>
    </changeSet>

    <changeSet id="extend MSG_TITLE to 120 characters" author="dba-presents.com">
        <modifyDataType tableName="MESSAGE" columnName="MSG_TITLE" newDataType="varchar(120)" />
    </changeSet>

</databaseChangeLog>

The XML file consists of two changesets. For testing purposes, I have two databases: SQL Server and Oracle. Liquibase has updateSQL option which prints instead of executing the generated script. When connected to SQL Server, the output is as below. I added intends and colors to make it more readable.

c:\> liquibase --driver=com.microsoft.sqlserver.jdbc.SQLServerDriver --url="jdbc:sqlserver://localhost\DEVSQL01;databaseName=MyDatabase" --username=liquibase --password=liquibasePass --changeLogFile=db-change-log.xml updateSQL
-- Changeset db-change-log.xml::create MESSAGE table::dba-presents.com
CREATE TABLE [dbo].[MESSAGE] (
	[MSG_ID] INT,
	[MSG_TITLE] VARCHAR(100)
)
GO

-- Changeset db-change-log.xml::extend MSG_TITLE to 120 characters::dba-presents.com
ALTER TABLE [dbo].[MESSAGE] ALTER COLUMN [MSG_TITLE] VARCHAR(120)
GO

A script generated for Oracle is slightly different:

c:\> liquibase --driver=oracle.jdbc.driver.OracleDriver --url="jdbc:oracle:thin:@//192.168.201.200:1521/pdbrac" --username=web --password=webPass --changeLogFile=db-change-log.xml updateSQL
-- Changeset db-change-log.xml::create MESSAGE table::dba-presents.com
CREATE TABLE WEB.MESSAGE (
        MSG_ID NUMBER(10),
        MSG_TITLE VARCHAR2(100)
);
-- Changeset db-change-log.xml::extend MSG_TITLE to 120 characters::dba-presents.com
ALTER TABLE WEB.MESSAGE MODIFY MSG_TITLE VARCHAR2(120);

Differences between the scripts are noticeable:

  1. SQL Server uses GO statement to separate scripts and Oracle requires semicolons at the end of statements.
  2. SQL Server uses square brackets as a way of using irregular identifiers.
  3. Liquibase recognizes schemas of the connections created to the databases - dbo and WEB.
  4. Both database engines use different data types. Liquibase int converts to number(10) in Oracle, varchar turns into varchar2.
  5. Changing a column data type is done by ALTER statement in SQL Server but Oracle uses MODIFY.

Those are only examples of what differences Liquibase automatically overcomes.

Do not miss valuable content. You will receive a monthly summary email. You can unsubscribe anytime.

 

Database-specific functions

Obviously, there are some functions that have different names between database engines. A good example is a method of getting the current date and time. SQL Server has getDate() function but Oracle has sysdate. An easy way to create a changeset to set MSG_SEND_DATE of all rows to the current time in a way that will work on both database engines is to use properties:

<?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">

    <property name="now" value="sysdate" dbms="oracle"/>
    <property name="now" value="getDate()" dbms="mssql"/>

    <changeSet id="create MESSAGE table" author="dba-presents.com">
        <update tableName="MESSAGE">
            <column name="MSG_SEND_DATE" valueDate="${now}" />
        </update>
    </changeSet>

</databaseChangeLog>

Liquibase connects to a database, checks which engine it is and if it is oracle it sets now property to sysdate, if mssql then getDate(). The above XML document converts to the following script for SQL Server:

-- Changeset db-change-log.xml::create MESSAGE table::dba-presents.com
UPDATE [dbo].[MESSAGE] SET [MSG_SEND_DATE] = GETDATE()
GO

and to the one for Oracle:

-- Changeset db-change-log.xml::create MESSAGE table::dba-presents.com
UPDATE WEB.MESSAGE SET MSG_SEND_DATE = sysdate;

The properties helps to keep having one changeset and produce valid scripts for different database engines even if the code is not universal.

 

Separate changesets for different engines

When the above options do not help, the last resort solution is to create separate changesets for all database engines that have to be supported. An example can be an update statement with a join operation. It is allowed in SQL Server but it is not in Oracle. Of course, a similar result can be achieved but in a different way. To support such cases Liquibase has dbms attribute of changeSet tag. A sample code is shown below.

<?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="Set username for SQL Server" author="dba-presents.com" dbms="mssql">
        <sql>
            UPDATE M
            SET MSG_USERNAME = U.USR_USERNAME
            FROM [MESSAGE] M
            JOIN [USER] U ON M.MSG_USR_ID = U.USR_ID
        </sql>
    </changeSet>

    <changeSet id="Set username for Oracle" author="dba-presents.com" dbms="oracle">
        <sql>
            UPDATE MESSAGE
            SET MSG_USERNAME = (
            SELECT USR_USERNAME
            FROM USER
            WHERE USR_ID = MSG_USERNAME);
        </sql>
    </changeSet>

</databaseChangeLog>

A script for SQL Server:

UPDATE M
SET MSG_USERNAME = U.USR_USERNAME
FROM [MESSAGE] M
	JOIN [USER] U ON M.MSG_USR_ID = U.USR_ID
GO

A script for Oracle:

UPDATE MESSAGE
SET MSG_USERNAME = (
        SELECT USR_USERNAME
        FROM USER 
        WHERE USR_ID = MSG_USERNAME);

Liquibase executes on a particular database only those changesets that do not have dbms attribute or have a matching one.

 

Conclusions

One DDL + DML script for multiple database engines is a brilliant idea. It is a big step in developing portable systems from the database perspective. Liquibase is a tool that makes it much easier. If I had to point out one drawback of it, I would say that it is a need of using XML instead SQL whenever it is possible. We are tied to SQL, but if portability matters, it might be worth to change it to XML and Liquibase.

 

What's next

You can also tell Liquibase to run a changeset only on a particular environment. Read Environments in Liquibase changesets.

 

If you like what I do, consider buying me a coffee :)

Buy me a coffeeBuy me a coffee