SQL file with JS code is not working with liquibase

Question

I am using snowflake to manage the database and Currently I am trying to implement liquibase with existing snowflake sql scripts.

Some of my sql files contains JS code, for example -

create or replace function GetTimeSeries (businessDate text)
returns varchar
language javascript
as
$$
    var bDate = new Date(BUSINESSDATE);
    if(BUSINESSDATE == '' || bDate > new Date('2059-09-18')) {
        return 32767;
    }
    return Math.ceil(parseFloat((bDate-(new Date('01/01/1970')))/(24*3600*1000)));
    
$$;

So, this is working fine when I try to execute this manually from the snowflake worksheet.

But, if I am trying to execute this using liquibase cli -

liquibase update --defaults-file=dev.liquibase.properties --default-schema-name=DEV "-Dschema=DEV"

it is giving error -

Unexpected error running Liquibase: Migration failed for changeset ../changelogs/dev/..
     Reason: liquibase.exception.DatabaseException: SQL compilation error:
parse error line 6 at position 39 near '<EOF>'.
syntax error line 5 at position 1 unexpected '$'. [Failed SQL: (1003) create or replace procedure GetTimeSeries (businessDate text)
returns text
language javascript
as
$$
    var bDate = new Date(BUSINESSDATE);

I have made the following change to my sql file in order to make it work with liquibase -

--liquibase formatted sql

-- changeset Bhushan:1
create or replace function ${schema}.GetTimeSeries (businessDate text)
returns varchar
language javascript
as
$$
    var bDate = new Date(BUSINESSDATE);
    if(BUSINESSDATE == '' || bDate > new Date('2059-09-18')) {
        return 32767;
    }
    return Math.ceil(parseFloat((bDate-(new Date('01/01/1970')))/(24*3600*1000)));

$$;

So, any idea about how can I execute sql files with JS code in it using liquibase? otherwise there are some procedures which contains lots of JS code and I need to update them to sql statements manually.

Answer 1

As Stored procedure JS script may contain delimiter semicolon (";"), you can overwrite the default behaviour of endDelimiter with space ("") in your sqlFile change type (assume you are using sqlFile for procedures and functions).

Please read through below link for more detail: https://docs.liquibase.com/change-types/enddelimiter-sql.html