sql

The sql tag allows you to specify whatever SQL you want. It is useful for complex changes that aren’t supported through Liquibase's automated Change Type tags and to work around bugs and limitations of Liquibase. The SQL contained in the sql tag can be multi-line.

The createProcedure Change Type is the best way to create stored procedures.

The ‘sql’ tag 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 <space>–<space> 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.

Available attributes

Name	Description	Required for	Supports	Since
dbms	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	Delimiter to apply to the end of the statement. Defaults to ';', may be set to ''.		all
splitStatements	Set to false to not have Liquibase split statements on ;'s and GO's. Defaults to true if not set		all
sql	The SQL to execute	all	all
stripComments	Set to true to remove any comments in the SQL before executing, otherwise false.		all

XML example

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

YAML example

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

JSON example

{  "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  }  }]  }  }

SQL example

insert  into  person  (name)  values  ('Bob')  
GO

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