Liquibase and pure SQL changesets

Not everyone knows, but since version 2.0 Liquibase supports the ability to use files with "pure SQL" as changeset files. Under the cut, I want to describe a little what they consist of.

Comments are used to define metadata in SQL files, each changeset file begins with a comment:

--liquibase formatted sql

Each changeset in the file begins with a comment, which indicates all the necessary parameters in the following form:

--changeset author:id attribute1:value1 attribute2:value2 [...]

The following attributes can be set for the changeset:

Attribute	Description
stripComments	If set to true, then all comments are deleted before executing SQL statements. The default value is true.
splitStatements	If set to false, then Liquibase will not separate SQL expressions on the “;” character; it is used to describe routines.
endDelimiter	Specifies the delimiter of SQL statements, the default is “;”.
runAlways	If set to true, then the list of changes will be executed during each build of the project.
runOnChange	If set to true, then when making changeset changes, it will be executed at the next build of the project.
context	Creating a label for changeset, which can later be done on request.
runInTransaction	If set to true, then all SQL statements will be executed in a single transaction, if possible. The default value is true.
failOnError	True - all changeset will be canceled if errors occur at runtime.
dbms	An indication of the type of DBMS for which changeset is written.

After setting the parameters, preconditions are indicated (optional). Next is a set of SQL statements, separated by a semicolon or a character specified in the “endDelimiter” attribute.

At the moment, only one kind of precondition is supported in changesets on pure SQL: SQL Check. Precondition Example:

--precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM my_table

The expectedResult parameter is passed the value that the SQL query returns. A query must necessarily return a single value.

To set the behavior of the precondition check processing, a syntax similar to setting changeset is used:

--preconditions attribute1:value1 attribute2:value2 [...]

Attributes may be as follows:

Attribute	Description
onFail	Actions when changeset cannot be executed.
onError	Actions when changeset return an error.
onUpdateSQL	Actions that will be performed on the changeset if it is executed in updateSQL mode.
onFailMessage	A message to be returned if the changeset cannot be executed.
onErrorMessage	The message that will be returned if the changeset fails.

The following values ​​can be passed to the onFail and onError attributes:

Value	Description
Halt	Stop the execution of the changeset immediately.
CONTINUE	The contents of the changeset will be skipped and an attempt will be made to re-execute it next time.
MARK_RAN	Changeset will be marked as completed.
WARN	A warning will be generated and the changeset will continue to execute normally.

Changesets can include SQL statements for rollback. Rollback expressions are described in the form of a comment:

--rollback SQL STATEMENT

And finally, a small example of the changeset file:

--liquibase formatted sql
--changeset User1:1 
create table test1 (
    id int primary key,
    name varchar(255)
);
--rollback drop table test1;
--changeset User2:1 
--preconditions onFail:CONTINUE onError:CONTINUE
--precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM test1
insert into test1 (id, name) values (1, 'User1');
--rollback delete from test1 where id = 1;

Summing up all of the above, I would like to add that such files are read and written much easier than xml'ki, but not all buns are still supported (example by preconditions, only SQL Check is supported now).