SQL Parameter Reference Handler

A utility module for extracting and validating parameter references from SQL queries in a BI (Business Intelligence) system. The system supports parameterized SQL queries where parameters can be referenced using special syntax, and needs robust extraction and validation to ensure queries are properly constructed before execution.

Background

In a BI platform, users write SQL queries with dynamic parameter references. These parameters can be:

• Project-level parameters: Shared across an entire project (e.g., region, date_range)
• Model-level parameters: Scoped to specific data models (e.g., customers.status, orders.priority)

Parameters are referenced in SQL using the syntax: ${lightdash.parameters.param_name} or ${ld.parameters.param_name} (shorthand).

For model-level parameters, the syntax includes a model prefix: ${ld.parameters.model_name.param_name}.

Requirements

Parameter Reference Extraction

Implement a function getParameterReferences(sql: string): string[] that:

• Extracts all parameter references from a SQL string
• Returns unique parameter names (without the ${lightdash.parameters. or ${ld.parameters. wrapper)
• Handles both full format (lightdash.parameters) and shorthand (ld.parameters)
• Supports project-level parameters (e.g., status from ${ld.parameters.status})
• Supports model-level parameters (e.g., customers.status from ${ld.parameters.customers.status})
• Returns an empty array if no parameters are found
• Deduplicates parameter names if the same parameter appears multiple times
• Only matches parameters with valid names (alphanumeric and underscores, optionally one dot for model-level)

Parameter Name Validation

Implement a function validateParameterNames(parameters: Record<string, ParameterConfig> | undefined): ValidationResult that:

• Validates that parameter names follow the allowed pattern: alphanumeric characters, underscores, and hyphens only
• Returns a result object with:
  • isInvalid: boolean - true if any parameter has an invalid name
  • invalidParameters: string[] - array of parameter names that don't match the pattern
• Handles undefined or empty parameter objects gracefully
• Rejects names containing dots, spaces, and special characters (except underscore and hyphen)

Types

export type ParameterConfig = {
    label: string;
    default?: string;
    description?: string;
};

export type ValidationResult = {
    isInvalid: boolean;
    invalidParameters: string[];
};

export function getParameterReferences(sql: string): string[];

export function validateParameterNames(
    parameters: Record<string, ParameterConfig> | undefined
): ValidationResult;

Test Cases

Parameter Extraction Tests

• Extracts a single project-level parameter with lightdash.parameters format @test
• Extracts a single project-level parameter with ld.parameters shorthand @test
• Extracts multiple different parameters from a query @test
• Returns unique parameter names when duplicates exist in the SQL @test
• Extracts model-level parameters with dot notation (e.g., customers.status) @test
• Handles mixed project-level and model-level parameters in the same query @test
• Returns empty array when no parameters are present @test
• Ignores malformed parameter syntax (missing closing brace, invalid characters) @test

Parameter Name Validation Tests

• Accepts valid parameter names (alphanumeric, underscores, hyphens) @test
• Rejects parameter names with dots, spaces, and special characters @test
• Handles undefined or empty parameter objects without errors @test
• Correctly identifies both valid and invalid parameters in a mixed set @test

Implementation

@generates

Dependencies { .dependencies }

@lightdash/common { .dependency }

Provides TypeScript types and utilities for the Lightdash BI platform.