Example Changelogs: 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.
Note: When creating an SQL changelog file with the
diffChangeLog 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.
liquibase --changeLogFile=mychangelog.oracle.sql generateChangeLog
liquibase --changeLogFile=mychangelog.oracle.sql diffChangeLog
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
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
If you use the Liquibase Pro feature
runWith=sqlPlus, follow these rules with formatted SQL changelogs:
- Do not set the
endDelimiterproperty on the changeset. This affects SQLPlus.
- Ensure that the
SplitStatementsattribute is set to
true, which is the default value.
Use the following attributes for your changesets:
||Removes any comments in the SQL before executing when it is set to
||Removes Liquibase split statements on
||Removes Liquibase split statements on
||Specifies delimiter to apply to the end of the statement. Default value is:
||Specifies delimiter to apply to the end of the statement for rollback SQL. Default value is:
||Executes the changeset on every run, even if it has been run before.|
||Executes the change the first time it is seen and each time the changeset has been changed.|
||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.|
||Overrides the file name and path when creating the unique identifier of changesets. Required when moving or renaming changelogs.|
||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.|
Specifies whether the changeset can be run as a single transaction (if possible). Default value is:
||Specifies whether the migration fails if an error occurs while executing the changeset.|
||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
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
changesets may include statements to be applied when rolling back the changeset. Rollback statements have the following format of the comments:
--rollback SQL STATEMENT
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
‘go' at the end of lines. Because of this, if you have a comment or other non-statement ending
‘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 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
ignoreLines attribute allows you to ignore the specific lines. It is typically used when you run the same script with the other SQL tools.
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 ...
--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;