Example Changelogs: SQL Format

Liquibase supports Changelog files in the SQL format. Like other file types, you can use as many formatted SQL changelogs as your database needs.

Note: When creating an SQL changelog file with the generate-changelog and diff-changelog commands, add your database type name when you specify the changelog file. See the example below. If using the example, replace .oracle.sql with your database type. When in doubt about your database type name, check Supported Databases.

Example: liquibase generate-changelog --changelog-file=mychangelog.oracle.sql liquibase diff-changelog --changelog-file=mychangelog.oracle.sql

The changelog files may contain arbitrary SQL statements. The statements are converted to custom sql Change Types.

Formatted SQL files use comments to provide Liquibase with metadata. Each SQL file must begin with the following comment:

--liquibase formatted sql

Spacing

It is a best practice not to include a space before --liquibase formatted sql. Formatting variations may cause the Liquibase parser to return an error.

When specifying changeset attributes in a formatted SQL changelog, any attribute value that contains spaces must be quoted. For example:

--changeset your.name:1 context:"a or b"

`include` and `includeAll`

In Liquibase Pro 4.28.0 and later, you can use the include or includeAll tags in a formatted SQL root changelog to reference other changelog files. This feature is not available in Liquibase 4.27.0 and earlier.

You can reference formatted SQL changelogs from XML, YAML, and JSON root changelogs in all versions of Liquibase Pro and Liquibase Open Source.

changeset

Each changeset in a formatted SQL file begins with a comment of the form:

--changeset author:id attribute1:value1 attribute2:value2 [...]

The changeset comment is followed by one or more SQL statements, separated by semicolons or by the value of the <endDelimiter> attribute).

changeset attributes

Use the following attributes for your changesets:

Attribute	Description
`context`	Specifies the changeset contexts to match. Contexts are tags you can add to changesets to control which changesets will be executed in any particular migration run.
`dbms`	Specifies which database type(s) a changeset is to 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.
`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. Warning: In a formatted SQL changelog, if you set `runWith=sqlplus` on a changeset, do not set `endDelimiter` on that changeset. In this case, specifying a non-default delimiter may result in unintended consequences.
`failOnError`	Defines whether a database migration will fail if an error occurs while executing the changeset. Default: `true`.
`ignore`	Tells Liquibase to treat a particular changeset as if it does not exist. Default: `false`. Since 3.6 (XML/YAML/JSON). Since 4.19.0 (SQL).
`labels`	Specifies the changeset labels to match. Labels are tags you can add to changesets to control which changesets will be executed in any migration run.
`logicalFilePath`	Overrides the file name and path when creating the unique identifier of changesets. It is required when you want to move or rename changelogs.
`rollbackEndDelimiter`	Specifies delimiter to apply to the end of the statement for rollback SQL. Default value is: `";"`. It can also be set to `""`.
`rollbackSplitStatements`	Removes Liquibase split statements on `;'s` and `GO's` for rollback SQL when it is set to `false`. Default value is: `true`.
`runAlways`	Executes the changeset on every run, even if it has been run before. Default: `false`.
`runInTransaction`	Specifies whether the changeset can be run as a single transaction (if possible). Default: `true`. Since 1.9. Warning: If `runInTransaction` is set to `false` and an error occurs part way through running a changeset that contains multiple statements, the Liquibase DATABASECHANGELOG table will be left in an invalid state.
`runOnChange`	Executes the changeset when you create it and each time it changes. Default: `false`.
`runWith`	Specifies a native executor to run your SQL (`jdbc`, `mongosh`, `psql`, `sqlcmd`, `sqlpus`, or a custom executor). Default: `jdbc`.
`runWithSpoolFile`	Specifies a spool file to send output to when you deploy a particular changeset.
`splitStatements`	If `true`, Liquibase splits SQL statements using the delimiters `;` and `GO`. If `false`, Liquibase does not split SQL statements. Default: `true`. Tip: It is a best practice not to use `splitStatements=true` on changesets you are running with a native executor. Native executors handle statement splitting natively. Warning: In a formatted SQL changelog, if you set `runWith=sqlplus` on a changeset, Liquibase automatically sets the default value of `splitStatements` to `false` for that changeset. In this case, do not set the `splitStatements` attribute to `true` on `runWith` changesets. Changing the value from the default of `false` may result in unintended consequences.
`stripComments`	When `true`, removes any comments in the statement before executing. Otherwise, set it to `false`. Default: `false`. See also: `--pro-global-strip-comments` and `--pro-global-strip-comments-prioritized`.

Preconditions

Preconditions can be specified for each changeset. Currently, only the SQL check precondition is supported.

--preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM my_table

Rollback actions

Specify SQL with `rollback`

Your changesets may include statements to be applied when rolling back the changeset. Rollback statements have the following format:

--changeset liquibase-user:1
DROP PROCEDURE hello_world;
--rollback SQL STATEMENT

Specify a SQL file with `rollbackSqlFile`

In Liquibase 4.26.0 and later, you can use a rollbackSqlFile statement to specify a SQL file to execute in a rollback. A rollbackSqlFile statement in a formatted SQL changelog behaves the same way as a rollback statement, except that rollbackSqlFile points to a file instead of inline SQL. rollbackSqlFile requires a Liquibase Pro license key. For example:

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

Specifying rollbackSqlFile in a formatted SQL changelog is equivalent to using the sqlFile Change Type in an XML, YAML, or JSON changelog to roll back a statement.

In your rollbackSqlFile statement, you can specify the following parameters to change the behavior of your rollback:

Name	Description
`dbms`	Specifies which database type(s) a changeset is to 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.
`encoding`	Encoding used in the file specified in the `rollbackSqlFile` attribute.
`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.
`relativeToChangelogFile`	Specifies whether the file path is relative to the changelog file rather than looked up in the search path. Default: `false`.
`splitStatements`	If `true`, Liquibase splits SQL statements using the delimiters `;` and `GO`. If `false`, Liquibase does not split SQL statements. Default: `true`. Tip: It is a best practice not to use `splitStatements=true` on changesets you are running with a native executor. Native executors handle statement splitting natively.
`stripComments`	When `true`, removes any comments in the statement before executing. Otherwise, set it to `false`. Default: `false`. See also: `--pro-global-strip-comments` and `--pro-global-strip-comments-prioritized`.

For example:

--rollbackSqlFile path:release_1.0/rollback_45895.sql dbms:oracle encoding:UTF-8 endDelimiter:@ relativeToChangelogFile:true splitStatements:true stripComments:true

Comment

The comment is a description of the changeset. The format of the comment can be one of the following:

A multi-line 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, the statements will be split on a ‘;' or ‘go' at the end of lines. Because of this, if you have a comment or other non-statement ending ‘;' or ‘go', don't put it at the end of a line or you will get an invalid SQL.

Future releases of Liquibase may use comments to generate documentation.

--comment: some comment

Note: When you add a comment to your changeset and deploy this changeset, the comment will not be applied to the DATABASECHANGELOG tracking table.

Tip: You can also add SQL comments outside changesets using the format --my comment.

Valid checksum

Valid checksum is a checksum which is valid for a specific changeset, regardless of what is stored in the database. It is typically used when you need to change a changeset and don't want errors to be thrown on databases on which it has been already run. Nevertheless, it is not a recommended procedure. Since 3.5

--validCheckSum: 3:098f6bcd4621d373cade4e832627b4f6 
--validCheckSum: 7:ad0234829205b9033196ba818f7a872b

For more information about Liquibase checksums, see Changeset Checksums.

Ignore lines

The ignoreLines attribute allows you to ignore specific lines within a changeset. This is similar to the ignore attribute (see the changeset attributes section), but the scope can be more precise. ignoreLines is typically used when you run the same script with other SQL tools. Since 3.7

Marking two lines to be ignored:

--changeset author:id1  
CREATE OR REPLACE PACKAGE ... 
--ignoreLines:2
/
show errors;
--changeset author:id2  
CREATE OR REPLACE PACKAGE BODY ...

Making two lines to be ignored by using start-end syntax:

--changeset author:id1  
CREATE OR REPLACE PACKAGE ...  
--ignoreLines:start
/ 
show errors;
--ignoreLines:end
--changeset author:id2
CREATE OR REPLACE PACKAGE BODY ...

Sample changelog

--liquibase formatted sql

--changeset nvoxland:1
create table test1 (  
    id int primary key,
    name varchar(255)  
);  
--rollback drop table test1; 

--changeset nvoxland:2 
insert into test1 (id, name) values (1, ‘name 1′);
insert into test1 (id,  name) values (2, ‘name 2′);  

--changeset nvoxland:3 dbms:oracle
create sequence seq_test;