Multi-Warehouse SQL Query Formatter

A utility library that formats SQL queries for different data warehouse platforms, handling platform-specific quoting conventions, string escaping, and date/time functions.

Capabilities

Field Name Quoting

• Given a field name "user_id" and warehouse type "bigquery", returns "user_id" @test
• Given a field name "customer_name" and warehouse type "postgres", returns ""customer_name"" @test
• Given a field name "order_date" and warehouse type "snowflake", returns ""order_date"" @test

String Literal Escaping

• Given the string "O'Brien" and warehouse type "postgres", returns "'O''Brien'" with properly escaped single quote @test
• Given the string "Test\nNewline" and warehouse type "bigquery", returns a properly escaped string literal @test

Date Truncation SQL Generation

• Given warehouse type "bigquery" and truncation to "day", returns SQL using "DATE_TRUNC" function @test
• Given warehouse type "postgres" and truncation to "month", returns SQL using "DATE_TRUNC" function with appropriate syntax @test
• Given warehouse type "snowflake" and truncation to "week", returns SQL using "DATE_TRUNC" function with Snowflake-specific syntax @test

String Concatenation SQL

• Given warehouse type "postgres" and array of field names ["first_name", "last_name"], returns SQL using "||" operator @test
• Given warehouse type "bigquery" and array of field names ["street", "city", "zip"], returns SQL using "CONCAT" function @test

Implementation

@generates

API

/**
 * Supported warehouse types
 */
export type WarehouseType =
  | 'bigquery'
  | 'postgres'
  | 'snowflake'
  | 'redshift'
  | 'databricks'
  | 'trino'
  | 'clickhouse';

/**
 * Time units for date truncation
 */
export type TimeUnit = 'day' | 'week' | 'month' | 'year';

/**
 * Quotes a field name according to warehouse-specific conventions
 *
 * @param warehouseType - The type of warehouse
 * @param fieldName - The field name to quote
 * @returns The quoted field name
 */
export function quoteFieldName(warehouseType: WarehouseType, fieldName: string): string;

/**
 * Escapes and quotes a string literal for use in SQL queries
 *
 * @param warehouseType - The type of warehouse
 * @param value - The string value to escape
 * @returns The escaped and quoted string literal
 */
export function escapeStringLiteral(warehouseType: WarehouseType, value: string): string;

/**
 * Generates SQL for truncating a date/timestamp to a specific time unit
 *
 * @param warehouseType - The type of warehouse
 * @param fieldExpression - The field or expression containing the date/timestamp
 * @param unit - The time unit to truncate to
 * @returns SQL expression for date truncation
 */
export function generateDateTruncSQL(
  warehouseType: WarehouseType,
  fieldExpression: string,
  unit: TimeUnit
): string;

/**
 * Generates SQL for concatenating multiple string fields
 *
 * @param warehouseType - The type of warehouse
 * @param fieldNames - Array of field names to concatenate
 * @returns SQL expression for string concatenation
 */
export function generateConcatSQL(warehouseType: WarehouseType, fieldNames: string[]): string;

Dependencies { .dependencies }

@lightdash/common { .dependency }

Provides warehouse abstraction utilities and type definitions for working with multiple data warehouse platforms.