The endDelimiter
SQL attribute
The endDelimiter
attribute can be set in a sql
or sqlFile
Change Type to override the default value of ;
. The endDelimiter
can be set to ''
or to a character other than ;
to indicate the end of the SQL statement. The attribute is generally used to prevent incomplete SQL statements from being processed when the SQL files contain statements referencing a ;
which Liquibase treats as the default end delimiter.
There is a corresponding rollbackEndDelimiter
that applies specifically to rollback for sql
and sqlFile
Change Types.
The endDelimiter
can also be set in the liquibase.properties
file by setting delimiter=<string>
.
Uses
Below are some examples where endDelimiters
would be specified:
Use endDelimiter
in SQL for PROCEDURE
and FUNCTION
An endDelimiter
may be used when the changeset contains SQL to create a stored procedure or function that contains the default ';'
end delimiter. To avoid incomplete statements being sent to the database, the changeset must be marked to have a different endDelimiter
.
Use endDelimiter
for some DBMS systems to run multiple statements
The endDelimiter
must be specified for some dbms systems to run multiple statements.
Setting the endDelimiter
attribute
Escaping an endDelimiter
When setting an endDelimiter
please note that certain DBMS and operating systems require delimiter values to be escaped. For example, a $$
endDelimiter
with mysql
would need to be escaped as: endDelimiter="\$\$"
. The Liquibase migration error you would see to indicate the delimiter needs to be escaped would be:
Unexpected error running Liquibase: Migration failed for change set <changelog>::<id>::<author>:
Reason: liquibase.exception.DatabaseException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$$$

endDelimiter
attribute
--liquibase formatted sql
--changeset your.name:101 endDelimiter:/
CREATE FUNCTION calcProfit2(cost FLOAT, price FLOAT)
RETURNS DECIMAL(9,2)
DETERMINISTIC
BEGIN
DECLARE profit DECIMAL(9,2);
SET profit = price-cost;
RETURN profit;
END
/
CREATE FUNCTION calcProfit3(cost FLOAT, price FLOAT) RETURNS DECIMAL(9,3)
DETERMINISTIC
BEGIN
DECLARE profit DECIMAL(9,3);
SET profit = price-cost;
RETURN profit;
END
/

endDelimiter
attribute
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:pro="http://www.liquibase.org/xml/ns/pro"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.10.xsd
http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-3.10.xsd">
<changeSet author="your.name" id="sql-example">
<sql dbms="mysql" splitStatements="true" endDelimiter="/" stripComments="true">
DROP FUNCTION IF EXISTS calcProfit;
/
CREATE FUNCTION calcProfit(cost FLOAT, price FLOAT) RETURNS DECIMAL(9,2)
DETERMINISTIC
BEGIN
DECLARE profit DECIMAL(9,2);
SET profit = price-cost;
RETURN profit;
END
/
</sql>
</changeSet>
</databaseChangeLog>

endDelimiter
attribute
databaseChangeLog:
- changeSet:
id: 1001
author: your.name
changes:
- sql:
endDelimiter: "@@"
splitStatements: true
sql: DROP FUNCTION IF EXISTS calcProfit9; @@ CREATE FUNCTION calcProfit9(cost FLOAT, price FLOAT) RETURNS DECIMAL(9,2) DETERMINISTIC BEGIN DECLARE profit DECIMAL(9,2); SET profit = price-cost; RETURN profit; END @@
stripComments: true

endDelimiter
attribute
{
"databaseChangeLog":[
{
"changeSet":{
"id":"1001",
"author":"your.name",
"changes":[
{
"sql":{
"endDelimiter":"@@",
"dbms":"mysql",
"splitStatements":true,
"sql":"DROP FUNCTION IF EXISTS calcProfit;\n@@\nCREATE FUNCTION calcProfit(cost FLOAT, price FLOAT) RETURNS DECIMAL(9,2)\nDETERMINISTIC\nBEGIN\nDECLARE profit DECIMAL(9,2);\nSET profit = price-cost;\nRETURN profit;\nEND\n@@",
"stripComments":true
}
}
]
}
}
]
}