SQL Formatter: Tokens, Highlighting and Indentation

1. Problem: whitespace splitting cannot highlight SQL

In SELECT 'a,b' -- note, the comma is inside a string and everything after -- is a comment. A split-based highlighter colors string content as keywords or colors comment words as SQL. It needs at least normal, quote, and comment states.

2. Minimal reproduction: keywords inside data are not SQL keywords

This input illustrates token boundaries. The keyword set is intentionally small and is not a complete dialect vocabulary.

const sql = "SELECT 'FROM users' AS note -- SELECT inside comment\nFROM users";
// Conceptual tokens:
// KEYWORD SELECT, STRING 'FROM users', WORD AS, WORD note, COMMENT -- ..., KEYWORD FROM, WORD users

3. Root cause: a lexer needs state, not replacement regexes

Strings may contain escapes or doubled quotes, comments may span lines, and identifiers may use dialect-specific quotes. The lexer must decide when to enter and leave each state; one mistake shifts highlighting and indentation for the rest of the line.

4. Recommendation: produce tokens, then consume them

Keep token kind, source text, and offsets. Highlighting maps tokens to CSS classes, while indentation uses delimiter and keyword state. Preserve unknown extensions as tokens so lexical recognition, visual styling, and formatting rules remain separate.

5. Complete code: a constrained lexer and highlight mapping

This lexer handles words, numbers, strings, line/block comments, and punctuation, preserving token ranges. It does not build an AST, verify table/column semantics, or cover every SQL dialect.

type TokenKind = "keyword" | "word" | "number" | "string" | "comment" | "punctuation";
type Token = { kind: TokenKind; text: string; start: number; end: number };
const keywords = new Set(["SELECT", "FROM", "WHERE", "JOIN", "AND", "OR", "AS"]);
function lexSql(source: string): Token[] {
  const tokens: Token[] = [];
  let i = 0;
  while (i < source.length) {
    const start = i, ch = source[i];
    if (/\s/.test(ch)) { i += 1; continue; }
    if (ch === "-" && source[i + 1] === "-") { i += 2; while (i < source.length && source[i] !== "\n") i += 1; tokens.push({ kind: "comment", text: source.slice(start, i), start, end: i }); continue; }
    if (ch === "/" && source[i + 1] === "*") { i += 2; while (i + 1 < source.length && !(source[i] === "*" && source[i + 1] === "/")) i += 1; i = Math.min(source.length, i + 2); tokens.push({ kind: "comment", text: source.slice(start, i), start, end: i }); continue; }
    if (ch === "'" || ch === "\"" || ch === String.fromCharCode(96)) { const quote = ch; i += 1; while (i < source.length) { if (source[i] === quote && source[i + 1] === quote) { i += 2; continue; } if (source[i] === quote) { i += 1; break; } i += 1; } tokens.push({ kind: "string", text: source.slice(start, i), start, end: i }); continue; }
    if (/\d/.test(ch)) { i += 1; while (i < source.length && /[\d.]/.test(source[i])) i += 1; tokens.push({ kind: "number", text: source.slice(start, i), start, end: i }); continue; }
    if (/[A-Za-z_]/.test(ch)) { i += 1; while (i < source.length && /[A-Za-z0-9_$]/.test(source[i])) i += 1; const text = source.slice(start, i); tokens.push({ kind: keywords.has(text.toUpperCase()) ? "keyword" : "word", text, start, end: i }); continue; }
    i += 1; tokens.push({ kind: "punctuation", text: ch, start, end: i });
  }
  return tokens;
}
function highlightClass(token: Token): string { return "sql-" + token.kind; }
console.log(lexSql("SELECT 'FROM users' -- note\nFROM users").map(highlightClass));

6. Incorrect approaches

Wrapping every uppercase word as a keyword damages strings and comments. Indenting only by delimiter count ignores comments and dialect constructs. A successful lexer does not prove SQL validity; it cannot detect column, type, or semantic errors.

7. Boundaries: escapes, backticks, and indentation state

Dialects differ on backslashes, doubled quotes, backtick identifiers, and dollar-quoted strings. An indenter must also decide how parentheses, CASE, JOIN, and subqueries change levels. Unknown tokens should remain intact and not corrupt later offsets.

8. Verification

Test keywords inside strings, single/double/backtick quotes, line/block comments, numbers, decimals, nested delimiters, and multiline queries. Assert that concatenated token text equals the input, strings/comments are not keywords, and offsets increase monotonically. Verify indentation separately.

9. FAQ

Can a lexer replace a SQL parser? No; it provides lexical tokens, not a complete AST or semantic validation.

Why can highlighting look right while formatting is wrong? Highlighting and indentation may have different state rules; an omitted dialect construct shifts formatting.

Should an unknown function be highlighted as a keyword? Do not guess; keep a word or extension token and let dialect configuration decide.

10. Summary

SQL highlighting and basic formatting depend on lexer state, token text, and offsets. Strings, comments, quotes, and delimiters affect indentation. A lexer/highlighter is not a complete SQL parser; verify token fidelity and delegate semantic checks to a real parser or database.