endDelimiter SQL attribute

The endDelimiter attribute can be set in a sql or sqlFile Change Type to override the default value of ;. It is only used with the execute-sql command. endDelimiter can be set to '' or to a character other than ; to indicate the end of the SQL statement. This 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.

A corresponding rollbackEndDelimiter applies specifically to rollback for sql and sqlFile Change Types. The rollbackEndDelimiter attribute is only valid in formatted SQL changelogs.

Tip: All changelog attributes use the camelCase format.

Uses

The following examples demonstrate use of the endDelimiters SQL attribute:

Use endDelimiter in SQL for PROCEDURE and FUNCTION

An endDelimiter can 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, note that certain DBMS and operating systems require delimiter values to be escaped. For example, a $$ endDelimiter with mysql requires escaping as: endDelimiter="\$\$". A Liquibase migration error indicates the delimiter that 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 '$$$
--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
/
<?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:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    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-latest.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.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>
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
{
   "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
             }
           }
         ] 
       }
     }
   ]
 }