@memberjunction/sql-dialect

Version: 5.2.0 Zero runtime dependencies

Overview

@memberjunction/sql-dialect is an abstract SQL dialect layer that enables database-agnostic SQL generation across MemberJunction. It encapsulates every platform-specific SQL syntax pattern -- identifier quoting, pagination, data types, DDL generation, full-text search, and more -- into a single, testable abstraction with zero database driver dependencies.

This package is used by CodeGen, data providers, and SQL converters throughout the MemberJunction monorepo. When code needs to emit SQL that works on both SQL Server and PostgreSQL, it programs against the SQLDialect abstract class and lets the concrete dialect handle platform differences.

Architecture

Class Hierarchy

SQLDialect (abstract base)
  |-- SQLServerDialect
  |-- PostgreSQLDialect
Copy

SQLDialect defines approximately 30 abstract methods spanning identifier quoting, pagination, literal expressions, INSERT/UPDATE return patterns, DDL generation, full-text search, data type mapping, and schema introspection. Each concrete dialect implements every method with platform-native SQL.

Key Interfaces

Key Methods

Identifier Quoting

Pagination

Literals and Expressions

INSERT/UPDATE Return Patterns

DDL Generation

Full-Text Search

String and JSON Functions

Parameters and Procedure Calls

CTE / Recursion

Permissions and Comments

Schema Introspection

Data Type Mapping

DataTypeMap

The DataTypeMap interface defines how data types are translated between database platforms:

interface MappedType {
    typeName: string;              // Target type name (e.g., "UUID", "BOOLEAN")
    supportsLength: boolean;       // Whether the type accepts a length parameter
    supportsPrecisionScale: boolean; // Whether the type accepts precision/scale
    defaultLength?: number;        // Default length when applicable
}

interface DataTypeMap {
    MapType(sourceType: string, sourceLength?: number,
            sourcePrecision?: number, sourceScale?: number): MappedType;

    MapTypeToString(sourceType: string, sourceLength?: number,
                    sourcePrecision?: number, sourceScale?: number): string;
}
Copy

SQLServerDataTypeMap is an identity mapper (SQL Server types map to themselves). PostgreSQLDataTypeMap maps SQL Server types to their PostgreSQL equivalents.

Type Mapping Reference (SQL Server to PostgreSQL)

PostgreSQL-native types (UUID, BOOLEAN, TIMESTAMPTZ, JSONB, BYTEA, SERIAL, BIGSERIAL, DOUBLE PRECISION) pass through unchanged.

Usage Examples

import {
    SQLDialect,
    SQLServerDialect,
    PostgreSQLDialect
} from '@memberjunction/sql-dialect';

// Create a dialect instance
const dialect: SQLDialect = new PostgreSQLDialect();

// Identifier quoting
dialect.QuoteIdentifier('UserName');        // "UserName"
dialect.QuoteSchema('__mj', 'User');        // __mj."User"

// Pagination
const { prefix, suffix } = dialect.LimitClause(10, 20);
// prefix: '', suffix: 'LIMIT 10 OFFSET 20'

// Boolean and timestamp literals
dialect.BooleanLiteral(true);               // 'true'
dialect.CurrentTimestampUTC();              // "(NOW() AT TIME ZONE 'UTC')"

// UUID generation
dialect.NewUUID();                          // 'gen_random_uuid()'

// Data type mapping
const mapped = dialect.MapDataType('UNIQUEIDENTIFIER');
// { typeName: 'UUID', supportsLength: false, supportsPrecisionScale: false }

dialect.MapDataTypeToString('NVARCHAR', 255);
// 'VARCHAR(255)'

dialect.MapDataTypeToString('NVARCHAR', -1);
// 'TEXT'

dialect.MapDataTypeToString('DECIMAL', undefined, 10, 2);
// 'NUMERIC(10,2)'

// INSERT return clause
dialect.ReturnInsertedClause();             // 'RETURNING *'
dialect.ReturnInsertedClause(['ID', 'Name']);
// 'RETURNING "ID", "Name"'

// Conditional expression
dialect.IIF('x > 0', "'positive'", "'non-positive'");
// "CASE WHEN x > 0 THEN 'positive' ELSE 'non-positive' END"

// Procedure call
dialect.ProcedureCallSyntax('__mj', 'spCreateUser', ['$1', '$2']);
// 'SELECT * FROM __mj."spCreateUser"($1, $2)'

// Trigger DDL
const triggerSQL = dialect.TriggerDDL({
    schema: '__mj',
    tableName: 'User',
    triggerName: 'trgUpdateUser',
    timing: 'BEFORE',
    events: ['UPDATE'],
    body: 'NEW.__mj_UpdatedAt = NOW();',
    functionName: 'fn_update_user_timestamp',
    forEach: 'ROW'
});
// Generates:
//   CREATE OR REPLACE FUNCTION __mj."fn_update_user_timestamp"()
//   RETURNS TRIGGER AS $$ BEGIN ... END; $$ LANGUAGE plpgsql;
//   DROP TRIGGER IF EXISTS ... ;
//   CREATE TRIGGER "trgUpdateUser" BEFORE UPDATE ON __mj."User"
//       FOR EACH ROW EXECUTE FUNCTION __mj."fn_update_user_timestamp"();

// Index DDL
const indexSQL = dialect.IndexDDL({
    schema: '__mj',
    tableName: 'User',
    indexName: 'idx_user_email',
    columns: ['Email'],
    unique: true,
    method: 'btree'
});
// 'CREATE UNIQUE INDEX IF NOT EXISTS "idx_user_email"
//      ON __mj."User" USING btree("Email")'
Copy

Polymorphic Usage

The key benefit is writing database-agnostic code that works with any dialect:

function buildSelectQuery(dialect: SQLDialect, schema: string, table: string,
                          columns: string[], limit: number): string {
    const { prefix, suffix } = dialect.LimitClause(limit);
    const qualifiedTable = dialect.QuoteSchema(schema, table);
    const quotedCols = columns.map(c => dialect.QuoteIdentifier(c)).join(', ');

    return `SELECT ${prefix} ${quotedCols} FROM ${qualifiedTable} ${suffix}`.trim();
}

// SQL Server output:
// SELECT TOP 10 [ID], [Name] FROM [__mj].[User]

// PostgreSQL output:
// SELECT "ID", "Name" FROM __mj."User" LIMIT 10
Copy

Adding a New Dialect

To add support for a new database platform (e.g., MySQL):

1. Create the type map class implementing DataTypeMap:

import { DataTypeMap, MappedType } from '@memberjunction/sql-dialect';

class MySQLDataTypeMap implements DataTypeMap {
    MapType(sourceType: string, sourceLength?: number,
            sourcePrecision?: number, sourceScale?: number): MappedType {
        const normalized = sourceType.toUpperCase().trim();
        switch (normalized) {
            case 'UNIQUEIDENTIFIER':
                return { typeName: 'CHAR', supportsLength: true,
                         supportsPrecisionScale: false, defaultLength: 36 };
            case 'BIT':
                return { typeName: 'TINYINT(1)', supportsLength: false,
                         supportsPrecisionScale: false };
            // ... map remaining types
            default:
                return { typeName: normalized, supportsLength: false,
                         supportsPrecisionScale: false };
        }
    }

    MapTypeToString(sourceType: string, sourceLength?: number,
                    sourcePrecision?: number, sourceScale?: number): string {
        const mapped = this.MapType(sourceType, sourceLength, sourcePrecision, sourceScale);
        // Format with length/precision as needed
        return mapped.typeName;
    }
}
Copy

2. Create the dialect class extending SQLDialect:

import { SQLDialect, DataTypeMap } from '@memberjunction/sql-dialect';

export class MySQLDialect extends SQLDialect {
    get PlatformKey(): DatabasePlatform { return 'mysql' as DatabasePlatform; }
    get TypeMap(): DataTypeMap { return new MySQLDataTypeMap(); }

    QuoteIdentifier(name: string): string { return `\`${name}\``; }
    QuoteSchema(schema: string, object: string): string {
        return `\`${schema}\`.\`${object}\``;
    }
    LimitClause(limit: number, offset?: number): LimitClauseResult {
        const suffix = offset != null
            ? `LIMIT ${limit} OFFSET ${offset}`
            : `LIMIT ${limit}`;
        return { prefix: '', suffix };
    }
    BooleanLiteral(value: boolean): string { return value ? '1' : '0'; }

    // Query rendering: the pipeline reads these instead of checking the platform name
    get SelectListPagingOrderBy(): string | null { return null; }
    get PagingRequiresOrderBy(): boolean { return false; }
    get SupportsEscapeStringLiterals(): boolean { return false; }
    get SupportsDollarQuotedStrings(): boolean { return false; }
    get QueryHintKeyword(): string | null { return null; }
    get CallerSQLForbiddenFunctions(): readonly string[] { return ['load_file']; }
    StringLiteralPrefix(_text: string): string { return ''; }
    EscapeLikePattern(text: string): string {
        return text.replace(/\\/g, '\\\\').replace(/%/g, '\\%').replace(/_/g, '\\_');
    }
    BooleanParameterValue(value: boolean): boolean | number { return value ? 1 : 0; }
    // ... implement all remaining abstract methods (~25+)
}
Copy

The query rendering members decide how row caps, paging, composed queries and the RunQuery template filters behave on the platform; see Query rendering members.

3. Update the DatabasePlatform type in sqlDialect.ts to include the new platform key.

4. Export from index.ts:

export { MySQLDialect } from './mysqlDialect.js';
Copy

5. Add tests in src/__tests__/mysqlDialect.test.ts covering every method. The existing crossDialect.test.ts provides a pattern for testing multiple dialects against the same assertions.

Side-by-Side Dialect Comparison

Query rendering members

The RunQuery rendering pipeline (SQLParser's lexer and paging-shape analysis, the paging and composition engines in @memberjunction/generic-database-provider, the template filters in @memberjunction/core and the parameter processor) reads these members rather than the platform name, so a new dialect gets correct rendering by declaring them:

Installation

npm install @memberjunction/sql-dialect
Copy

Or, in a MemberJunction workspace, add the dependency to your package's package.json and run npm install from the repo root.

License

ISC