sqlFile

The <sqlFile> Change Type allows you to specify SQL statements in an external file.

Uses

The <sqlFile> Change Type is useful for complex changes that are not supported through Liquibase automated Change Types such as stored procedures. The SQL contained in the <sqlFile> Change Type can be multi-line.

Tip: If you use psql, SQL Plus, or sqlcmd utility and Liquibase Pro, see Use PSQL and runWith on PostgreSQL, Use SQL Plus and runWith on Oracle, and Use SQLCMD and runWith on MSSQL Server.

Using the sqlFile Change Type

<sqlFile> finds the file in the search path.

<sqlFile> supports multiple SQL statements in the same file:

  • Single-line SQL statements can be separated using a ; at the end of the last line of the SQL or a GO statement. A GO statement must be on a separate line between the two SQL statements.
  • Multi-line SQL statements are also supported. Only a ; or GO statement will finish a multi-line SQL statement, a new line is not enough.
  • Files containing a single SQL statement do not need to use a ; or GO statement.

<sqlFile> supports comments using 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.

You can also nest <sqlFile> within the rollback tag in a changeset:

Note: By default Liquibase will attempt to split statements on a ; or GO at the end of lines. If you have a comment or non-statement ending with ; or GO, do not include it at the end of a line or you will get invalid SQL.

Currently, there is no SQL Example for the <sqlFile> Change Type because you can just put SQL directly into a SQL formatted changelog.

<changeSet author="liquibase-docs" id="sqlFile-example">
     <sqlFile dbms="!h2, oracle, mysql"
        encoding="UTF-8"
        endDelimiter="\nGO"
        path="my/path/file.sql"
        relativeToChangelogFile="true"
        splitStatements="true"
        stripComments="true"/>
</changeSet>
-  changeSet:
        id:  sqlFile-example
        author:  liquibase-docs
        changes:
        -  sqlFile:
        	dbms:  '!h2,  oracle,  mysql'
        	endDelimiter:  \nGO
        	splitStatements:  true
        	path:  my/path/file.sql
        	stripComments:  true
{
   "changeSet":{
   "id":"sqlFile-example",
   "author":"liquibase-docs",
   "changes":[
      {
        "sqlFile":{
          "dbms":"!h2,  oracle,  mysql",
          "endDelimiter":"\nGO",
          "splitStatements":true,
          "path":"my/path/file.sql",
          "stripComments":true
               }
             }
           ]
         }
       }

Available attributes

Name Description Required for Supports Since
dbms

Specifies which database type(s) a changeset is to be used for. See valid database type names on Supported Databases. Separate multiple databases with commas. Specify that a changeset is not applicable to a particular database type by prefixing with !. The keywords all and none are also available.

 all 3.0
encoding

Encoding used in the file defined in the path attribute

 all
endDelimiter

Specifies delimiter to apply to the end of the statement. Default: ";". It can also be set to "" (empty).

 all
path Specifies the file path of the SQL file to load. all all
relativeToChangelogFile

Specifies whether the file path is relative to the changelog file rather than looked up in the search path. Default: false.

 all
splitStatements

Removes Liquibase split statements on ;'s and GO's when it is set to false. Default: true.

 all
stripComments

Removes any comments in the SQL before executing when it is set to true. Otherwise, set it to false. Default: true.

 all

Database support

Database Note 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
Snowflake Supported No
SQL Server Supported No
SQLite Supported No
Sybase Supported No
Sybase Anywhere Supported No
Snowflake Supported No

Related links