@memberjunction/sql-dialect

No database driver dependencies. The only runtime dependency is @memberjunction/global, whose class factory lets other packages register their own dialects.

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 no database driver dependencies.

This package is used by CodeGen, data providers, the SQL parser, the RunQuery pipeline 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.

It ships six dialects:

• Platform dialects for the databases MJ itself runs on: SQLServerDialect ('sqlserver') and PostgreSQLDialect ('postgresql'). These are full SQLDialects, with DDL.
• Query-only dialects for External Data Sources, databases MJ reads but does not run on: MySQLQueryDialect ('mysql'), OracleQueryDialect ('oracle'), SnowflakeQueryDialect ('snowflake') and DatabricksQueryDialect ('databricks'). See Query-only dialects.

Architecture

Class Hierarchy

QueryDialect (abstract: Key, DisplayName, Lexical, Grammar, Writer, Types, Binding)
  |-- SQLDialect (abstract: adds PlatformKey, DDL, FormatterLanguage and the method surface below)
  |     |-- SQLServerDialect
  |     |-- PostgreSQLDialect
  |-- AnsiQueryDialect (standard SQL facets)
        |-- MySQLQueryDialect
        |-- OracleQueryDialect
        |-- SnowflakeQueryDialect
        |-- DatabricksQueryDialect
Copy

A dialect is split into facets: small objects that each hold one kind of fact about the database. QueryDialect is the set of facets needed to read, screen and write queries. SQLDialect adds the DDL facet MJ needs to create and run its own schema. Every method listed under Key Methods is a member of SQLDialect that delegates to one of its facets; those methods are kept for existing callers, and new code that only needs one kind of fact can read the facet directly.

Facets

The facets of the shipped dialects are exported, so a new dialect can start from one and change only what differs: ANSI_LEXICAL, ANSI_GRAMMAR, SQL_SERVER_LEXICAL, SQL_SERVER_GRAMMAR, SQL_SERVER_TYPES, POSTGRESQL_LEXICAL, POSTGRESQL_GRAMMAR, POSTGRESQL_TYPES, MYSQL_LEXICAL, MYSQL_GRAMMAR, ORACLE_LEXICAL, ORACLE_GRAMMAR, SNOWFLAKE_LEXICAL, SNOWFLAKE_GRAMMAR, DATABRICKS_LEXICAL and DATABRICKS_GRAMMAR, plus the writer classes (AnsiWriter, SQLServerWriter, PostgreSQLWriter, MySQLWriter, OracleWriter, SnowflakeWriter, DatabricksWriter), the binding classes (SQLServerBinding, PostgreSQLBinding) and the DDL classes (BaseSchemaDDL, SQLServerSchemaDDL, PostgreSQLSchemaDDL).

ParserDialect and the deprecated SQLParserDialect

ParserDialect is Pick<QueryDialect, 'Key' | 'Lexical' | 'Grammar'>: the part of a dialect the SQL tokenizer and parser in @memberjunction/sql-parser read. Any QueryDialect satisfies it.

SQLParserDialect is the older, flat interface the parser used to read (ParserDialect as a string, QuoteIdentifier, AllowsOrderByInCTE, SupportsEscapeStringLiterals, QueryHintKeyword, and so on). It is deprecated in favour of ParserDialect or QueryDialect. SQLDialect still implements it, so code that passes a SQLDialect where a SQLParserDialect is expected keeps working; it is kept until 7.0.

Key Interfaces

Resolving a dialect

Callers get a dialect from a key instead of constructing one, so no package keeps its own switch over platform names.

How a key resolves:

1. Keys match exactly, case included. 'MySQL' is an unknown key; there are no aliases ('mssql', 'postgres' and 'pg' are not recognized).
2. Built-in keys resolve directly. The six shipped dialects are constructed by the factory, not looked up in the class factory, so import order cannot make one unresolvable and a registration under a built-in key cannot replace it.
3. Any other key resolves through the MJ class factory. A package registers its dialect with @RegisterClass(QueryDialect, '<key>'), and both GetQueryDialect and RegisteredDialectKeys then see it. GetDialect serves such a registration only if the class is a full SQLDialect.
4. An unknown key throws, naming the keys that do resolve. Nothing falls back to a default dialect.

import { GetDialect, GetQueryDialect, IsSupportedPlatform } from '@memberjunction/sql-dialect';

const platform = GetDialect('postgresql');       // PostgreSQLDialect
platform.DDL.BatchSeparator();                   // ''

const source = GetQueryDialect('snowflake');     // SnowflakeQueryDialect
source.Writer.QuoteIdentifier('Name');           // '"Name"'
source.Writer.LimitClause(10).suffix;            // 'LIMIT 10'
source.Grammar.ParserGrammar;                    // 'Snowflake'

IsSupportedPlatform('snowflake');                // false
GetDialect('snowflake');                         // throws: not a full SQLDialect
Copy

Query-only dialects

A query-only dialect describes a database MJ reads through an External Data Source. It has the Lexical, Grammar and Writer facets, which is enough for MJ to:

• tokenize the query text safely (strings, quoted identifiers and comments end where the database says they do);
• screen it: refuse write statements (WriteStatementKeywords) and functions caller SQL may not call (CallerSQLForbiddenFunctions);
• cap and page its rows with the database's own row-cap syntax;
• render RunQuery template filters (sqlString, sqlIn, sqlBoolean, sqlIdentifier, the LIKE filters) with the database's literal and quoting rules.

What a query-only dialect does not have:

• No DDL facet. GetDialect refuses it, so CodeGen, migrations and anything else that creates schema cannot use it.
• No Types or Binding facet on the shipped ones (both are null). A boolean query parameter therefore keeps its JavaScript boolean value instead of being converted for the driver.
• No node-sql-parser grammar for Oracle (ParserGrammar is null), so features that need an AST are not available there.

Key Methods

These are members of SQLDialect. Each delegates to a facet: quoting, literals and expressions to Writer, placeholders and boolean binding to Binding, grammar facts to Grammar, and DDL, return patterns, introspection and type mapping to DDL.

Identifier Quoting

PostgreSQL auto-quoting (whole-statement)

The two methods above quote one identifier you have already identified. postgresqlAutoQuote.ts solves the harder problem: taking arbitrary SQL and deciding which of its bare words are identifiers that must be quoted to survive PostgreSQL's lowercase folding.

The ALL-CAPS rule is the load-bearing part: the set of SQL keywords and the set of MJ column names intersect (Name, Text, Length, Values, Rank, …), so a case-insensitive denylist emits those columns bare and PostgreSQL then rejects them. TEXT is the type; Text is the column.

This is not the only quoter — PostgreSQLDataProvider has a metadata-driven one that knows an entity's field list and is used where that context exists. See the module docblock; they are not duplicates.

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

First decide which kind of dialect the database needs:

• A query-only dialect when MJ only reads the database through an External Data Source. It needs the Lexical, Grammar and Writer facets, and optionally Types and Binding.
• A platform dialect when MJ itself will run on the database. It needs every facet, including DDL.

A query-only dialect

Extend AnsiQueryDialect, which supplies standard SQL facets, and override only what the database does differently. Build each data facet by spreading the closest existing one (ANSI_LEXICAL, MYSQL_GRAMMAR, and so on) and freezing the result; build the writer by extending AnsiWriter or another shipped writer.

import { RegisterClass } from '@memberjunction/global';
import {
    QueryDialect, AnsiQueryDialect, AnsiWriter, ANSI_LEXICAL, ANSI_GRAMMAR, ANSI_WRITE_STATEMENT_KEYWORDS,
    GetQueryDialect,
} from '@memberjunction/sql-dialect';
import type { DialectKey, DialectLexicalRules, DialectGrammar, DialectWriter, LimitClauseResult } from '@memberjunction/sql-dialect';

/** Lexical rules: standard SQL, plus backtick identifiers and `#` line comments. */
const ACME_LEXICAL: DialectLexicalRules = Object.freeze({
    ...ANSI_LEXICAL,
    IdentifierQuotes: Object.freeze([Object.freeze({ Open: '`', Close: '`', DoubledCloseEscape: true })]),
    LineComments: Object.freeze([
        Object.freeze({ Opener: '--', RequiresWhitespaceAfter: false }),
        Object.freeze({ Opener: '#', RequiresWhitespaceAfter: false }),
    ]),
    UnquotedIdentifierCase: 'none',
});

/** Grammar facts: standard SQL, with LIMIT row caps and two extra write keywords. */
const ACME_GRAMMAR: DialectGrammar = Object.freeze({
    ...ANSI_GRAMMAR,
    ParserGrammar: null,
    RowCapForms: Object.freeze(['limit-offset'] as const),
    WriteStatementKeywords: Object.freeze([...ANSI_WRITE_STATEMENT_KEYWORDS, 'UPSERT', 'LOAD']),
    CallerSQLForbiddenFunctions: Object.freeze(['read_file']),
});

/** Writes what differs from standard SQL; everything else comes from AnsiWriter. */
class AcmeWriter extends AnsiWriter {
    QuoteIdentifier(name: string): string {
        return `\`${name.replace(/`/g, '``')}\``;
    }

    LimitClause(limit: number, offset?: number): LimitClauseResult {
        return { prefix: '', suffix: offset != null ? `LIMIT ${limit} OFFSET ${offset}` : `LIMIT ${limit}` };
    }
}

@RegisterClass(QueryDialect, 'acme')
export class AcmeQueryDialect extends AnsiQueryDialect {
    private readonly writer: DialectWriter = Object.freeze(new AcmeWriter());

    get Key(): DialectKey { return 'acme'; }
    get DisplayName(): string { return 'Acme SQL'; }
    get Lexical(): DialectLexicalRules { return ACME_LEXICAL; }
    get Grammar(): DialectGrammar { return ACME_GRAMMAR; }
    get Writer(): DialectWriter { return this.writer; }
}

GetQueryDialect('acme').Writer.QuoteIdentifier('Name');   // '`Name`'
Copy

The lexical and grammar facts are what keep the RunQuery safety checks sound on that database: a wrong string or comment rule lets text hide from the scan for write statements, so take each one from the database's own documentation.

Registered this way, in your own package, the dialect resolves as soon as the module that declares it has been loaded. The key must not be one of the built-in keys, which cannot be replaced.

To ship a query-only dialect in this package instead:

1. Add it under src/dialects/ and export it, its writer and its facet constants from src/index.ts.
2. Add the class to QUERY_ONLY_DIALECT_CLASSES in src/dialectFactory.ts, so it resolves without the class factory. Do not register it with @RegisterClass.
3. Add its row to EXPECTED_FACTS in src/__tests__/conformance/dialectConformance.test.ts. The conformance suite runs over every key RegisteredDialectKeys() returns. It checks the declared facts, that the data facets are frozen, that a quoted identifier reads back the way the lexical rules say, that row caps use a form the grammar declares, and that every writer method writes without throwing.

A platform dialect

Extend SQLDialect and supply every facet. The members listed under Key Methods are already implemented by SQLDialect and delegate to the facets, so there are no per-method overrides to write.

SQLServerDialect and PostgreSQLDialect are the reference implementations. Then add the class to PLATFORM_DIALECT_CLASSES in src/dialectFactory.ts, export it from src/index.ts, and add its row to EXPECTED_FACTS in the conformance suite. The existing crossDialect.test.ts provides a pattern for testing multiple dialects against the same assertions.

The DataTypeMap maps SQL Server types to the new platform's types:

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

class AcmeDataTypeMap 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: 'BOOLEAN', 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

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

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 facts rather than the platform name, so a new dialect gets correct rendering by declaring them. Each render uses the dialect of the request: the provider's platform, or the External Data Source's dialect for a query on one.

The SQLDialect members in the first column are kept for existing callers; each reads the facet member in the second column, which is what code written against a QueryDialect reads.

The template filters read only the Writer facet, so they work for a query-only dialect too. A dialect without a Binding facet keeps a boolean parameter as a JavaScript boolean.

Installation

npm install @memberjunction/sql-dialect
Copy

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

License

Business Source License 1.1 — see LICENSE for details.