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 PSQL and runWith on PostgreSQL, Use SQL Plus and runWith on Oracle Database, and Use SQLCMD and runWith on 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 aGO
statement. AGO
statement must be on a separate line between the two SQL statements. - Multi-line SQL statements are also supported. Only a
;
orGO
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
;
orGO
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:
- Add the Change Type to your changeset, as shown in the examples on this page.
- Specify any required attributes. Use the table on this page to see which ones your database requires.
- Deploy your changeset by running the
update
command:
liquibase update
Examples
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
xmlns:pro="http://www.liquibase.org/xml/ns/pro"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">
<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>
</databaseChangeLog>
databaseChangeLog:
- changeSet:
id: sqlFile-example
author: liquibase-docs
changes:
- sqlFile:
dbms: '!h2, oracle, mysql'
endDelimiter: \nGO
splitStatements: true
path: my/path/file.sql
stripComments: true
{
"databaseChangeLog": [
{
"changeSet": {
"id": "sqlFile-example",
"author": "liquibase-docs",
"changes": [
{
"sqlFile": {
"dbms": "!h2, oracle, mysql",
"endDelimiter": "\nGO",
"splitStatements": true,
"path": "my/path/file.sql",
"stripComments": true
}
}
]
}
}
]
}
Currently, there is no SQL Example for the sqlFile
Change Type because you can just put SQL directly into a SQL formatted changelog.
Available attributes
Name | Description | Required for | Supports | Since |
---|---|---|---|---|
dbms
|
Specifies which database type |
all | 3.0 | |
encoding
|
Encoding used in the file specified in the |
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 ( Tip: It is a best practice not to use |
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: |
all | ||
splitStatements
|
Removes Liquibase split statements on Tip: It is a best practice not to use |
all | ||
stripComments
|
When |
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 |