Liquibase and pure SQL changesets

  • Tutorial
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:
stripCommentsIf set to true, then all comments are deleted before executing SQL statements. The default value is true.
splitStatementsIf set to false, then Liquibase will not separate SQL expressions on the “;” character; it is used to describe routines.
endDelimiterSpecifies the delimiter of SQL statements, the default is “;”.
runAlwaysIf set to true, then the list of changes will be executed during each build of the project.
runOnChangeIf set to true, then when making changeset changes, it will be executed at the next build of the project.
contextCreating a label for changeset, which can later be done on request.
runInTransactionIf set to true, then all SQL statements will be executed in a single transaction, if possible. The default value is true.
failOnErrorTrue - all changeset will be canceled if errors occur at runtime.
dbmsAn 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:
onFailActions when changeset cannot be executed.
onErrorActions when changeset return an error.
onUpdateSQLActions that will be performed on the changeset if it is executed in updateSQL mode.
onFailMessageA message to be returned if the changeset cannot be executed.
onErrorMessageThe message that will be returned if the changeset fails.

The following values ​​can be passed to the onFail and onError attributes:
HaltStop the execution of the changeset immediately.
CONTINUEThe contents of the changeset will be skipped and an attempt will be made to re-execute it next time.
MARK_RANChangeset will be marked as completed.
WARNA 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).

Also popular now: