sql

The <SQL> Change Type allows you to specify whatever SQL you want.

Uses

It is useful for complex changes that aren't supported through Liquibase's automated Change Types and to work around bugs and limitations of Liquibase. The SQL contained in the <SQL> Change Type can be multi-line.

Using the SQL Change Type

The <SQL> Change Type can also support multi-line statements in the same file. Statements can either be split using a ; at the end of the last line of the SQL or a GO on its own on the line between the statements can be used. Multi-line SQL statements are also supported and only a ; or GO statement will finish a statement, a new line is not enough. Files containing a single statement do not need to use a ; or GO.

The SQL change can also contain comments of either of the following formats:

  • A multi-line comment that starts with /* and ends with */.
  • A single line comment starting with -- and finishing at the end of the line.

Note: By default it will attempt to split statements on a ; or GO at the end of lines. Because of this, if you have a comment or some other non-statement ending ; or GO, don't have it at the end of a line or you will get invalid SQL.

Currently, there is no SQL example for the <sql> tag, since you can just put SQL into a SQL formatted changelog.

<changeSet  author="liquibase-docs"  id="sql-example">
  <sql dbms="!h2, oracle, mysql"
   endDelimiter="\nGO"
   splitStatements="true"
   stripComments="true">insert into person (name) values ('Bob')
   
<comment>What about Bob?</comment>
   </sql>
</changeSet>
changeSet:  
   id:  sql-example  
   author:  liquibase-docs  
   changes:  
    -  sql:  
    	comment:  What about Bob?  
    	dbms:  '!h2,  oracle,  mysql'  
    	endDelimiter:  \nGO  
   		splitStatements:  true  
    	sql:  insert into person (name) values ('Bob')  
    	stripComments:  true

{ 
	"databaseChangeLog": [
		{
			"changeSet": {
				"id": "sql-example",
				"author": "liquibase-docs",
				"changes": [
				{
					"sql": {
						"comment": "What about Bob?",
						"dbms": "!h2, oracle, mysql",
						"endDelimiter": "GO",
						"splitStatements": true,
						"sql": "insert into person (name) values ('Bob')",
						"stripComments": true
					}
				}
			]
		}
	}
]}

Available attributes

Name Description Required for Supports Since
dbms Specifies logical expression of database type(s) on which the change must be applied. Valid database type names are listed on the supported databases page. It can be a comma-separated list of multiple databases or you can also specify that a change is not applicable to a particular database type by prefixing with !. The keywords all and none are also available. all 3.0
endDelimiter Indicates the end of the SQL statement and prevents incomplete SQL statements from being processed when the SQL files contain statements referencing a ; which Liquibase treats as the default end delimiter. You can use the endDelimiter when the changeset contains SQL to create a stored procedure or function that contains the default ; end delimiter to separate the statements inside the procedure.
For more information, see The endDelimiter SQL attribute.		 all
splitStatements Set to false to not have Liquibase split statements on ;'s and GO's. Defaults to true if not set all
sql Specifies the SQL to execute all all
stripComments Set to true to remove any comments in the SQL before executing, otherwise false. all

Database support

Database Notes Auto rollback
DB2/LUW Supported No
DB2/z Supported No
Derby Supported No
Firebird Supported No
H2 Supported No
HyperSQL Supported No
INGRES Supported No
Informix Supported No
MariaDB Supported No
MySQL Supported No
Oracle Supported No
PostgreSQL Supported No
SQL Server Supported No
SQLite Supported No
Sybase Supported No
Sybase Anywhere Supported No