Invalid syntax with sequelize >= 6.29.1 enum migration

[knixeur]

Describe the bug

A syntax error is thrown while running a create type migration.

Unable to connect to the database: SequelizeDatabaseError: 💔 Your query failed to parse.\n' +
        'This is most likely due to a SQL syntax error. However, you might also have hit a bug, or an unimplemented feature of pg-mem.\n' +
        'If this is the case, please file an issue at https://github.com/oguimbal/pg-mem along with a query that reproduces this syntax error.\n' +
        '\n' +
        '👉 Failed query:\n' +
        '\n' +
        `    DROP TYPE IF EXISTS "public"."enum_aaaaaaaaa_aaaa_aaaaaa"; DO 'BEGIN CREATE TYPE "public"."enum_aaaaaaaaa_aaaa_aaaaaa" AS ENUM(''bbbbb'', ''bbbbb'', ''bbbbb'', ''bbbbb''); EXCEPTION WHEN duplicate_object THEN null; END';;\n` +
        '\n' +
        '💀 Syntax error at line 1 col 63:\n' +
        '\n' +
        `1  DROP TYPE IF EXISTS "public"."enum_aaaaaaaaa_aaaa_aaaaaa"; DO 'BEGIN CREATE TYPE "public"."enum_aaaaaaaaa_aaaa_aaaaaa" AS ENUM(''bbbbb'', ''bbbbb'', ''bbbbb'', ''bbbbb''); EXCEPTION WHEN duplicate_object THEN null; END';;\n` +
        '                                                                 ^\n' +
        `Unexpected string token: "BEGIN CREATE TYPE \\"public\\".\\"enum_aaaaaaaaa_aaaa_aaaaaa\\" AS ENUM('es_AR', 'es_CL', 'es_CO', 'pt_BR'); EXCEPTION WHEN duplicate_object THEN null; END". Instead, I was expecting to see one of the following:\n` +
        '\n' +
        '    - A "codeblock" token\n' +
        '    - A "word" token\n' +
        '\n',

To Reproduce

DROP TYPE IF EXISTS "public"."enum_aaaaaaaaa_aaaa_aaaaaa"; DO 'BEGIN CREATE TYPE "public"."enum_aaaaaaaaa_aaaa_aaaaaa" AS ENUM(''bbbbb'', ''bbbbb'', ''bbbbb'', ''bbbbb''); EXCEPTION WHEN duplicate_object THEN null; END';;

pg-mem version

2.6.12

Others

This started happening after this PR was merged into sequelize

• https://github.com/sequelize/sequelize/pull/15718

Relevant commit:

• https://github.com/sequelize/sequelize/commit/1b94462bd5236238346e255fbbe670bd97902d93

Thanks for pg-mem and let me know if you need more info.

[luizcieslak]

@knixeur were you able to work around this?

[knixeur]

Mmm probably not, IIRC I did a work around to prevent that and moved on

Edit: And here is the workaround :)

    db.public.interceptQueries((queryText) => {
      if (queryText.search(/(pg_views|pg_matviews|pg_tables|pg_enum)/g) > -1) {
        return [];
      }
      if (
        queryText.includes("DO 'BEGIN CREATE TYPE") &&
        queryText.includes('AS ENUM') &&
        queryText.includes(" EXCEPTION WHEN duplicate_object THEN null; END';")
      ) {
        db.public.none(
          queryText
            .replace(
              /DROP TYPE IF EXISTS (.+); DO 'BEGIN CREATE TYPE (.+) AS ENUM\((.+)\); EXCEPTION WHEN duplicate_object THEN null; END';/,
              'DROP TYPE IF EXISTS $1; CREATE TYPE $2 AS ENUM($3)',
            )
            .replace(/''/g, "'"),
        );
        return [];
      }
      return null;
    });

Not my proudest production but I needed to move on :)

[luizcieslak]

thank you for sharing @knixeur, I needed some small modifications as mine don't have the initial DROP TYPE IF EXISTS but then it worked smoothly:

pgMemDb.public.interceptQueries((queryText) => {
      if (queryText.search(/(pg_views|pg_matviews|pg_tables|pg_enum)/g) > -1) {
        return []
      }
      if (
        queryText.includes("DO 'BEGIN CREATE TYPE") &&
        queryText.includes("AS ENUM") &&
        queryText.includes(" EXCEPTION WHEN duplicate_object THEN null; END';")
      ) {
        pgMemDb.public.none(
          queryText
            .replace(
              /DO 'BEGIN CREATE TYPE (.+) AS ENUM\((.+)\); EXCEPTION WHEN duplicate_object THEN null; END';/,
              'CREATE TYPE $1 AS ENUM($2);',
            )
            .replace(/''/g, "'")
        )
        return []
      }