sql-formatter crash on $$ SQL delimiter

[karlhorky]

Hi @Sec-ant , hope you're well! 👋

(very similar to #35, maybe the same issue)

sql-formatter crashes with prettier-plugin-embed, embedded SQL in JavaScript/TypeScript template string and prettier-plugin-sql, when the $$ delimiter is used:

a.sql

CREATE
           OR REPLACE FUNCTION INCREMENT (
  i INTEGER
) RETURNS INTEGER AS $$
        BEGIN
                RETURN i + 1;
            END;
$$ LANGUAGE plpgsql

a.js

function up(sql) {
  await sql`CREATE
     OR REPLACE FUNCTION INCREMENT (
  i INTEGER
) RETURNS INTEGER AS $$
        BEGIN
                RETURN i + 1;
            END;
$$ LANGUAGE plpgsql`;
}

$ pnpm prettier a.sql --write
a.sql 58ms

$ pnpm prettier a.js --write
a.js
Error: Parse error: Unexpected "$$
       " at line 4 column 22
    at TokenizerEngine.createParseError (file:///Users/k/p/project/node_modules/sql-formatter/lib/lexer/TokenizerEngine.js:53:12)
    at TokenizerEngine.tokenize (file:///Users/k/p/project/node_modules/sql-formatter/lib/lexer/TokenizerEngine.js:35:22)
    at Tokenizer.tokenize (file:///Users/k/p/project/node_modules/sql-formatter/lib/lexer/Tokenizer.js:16:47)
    at LexerAdapter.tokenize (file:///Users/k/p/project/node_modules/sql-formatter/lib/parser/createParser.js:16:76)
    at LexerAdapter.reset (file:///Users/k/p/project/node_modules/sql-formatter/lib/parser/LexerAdapter.js:17:24)
    at Parser.feed (/Users/k/p/project/node_modules/nearley/lib/nearley.js:281:15)
    at Object.parse (file:///Users/k/p/project/node_modules/sql-formatter/lib/parser/createParser.js:26:18)
    at Formatter.parse (file:///Users/k/p/project/node_modules/sql-formatter/lib/formatter/Formatter.js:32:49)
    at Formatter.format (file:///Users/k/p/project/node_modules/sql-formatter/lib/formatter/Formatter.js:25:22)
    at formatDialect (file:///Users/k/p/project/node_modules/sql-formatter/lib/sqlFormatter.js:79:57)
a.js 40ms (unchanged)

At first, from the error message, I thought it was the lack of support for $$ delimiters:

• Feature request: Support for delimiter sql-formatter-org/sql-formatter#184

...but the $$ delimiter does not crash with prettier-plugin-sql used directly with .sql files.

I logged out the query variables in both cases from inside prettier-plugin-sql, but the query seems to be visually the same...

a.sql

CREATE
OR REPLACE FUNCTION INCREMENT (
  i INTEGER
) RETURNS INTEGER AS $$
        BEGIN
                RETURN i + 1;
        END;
$$ LANGUAGE plpgsql

a.js

CREATE
OR REPLACE FUNCTION INCREMENT (
  i INTEGER
) RETURNS INTEGER AS $$
        BEGIN
                RETURN i + 1;
            END;
$$ LANGUAGE plpgsql

My versions and config, maybe I've configured something incorrectly? 🤔

package.json

    "prettier": "3.1.0",
    "prettier-plugin-embed": "0.2.5",
    "prettier-plugin-sql": "0.16.0",

prettier.config.mjs

/** @type {import('prettier').Config} */
const prettierConfig = {
  plugins: [
    'prettier-plugin-tailwindcss',
    'prettier-plugin-embed',
    'prettier-plugin-sql',
  ],
  // Avoid excessive diffs on changes to MDX files
  proseWrap: 'never',
  singleQuote: true,
  trailingComma: 'all',
};

/** @type {import('prettier-plugin-embed').PrettierPluginEmbedOptions} */
const prettierPluginEmbedConfig = {
  embeddedSqlIdentifiers: ['sql'],
};

/** @type {import('prettier-plugin-sql').SqlBaseOptions} */
const prettierPluginSqlConfig = {
  language: 'postgresql',
  keywordCase: 'upper',
  // - Wrap all parenthesized expressions to new lines (eg. `INSERT` columns)
  // - Do not wrap foreign keys (eg. `REFERENCES table_name (id)`)
  // - Do not wrap column type expressions (eg. `VARCHAR(255)`)
  expressionWidth: 8,
};

const config = {
  ...prettierConfig,
  ...prettierPluginEmbedConfig,
  ...prettierPluginSqlConfig,
};

export default config;

cc @JounQin

[karlhorky]

Oh interesting, I just upgraded to sql-formatter@14.0.0, which offers better error messages, and it hinted that the dialect in my config above is not being passed through prettier-plugin-sql to sql-formatter:

This likely happens because you're using the default "sql" dialect.
If possible, please select a more specific dialect (like sqlite, postgresql, etc).

[karlhorky]

Patching sql-formatter lib/sqlFormatter.js file to hard-code the 'postgresql' dialect / language as below seems to work

-const canonicalDialectName = dialectNameMap[cfg.language || 'sql'];
+const canonicalDialectName = dialectNameMap['postgresql'];

Maybe the option is being recognized / passed incorrectly through prettier-plugin-sql when prettier-plugin-embed is used?

[karlhorky]

Since this seems more and more like a duplicate of the following issue, I'm going to close this to avoid duplicate comments:

• sql-formatter crash on SQL casts, tilde, square brackets, $$ delimiter #35