Change: ‘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 refactoring tags and to work around bugs and limitations of Liquibase. The SQL contained in the sql tag can be multi-line.

The createProcedure refactoring is the best way to create stored procedures.

The ‘sql’ tag can also support multiline 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.Multiline 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 multiline 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

NameDescriptionRequired ForSupportsSince
commentall
dbmsall3.0
endDelimiterDelimiter to apply to the end of the statement. Defaults to ';', may be set to ''.all
splitStatementsSet to false to not have liquibase split statements on ;'s and GO's. Defaults to true if not setall
sqlallall
stripCommentsSet to true to remove any comments in the SQL before executing, otherwise false. Defaults to false if not setall
<changeSet author="liquibase-docs" id="sql-example">
    <sql dbms="h2, oracle"
            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
      endDelimiter: \nGO
      splitStatements: true
      sql: insert into person (name) values ('Bob')
      stripComments: true
{
  "changeSet": {
    "id": "sql-example",
    "author": "liquibase-docs",
    "changes": [
      {
        "sql": {
          "comment": "What about Bob?",
          "dbms": "h2, oracle",
          "endDelimiter": "\\nGO",
          "splitStatements": true,
          "sql": "insert into person (name) values ('Bob')",
          "stripComments": true
        }
      }]
    
  }
}

SQL Generated From Above Sample (MySQL)

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

Database Support

DatabaseNotesAuto Rollback
DB2SupportedNo
DerbySupportedNo
FirebirdSupportedNo
H2SupportedNo
HyperSQLSupportedNo
InformixSupportedNo
MySQLSupportedNo
OracleSupportedNo
PostgreSQLSupportedNo
SQL ServerSupportedNo
SQLiteSupportedNo
SybaseSupportedNo
Sybase AnywhereSupportedNo

Missing, incomplete or wrong information? Please send a pull request