tessl/npm-sql-formatter

Format whitespace in a SQL query to make it more readable with support for multiple SQL dialects

—

Pending

Overview

Eval results

Files

Parameter Replacement

Name: tessl/npm-sql-formatter
Author: tessl

Parameter placeholder replacement for prepared statements with support for positional, named, and custom parameter types.

Capabilities

Parameter Items

Key-value mapping for named parameter replacement.

type ParamItems = { [k: string]: string };

Usage Examples:

import { format } from "sql-formatter";

// Named parameters with object mapping
const result = format(
  "SELECT * FROM users WHERE id = :userId AND role = :userRole",
  {
    params: {
      userId: "123",
      userRole: "admin"
    }
  }
);

Parameter Types Configuration

Configuration for different types of parameter placeholders.

interface ParamTypes {
  /** Enable positional "?" parameter placeholders */
  positional?: boolean;
  /** Prefixes for numbered parameter placeholders to support, e.g. :1, :2, :3 */
  numbered?: ("?" | ":" | "$")[];
  /** Prefixes for named parameter placeholders to support, e.g. :name */
  named?: (":" | "@" | "$")[];
  /** Prefixes for quoted parameter placeholders to support, e.g. :"name" */
  quoted?: (":" | "@" | "$")[];
  /** Custom parameter type definitions */
  custom?: CustomParameter[];
}

interface CustomParameter {
  /** Regex pattern for matching the parameter */
  regex: string;
  /** Takes the matched parameter string and returns the name of the parameter */
  key?: (text: string) => string;
}

Positional Parameters

Simple positional parameter replacement using ? placeholders.

Usage Examples:

import { format } from "sql-formatter";

// Array of values for positional parameters
const result = format(
  "SELECT * FROM users WHERE id = ? AND status = ? AND created_at > ?",
  {
    params: ["123", "active", "2023-01-01"]
  }
);

// Result:
// SELECT
//   *
// FROM
//   users
// WHERE
//   id = '123'
//   AND status = 'active'
//   AND created_at > '2023-01-01'

Named Parameters

Named parameter replacement with various prefix styles.

Usage Examples:

import { format } from "sql-formatter";

// Colon-prefixed named parameters (:name)
const result = format(
  "SELECT * FROM users WHERE department = :dept AND salary > :minSalary",
  {
    params: {
      dept: "Engineering", 
      minSalary: "50000"
    }
  }
);

// At-sign prefixed parameters (@name) - SQL Server style
const result = format(
  "SELECT * FROM users WHERE id = @userId",
  {
    params: { userId: "456" }
  }
);

// Dollar-prefixed parameters ($name) - PostgreSQL style  
const result = format(
  "SELECT * FROM users WHERE id = $1 AND role = $2",
  {
    language: "postgresql",
    params: ["789", "admin"]
  }
);

Numbered Parameters

Numbered parameter placeholders with prefixes.

Usage Examples:

import { format } from "sql-formatter";

// Colon-prefixed numbered parameters (:1, :2, :3)
const result = format(
  "SELECT * FROM users WHERE id = :1 AND role = :2",
  {
    paramTypes: { numbered: [":"] },
    params: ["123", "admin"]
  }
);

Quoted Parameters

Quoted parameter placeholders that use identifier quote types.

Usage Examples:

import { format } from "sql-formatter";

// Quoted named parameters with colons (:"parameter_name")
const result = format(
  'SELECT * FROM users WHERE id = :"user_id"',
  {
    paramTypes: { quoted: [":"] },
    params: { user_id: "123" }
  }
);

Custom Parameters

Define custom parameter patterns with regex matching.

interface CustomParameter {
  /** Regex pattern for matching the parameter */
  regex: string;
  /** Function to extract parameter name from matched text */
  key?: (text: string) => string;
}

Usage Examples:

import { format } from "sql-formatter";

// Custom curly brace parameters {paramName}
const result = format(
  "SELECT * FROM users WHERE id = {userId} AND role = {userRole}",
  {
    paramTypes: {
      custom: [{
        regex: "\\{\\w+\\}",
        key: (text) => text.slice(1, -1) // Remove { and }
      }]
    },
    params: {
      userId: "123",
      userRole: "admin"
    }
  }
);

// Custom hash parameters #{param}
const result = format(
  "SELECT * FROM users WHERE status IN (#{activeStatuses})",
  {
    paramTypes: {
      custom: [{
        regex: "#\\{[^}]+\\}",
        key: (text) => text.slice(2, -1) // Remove #{ and }
      }]
    },
    params: {
      activeStatuses: "'active', 'verified'"
    }
  }
);

Parameter Handling Class

Internal parameter replacement implementation.

class Params {
  constructor(params: ParamItems | string[] | undefined);
  
  /** Returns param value that matches given placeholder with param key */
  get(options: { key?: string; text: string }): string;
  
  /** Returns index of current positional parameter */
  getPositionalParameterIndex(): number;
  
  /** Sets index of current positional parameter */
  setPositionalParameterIndex(i: number): void;
}

This class is used internally by the formatter but is exported for advanced use cases.

Parameter Validation

The parameter system validates parameter configurations and values.

Common Validation Rules:

All parameter values should be strings
Custom parameter regexes cannot be empty
Parameter types must specify valid prefixes

Usage Examples:

import { format } from "sql-formatter";

// Invalid parameter configuration will show warning
const result = format("SELECT * FROM users WHERE id = ?", {
  params: [123] // Warning: should be string
});

// Invalid custom parameter regex will throw ConfigError
const result = format("SELECT * FROM users WHERE id = {userId}", {
  paramTypes: {
    custom: [{
      regex: "" // Error: empty regex
    }]
  }
});

Disable Formatting for Parameters

You can disable formatting for sections containing problematic parameters:

/* sql-formatter-disable */
SELECT * FROM users WHERE complex_param = {some_complex_param};
/* sql-formatter-enable */
SELECT * FROM products WHERE id = ?;

Install with Tessl CLI