Migration failed: pq: unterminated dollar-quoted string at or near

[mantyr]

Unexpected behavior.

cat ./migrations/20240130094405-events.sql

-- +migrate Up

CREATE FUNCTION notify_event() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
    DECLARE
        object json;
        notification json;
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            object = row_to_json(OLD);
        ELSE
            object = row_to_json(NEW);
        END IF;
        notification = json_build_object(
            'object_type', TG_TABLE_NAME,
            'object_id', object -> TG_ARGV[0],
            'action', TG_OP);

        PERFORM pg_notify('events',notification::text);
        RETURN NULL;
    END;
$$;

-- +migrate Down

sql-migrate --version
v1.6.1

godotenv -f ./env/postgres.env sql-migrate up
Migration failed: pq: unterminated dollar-quoted string at or near "$$
    DECLARE
        object json" handling 20240130094405-events.sql

The same migration via regular psql runs successfully.

psql --version
psql (PostgreSQL) 14.2

$(eval include ./env/postgres.env)
PGPASSWORD="${POSTGRES_PASSWORD}" psql -h ${POSTGRES_HOST} -p ${POSTGRES_PORT} -U ${POSTGRES_USER} ${POSTGRES_DB} < ./migrations/20240130094405-events.sql
CREATE FUNCTION

[mantyr]

Solution to this problem:

• https://github.com/lib/pq/issues/974

-- +migrate StatementBegin

SQL

-- +migrate StatementEnd

Example:

cat 20240130094405-events.sql

-- +migrate Up

-- +migrate StatementBegin
CREATE FUNCTION notify_event() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
    DECLARE
        object json;
        notification json;
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            object = row_to_json(OLD);
        ELSE
            object = row_to_json(NEW);
        END IF;
        notification = json_build_object(
            'object_type', TG_TABLE_NAME,
            'object_id', object -> TG_ARGV[0],
            'action', TG_OP);

        PERFORM pg_notify('events',notification::text);
        RETURN NULL;
    END;
    $$;
-- +migrate StatementEnd

-- +migrate Down

godotenv -f ./env/postgres.env sql-migrate up
Applied 1 migrations