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. You cannot use the include or includeAll tags in a formatted SQL changelog to reference other changelog files. However, you can use a formatted SQL changelog without these tags, or use an XML, YAML, or JSON root changelog that references your formatted SQL changelog(s).

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

Note: 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.

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
`contextFilter`	Specifies the changeset contexts to match. Contexts are expressions you can add to changesets to control which changesets are 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 Liquibase Database Tutorials. 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: "`;`". 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 changeset 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`	Removes Liquibase split statements on `;'s` and `GO's` when it is set to `false`. Default: `true`. 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`	Removes any comments in the SQL before executing when it is set to `true`. Otherwise, set it to `false`. Default: `true`.

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

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

--rollback SQL STATEMENT

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.

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;