How To: Generate SQL scripts from Liquibase changesets

There is a useful feature in Liquibase called Offline Database Support that allows you to generate the SQL scripts from the changesets without actually updating the database. This Offline Database Support functionality is called updateSql. This becomes handy when you cannot directly run Liquibase changesets on the target database or if the output of the changesets needs to be reviewed.

Note that updateSql checks the transactions within the DATABASECHANGELOG table to determine what SQL statements to generate. With that said, in the event that you cannot directly run the changesets against the target database, you can ask for the DATABASECHANGELOG table and use that to keep the history correct.

To streamline the process of generating SQL scripts, it would be useful to create a batch file that would contain the command and parameters. Let’s say that the filename is GenerateSQL.bat which currently contains the following values targeting an Oracle database:

C:\Dev\liquibase-3.3.2-bin\liquibase ^
--classpath="C:\Dev\liquibase-3.3.2-bin\lib\ojdbc7.jar" ^
--driver="oracle.jdbc.driver.OracleDriver" ^
--url=jdbc:"oracle:thin:@ORA-DBDEV:1521:DEVORA11G" ^
--username=DEV_LIQUIBASE_TEST ^
--password=asd123 ^
--changeLogFile="C:\Dev\LiquiBase\ORACLE\%1" ^
--logLevel=debug ^
--logFile="C:\Dev\LiquiBase\ORACLE\output.oracle.log" ^
updateSQL > C:\Dev\LiquiBase\ORACLE\output.oracle.%1.sql

where…
Line 01: Location of Liquibase binary
Line 02: Location of JDBC driver. Valid values are:

  • ojdbc7.jar (ORACLE)
  • sqljdbc41.jar (MSSQL)

Line 03: Name of the JDBC driver. Valid values are:

  • oracle.jdbc.driver.OracleDriver (ORACLE)
  • com.microsoft.sqlserver.jdbc.SQLServerDriver (MSSQL)

Line 04: Details of the database server. Valid values are:

  • jdbc:”oracle:thin:@<SERVER_NAME>:<PORT>:<SERVICE_ID>” (ORACLE)
    • jdbc:”oracle:thin:@SERVER01:1521:DEVORA11G”
  • jdbc:”sqlserver://<SERVER_NAME><SERVER_INSTANCE>;databaseName=<DB_NAME>” (MSSQL)
    • jdbc:”sqlserver://SERVER01SQL2014;databaseName=DEVSQL”

Line 05: Database server username
Line 06: Database server password
Line 07: Location of the Liquibase change set file (*.xml)
Line 09: The log file for the operation
Line 10: The file where the generated SQL scripts will be stored.

Lines 07 and 10 have “%1” which represents an argument passed during execution of the batch file. This batch file can be used as follows:

C:\Dev\Liquibase\GenerateSQL.bat ChangeSetFileName.xml

This command will create output.oracle.log and output.oracle.ChangeSetFileName.xml.sql

You can integrate this process in Visual Studio by following this article. Note that you need to change the contents of the batch file to use updateSql.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s