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 |
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 ( Warning: In a formatted SQL changelog, if you set |
failOnError
|
Defines whether a database migration will fail if an error occurs while executing the changeset. Default: |
ignore
|
Tells Liquibase to treat a particular changeset as if it does not exist. Default: |
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: |
runInTransaction
|
Specifies whether the changeset can be run as a single transaction (if possible). Default: Warning: If |
runOnChange
|
Executes the changeset when you create it and each time it changes. Default: |
runWith
|
Specifies a native executor to run your SQL ( |
runWithSpoolFile
|
Specifies a spool file to send output to when you deploy a particular changeset. |
splitStatements
|
Removes Liquibase split statements on Warning: In a formatted SQL changelog, if you set |
stripComments
|
Removes any comments in the SQL before executing when it is set to |
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;