changelogs in SQL Format

As of Liquibase 2.0, Liquibase includes support for “plain SQL” changelog files. These changelog may be included from XML changelog and 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 the value of the <endDelimiter> attribute).

Available Changeset attributes

The following attributes may be provided on each changeset:

Attribute Description
stripComments Set to true to remove any comments in the SQL before executing, otherwise false. Defaults to true if not set
splitStatements Set to false to not have Liquibase split statements on ;'s and GO's. Defaults to true if not set
rollbackSplitStatements Same as splitStatements but for rollback SQL
endDelimiter Delimiter to apply to the end of the statement. Defaults to ";", may be set to "".
rollbackEndDelimiter Same as endDelimiter but for rollback SQL
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 change set 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 Use to override the file name and path when creating the unique identifier of changesets. Required when moving or renaming change logs.
labels Labels are general purpose 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 Should the changeset be ran as a single transaction (if possible)? Defaults to true. Warning: be careful with this attribute. If set to false and an error occurs part way through running a changeset containing multiple statements, the Liquibase DATABASECHANGELOG table will be left in an invalid state
failOnError Should the migration fail if an error occurs while executing the changeset?
dbms 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.
logicalFilePath Sets a logical file path in DATABASECHANGELOG table instead of physical file location of SQL where the Liquibase executed.

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 are comments of the form

--rollback SQL STATEMENT

Comment

A description of thechangeset. Future releases of Liquibase may be able to make use of comments to generate documentation.

--comment: Some comment

Valid CheckSum

Checksum which are considered valid for this changeset, regardless of what is stored in the database. Used primarily when you need to change a changeset and don’t want errors thrown on databases on which it has already run (not a recommended procedure).Since 3.5

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

Ignore lines

Allow to ignore some lines. Useful when using same script with other SQL tool. Since 3.7

Mark two lines to be ignored:

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

Same effect 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;