SQL Formatter

Standalone, zero-dependency JavaScript implementation powering this tool. Free to inspect, copy, and build upon.

const SQL_KEYWORDS = [
    'SELECT','FROM','WHERE','JOIN','INNER JOIN','LEFT JOIN','RIGHT JOIN','FULL JOIN',
    'FULL OUTER JOIN','LEFT OUTER JOIN','RIGHT OUTER JOIN','CROSS JOIN','ON','AS',
    'AND','OR','NOT','IN','EXISTS','BETWEEN','LIKE','IS NULL','IS NOT NULL',
    'ORDER BY','GROUP BY','HAVING','LIMIT','OFFSET','UNION','UNION ALL','INTERSECT',
    'EXCEPT','INSERT INTO','VALUES','UPDATE','SET','DELETE FROM','DELETE',
    'CREATE TABLE','CREATE INDEX','CREATE VIEW','CREATE DATABASE',
    'ALTER TABLE','ADD COLUMN','DROP COLUMN','DROP TABLE','DROP DATABASE',
    'PRIMARY KEY','FOREIGN KEY','REFERENCES','UNIQUE','NOT NULL','DEFAULT',
    'CHECK','INDEX','CONSTRAINT','AUTO_INCREMENT','AUTOINCREMENT',
    'CASE','WHEN','THEN','ELSE','END','IF','DISTINCT','ALL','TOP','INTO',
    'WITH','RECURSIVE','RETURNING','BEGIN','COMMIT','ROLLBACK','TRANSACTION',
    'GRANT','REVOKE','TRUNCATE','EXPLAIN','ANALYZE','PRAGMA',
    'COUNT','SUM','AVG','MIN','MAX','COALESCE','NULLIF','CAST','CONVERT',
    'NOW','DATE','YEAR','MONTH','DAY','TRIM','LOWER','UPPER','LENGTH',
    'SUBSTRING','REPLACE','CONCAT','IFNULL','IIF','NVL','ROUND','ABS',
    'NULL','TRUE','FALSE','ASC','DESC','BY','TABLE','VIEW','DATABASE',
    'COLUMN','COLUMNS','ROW','ROWS','INT','INTEGER','VARCHAR','TEXT',
    'CHAR','BOOLEAN','BOOL','FLOAT','DOUBLE','DECIMAL','NUMERIC',
    'DATETIME','TIMESTAMP','BIGINT','SMALLINT','TINYINT','BLOB','JSON'
  ].sort((a, b) => b.length - a.length);

  const CLAUSE_KEYWORDS = [
    'SELECT','FROM','WHERE','ORDER BY','GROUP BY','HAVING','LIMIT','OFFSET',
    'UNION ALL','UNION','INTERSECT','EXCEPT',
    'INNER JOIN','LEFT OUTER JOIN','RIGHT OUTER JOIN','FULL OUTER JOIN',
    'FULL JOIN','LEFT JOIN','RIGHT JOIN','CROSS JOIN','JOIN',
    'ON','SET','VALUES','INSERT INTO','UPDATE','DELETE FROM','DELETE',
    'CREATE TABLE','CREATE INDEX','CREATE VIEW','ALTER TABLE',
    'DROP TABLE','WITH','RETURNING'
  ].sort((a, b) => b.length - a.length);

  function splitStatements(sql) {
    const stmts = [];
    let cur = '', inStr = false, strChar = '';
    for (let i = 0; i < sql.length; i++) {
      const c = sql[i];
      if (inStr) {
        cur += c;
        if (c === strChar && sql[i-1] !== '\\') inStr = false;
      } else if (c === "'" || c === '"' || c === '`') {
        inStr = true; strChar = c; cur += c;
      } else if (c === ';') {
        cur += ';';
        if (cur.trim()) stmts.push(cur.trim());
        cur = '';
      } else {
        cur += c;
      }
    }
    if (cur.trim()) stmts.push(cur.trim());
    return stmts.length ? stmts : [sql];
  }

  function formatStatement(sql) {
    if (!sql) return '';
    sql = sql.replace(/\s+/g, ' ').trim();
    for (const kw of SQL_KEYWORDS) {
      const re = new RegExp('(?<![\\w$])(' + kw.replace(/ /g, '\\s+') + ')(?![\\w$])', 'gi');
      sql = sql.replace(re, kw);
    }
    let result = '', i = 0, parenDepth = 0;
    const len = sql.length;

    function tryMatchClause() {
      if (parenDepth !== 0) return null;
      for (const kw of CLAUSE_KEYWORDS) {
        const re = new RegExp('^' + kw.replace(/ /g, '\\s+') + '(?![\\w$])', 'i');
        const m = sql.slice(i).match(re);
        if (m) return { kw, consumed: m[0].length };
      }
      return null;
    }

    while (i < len) {
      if (/\s/.test(sql[i])) { i++; continue; }
      const clause = tryMatchClause();
      if (clause) {
        if (result.length > 0) result += '\n';
        result += clause.kw;
        i += clause.consumed;
        if (i < len && !/[(\s;]/.test(sql[i])) result += ' ';
        continue;
      }
      if (sql[i] === "'" || sql[i] === '"' || sql[i] === '`') {
        const q = sql[i]; let j = i + 1;
        while (j < len) { if (sql[j] === q && sql[j-1] !== '\\') break; j++; }
        j++;
        if (result.length > 0 && !result.endsWith(' ') && !result.endsWith('(') && !result.endsWith('\n')) result += ' ';
        result += sql.slice(i, j); i = j; continue;
      }
      if (sql[i] === ',') {
        result += ',';
        result += parenDepth === 0 ? '\n    ' : ' ';
        i++; continue;
      }
      if (sql[i] === '(') { result += '('; parenDepth++; i++; continue; }
      if (sql[i] === ')') { result += ')'; parenDepth--; i++; continue; }
      if (sql[i] === ';') { result += ';'; i++; continue; }
      let j = i;
      while (j < len && !/[\s,();`'"]/.test(sql[j])) j++;
      if (j === i) { result += sql[i]; i++; continue; }
      const word = sql.slice(i, j);
      if (result.length > 0 && !result.endsWith(' ') && !result.endsWith('(') && !result.endsWith('\n')) result += ' ';
      result += word; i = j;
    }
    return result.trim();
  }

  function formatSQL() {
    const input = document.getElementById('sqlInput').value.trim();
    if (!input) return;
    const statements = splitStatements(input);
    const formatted = statements.map(s => formatStatement(s)).filter(Boolean).join('\n\n');
    document.getElementById('sqlOutput').innerHTML = highlightSQL(formatted);
    updateStats(input, formatted);
  }

  function minifySQL() {
    const input = document.getElementById('sqlInput').value.trim();
    if (!input) return;
    const sql = input.replace(/--[^\n]*/g,'').replace(/\/\*[\s\S]*?\*\//g,'').replace(/\s+/g,' ').trim();
    document.getElementById('sqlOutput').innerHTML = highlightSQL(sql);
    updateStats(input, sql);
  }

  function highlightSQL(sql) {
    const esc = s => s.replace(/&/g,'&amp;').replace(/</g,'&lt;').replace(/>/g,'&gt;');
    let result = '', i = 0;
    const len = sql.length;
    while (i < len) {
      if (sql[i]==='-'&&sql[i+1]==='-') {
        let j=i; while(j<len&&sql[j]!=='\n')j++;
        result+='<span class="sc">'+esc(sql.slice(i,j))+'</span>'; i=j; continue;
      }
      if (sql[i]==='/'&&sql[i+1]==='*') {
        let j=i+2; while(j<len&&!(sql[j]==='*'&&sql[j+1]==='/'))j++; j+=2;
        result+='<span class="sc">'+esc(sql.slice(i,j))+'</span>'; i=j; continue;
      }
      if (sql[i]==="'"||sql[i]==='"'||sql[i]==='`') {
        const q=sql[i]; let j=i+1;
        while(j<len){if(sql[j]===q&&sql[j-1]!=='\\')break;j++;}j++;
        result+='<span class="ss">'+esc(sql.slice(i,j))+'</span>'; i=j; continue;
      }
      if (/\d/.test(sql[i])) {
        let j=i; while(j<len&&/[\d.]/.test(sql[j]))j++;
        result+='<span class="sn">'+esc(sql.slice(i,j))+'</span>'; i=j; continue;
      }
      if (/[\w$]/.test(sql[i])) {
        let j=i; while(j<len&&/[\w$]/.test(sql[j]))j++;
        const word=sql.slice(i,j), upper=word.toUpperCase();
        if (SQL_KEYWORDS.includes(upper)) result+='<span class="sk">'+esc(word)+'</span>';
        else if (j<len&&sql[j]==='(') result+='<span class="sf">'+esc(word)+'</span>';
        else result+=esc(word);
        i=j; continue;
      }
      if (/[(),;=<>!+\-*\/|&~^%]/.test(sql[i])) {
        result+='<span class="sp">'+esc(sql[i])+'</span>'; i++; continue;
      }
      result+=esc(sql[i]); i++;
    }
    return result;
  }

  function updateStats(original, output) {
    document.getElementById('statOriginal').textContent = original ? original.length.toLocaleString() : '---';
    document.getElementById('statOutput').textContent = output !== null ? output.length.toLocaleString() : '---';
    const stmts = splitStatements(original || '').length;
    document.getElementById('statStatements').textContent = stmts || '---';
  }

  function copyOutput(btn) {
    const text = document.getElementById('sqlOutput').textContent;
    if (!text) return;
    navigator.clipboard.writeText(text).catch(() => {});
    btn.textContent = 'copied!'; btn.classList.add('copied');
    setTimeout(() => { btn.textContent = 'copy'; btn.classList.remove('copied'); }, 2000);
  }

  function clearAll() {
    document.getElementById('sqlInput').value = '';
    document.getElementById('sqlOutput').innerHTML = '';
    updateStats(null, null);
  }

  document.getElementById('sqlInput').addEventListener('keydown', function(e) {
    if ((e.ctrlKey||e.metaKey)&&e.key==='Enter') { e.preventDefault(); formatSQL(); }
    if (e.key==='Tab') {
      e.preventDefault();
      const s=this.selectionStart,end=this.selectionEnd;
      this.value=this.value.substring(0,s)+'  '+this.value.substring(end);
      this.selectionStart=this.selectionEnd=s+2;
    }
  });

  document.getElementById('sqlInput').value = "select u.id, u.name, u.email, count(o.id) as order_count, sum(o.total) as total_spent from users u left join orders o on u.id = o.user_id where u.created_at >= '2024-01-01' and u.status = 'active' group by u.id, u.name, u.email having count(o.id) > 0 order by total_spent desc limit 50;";
  formatSQL();