sqlFile

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

Uses

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

Tip: If you use psql, SQL Plus, or sqlcmd utility and Liquibase Pro, see Use Native Executors with PostgreSQL, Use Native Executors with Oracle Database, and Use native executors with Microsoft SQL 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.

Run sqlFile

To run this Change Type, follow these steps:

  1. Add the Change Type to your changeset, as shown in the examples on this page.

  2. Specify any required attributes. Use the table on this page to see which ones your database requires.

  3. Deploy your changeset by running the update command:liquibase update

Available attributes

Name

Description

Required for

Supports

Since

dbms

Specifies which database type(s)a changeset will be used for. See valid database type names on dbms. 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 specified in the path attribute. Default: UTF-8.

all

endDelimiter

Specifies delimiter to apply to the end of the statement. Your delimiter string can be a combination of one or more letters, symbols, and/or numbers, or the empty string (""). Default: ";". See also: --pro-global-end-delimiter and --pro-global-end-delimiter-prioritized.

Tip: It is a best practice not to use endDelimiter on changesets you are running with a native executor. Native executors handle delimiters natively.

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

If required, Liquibase will automatically add splitstatements:true to generated changesets in Formatted SQL changelogs. Otherwise, the default setting for generated changelogs is splitstatements:false.

Example: If the generated SQL has multiple SQL statements, then Liquibase adds splitStatements:true to the changelog.

Tip: It is best practice not to use splitStatements=true on changesets you are running with a native executor. Native executors handle statement splitting natively.

all

stripComments

When true, removes any comments in the statement before executing. If false, Liquibase does not remove any comments. Default: false. See also: --pro-global-strip-comments and --pro-global-strip-comments-prioritized.

all

Database support

Database

Note

Auto Rollback

DB2/LUW

Supported

No

DB2/z

Supported

No

Derby

Supported

No

Firebird

Supported

No

Google BigQuery

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

sqlFile examples

*There is no implementation for the sqlFile Change Type because you can just put SQL directly into a SQL formatted changelog.
In Liquibase 4.26.0 and later, you can use a rollbackSqlFile statement to specify rollback SQL for a changeset in a separate file:*

--changeset liquibase-user:1
DROP PROCEDURE hello_world;
--rollbackSqlFile path:release_1.0/rollback_45895.sql

*In your rollbackSqlFile statement, you can specify parameters to change the behavior of your rollback, such as a unique end delimiter. *