changelogs in SQL Format

Liquibase supports “plain SQL” changelog files. You can add the formatted SQL changelog to an XML master changelog if you have more than one formatted SQL changelog or just use a formatted SQL changelog directly without the master XML changelog.

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

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).

If you use the Liquibase Pro feature runWith=sqlPlus, follow these rules with formatted SQL changelogs:

  • Do not set the endDelimiter property on the changeset. This affects SQLPlus.
  • Ensure that the SplitStatements attribute is set to true, which is the default value.

changeset attributes

Use the following attributes for your changesets:

Attribute Description
stripComments Removes any comments in the SQL before executing when it is set to true. Otherwise, set it to false. Default value is: true.
splitStatements Removes Liquibase split statements on ;'s and GO's when it is set to false. Default value is: true.
rollbackSplitStatements Removes Liquibase split statements on ;'s and GO's for rollback SQL when it is set to false. Default value is: true.
endDelimiter Specifies delimiter to apply to the end of the statement. Default value is: ";". It can also be set to "".
rollbackEndDelimiter Specifies delimiter to apply to the end of the statement for rollback SQL. Default value is: ";". It can also be set to "".
runAlways Executes the changeset on every run, even if it has been run before.
runOnChange Executes the change the first time it is seen and each time the changeset has been changed.
context Executes the change if the particular context was passed at runtime. Any string can be used for the context name and they are checked case-insensitively.
logicalFilePath Overrides the file name and path when creating the unique identifier of changesets. Required when moving or renaming changelogs.
labels Specifies labels that are a general way to categorize changesets like contexts, but working in the opposite way. Instead of defining a set of contexts at runtime and then a match expression in the changeset, you define a set of labels in the context and a match expression at runtime.
runInTransaction

Specifies whether the changeset can be run as a single transaction (if possible). Default value is: true.

Warning: be careful with this attribute. If 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.
failOnError Specifies whether the migration fails if an error occurs while executing the changeset.
dbms Specifies the type of a database which that changeset is to be used for. When the migration step is running, it checks the database type against this attribute. Valid database type names are listed on the supported databases page. It is possible to list multiple databases separated by commas. You can also specify that a changeset is not applicable to a particular database type by prefixing with !. The keywords all and none are also available.

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

Ignore lines

The ignoreLines attribute allows you to ignore the specific lines. It is typically used when you run the same script with the 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;