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
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 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: ";".

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

Removes Liquibase split statements on ;'s and GO's when it is set to false. 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

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:

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

SQL file

In Liquibase 4.26.0 and later, you can also specify a SQL file to execute in a rollback. This requires a Liquibase Pro license key. For example:

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

This is equivalent to using the sqlFile Change Type in an XML, YAML, or JSON changelog. 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 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.

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: ";".

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

Removes Liquibase split statements on ;'s and GO's when it is set to false. 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

Removes any comments in the SQL before executing when it is set to true. Otherwise, set it to false. Default: true.

For example:

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

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;

Related links