25 MB lightweight cross-platform database client for 100+ databases, including MySQL, PostgreSQL, SQLite, Redis, MongoDB, DuckDB, SQL Server, and Dameng. Built-in AI, MCP Server, CLI, desktop and Docker. | 轻量级跨平台数据库管理工具,支持 MySQL、PostgreSQL、SQLite、Redis、MongoDB、达梦等 100+ 数据库,提供桌面端、Docker、CLI、内置 AI 助手和 MCP。
TypeScript aicliclickhousedatabasedatabase-clientdatabase-managementdockerguimcpmongodbmysqlpostgresqlredisrustsql-serversqlitetaurivue
import { DEFAULT_SQL_FORMATTER_SETTINGS, normalizeSqlFormatterSettings, sqlFormatterOptions, type SqlFormatterIndentStyle, type SqlFormatterSettings } from "@/lib/sql/sqlFormatterConfig";
import { formatSqlLayout, type SqlLayoutOptions } from "@/lib/sql/layout";
import { looksLikeXml } from "@/lib/sql/autoFormat";
import { compressCypherText, formatCypherText } from "@/lib/sql/cypherFormatter";
import { indentRoutineBlocks } from "@/lib/sql/routineBlockLayout";
import { tokenizeSqlSemantic } from "@/lib/sql/semantic/tokens";
export type SqlFormatDialect = "mysql" | "postgres" | "sqlite" | "sqlserver" | "oracle" | "clickhouse" | "dameng" | "duckdb" | "cypher" | "generic";
export const MAX_SQL_FORMAT_CHARS = 1_000_000;
type SqlFormatterModule = typeof import("sql-formatter");
// MySQL's REPLACE is both a write statement and a string function. The
// upstream tokenizer prioritizes the statement token, which cannot appear in
// a CASE branch. Resolve calls by the next non-comment token without changing
// REPLACE INTO / LOW_PRIORITY / DELAYED statements or the source text.
let mysqlFunctionSafeDialect: SqlFormatterModule["mysql"] | null = null;
function resolveMysqlFunctionSafeDialect(sqlFormatter: SqlFormatterModule): SqlFormatterModule["mysql"] {
if (mysqlFunctionSafeDialect) return mysqlFunctionSafeDialect;
const options = sqlFormatter.mysql.tokenizerOptions;
mysqlFunctionSafeDialect = {
...sqlFormatter.mysql,
tokenizerOptions: {
...options,
postProcess: (inputTokens) => {
const tokens = options.postProcess?.(inputTokens) ?? inputTokens;
return tokens.map((token, index) => {
if (token.type !== "RESERVED_CLAUSE" || token.text !== "REPLACE") return token;
let nextIndex = index + 1;
while (tokens[nextIndex]?.type === "LINE_COMMENT" || tokens[nextIndex]?.type === "BLOCK_COMMENT") nextIndex += 1;
if (tokens[nextIndex]?.type !== "OPEN_PAREN") return token;
return { ...token, type: "RESERVED_FUNCTION_NAME" as (typeof tokens)[number]["type"] };
});
},
},
};
return mysqlFunctionSafeDialect;
}
// sql-formatter classifies ClickHouse date-part abbreviations as reserved
// keywords even where ClickHouse accepts them as ordinary identifiers. Keep
// them identifier-like so keyword casing cannot rewrite aliases such as `m`.
const CLICKHOUSE_IDENTIFIER_LIKE_DATE_PARTS = new Set(["D", "DD", "H", "HH", "M", "MCS", "MI", "MM", "MS", "N", "NS", "Q", "QQ", "S", "SS", "WK", "WW", "YY", "YYYY"]);
let clickHouseIdentifierSafeDialect: SqlFormatterModule["clickhouse"] | null = null;
function resolveClickHouseIdentifierSafeDialect(sqlFormatter: SqlFormatterModule): SqlFormatterModule["clickhouse"] {
if (clickHouseIdentifierSafeDialect) return clickHouseIdentifierSafeDialect;
clickHouseIdentifierSafeDialect = {
...sqlFormatter.clickhouse,
tokenizerOptions: {
...sqlFormatter.clickhouse.tokenizerOptions,
reservedKeywords: sqlFormatter.clickhouse.tokenizerOptions.reservedKeywords.filter((keyword) => !CLICKHOUSE_IDENTIFIER_LIKE_DATE_PARTS.has(keyword)),
},
};
return clickHouseIdentifierSafeDialect;
}
// sql-formatter's plsql dialect only registers a minimal subset of character
// functions (e.g. SUBSTR, INSTR, LENGTH) and omits Oracle's byte/multibyte/code-point
// variants. Register them as reserved function names so functionCase normalizes them.
const ORACLE_EXTENDED_FUNCTION_NAMES = ["SUBSTRB", "SUBSTR2", "SUBSTR4", "SUBSTRC", "INSTRB", "INSTR2", "INSTR4", "INSTRC", "LENGTHB", "LENGTH2", "LENGTH4", "LENGTHC"];
// The bundled q-quote pattern uses `.`, so it stops matching at a line break.
// Keep each complete alternative literal as one token in both layout engines.
const ORACLE_Q_QUOTE_PATTERN = String.raw`[Nn]?[Qq]'(?:\[[\s\S]*?\]|\{[\s\S]*?\}|\([\s\S]*?\)|<[\s\S]*?>|(?<dbxOracleQuote>[^\s<\[({])[\s\S]*?\k<dbxOracleQuote>)'`;
let oracleExtendedDialect: SqlFormatterModule["plsql"] | null = null;
function resolveOracleExtendedDialect(sqlFormatter: SqlFormatterModule): SqlFormatterModule["plsql"] {
if (oracleExtendedDialect) return oracleExtendedDialect;
const existingFunctions = new Set(sqlFormatter.plsql.tokenizerOptions.reservedFunctionNames);
const extraFunctions = ORACLE_EXTENDED_FUNCTION_NAMES.filter((name) => !existingFunctions.has(name));
oracleExtendedDialect = {
...sqlFormatter.plsql,
tokenizerOptions: {
...sqlFormatter.plsql.tokenizerOptions,
reservedFunctionNames: [...sqlFormatter.plsql.tokenizerOptions.reservedFunctionNames, ...extraFunctions],
stringTypes: sqlFormatter.plsql.tokenizerOptions.stringTypes.map((type) => (typeof type !== "string" && "quote" in type && type.quote === "q''" ? { regex: ORACLE_Q_QUOTE_PATTERN } : type)),
},
};
return oracleExtendedDialect;
}
// sql-formatter 15's PostgreSQL grammar predates PostgreSQL 15's MERGE
// statement: MERGE / MATCHED are plain identifiers to it, so the DBX layout
// printer — which splits a statement at its clauses — receives a flat child
// list it cannot recognise and puts every token on its own line. Register
// PostgreSQL's MERGE clauses (and the bare `INSERT` action, which has no
// `INTO`) so MERGE keeps the same one-clause-per-line shape as INSERT/UPDATE.
// The statement itself is untouched: the words are only recognized as clause
// boundaries. `WHEN MATCHED` / `WHEN NOT MATCHED` are registered without the
// trailing `THEN` so the optional `AND <condition>` form still splits here.
const POSTGRES_MERGE_CLAUSES = ["MERGE INTO", "WHEN MATCHED", "WHEN NOT MATCHED", "UPDATE SET", "INSERT"];
let postgresMergeDialect: SqlFormatterModule["postgresql"] | null = null;
function resolvePostgresMergeDialect(sqlFormatter: SqlFormatterModule): SqlFormatterModule["postgresql"] {
if (postgresMergeDialect) return postgresMergeDialect;
const clauses = sqlFormatter.postgresql.tokenizerOptions.reservedClauses;
const extraClauses = POSTGRES_MERGE_CLAUSES.filter((clause) => !clauses.includes(clause));
postgresMergeDialect = {
...sqlFormatter.postgresql,
tokenizerOptions: {
...sqlFormatter.postgresql.tokenizerOptions,
reservedClauses: [...clauses, ...extraClauses],
},
};
return postgresMergeDialect;
}
export function canFormatSqlForDatabaseType(dbType: string | null | undefined): boolean {
return dbType !== "redis" && dbType !== "victoriametrics";
}
/**
* Thrown by {@link formatSqlText} when the input is XML-looking and must never
* be run through the SQL formatter. sql-formatter silently rewrites well-formed
* XML into corrupted output, so this guard keeps any caller (including future
* ones) from corrupting structured text. Callers that can format XML should
* route before calling (see {@link detectAndFormatStructured}); this is
* defense-in-depth.
*/
export class UnsupportedStructuredInputError extends Error {
constructor(readonly detectedType: "xml") {
super(`Cannot format ${detectedType} content as SQL.`);
this.name = "UnsupportedStructuredInputError";
}
}
/**
* Maps a connection's database type to the SQL-formatter dialect to use.
*
* Postgres-compatible engines (GaussDB/openGauss/Kingbase/...) reuse the
* "postgres" grammar, SQLite-compatible ones reuse "sqlite", and DuckDB gets
* a scoped preprocessing pass over the generic grammar. Anything unrecognized
* falls back to the permissive "generic" dialect. Centralized here so every
* surface that formats SQL (editor, object source, DDL viewers) stays in sync.
*/
export function sqlFormatDialectForDbType(dbType: string | null | undefined): SqlFormatDialect {
switch (dbType) {
case "mysql":
return "mysql";
case "postgres":
case "kwdb":
case "gaussdb":
case "opengauss":
case "questdb":
case "kingbase":
case "highgo":
case "uxdb":
case "vastbase":
case "redshift":
return "postgres";
case "sqlite":
case "rqlite":
case "turso":
case "cloudflare-d1":
return "sqlite";
case "sqlserver":
return "sqlserver";
case "oracle":
// OceanBase Oracle mode speaks Oracle SQL, so it reuses the PL/SQL grammar.
case "oceanbase-oracle":
return "oracle";
case "clickhouse":
return "clickhouse";
case "dameng":
return "dameng";
case "duckdb":
return "duckdb";
case "neo4j":
return "cypher";
default:
return "generic";
}
}
function formatterLanguage(dialect: SqlFormatDialect) {
switch (dialect) {
case "mysql":
return "mysql";
case "postgres":
return "postgresql";
case "sqlite":
return "sqlite";
case "sqlserver":
return "transactsql";
case "oracle":
return "plsql";
case "clickhouse":
return "clickhouse";
default:
return "sql";
}
}
/**
* sql-formatter's language key for a format dialect, or `null` for a dialect
* that has no SQL grammar at all (Cypher — see {@link formatSqlText}, which
* routes it to the Cypher formatter before reaching sql-formatter).
*
* Exported so callers that need sql-formatter's own tokenization — keyword-only
* case conversion — resolve the dialect exactly the way formatting does.
*/
export function formatterLanguageForDialect(dialect: SqlFormatDialect): string | null {
return dialect === "cypher" ? null : formatterLanguage(dialect);
}
function splitTrailingStandaloneDot(sql: string): { body: string; suffix: string } | null {
const match = /\s+\.(\s*)$/.exec(sql);
if (!match || match.index === 0) return null;
return {
body: sql.slice(0, match.index),
suffix: match[0],
};
}
interface EmptyLineProtection {
sql: string;
markers: string[];
}
/**
* Replaces blank source lines with unique line comments while the third-party
* formatter runs. sql-formatter intentionally normalizes whitespace, whereas
* the optional DBX setting needs to retain visual paragraph boundaries such as
* the blank line between a heading comment and a query. Line comments are
* valid at every SQL code boundary and are restored only after all DBX layout
* post-processing is complete.
*/
function protectEmptyLines(sql: string, originalSql = sql): EmptyLineProtection {
let namespace = 0;
while (originalSql.includes(`__DBX_PRESERVE_EMPTY_LINE_${namespace}_`)) namespace += 1;
const markers: string[] = [];
const lineBreakPattern = /\r\n|\r|\n/g;
let output = "";
let lineStart = 0;
let match: RegExpExecArray | null;
while ((match = lineBreakPattern.exec(sql))) {
const line = sql.slice(lineStart, match.index);
if (line.trim().length === 0) {
const marker = `-- __DBX_PRESERVE_EMPTY_LINE_${namespace}_${markers.length}__`;
markers.push(marker);
output += marker;
} else {
output += line;
}
output += match[0];
lineStart = lineBreakPattern.lastIndex;
}
// A final non-terminated blank line is still a user-authored empty line.
// Do not manufacture one after a trailing line break, though: that would
// turn the normal EOF sentinel into an additional preserved blank line.
const finalLine = sql.slice(lineStart);
if (finalLine.length > 0 && finalLine.trim().length === 0) {
const marker = `-- __DBX_PRESERVE_EMPTY_LINE_${namespace}_${markers.length}__`;
markers.push(marker);
output += marker;
} else {
output += finalLine;
}
return { sql: markers.length > 0 ? output : sql, markers };
}
function restoreProtectedEmptyLines(sql: string, markers: readonly string[]): string {
if (markers.length === 0) return sql;
const markerSet = new Set(markers);
// Keep each terminator: normalizing the whole result would also rewrite
// line breaks inside string literals and block comments.
const lines = Array.from(sql.matchAll(/[^\r\n]*(?:\r\n|\r|\n|$)/g), ([line]) => line);
for (let index = 0; index < lines.length;) {
if (!markerSet.has(lines[index].trim())) {
index += 1;
continue;
}
let runEnd = index;
while (runEnd < lines.length && markerSet.has(lines[runEnd].trim())) runEnd += 1;
const markerCount = runEnd - index;
// sql-formatter adds `linesBetweenQueries` blank lines before a marker
// group placed between statements. Those lines did not exist in the
// source—the marker group itself represents the source blank lines—so
// remove at most one generated line per original blank line. This keeps
// the larger of the user-authored spacing and the formatter's configured
// query spacing instead of adding the two counts together.
let removed = 0;
while (removed < markerCount && index > 0 && lines[index - 1].trim().length === 0) {
lines.splice(index - 1, 1);
index -= 1;
runEnd -= 1;
removed += 1;
}
for (let markerIndex = index; markerIndex < runEnd; markerIndex += 1) {
lines[markerIndex] = lines[markerIndex].replace(/[^\r\n]+/, "");
}
index = runEnd;
}
return lines.join("");
}
const DUCKDB_ALIAS_IDENTIFIER_START_RE = /[\p{L}_]/u;
const DUCKDB_ALIAS_IDENTIFIER_CHAR_RE = /[\p{L}\p{N}_]/u;
function hasDuckDbBarePrefixAliasBefore(sql: string, colonIndex: number): boolean {
let start = colonIndex;
while (start > 0 && DUCKDB_ALIAS_IDENTIFIER_CHAR_RE.test(sql[start - 1])) start -= 1;
return start < colonIndex && DUCKDB_ALIAS_IDENTIFIER_START_RE.test(sql[start]);
}
function duckDbDollarQuoteMarkerAt(sql: string, index: number): string | null {
if (sql[index] !== "$" || DUCKDB_ALIAS_IDENTIFIER_CHAR_RE.test(sql[index - 1] ?? "")) return null;
if (sql[index + 1] === "$") return "$$";
if (!/[A-Za-z_]/.test(sql[index + 1] ?? "")) return null;
let end = index + 2;
while (/[A-Za-z0-9_]/.test(sql[end] ?? "")) end += 1;
return sql[end] === "$" ? sql.slice(index, end + 1) : null;
}
function protectDuckDbPrefixAliasSeparators(sql: string): { sql: string; marker: string | null } {
let markerIndex = 0;
let marker = "";
do {
marker = `/*__DBX_DUCKDB_PREFIX_ALIAS_COLON_${markerIndex}__*/`;
markerIndex += 1;
} while (sql.includes(marker));
let protectedSql = "";
let quotedIdentifierEnd = -1;
let replaced = false;
let index = 0;
while (index < sql.length) {
const ch = sql[index];
const next = sql[index + 1];
if (ch === "-" && next === "-") {
const start = index;
index += 2;
while (index < sql.length && sql[index] !== "\n" && sql[index] !== "\r") index += 1;
protectedSql += sql.slice(start, index);
continue;
}
if (ch === "/" && next === "*") {
const start = index;
index += 2;
let depth = 1;
while (index < sql.length && depth > 0) {
if (sql[index] === "/" && sql[index + 1] === "*") {
depth += 1;
index += 2;
} else if (sql[index] === "*" && sql[index + 1] === "/") {
depth -= 1;
index += 2;
} else {
index += 1;
}
}
protectedSql += sql.slice(start, index);
continue;
}
const dollarQuoteMarker = ch === "$" ? duckDbDollarQuoteMarkerAt(sql, index) : null;
if (dollarQuoteMarker) {
const start = index;
const end = sql.indexOf(dollarQuoteMarker, index + dollarQuoteMarker.length);
index = end < 0 ? sql.length : end + dollarQuoteMarker.length;
protectedSql += sql.slice(start, index);
continue;
}
if (ch === "'" || ch === '"' || ch === "`") {
const start = index;
const quote = ch;
let closed = false;
index += 1;
while (index < sql.length) {
if (sql[index] === "\\" && index + 1 < sql.length) {
index += 2;
continue;
}
if (sql[index] === quote) {
if (sql[index + 1] === quote) {
index += 2;
continue;
}
index += 1;
closed = true;
break;
}
index += 1;
}
protectedSql += sql.slice(start, index);
if (quote === '"' && closed) quotedIdentifierEnd = index;
continue;
}
const prefixAlias = ch === ":" && sql[index - 1] !== ":" && next !== ":" && next !== "=" && (quotedIdentifierEnd === index || hasDuckDbBarePrefixAliasBefore(sql, index));
if (prefixAlias) {
// sql-formatter tokenizes a compact DuckDB prefix alias as a named
// parameter and inserts a space before `:`. Keeping an opaque separator
// through formatting preserves the boundary used by DBX's parameter scan.
protectedSql += marker;
replaced = true;
index += 1;
continue;
}
protectedSql += ch;
index += 1;
}
return replaced ? { sql: protectedSql, marker } : { sql, marker: null };
}
function restoreDuckDbPrefixAliasSeparators(sql: string, marker: string | null): string {
return marker ? sql.split(marker).join(":") : sql;
}
function protectBlockCommentLineEndings(sql: string, dialect: SqlFormatDialect): { sql: string; markers: Map<string, string> } {
const markers = new Map<string, string>();
if (!sql.includes("\r")) return { sql, markers };
let namespace = 0;
while (sql.includes(`__DBX_PRESERVE_BLOCK_COMMENT_${namespace}_`)) namespace += 1;
const { masked, spans } = maskStringAndCommentSpans(sql, dialect);
const protectedSpans = spans.map((span) => {
if (!span.startsWith("/*") || !span.includes("\r")) return span;
// Keep hints and MySQL executable comments in their original comment category.
const kind = span[2] === "+" || span[2] === "!" ? span[2] : "";
const marker = `/*${kind}__DBX_PRESERVE_BLOCK_COMMENT_${namespace}_${markers.size}__*/`;
markers.set(marker, span);
return marker;
});
return { sql: restoreSpans(masked, protectedSpans), markers };
}
export type SqlFormatterDiagnostic = "custom-rules-fallback";
export async function formatSqlText(sql: string, dialect: SqlFormatDialect = "generic", settings: Partial<SqlFormatterSettings> = DEFAULT_SQL_FORMATTER_SETTINGS, onDiagnostic?: (diagnostic: SqlFormatterDiagnostic) => void): Promise<string> {
if (!sql.trim()) return sql;
if (sql.length > MAX_SQL_FORMAT_CHARS) {
throw new Error("SQL is too large to format safely.");
}
if (looksLikeXml(sql)) {
throw new UnsupportedStructuredInputError("xml");
}
const normalizedSettings = normalizeSqlFormatterSettings(settings);
if (dialect === "cypher") return formatCypherText(sql, normalizedSettings);
const sqlFormatter = await import("sql-formatter");
const { format, formatDialect } = sqlFormatter;
const language = formatterLanguage(dialect);
const emptyLineMode = normalizedSettings.customRulesEnabled && normalizedSettings.emptyLineMode !== "inherit" ? normalizedSettings.emptyLineMode : normalizedSettings.preserveEmptyLines ? "preserve" : "remove";
const linesBetweenQueries =
normalizedSettings.customRulesEnabled && normalizedSettings.emptyLineMode === "remove" ? 0 : normalizedSettings.customRulesEnabled && normalizedSettings.emptyLineMode === "single" && normalizedSettings.linesBetweenQueries === 2 ? 1 : normalizedSettings.linesBetweenQueries;
const options = sqlFormatterOptions({ ...normalizedSettings, linesBetweenQueries });
// The external formatter reindents multiline block comments and drops CRs.
// Preserve affected comments until all formatting and layout passes finish.
const commentProtection = protectBlockCommentLineEndings(sql, dialect);
const emptyLineProtection = emptyLineMode === "remove" ? null : protectCodeEmptyLines(commentProtection.sql, dialect, emptyLineMode === "single");
const sqlWithProtectedEmptyLines = emptyLineProtection?.sql ?? commentProtection.sql;
const protectedInput = dialect === "duckdb" ? protectDuckDbPrefixAliasSeparators(sqlWithProtectedEmptyLines) : { sql: sqlWithProtectedEmptyLines, marker: null };
const formatterOptions =
language === "postgresql"
? {
...options,
paramTypes: {
...options.paramTypes,
// PostgreSQL itself uses double quotes, but users commonly format
// imported MySQL-style SQL before correcting it. Treat complete
// backtick spans as opaque tokens so the PostgreSQL lexer can keep
// formatting the surrounding statement without rewriting them.
custom: [...options.paramTypes.custom, { regex: "`(?:``|[^`])*`" }],
},
}
: options;
const resolvedDialect =
dialect === "mysql"
? resolveMysqlFunctionSafeDialect(sqlFormatter)
: dialect === "clickhouse"
? resolveClickHouseIdentifierSafeDialect(sqlFormatter)
: dialect === "oracle"
? resolveOracleExtendedDialect(sqlFormatter)
: dialect === "postgres"
? resolvePostgresMergeDialect(sqlFormatter)
: undefined;
const formatWithFallback = (input: string): string => {
try {
if (resolvedDialect) {
return formatDialect(input, { ...formatterOptions, dialect: resolvedDialect });
}
return format(input, { language, ...formatterOptions });
} catch (err) {
// The generic "sql" dialect can't parse many real-world constructs (PostgreSQL
// `::` casts, GaussDB/openGauss materialized-view DDL, T-SQL specifics, ...).
// Retry once with the more permissive PostgreSQL grammar, which is a superset
// that tolerates most of these, before surfacing the failure.
if (language !== "postgresql") {
try {
return format(input, { language: "postgresql", ...options });
} catch {
// fall through to the original error below
}
}
throw err;
}
};
let appliedCustomRules = false;
const finalizeFormattedSql = (formatted: string): string => {
if (normalizedSettings.customRulesEnabled && !appliedCustomRules) {
onDiagnostic?.("custom-rules-fallback");
return formatted;
}
const restoredDuckDbAliases = restoreDuckDbPrefixAliasSeparators(formatted, protectedInput.marker);
const laidOut = appliedCustomRules ? normalizeLikeOperatorCase(restoredDuckDbAliases, normalizedSettings, dialect) : applySqlFormatterLayout(restoredDuckDbAliases, normalizedSettings, dialect);
const restored = emptyLineProtection ? restoreProtectedEmptyLines(laidOut, emptyLineProtection.markers) : laidOut;
return commentProtection.markers.size ? restored.replace(/\/\*[+!]?__DBX_PRESERVE_BLOCK_COMMENT_\d+_\d+__\*\//g, (marker) => commentProtection.markers.get(marker) ?? marker) : restored;
};
// DBX's own layout printer produces the default `dbx` style. It needs the AST
// and sql-formatter's internal layout machinery, so it can decline an input —
// an unparseable statement, an internal shape that moved. `null` means "use
// the public formatter", which is also what the `classic` layout style asks
// for: that style is sql-formatter's own layout, i.e. the pre-v0.6.16 default,
// and the applySqlFormatterLayout post-passes exist to serve it. The tabular
// indent styles likewise are an alternative layout this printer deliberately
// does not reproduce.
const layoutOptions: Partial<SqlLayoutOptions> = {
lineWidth: normalizedSettings.expressionWidth,
indentWidth: normalizedSettings.tabWidth,
useTabs: normalizedSettings.useTabs,
linesBetweenQueries,
fromClauseSourceOnSameLine: normalizedSettings.fromClauseLayout === "sameLine",
keywordCase: normalizedSettings.keywordCase,
logicalOperatorNewline: normalizedSettings.logicalOperatorNewline,
commaPosition: normalizedSettings.commaPosition,
newlineBeforeSemicolon: normalizedSettings.newlineBeforeSemicolon,
};
const usesDefaultStyle = normalizedSettings.layoutStyle === "dbx" && normalizedSettings.indentStyle === "standard";
const formatOnce = async (input: string): Promise<string> => {
if (normalizedSettings.customRulesEnabled) {
const customLayout = await formatSqlLayout({ sql: input, language, dialectOptions: resolvedDialect, cfg: formatterOptions, options: { ...layoutOptions, rules: normalizedSettings } });
appliedCustomRules = customLayout !== null;
if (customLayout !== null) return customLayout;
// Retry the original input through the saved base layout, without rule-only
// empty-line markers or spacing overrides. The disabled flag bounds the retry.
return formatSqlText(sql, dialect, { ...normalizedSettings, customRulesEnabled: false });
}
const laidOut = usesDefaultStyle ? await formatSqlLayout({ sql: input, language, dialectOptions: resolvedDialect, cfg: formatterOptions, options: layoutOptions }) : null;
return laidOut ?? formatWithFallback(input);
};
// Routine bodies have no block nodes in sql-formatter's grammar, so the
// clause layout prints their statements at column 0. Re-indent them once all
// other passes have run, i.e. on text that is already the final SQL.
const applyRoutineBlockLayout = (formatted: string): string => indentRoutineBlocks(formatted, dialect, normalizedSettings.tabWidth, normalizedSettings.useTabs);
try {
return applyRoutineBlockLayout(finalizeFormattedSql(await formatOnce(protectedInput.sql)));
} catch (err) {
const trailingDot = dialect === "dameng" ? splitTrailingStandaloneDot(protectedInput.sql) : null;
if (!trailingDot) throw err;
try {
return applyRoutineBlockLayout(finalizeFormattedSql(`${await formatOnce(trailingDot.body)}${trailingDot.suffix}`));
} catch {
throw err;
}
}
}
/**
* Replaces block comments, line comments and string/identifier literals in
* `sql` with opaque placeholders, returning the masked text plus the captured
* spans in source order. The two regexes in {@link keepLogicalOperatorsOnSameLine}
* operate on plain text and cannot tell a logical operator inside a comment or
* string literal from one in a real SQL clause, so a multi-line `/* ... AND ...
* OR ... *\/` comment would get its internal line breaks collapsed. Masking
* those spans first keeps the regexes away from them; {@link restoreSpans} puts
* the original text back afterwards.
*
* The scanner reuses the same dialect-aware token recognition as
* {@link compressSqlText}: block comments (with nesting for Postgres/SQL
* Server/ClickHouse), `--`/`#` line comments, Postgres/DuckDB dollar-quoted strings,
* single-quoted strings (`''` and MySQL/`E'...'` backslash escapes), double-
* quoted identifiers (`""` escapes), MySQL backtick identifiers and SQL Server
* `[...]` identifiers. sql-formatter normalizes string literals to a single
* line, but masking them is cheap and defends against callers that ever pass
* raw (non-formatter) SQL through this path.
*/
function maskStringAndCommentSpans(sql: string, dialect: SqlFormatDialect, protectLineCommentTerminator = false): { masked: string; spans: string[] } {
const len = sql.length;
const spans: string[] = [];
const placeholder = (index: number) => `\x00${index}\x00`;
let out = "";
let i = 0;
const oracleQuotes =
dialect === "oracle"
? new Map(
tokenizeSqlSemantic(sql, "oracle")
.filter((token) => token.kind === "string" && (token.quote === "q'" || token.quote === "nq'"))
.map((token) => [token.span.start, token.span.end]),
)
: null;
const isIdentifierPart = (c: string | undefined) => c !== undefined && /[A-Za-z0-9_$]/.test(c);
const supportsNestedBlockComments = dialect === "postgres" || dialect === "sqlserver" || dialect === "clickhouse";
const isMysqlDashComment = (c: string | undefined) => c === undefined || c.charCodeAt(0) <= 32 || c.charCodeAt(0) === 127;
const dollarQuoteTagAt = (position: number): string | null => {
if (sql[position] !== "$" || isIdentifierPart(sql[position - 1])) return null;
if (sql[position + 1] === "$") return "$$";
if (!/[A-Za-z_]/.test(sql[position + 1] ?? "")) return null;
let end = position + 2;
while (/[A-Za-z0-9_]/.test(sql[end] ?? "")) end++;
return sql[end] === "$" ? sql.slice(position, end + 1) : null;
};
// Captures a full span starting at `start` (already consumed into `i`) and
// emits a placeholder. `end` is the index just past the span terminator.
const emit = (start: number, end: number) => {
spans.push(sql.slice(start, end));
out += placeholder(spans.length - 1);
};
while (i < len) {
const ch = sql[i];
const next = sql[i + 1];
const oracleQuoteEnd = oracleQuotes?.get(i);
if (oracleQuoteEnd !== undefined) {
emit(i, oracleQuoteEnd);
i = oracleQuoteEnd;
continue;
}
// 块注释 /* ... */(含嵌套)
if (ch === "/" && next === "*") {
const start = i;
i += 2;
let depth = 1;
while (i < len && depth > 0) {
if (supportsNestedBlockComments && sql[i] === "/" && sql[i + 1] === "*") {
depth++;
i += 2;
} else if (sql[i] === "*" && sql[i + 1] === "/") {
depth--;
i += 2;
} else {
i++;
}
}
// 未闭合的块注释:原样保留剩余文本(不遮罩),避免破坏用户输入。
if (depth > 0) {
out += sql.slice(start);
i = len;
} else {
emit(start, i);
}
continue;
}
// 行注释 -- ... / # ...(不跨行,但遮罩可防御未来变更)
const startsDashComment = ch === "-" && next === "-" && (dialect !== "mysql" || isMysqlDashComment(sql[i + 2]));
if (startsDashComment || (dialect === "mysql" && ch === "#")) {
const start = i;
i += startsDashComment ? 2 : 1;
while (i < len && sql[i] !== "\n" && sql[i] !== "\r") i++;
if (protectLineCommentTerminator && i < len) i += sql[i] === "\r" && sql[i + 1] === "\n" ? 2 : 1;
emit(start, i);
continue;
}
// PostgreSQL dollar-quoted 字符串
if ((dialect === "postgres" || dialect === "duckdb") && ch === "$") {
const tag = dollarQuoteTagAt(i);
if (tag) {
const start = i;
i += tag.length;
const end = sql.indexOf(tag, i);
if (end < 0) {
out += sql.slice(start);
i = len;
} else {
emit(start, end + tag.length);
i = end + tag.length;
}
continue;
}
}
// 单引号字符串('' 转义;MySQL/PG E'...' 反斜杠转义)
if (ch === "'") {
const start = i;
i++;
const postgresEscapeString = dialect === "postgres" && (sql[i - 2] === "E" || sql[i - 2] === "e") && !isIdentifierPart(sql[i - 3]);
while (i < len) {
const c = sql[i];
if ((dialect === "mysql" || postgresEscapeString) && c === "\\" && i + 1 < len) {
i += 2;
continue;
}
if (c === "'") {
if (sql[i + 1] === "'") {
i += 2;
continue;
}
i++;
break;
}
i++;
}
emit(start, i);
continue;
}
// 双引号标识符("" 转义)
if (ch === '"') {
const start = i;
i++;
while (i < len) {
if (dialect === "mysql" && sql[i] === "\\" && i + 1 < len) {
i += 2;
continue;
}
if (sql[i] === '"') {
if (sql[i + 1] === '"') {
i += 2;
continue;
}
i++;
break;
}
i++;
}
emit(start, i);
continue;
}
// 反引号标识符(MySQL)
if (ch === "`") {
const start = i;
i++;
while (i < len && sql[i] !== "`") i++;
if (i < len) i++;
emit(start, i);
continue;
}
// SQL Server 方括号标识符 [...](]] 为转义 ])
if (dialect === "sqlserver" && ch === "[") {
const start = i;
i++;
while (i < len) {
if (sql[i] === "]") {
if (sql[i + 1] === "]") {
i += 2;
continue;
}
i++;
break;
}
i++;
}
emit(start, i);
continue;
}
out += ch;
i++;
}
return { masked: out, spans };
}
/** Hide quoted/comment spans before identifying code-only paragraph boundaries. */
function protectCodeEmptyLines(sql: string, dialect: SqlFormatDialect, single: boolean): EmptyLineProtection {
const { masked, spans } = maskStringAndCommentSpans(sql, dialect);
const code = single ? masked.replace(/(?:\r\n|\r|\n)[ \t]*(?:(?:\r\n|\r|\n)[ \t]*){2,}/g, "\n\n") : masked;
const protectedLines = protectEmptyLines(code, sql);
return { sql: restoreSpans(protectedLines.sql, spans), markers: protectedLines.markers };
}
function restoreSpans(masked: string, spans: string[]): string {
return masked.replace(/\x00(\d+)\x00/g, (_, index) => spans[Number(index)] ?? "");
}
function keepLogicalOperatorsOnSameLine(sql: string, dialect: SqlFormatDialect = "generic"): string {
// 先遮罩块注释/字符串/引号标识符,避免正则命中注释或字面量内部的 AND/OR/XOR
// (块注释内部跨行的 AND/OR 会被误折叠成空格,破坏用户多行注释格式)。
// A line comment's terminator is a code boundary, not optional layout whitespace.
const { masked, spans } = maskStringAndCommentSpans(sql, dialect, true);
const collapsed = masked.replace(/\n[ \t]*(AND|OR|XOR)\b/gi, " $1").replace(/\b(AND|OR|XOR)[ \t]*\n[ \t]*/gi, "$1 ");
return restoreSpans(collapsed, spans);
}
function normalizeLikeOperatorCase(sql: string, settings: SqlFormatterSettings, dialect: SqlFormatDialect): string {
if ((dialect !== "mysql" && dialect !== "sqlite") || settings.keywordCase === "preserve") return sql;
// sql-formatter classifies LIKE as a reserved function in these dialects,
// so an operator follows functionCase instead of keywordCase. Only adjust
// operator-shaped occurrences; keep LIKE(...) functions and qualified
// identifiers under their existing function/identifier case settings.
const { masked, spans } = maskStringAndCommentSpans(sql, dialect);
const keyword = settings.keywordCase === "upper" ? "LIKE" : "like";
const normalized = masked.replace(/(^|[^.\w$:@{#])LIKE\b(?!\s*\()/gi, (_match, prefix: string) => `${prefix}${keyword}`);
return restoreSpans(normalized, spans);
}
function keepFromClauseAndFirstSourceOnSameLine(sql: string, indentStyle: SqlFormatterIndentStyle, dialect: SqlFormatDialect): string {
const { masked, spans } = maskStringAndCommentSpans(sql, dialect);
const lines = masked.split("\n");
for (let index = 0; index < lines.length - 1; index += 1) {
const clauseMatch = lines[index].match(/^(\s*)FROM\s*$/i);
if (!clauseMatch) continue;
const sourceLine = lines[index + 1];
const sourceMatch = sourceLine.match(/^(\s+)(\S.*)$/);
if (!sourceMatch) continue;
const source = sourceMatch[2];
// Keep derived tables and leading comments multiline; merging these would
// make nested SQL and comment boundaries substantially harder to read.
const leadingSpan = source.startsWith("\x00") ? spans[Number(source.split("\x00", 3)[1])] : "";
if (source.startsWith("(") || leadingSpan.startsWith("/*") || leadingSpan.startsWith("--") || (dialect === "mysql" && leadingSpan.startsWith("#"))) continue;
const clauseIndent = clauseMatch[1];
const sourceIndent = sourceMatch[1];
// The source line's indent is the gap to reuse only for the tabular styles:
// there it is the column sql-formatter aligned every clause to, and
// collapsing it would destroy that alignment (`FROM t`). Under the
// standard indent the same indent is just the clause body's step, so reusing
// it doubles the gap (`FROM t`) — the upstream one-line form is a single
// space, and that is what merging is supposed to produce.
const separator = indentStyle === "standard" ? " " : sourceIndent.startsWith(clauseIndent) ? sourceIndent.slice(clauseIndent.length) : " ";
lines[index] = `${lines[index]}${separator || " "}${source}`;
lines.splice(index + 1, 1);
index -= 1;
}
return restoreSpans(lines.join("\n"), spans);
}
function formatLeadingCommas(sql: string, dialect: SqlFormatDialect = "generic"): string {
const { masked, spans } = maskStringAndCommentSpans(sql, dialect);
const lines = masked.split("\n");
for (let i = 0; i < lines.length; i += 1) {
const match = lines[i].match(/^(.*),\s*((?:\x00\d+\x00\s*)*)$/);
if (!match || match[1].trim().length === 0) continue;
let j = i + 1;
while (j < lines.length) {
const trimmed = lines[j].trim();
if (!trimmed) {
j += 1;
continue;
}
if (/^(?:\x00\d+\x00\s*)+$/.test(trimmed)) {
j += 1;
continue;
}
break;
}
if (j >= lines.length) continue;
const nextTrimmed = lines[j].trim();
if (nextTrimmed.startsWith(")") || nextTrimmed.startsWith("]") || nextTrimmed.startsWith(";") || nextTrimmed.startsWith(",")) {
continue;
}
const codeBeforeComma = match[1].trimEnd();
const trailingComments = match[2] ? (codeBeforeComma.endsWith(" ") ? match[2] : ` ${match[2]}`) : "";
lines[i] = codeBeforeComma + trailingComments;
const nextIndentMatch = lines[j].match(/^(\s*)/);
const nextIndent = nextIndentMatch ? nextIndentMatch[1] : "";
const nextRest = lines[j].slice(nextIndent.length);
const currentIndentMatch = lines[i].match(/^(\s*)/);
const currentIndent = currentIndentMatch ? currentIndentMatch[1] : "";
if (currentIndent === nextIndent) {
lines[j] = `${nextIndent}, ${nextRest}`;
} else if (nextIndent.length >= 2) {
lines[j] = `${nextIndent.slice(0, -2)}, ${nextRest}`;
} else {
lines[j] = `${nextIndent}, ${nextRest}`;
}
}
return restoreSpans(lines.join("\n"), spans);
}
/**
* The text-level passes applied to whatever the formatter produced, whether the
* default style's layout printer or sql-formatter itself.
*
* The two settings-driven passes fix shapes the printer already gets right on
* its own — it keeps a `FROM` source on the keyword's line and joins operators
* for `none` — so they are no-ops on its output. They stay because a statement
* the printer declines falls back to sql-formatter's layout for the whole input,
* which does break those shapes; see `formatSqlText`. {@link
* normalizeLikeOperatorCase} is unrelated to either: it corrects a dialect quirk
* in both paths.
*/
function applySqlFormatterLayout(sql: string, settings: SqlFormatterSettings, dialect: SqlFormatDialect): string {
let formatted = normalizeLikeOperatorCase(sql, settings, dialect);
if (settings.logicalOperatorNewline === "none") formatted = keepLogicalOperatorsOnSameLine(formatted, dialect);
if (settings.fromClauseLayout === "sameLine") formatted = keepFromClauseAndFirstSourceOnSameLine(formatted, settings.indentStyle, dialect);
if (settings.commaPosition === "before") formatted = formatLeadingCommas(formatted, dialect);
return formatted;
}
/**
* sql-formatter throws two distinct shapes when it can't parse the input:
* - Grammar-level (nearley parser): `Parse error at token: ... ` with `.offset`/`.token`
* set on the error, e.g. valid tokens in an unexpected position (mid-edit SQL).
* - Lexer-level (TokenizerEngine): a plain `Parse error: Unexpected "..." at line ...`
* when a character sequence doesn't match any token rule at all — e.g. full-width
* punctuation (`≠`, `(`, `)`) that MySQL accepts in identifiers/expressions but
* sql-formatter's tokenizer doesn't recognize.
* Both mean "sql-formatter can't handle this text", so both should fall back to the
* original SQL instead of surfacing a failure.
*/
function isSqlFormatterParseError(error: unknown): boolean {
if (!(error instanceof Error)) return false;
if (error.message.startsWith("Parse error: Unexpected ")) return true;
if (!error.message.startsWith("Parse error at token:")) return false;
const candidate = error as Error & { offset?: unknown; token?: unknown };
return typeof candidate.offset === "number" && candidate.token !== undefined;
}
export async function formatSqlForEditing(sql: string, dialect: SqlFormatDialect = "generic", settings: Partial<SqlFormatterSettings> = DEFAULT_SQL_FORMATTER_SETTINGS): Promise<string> {
try {
return await formatSqlText(sql, dialect, settings);
} catch (error) {
if (isSqlFormatterParseError(error)) return sql;
throw error;
}
}
/**
* 压缩 SQL 时使用的方言。不同方言对引号、注释、转义的处理不同:
* - `mysql`:保留 MySQL 可执行注释与 optimizer hint;单引号字符串支持反斜杠转义
* - `postgres`:支持 dollar-quoted 字符串
* - `sqlserver`:支持方括号标识符
* - `generic` / 其它:仅处理标准单/双引号与块/行注释
*/
export type SqlCompressDialect = SqlFormatDialect;
/**
* 将 SQL 压缩成一行可执行文本:折叠所有空白(含换行)为单个空格,
* 移除普通行注释(-- ...)与普通块注释(/* ... *\/),
* 同时按方言完整保留字符串字面量、引号标识符、可执行注释与 optimizer hint。
*
* 方言感知说明:
* - MySQL:可执行注释作为可执行代码原样保留(仅折叠内部空白);
* optimizer hint 原样保留;单引号字符串内反斜杠转义保留
* - PostgreSQL:dollar-quoted 字符串原样保留(含标签形式)
* - SQL Server:方括号标识符原样保留(双右括号为转义)
* - 所有方言:单引号字符串、双引号标识符、反引号标识符均保留
*/
export function compressSqlText(sql: string, dialect: SqlCompressDialect = "generic"): string {
if (!sql.trim()) return sql;
if (dialect === "cypher") return compressCypherText(sql);
const len = sql.length;
let out = "";
let i = 0;
const isWhitespace = (c: string) => c === " " || c === "\t" || c === "\n" || c === "\r" || c === "\f" || c === "\v";
const isIdentifierPart = (c: string | undefined) => c !== undefined && /[A-Za-z0-9_$]/.test(c);
const isMysqlDashComment = (c: string | undefined) => c === undefined || c.charCodeAt(0) <= 32 || c.charCodeAt(0) === 127;
const supportsNestedBlockComments = dialect === "postgres" || dialect === "sqlserver" || dialect === "clickhouse";
const dollarQuoteTagAt = (position: number): string | null => {
if (sql[position] !== "$" || isIdentifierPart(sql[position - 1])) return null;
if (sql[position + 1] === "$") return "$$";
if (!/[A-Za-z_]/.test(sql[position + 1] ?? "")) return null;
let end = position + 2;
while (/[A-Za-z0-9_]/.test(sql[end] ?? "")) end++;
return sql[end] === "$" ? sql.slice(position, end + 1) : null;
};
// 折叠一段空白为单个空格(仅在 out 非空且不以空格结尾时追加)
const collapseWhitespace = () => {
let containsLineBreak = false;
while (i < len && isWhitespace(sql[i])) i++;
for (let j = i - 1; j >= 0 && isWhitespace(sql[j]); j--) {
if (sql[j] === "\n" || sql[j] === "\r") {
containsLineBreak = true;
break;
}
}
// PostgreSQL only concatenates adjacent string literals when their separating
// whitespace contains a newline, so flattening this case would make valid SQL invalid.
if (dialect === "postgres" && containsLineBreak && out.endsWith("'") && sql[i] === "'") {
out += "\n";
} else if (out && !out.endsWith(" ")) {
out += " ";
}
};
while (i < len) {
const ch = sql[i];
const next = sql[i + 1];
// 块注释 /* ... */ —— 需区分普通块注释、MySQL 可执行注释 /*! */、optimizer hint /*+ */
if (ch === "/" && next === "*") {
const third = sql[i + 2];
const isExecutableMysql = third === "!";
const isOptimizerHint = third === "+";
if (isExecutableMysql || isOptimizerHint) {
const contentStart = i + 3;
const end = sql.indexOf("*/", contentStart);
if (end < 0) {
// Keep malformed input malformed instead of silently turning it into executable SQL.
out += sql.slice(i);
break;
}
const content = sql.slice(contentStart, end);
const leadingSpace = /^\s/.test(content) ? " " : "";
const trailingSpace = /\s$/.test(content) ? " " : "";
const compressedContent = content.trim() ? compressSqlText(content, dialect) : "";
out += `/*${third}${leadingSpace}${compressedContent}${trailingSpace}*/`;
i = end + 2;
continue;
}
// 普通块注释 —— 移除
const commentStart = i;
i += 2;
let depth = 1;
while (i < len && depth > 0) {
if (supportsNestedBlockComments && sql[i] === "/" && sql[i + 1] === "*") {
depth++;
i += 2;
} else if (sql[i] === "*" && sql[i + 1] === "/") {
depth--;
i += 2;
} else {
i++;
}
}
if (depth > 0) {
// Removing an unterminated comment can expose a destructive statement that was invalid before.
out += sql.slice(commentStart);
break;
}
if (out && !out.endsWith(" ")) out += " ";
continue;
}
// MySQL additionally supports # comments and requires whitespace/control after --.
const startsDashComment = ch === "-" && next === "-" && (dialect !== "mysql" || isMysqlDashComment(sql[i + 2]));
if (startsDashComment || (dialect === "mysql" && ch === "#")) {
i += startsDashComment ? 2 : 1;
while (i < len && sql[i] !== "\n" && sql[i] !== "\r") i++;
continue;
}
// PostgreSQL dollar-quoted 字符串:$$...$$ 或 $tag$...$tag$
if (dialect === "postgres" && ch === "$") {
const tag = dollarQuoteTagAt(i);
if (tag) {
out += tag;
i += tag.length;
const end = sql.indexOf(tag, i);
if (end < 0) {
out += sql.slice(i);
break;
}
out += sql.slice(i, end + tag.length);
i = end + tag.length;
continue;
}
}
// 单引号字符串字面量(处理 '' 转义;MySQL 额外处理反斜杠转义)
if (ch === "'") {
out += "'";
i++;
const postgresEscapeString = dialect === "postgres" && (sql[i - 2] === "E" || sql[i - 2] === "e") && !isIdentifierPart(sql[i - 3]);
while (i < len) {
const c = sql[i];
// MySQL strings and PostgreSQL E'...' strings use backslash escapes.
if ((dialect === "mysql" || postgresEscapeString) && c === "\\" && i + 1 < len) {
out += c;
out += sql[i + 1];
i += 2;
continue;
}
out += c;
if (c === "'") {
if (sql[i + 1] === "'") {
out += sql[i + 1];
i += 2;
continue;
}
i++;
break;
}
i++;
}
continue;
}
// 双引号标识符(处理 "" 转义)
if (ch === '"') {
out += '"';
i++;
while (i < len) {
if (dialect === "mysql" && sql[i] === "\\" && i + 1 < len) {
out += sql[i];
out += sql[i + 1];
i += 2;
continue;
}
out += sql[i];
if (sql[i] === '"') {
if (sql[i + 1] === '"') {
out += sql[i + 1];
i += 2;
continue;
}
i++;
break;
}
i++;
}
continue;
}
// 反引号标识符(MySQL)
if (ch === "`") {
out += "`";
i++;
while (i < len && sql[i] !== "`") {
out += sql[i];
i++;
}
if (i < len) {
out += "`";
i++;
}
continue;
}
// SQL Server 方括号标识符 [...](]] 为转义 ])
if (dialect === "sqlserver" && ch === "[") {
out += "[";
i++;
while (i < len) {
const c = sql[i];
out += c;
if (c === "]") {
if (sql[i + 1] === "]") {
out += sql[i + 1];
i += 2;
continue;
}
i++;
break;
}
i++;
}
continue;
}
// 空白 —— 折叠为单个空格
if (isWhitespace(ch)) {
collapseWhitespace();
continue;
}
out += ch;
i++;
}
return out.trim();
}
/**
* Format SQL for *display* (object source, view/table DDL viewers).
*
* Unlike `formatSqlText`, this never throws: if the SQL can't be parsed by the
* formatter (vendor-specific DDL, oversized input, ...) the original text is
* returned unchanged so the viewer still shows the source. Use this for
* read-only/auto-format surfaces; use `formatSqlText` where a thrown error
* should surface to the user (e.g. the explicit "Format SQL" command).
*/
export async function formatSqlForDisplay(sql: string, dialect: SqlFormatDialect = "generic", settings: Partial<SqlFormatterSettings> = DEFAULT_SQL_FORMATTER_SETTINGS): Promise<string> {
if (!sql.trim()) return sql;
try {
return await formatSqlText(sql, dialect, settings);
} catch {
return sql;
}
}