Create stored procedure with SQL tag in Liquibase

Stored procedures and functions can be easily managed through Liquibase Pro edition. Are we doomed when using the Community one? Oh, no. As with any other database change, it can be done using the <SQL> tag which can work with any SQL code. But Liquibase may complain on an SQL syntax error ...

Stored procedure in SQL

Any database engine has its own syntax for stored code like procedures, functions, and triggers. It makes it difficult for any parsing tools that try to be universal between various SQL dialects. Here is an example of a statement that creates a stored procedure in MySQL database:

DELIMITER //

create procedure some_proc (out title int)
begin
   select count(1) into title from titles
   where emp_no = 123;
end //

DELIMITER ;

It is worth pointing out that by default SQL uses a semicolon (;) to separate statements in a batch or a script. As a stored procedure may contain multiple statements, they can be separated using semicolons. A problem begins with detecting the end of the stored procedure create statement - the parser may not be sure if a particular semicolon means the end of a statement inside the procedure or the end of the procedure.

That is the reason why the above script defines a new delimiter on the first line. Thanks to that, the parser stops treating further semicolons as delimiters and passes them to the database engine as any other character.

Stored procedure in Liquibase

Liquibase Community does not provide any custom tag for creating a stored procedure, but we can successfully use the <SQL> tag, which comes in handy when no special tag is available.

Failed 1st attempt

The thing is that the most straight-forward solution fails with SQL syntax error.

<changeSet id="test 1 - failed" author="DBA presents">
  <sql>
    DELIMITER //
    create procedure some_proc (out title int)
    begin
      select count(1) into title from titles
      where emp_no = 123;
    end //
    DELIMITER ;
  </sql>
</changeSet>

Unexpected error running Liquibase: 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 'DELIMITER //
 create procedure some_proc (out title int)
 ' at line 1 [Failed SQL: (1064) DELIMITER //
 create procedure some_proc (out title int)
 begin
 select count(1) into title from titles
 where emp_no = 123]

 Liquibase returned the error because the DELIMITER command is known to MySQL, not Liquibase, so it does not change the delimiter for Liquibase parser.

endDelimiter attribute

So maybe we should use a DELIMITER alternative that is actually known to Liquibase? Yes! That is an option. Although it is not a command, but the <SQL> tag has the endDelimiter attribute that we can use. It can be set to the new delimiter string that we want to use. Here is a Liquibase change that does that:

<changeSet id="test 2 - endDelimiter" author="DBA presents">
  <sql endDelimiter="//">
    create procedure some_proc (out title int)
    begin
      select count(1) into title from titles
      where emp_no = 123;
    end //
  </sql>
</changeSet>

It works, but we can still make it better.

splitStatements attribute

When there is only one statement in the whole <SQL> tag, why to even bother defining a new delimiter? We can simply tell Liquibase to not even try splitting the <SQL> body to separate statements by setting splitStatements to false.

<changeSet id="test 2 - splitStatements" author="DBA presents">
  <sql splitStatements="false">
    create procedure some_proc (out title int)
    begin
      select count(1) into title from titles
      where emp_no = 123;
    end
  </sql>
</changeSet>

By default the splitStatements attribute is set to true, so Liquibase tries to parse and split all code provided between <SQL> tags. When set to false, Liquibase does not split it, but sends the whole directly to the database engine.