SQL Formatting: Dialects and Syntax Compatibility

1. Problem: formatted text is not dialect-correct text

The same text can use different keywords, functions, and identifier quoting in different databases. Without a target dialect, uppercasing or reordering tokens can alter reserved words, casts, or string content. Preserving meaning matters more than uniform appearance.

2. Minimal reproduction: three dialects write it differently

These fragments only show why a dialect must be declared; they are not interchangeable SQL. A formatter should preserve token boundaries and fail or retain unknown extensions conservatively.

const samples = {
  postgresql: "SELECT payload::jsonb ->> 'name' FROM \"User\";",
  mysql: "SELECT JSON_EXTRACT(" + String.fromCharCode(96) + "payload" + String.fromCharCode(96) + ", '$.name') FROM " + String.fromCharCode(96) + "User" + String.fromCharCode(96) + ";",
  sqlite: "SELECT json_extract(payload, '$.name') FROM User;",
};
console.log(samples.postgresql);
console.log(samples.mysql);
console.log(samples.sqlite);

3. Root cause: keywords and functions are dialect vocabularies

Reserved words, function signatures, casts, LIMIT/OFFSET forms, and quote rules can differ. Treating every uppercase word as a keyword or every unknown call as safely reorderable crosses a formatter's boundary.

4. Recommendation: let dialect rules format, not translate

Declare the target dialect before formatting and use its keyword, function, identifier-quote, and placeholder rules. When syntax is unsupported or cannot be tokenized, retain it and warn rather than silently producing another database's form.

5. Complete code: warn about dialect features conservatively

This is an input check, not a formatter. It reminds callers to select a dialect and does not inspect string contents or prove executability; a declared-dialect formatter still performs the formatting.

type Dialect = "postgresql" | "mysql" | "sqlite";
function dialectNotes(sql: string, dialect: Dialect): string[] {
  const notes: string[] = [];
  if (sql.includes("::") && dialect !== "postgresql") notes.push("type-cast syntax is PostgreSQL-specific");
  if (sql.includes(String.fromCharCode(96)) && dialect !== "mysql") notes.push("backtick identifiers need dialect confirmation");
  if (sql.includes("JSON_EXTRACT") && dialect === "postgresql") notes.push("check the PostgreSQL JSON operator/function form");
  return notes;
}
console.log(dialectNotes("SELECT payload::jsonb FROM data", "mysql"));
// ["type-cast syntax is PostgreSQL-specific"]

6. Incorrect approaches

Merging PostgreSQL, MySQL, and SQLite keyword lists misclassifies reserved words. Replacing single, double, and backtick quotes uniformly changes identifiers or strings. Calling formatted, parseable output a dialect migration also ignores function and type semantics.

7. Boundaries: comments, placeholders, and extensions

Keywords inside comments and strings, plus parameter placeholders, must not be reordered. Drivers may use $1, ?, or named parameters; formatting should preserve them. If vendor extensions, procedures, or hints are unknown, offer retain, warn, or reject rather than guessing.

8. Verification

For each dialect, prepare samples with keywords, functions, casts, identifier quotes, strings, comments, and placeholders. Use the target parser or client to compare AST or executability and assert strings, parameters, and comments are unchanged; visual neatness is not enough.

9. FAQ

Can a formatter convert MySQL SQL to PostgreSQL? Usually not; formatting and dialect migration are different tasks.

Should an unknown function be renamed? No; retain it and warn about the target dialect.

Why do formatters indent the same SQL differently? Dialect profiles can tokenize keywords and functions differently; use the declared target profile.

10. Summary

Dialect compatibility means semantic fidelity: declare the dialect, use its keyword/function/quote rules, handle unknown extensions conservatively, and verify equivalence with a target parser. Consistent appearance is not SQL migration.