Format SQL

format-sql

#!/usr/bin/env bash

SCRIPT_DIR=$( cd -- "$( dirname -- "${BASH_SOURCE[0]}" )" &> /dev/null && pwd )
npx tsx ${SCRIPT_DIR}/format-sql.ts

format-sql.ts

import * as readline from 'readline';

/**
 * Parse SQL parameter list into a Map
 * 
 * @param paramText - Text containing SQL parameters in format "$1 = 'value', $2 = 'value2'..."
 * @returns Map of parameter names to their values
 */
function parseParameters(paramText: string): Map<string, string> {
  const paramMap = new Map<string, string>();
  
  // Match all parameter definitions ($n = 'value')
  const paramRegex = /\$(\d+)\s*=\s*'([^']*)'/g;
  let match;
  
  while ((match = paramRegex.exec(paramText)) !== null) {
    const paramName = `$${match[1]}`;
    const paramValue = match[2];
    paramMap.set(paramName, paramValue);
  }
  
  return paramMap;
}

/**
 * Replace parameters in SQL query with their values
 * 
 * @param query - SQL query with parameterized values ($1, $2, etc.)
 * @param params - Map of parameter names to their values
 * @returns SQL query with parameters replaced by their values
 */
function replaceParameters(query: string, params: Map<string, string>): string {
  let resultQuery = query;
  
  // Sort parameters by length in descending order to avoid partial replacements
  // (e.g., $1 being replaced in $10)
  const sortedParams = [...params.entries()].sort((a, b) => b[0].length - a[0].length);
  
  for (const [param, value] of sortedParams) {
    // Create a regex that matches the parameter exactly (not as part of another parameter)
    const paramRegex = new RegExp(`\\${param}(?![0-9])`, 'g');
    resultQuery = resultQuery.replace(paramRegex, `'${value}'`);
  }
  
  return resultQuery;
}

/**
 * Process a SQL query by replacing its parameters with their values
 * 
 * @param queryText - SQL query with parameterized values
 * @param paramText - Text containing parameter definitions
 * @returns SQL query with parameters replaced by their values
 */
function processQuery(queryText: string, paramText: string): string {
  const params = parseParameters(paramText);
  return replaceParameters(queryText, params);
}

function main() {
  let queryText: string | null = null;
  let paramText: string | null = null;
  const inputLines: string[] = [];

  const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout,
    terminal: false
  });

  console.log("Paste SQL query and parameters (Press Enter when finished):");

  rl.on('line', (line) => {
    if (line.trim().toUpperCase() === 'END') {
      rl.close();
      return;
    }

    const trimmedLine = line.trim();
    if (trimmedLine.startsWith("Query Text:")) {
      queryText = trimmedLine.substring("Query Text:".length).trim();
    } else if (trimmedLine.startsWith("Query Parameters:")) {
      paramText = trimmedLine.substring("Query Parameters:".length).trim();
    }

    if (queryText && paramText) {
      rl.close();
    }

  });

  rl.on('close', () => {
    // Parse input to find query text and parameters
    // for (const line of inputLines) {
    //   const trimmedLine = line.trim();
    //   
    //   if (trimmedLine.startsWith("Query Text:")) {
    //     queryText = trimmedLine.substring("Query Text:".length).trim();
    //   } else if (trimmedLine.startsWith("Query Parameters:")) {
    //     paramText = trimmedLine.substring("Query Parameters:".length).trim();
    //   }
    // }

    // Process query if both query text and parameters were found
    if (queryText && paramText) {
      const params = parseParameters(paramText);
      const finalQuery = replaceParameters(queryText, params);
      
      console.log("\n--- Processed Query ---");
      console.log(finalQuery);
    } else {
      if (!queryText) {
        console.error("Error: No query text found. Make sure it's prefixed with 'Query Text: '");
      }
      if (!paramText) {
        console.error("Error: No parameters found. Make sure they're prefixed with 'Query Parameters: '");
      }
    }
  });
}

main();