Feature Request: Flyway placeholder support

[TyIsI]

Describe the Feature Flyway is a SQL migration manager and supports placeholders.

Example: ${flyway:database}

When trying to format the following string with the VS Code extension:

ALTER DATABASE ${flyway:DATABASE} CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

It gets formatted as:

ALTER DATABASE
  $ { flyway: DATABASE } CHARACTER
SET
  = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Note: The VS Code extension uses sql-formatter version 8.0.2.

Why do you want this feature?

It would be nice to be able to use placeholder variables.

[TyIsI]

The same input gives the following error using the 10.0.0 CLI version:

Error: Parse error: Unexpected "{flyway:DATABASE} CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

[nene]

SQL Formatter does have placeholder support, however it's designed to work with value placeholders as in prepared statements. These integrate well with SQL, as in many SQL dialects the placeholder syntax is part of the language itself.

The placeholders supported by Flyway are more akin to a templating language. As seen in your example, they can be used to stand in for various identifiers. I bet they can really be used for any sort of text, like:

${create_table} users (
  id ${id_type},
  name ${string_type}
) ${collation};

I don't suggest anybody using the placeholders like this, but there's always the possibility. And it would be pretty much impossible to know how to format code like that.

There could be a middle-ground of only allowing these placeholders in places where identifiers or values are expected. Though it's just my guess that those placeholders are mostly used for substituting identifiers - for all I know, substituting keywords might be just as common, in which case it's not really feasible to support such syntax.

[matthias-Q]

@nene I guess this would also apply to jinja templates used by dbt, right? So, there is no chance that sql-formatter will support SQLs which are littered with placeholder statements.

For dbt, they usually look like this: {{ ...}} or {% ... %}

[nene]

Yes. Quite unlikely. The parsing of SQL is complex enough without adding templating syntax which would make the task theoretically impossible.

[nene]

Actually... turns out that the formatter actually supports ${name} placeholders. I had completely forgotten that we had support for these for Hive and Spark languages. (I thought we had dropped it, as it has all the aforementioned problems, but apparently not). It's just that they are currently classified as "variables", which isn't really correct.

So, currently these sort-of work when you use language: "hive" or "spark" option.

[mvgijssel]

In that case is it maybe possible to add support for {{ ...}} and {% ... %} as well? Currently using sqlfluff in a dbt project, but it's way to slow for things like auto formatting.

[nene]

Now you can add support for these through use of custom string types. See #503.

Though for that you need to implement a custom dialect. Which might look daunting, but is not that hard. You'll basically need to extend an existing dialect, and add additional string types. Something like this:

import { formatDialect, hive, DialectOptions } from "sql-formatter";

// extend Hive dialect with additional quote types: {{..}} and {%..%}
const myDialect: DialectOptions = {
  tokenizerOptions: {
    ...hive.tokenizerOptions,
    stringTypes: [
       ...hive.tokenizerOptions.stringTypes,
       { regex: String.raw`\{\{.*?\}\}` },
       { regex: String.raw`\{%.*?%\}` },
    ],
  },
  formatOptions: {
    ...hive.formatOptions,
  },
};

console.log(formatDialect("SELECT {{template-syntax}}", { dialect: myDialect }));