Change: ‘sqlFile’
The ‘sqlFile’ tag allows you to specify any sql statements and have it stored external in a file. It is useful for complex changes that are not supported through Liquibase’s automated refactoring tags such as stored procedures.
The sqlFile refactoring finds the file by searching in the following order:
The file is searched for in the classpath. This can be manually set and by default the Liquibase startup script adds the current directory when run. The file is searched for using the file attribute as a file name. This allows absolute paths to be used or relative paths to the working directory to be used. The ‘sqlFile’ 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 file 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
Available Attributes
| Name | Description |
|---|---|
| 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.@ v3.0E.g. 'h2, oracle' |
| encoding | Name of the encoding (as specified in java.nio.Charset javadoc) used in the file defined in the `path` attributeDefault: 'utf-8' |
| endDelimiter | Delimiter to apply to the end of the statement. Defaults to ';', may be set to ''.E.g. '\nGO' |
| path | The file path of the SQL file to loadE.g. 'my/path/file.sql' |
| relativeToChangelogFile | booleanWhether the file path is relative to the root changelog file rather than to the classpath. |
| splitStatements | booleanSet to false to not have liquibase split statements on ;'s and GO's. Defaults to true if not setDefault: true |
| stripComments | booleanSet to true to remove any comments in the SQL before executing, otherwise false. Defaults to false if not set |
<changeSet author="liquibase-docs" id="sqlFile-example">
<sqlFile dbms="h2, oracle"
encoding="utf8"
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
encoding: utf8
endDelimiter: \nGO
path: my/path/file.sql
relativeToChangelogFile: true
splitStatements: true
stripComments: true{
"changeSet": {
"id": "sqlFile-example",
"author": "liquibase-docs",
"changes": [
{
"sqlFile": {
"dbms": "h2, oracle",
"encoding": "utf8",
"endDelimiter": "\\nGO",
"path": "my/path/file.sql",
"relativeToChangelogFile": true,
"splitStatements": true,
"stripComments": true
}
}]
}
}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 |