endDelimiter SQL attribute

endDelimiter is an attribute that lets you specify a delimiter to separate raw SQL statements in your changesets. When you run commands like update or rollback, Liquibase first parses any raw SQL you want to execute. Liquibase looks for the endDelimiter string in each changeset and splits the SQL on the endDelimter boundary. This ensures that your database receives only one SQL statement at a time, even if your raw SQL contains many statements.

You can use endDelimiter in a individual changeset containing the sql or sqlFile Change Types. endDelimiter is valid in Formatted SQL, XML, YAML, and JSON changelogs. The default value is ; (semicolon). Your custom delimiter can be a combination of one or more letters, symbols, and/or numbers, or the empty string.

If your SQL only contains one semicolon at the end of every statement (or only one statement with no semicolon), you don't have to specify a custom delimiter. However, if your SQL embeds stored logic containing one or more semicolons, Liquibase incorrectly parses your SQL as multiple statements, causing an error. You can set a different delimiter to prevent Liquibase from processing incomplete SQL statements.

Tip: It is a best practice not to use endDelimiter on changesets you are running with a native executor. Native executors handle delimiters natively.

Delimiter settings

This page describes setting endDelimiter on changesets. However, there are several ways to set delimiters:

  1. endDelimiter set on a specific changeset (highest precedence)
  2. endDelimiter set in a changelog called by include or includeAll in modifyChangeSets (4.25.1+)
  3. --pro-global-end-delimiter set globally (lowest precedence) (4.25.1+)
  4. endDelimiter set in a modifyChangeset tag

Note: You can give --pro-global-end-delimiter the highest precedence of all delimiter settings by also using the --pro-global-end-delimiter-prioritized parameter.

Also, when running SQL with the execute-sql command, you can specify a delimiter with the --delimiter argument.

You can also use the rollbackEndDelimiter attribute on sql and sqlFile Change Types in your changelog. This lets you specify a delimiter for rollback SQL. It is only valid in Formatted SQL changelogs.

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.

Syntax

Note: All changelog attributes use the camelCase format.

--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
/
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
                        }
                    }
                ]
            }
        }
    ]
}
<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>

Troubleshooting

Escaping an end delimiter

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 '$$$

Newline behavior with a / end delimiter

Prior to Liquibase 4.9.0, the delimiter / (forward slash) acts as a division operator.

In Liquibase 4.9.0 to 4.14.0, the delimiter / is treated as a regular character. If you use it in a PL/SQL block, for example as a division operator, Liquibase fails on Oracle databases:

Caused by: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for change set filepath/example-changeset.sql::example-id::example-author:
		Reason: liquibase.exception.DatabaseException: ORA-00900: invalid SQL statement

In Liquibase 4.15.0 to 4.28.0, Liquibase fixes this by requiring a newline before the / to treat it as a delimiter. Otherwise, the / is treated as a regular character. However, using a newline in the wrong place can lead to the error message "ORA-00922: missing or invalid option" on Oracle databases.

In Liquibase 4.29.0 to 4.29.1, Liquibase fixes this by requiring a newline after the / to treat it as a delimiter, not before. Otherwise, the / is treated as a regular character.

In Liquibase 4.29.2 and later, the / is treated as a regular character when --strict=true and as a delimiter when --strict=false or not specified. For more information, see --strict.

Related links