Efficient SQL Formatting in JavaScript Without a Heavy Parser

SQL formatting in JavaScript has traditionally been seen as a complex task that demands a full parser. However, a recent approach simplifies this by focusing on tokenization using keywords and a set of straightforward formatting rules. This method effectively handles about 90% of real-world SQL formatting needs without the overhead of maintaining a full Abstract Syntax Tree (AST) parser.

The Core Idea

The core idea behind this approach is to focus on the essential aspects of SQL formatting: uppercasing reserved words, adding newlines before clauses, and indenting sub-clauses. Rather than building a full parser, which can be overkill for the task, the method uses a keyword-based tokenization process and applies formatting rules to achieve the desired output.

Tokenization

The process begins with tokenization, where the SQL string is split into manageable parts based on a predefined list of SQL keywords. Here's a look at how the tokenization function works:

const KEYWORDS = [
  'SELECT', 'FROM', 'WHERE', 'JOIN', 'LEFT JOIN', 'RIGHT JOIN',
  'INNER JOIN', 'ON', 'GROUP BY', 'ORDER BY', 'HAVING', 'LIMIT',
  'INSERT INTO', 'VALUES', 'UPDATE', 'SET', 'DELETE FROM',
  'CREATE TABLE', 'DROP TABLE', 'ALTER TABLE', 'WITH', 'UNION'
];

function tokenize(sql) {
  // Sort by length descending to match longer keywords first
  const sorted = [...KEYWORDS].sort((a, b) => b.length - a.length);
  const pattern = sorted.map(k => k.replace(/ /g, '\s+')).join('|');
  const regex = new RegExp(`(${pattern})`, 'gi');
  return sql.split(regex).filter(t => t.trim());
}

This function sorts keywords by length to prioritize longer matches, constructs a regular expression pattern, and splits the SQL string into tokens. This ensures that phrases like "LEFT JOIN" are matched before individual words like "JOIN", effectively capturing the intended SQL constructs without ambiguity. The regex is case-insensitive, enabling it to match keywords regardless of their case in the input SQL.

Formatting Rules

Once tokenized, the formatting rules are applied. The key rules include adding newlines before certain clauses and indenting logical operators. Here’s the formatting function:

const NEWLINE_BEFORE = new Set([
  'SELECT', 'FROM', 'WHERE', 'GROUP BY', 'ORDER BY',
  'HAVING', 'LIMIT', 'UNION'
]);
const INDENT_BEFORE = new Set(['AND', 'OR', 'ON']);

function format(sql) {
  const tokens = tokenize(sql);
  let result = '';
  let indent = 0;
  for (const token of tokens) {
    const upper = token.trim().toUpperCase();
    if (NEWLINE_BEFORE.has(upper)) {
      result += `\n${upper}`;
    } else if (INDENT_BEFORE.has(upper)) {
      result += `\n  ${upper}`;
    } else if (upper === 'JOIN' || upper.endsWith(' JOIN')) {
      result += `\n${upper}`;
    } else {
      result += ` ${token.trim()}`;
    }
  }
  return result.trim();
}

This function ensures that key SQL clauses start on a new line, improving readability. Indentation is applied to logical operators, which often appear in complex WHERE clauses, making the SQL easier to follow. The use of sets for NEWLINE_BEFORE and INDENT_BEFORE allows for efficient lookups, ensuring the formatting process remains performant even with large SQL statements.

Handling Strings and Comments

A critical aspect of SQL formatting is ensuring that keywords within string literals or comments are not mistakenly uppercased. The solution involves replacing these with placeholders during formatting and restoring them afterwards.

function stripStrings(sql) {
  const placeholders = [];
  const stripped = sql.replace(/'([^']*)'/g, (match) => {
    const idx = placeholders.length;
    placeholders.push(match);
    return `__STR${idx}__`;
  });
  return { stripped, placeholders };
}

function restoreStrings(sql, placeholders) {
  return sql.replace(/__STR(\d+)__/g, (_, i) => placeholders[i]);
}

This two-step process ensures that string literals are preserved during the formatting. By temporarily replacing strings with unique placeholders, the formatting logic can safely apply transformations without altering the contents of the strings. After formatting, the placeholders are replaced with the original strings, ensuring the SQL remains semantically correct.

Minification

For those looking to minify SQL rather than format it, the reverse operation is straightforward: remove all whitespace to produce a single line of SQL code. Here's how:

function minify(sql) {
  return sql.replace(/\s+/g, ' ').trim();
}

This minification function collapses all whitespace, making the SQL compact and suitable for environments where space is at a premium. This is particularly useful in scenarios where SQL is transmitted over networks or stored in environments with strict space constraints.

Implementing in Production

For developers maintaining production codebases, implementing this simplified SQL formatter can significantly streamline the process of ensuring SQL readability and maintenance. The approach offers a lightweight alternative to more resource-intensive parsing methods.

• Integrate the tokenization and formatting functions into your JavaScript project where SQL formatting is required.
• Use the stripStrings and restoreStrings functions to handle SQL strings and comments accurately.
• Consider utilizing the minification function if your application benefits from reduced SQL size, such as in network transmission or storage scenarios.

For a more detailed exploration of JavaScript applications, you might find our SvelteKit 3 Release Candidate post insightful, which focuses on JavaScript framework advancements.

Limitations and Considerations

While this approach addresses most SQL formatting needs, it is not without its limitations. It’s crucial to understand where this method might fall short:

• Complex SQL Constructs: This method may not handle intricate SQL constructs or vendor-specific extensions effectively.
• Keyword Limitations: The approach relies heavily on the predefined list of keywords, which might not cover every case in diverse SQL dialects.
• Non-Standard SQL: If your SQL contains non-standard elements, adjustments to the tokenization or formatting rules may be necessary.

Given these limitations, this method is best suited for environments where standard SQL is used and readability is prioritized over exhaustive parsing capabilities. For more advanced SQL parsing, consider exploring full AST parsers or other tooling options.

Moreover, while the method handles most common cases, developers should be aware of the potential need for additional keyword updates or custom rules as SQL dialects evolve. In my experience, it can be beneficial to periodically review the keyword set and adjust the regex patterns to accommodate any new SQL syntax or features that may arise.

In summary, the keyword-based tokenization and rule application for SQL formatting in JavaScript offers an elegant and efficient solution for most practical needs. By focusing on the essentials of SQL readability, developers can maintain clean and manageable code without the complexity of full parsing systems. For those working on JavaScript-heavy environments, integrating this approach can enhance productivity and ensure better code maintainability.

For more insights on JavaScript and its applications, consider checking out our Next.js and Edge Middleware article, which delves into innovative uses of JavaScript in web development.