@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

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

Interface	Purpose
`LimitClauseResult`	`{ prefix: string; suffix: string }` — Flexible pagination fragments. SQL Server uses `prefix` (TOP), PostgreSQL uses `suffix` (LIMIT/OFFSET).
`SchemaIntrospectionSQL`	Catalog query templates for discovering tables, columns, constraints, foreign keys, and indexes.
`TriggerOptions`	Configuration for trigger DDL generation (schema, table, timing, events, body, function name, FOR EACH ROW/STATEMENT).
`IndexOptions`	Configuration for index DDL generation (columns, uniqueness, method, partial WHERE, INCLUDE columns).
`DataTypeMap`	Maps source database types to target platform types.
`MappedType`	Describes a mapped type: `typeName`, `supportsLength`, `supportsPrecisionScale`, `defaultLength`.
`DatabasePlatform`	Union type: `'sqlserver' \| 'postgresql'`

Key Methods

Identifier Quoting

Method	Description	SQL Server	PostgreSQL
`QuoteIdentifier(name)`	Wraps a single identifier	`[name]`	`"name"`
`QuoteSchema(schema, object)`	Schema-qualified reference	`[schema].[object]`	`schema."object"`

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.

Export	Description
`AutoQuotePostgreSQLIdentifiers(sql)`	Tokenizes a statement and quotes the words that are identifiers. Skips string literals, dollar-quoted bodies and comments. Idempotent.
`PostgreSQLQuotingKeywords`	Keywords recognized case-sensitively, ALL-CAPS only. A word here is safe even when it is also an MJ column name — only the ALL-CAPS spelling is exempted, so the mixed-case column form still quotes.
`PostgreSQLStructuralKeywords`	A small tier matched case-INsensitively, so stored `UserView.OrderBy` / `ExtraFilter` fragments written in Title Case keep working. Guarded by a test that fails if a real column ever collides.

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

Method	Description	SQL Server	PostgreSQL
`LimitClause(limit, offset?)`	Returns `{ prefix, suffix }`	Without offset: `prefix: 'TOP 10'`. With offset: `suffix: 'OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY'`	`suffix: 'LIMIT 10 OFFSET 20'`

Literals and Expressions

Method	Description	SQL Server	PostgreSQL
`BooleanLiteral(value)`	Platform boolean	`1` / `0`	`true` / `false`
`CurrentTimestampUTC()`	Current UTC time	`GETUTCDATE()`	`(NOW() AT TIME ZONE 'UTC')`
`NewUUID()`	Generate UUID	`NEWID()`	`gen_random_uuid()`
`CastToText(expr)`	Cast to text type	`CAST(expr AS NVARCHAR(MAX))`	`CAST(expr AS TEXT)`
`CastToUUID(expr)`	Cast to UUID type	`CAST(expr AS UNIQUEIDENTIFIER)`	`CAST(expr AS UUID)`
`Coalesce(expr, fallback)`	Null coalescing (concrete)	`COALESCE(expr, fallback)`	`COALESCE(expr, fallback)`
`IsNull(expr, fallback)`	Alias for Coalesce	`COALESCE(expr, fallback)`	`COALESCE(expr, fallback)`
`IIF(condition, trueVal, falseVal)`	Conditional expression	`IIF(cond, t, f)`	`CASE WHEN cond THEN t ELSE f END`

INSERT/UPDATE Return Patterns

Method	Description	SQL Server	PostgreSQL
`ReturnInsertedClause(columns?)`	Get inserted values back	`OUTPUT INSERTED.*` or `OUTPUT INSERTED.[col]`	`RETURNING *` or `RETURNING "col"`
`AutoIncrementPKExpression()`	Auto-increment DDL	`IDENTITY(1,1)`	`GENERATED ALWAYS AS IDENTITY`
`UUIDPKDefault()`	Default UUID PK expression	`NEWSEQUENTIALID()`	`gen_random_uuid()`
`ScopeIdentityExpression()`	Last inserted identity	`SCOPE_IDENTITY()`	`lastval()`
`RowCountExpression()`	Rows affected	`@@ROWCOUNT`	`ROW_COUNT` (via GET DIAGNOSTICS)

DDL Generation

Method	Description
`TriggerDDL(options: TriggerOptions)`	Full trigger creation DDL. SQL Server emits `CREATE TRIGGER ... AS BEGIN ... END`. PostgreSQL emits a companion `CREATE OR REPLACE FUNCTION` plus `CREATE TRIGGER ... EXECUTE FUNCTION`.
`IndexDDL(options: IndexOptions)`	Index creation DDL. PostgreSQL supports `USING method`, partial `WHERE`, and `IF NOT EXISTS`. SQL Server supports `INCLUDE` columns.
`ExistenceCheckSQL(objectType, schema, name)`	Check if a database object exists. SQL Server uses `OBJECT_ID()`. PostgreSQL uses `pg_catalog` queries. Supports TABLE, VIEW, FUNCTION, PROCEDURE, TRIGGER.
`CreateOrReplaceSupported(objectType)`	Whether `CREATE OR REPLACE` is available. SQL Server: always `false`. PostgreSQL: `true` for FUNCTION, VIEW, PROCEDURE.
`BatchSeparator()`	Statement batch separator. SQL Server: `GO`. PostgreSQL: `""` (empty string).

Full-Text Search

Method	Description	SQL Server	PostgreSQL
`FullTextSearchPredicate(column, searchTerm)`	Search predicate	`CONTAINS([column], term)`	`column @@ plainto_tsquery('english', term)`
`FullTextIndexDDL(table, columns, catalog?)`	Index creation DDL	Fulltext catalog + fulltext index	tsvector column + GIN index + update trigger

String and JSON Functions

Method	Description	SQL Server	PostgreSQL
`StringSplitFunction(value, delimiter)`	Split string to rows	`STRING_SPLIT(value, delim)`	`unnest(string_to_array(value, delim))`
`JsonExtract(column, path)`	Extract JSON value	`JSON_VALUE(column, 'path')`	`column->>'path'`
`ConcatOperator()`	String concatenation	`+`	`\|\|`

Parameters and Procedure Calls

Method	Description	SQL Server	PostgreSQL
`ParameterPlaceholder(index)`	Positional parameter	`@p0`, `@p1`, …	`$1`, `$2`, …
`ProcedureCallSyntax(schema, name, params)`	Call stored procedure/function	`EXEC [schema].[name] @p0, @p1`	`SELECT * FROM schema."name"($1, $2)`

CTE / Recursion

Method	Description	SQL Server	PostgreSQL
`RecursiveCTESyntax()`	Recursive CTE keyword	`WITH`	`WITH RECURSIVE`

Permissions and Comments

Method	Description	SQL Server	PostgreSQL
`GrantPermission(permission, objectType, schema, object, role)`	Grant access	`GRANT ... ON [s].[o] TO [r]`	`GRANT ... ON s."o" TO "r"`
`CommentOnObject(objectType, schema, name, comment)`	Add description	`EXEC sp_addextendedproperty ...`	`COMMENT ON TYPE s."name" IS '...'`

Schema Introspection

Method	Description
`SchemaIntrospectionQueries()`	Returns a `SchemaIntrospectionSQL` object with platform-specific catalog queries for listing tables, columns, constraints, foreign keys, indexes, and checking object existence.

Data Type Mapping

Method	Description
`get TypeMap(): DataTypeMap`	Returns the dialect-specific type mapper instance.
`MapDataType(sourceType, length?, precision?, scale?)`	Convenience wrapper that calls `TypeMap.MapType()`. Returns a `MappedType`.
`MapDataTypeToString(sourceType, length?, precision?, scale?)`	Convenience wrapper that calls `TypeMap.MapTypeToString()`. Returns a formatted type string like `VARCHAR(255)` or `NUMERIC(10,2)`.

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;
}

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)

SQL Server Type	PostgreSQL Type	Notes
`UNIQUEIDENTIFIER`	`UUID`
`BIT`	`BOOLEAN`
`NVARCHAR(n)`	`VARCHAR(n)`
`NVARCHAR(MAX)`	`TEXT`	Length = -1 or unspecified
`VARCHAR(MAX)`	`TEXT`	Length = -1 or unspecified
`NCHAR` / `CHAR`	`CHAR`	Preserves length
`INT` / `INTEGER`	`INTEGER`
`BIGINT`	`BIGINT`
`SMALLINT`	`SMALLINT`
`TINYINT`	`SMALLINT`	No TINYINT in PostgreSQL
`DECIMAL` / `NUMERIC`	`NUMERIC`	Preserves precision/scale
`FLOAT(1-24)`	`REAL`
`FLOAT(25-53)`	`DOUBLE PRECISION`
`REAL`	`REAL`
`MONEY`	`NUMERIC(19,4)`
`SMALLMONEY`	`NUMERIC(10,4)`
`DATE`	`DATE`
`DATETIME` / `DATETIME2`	`TIMESTAMP`
`DATETIMEOFFSET`	`TIMESTAMPTZ`
`SMALLDATETIME`	`TIMESTAMP(0)`
`TIME`	`TIME`
`TEXT` / `NTEXT`	`TEXT`
`IMAGE`	`BYTEA`
`VARBINARY` / `BINARY`	`BYTEA`
`XML`	`XML`

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")'

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

Adding a New Dialect

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

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;
    }
}

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'; }
    // ... implement all remaining abstract methods (~25+)
}

Update the DatabasePlatform type in sqlDialect.ts to include the new platform key.
Export from index.ts:

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

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

Feature	SQL Server (`SQLServerDialect`)	PostgreSQL (`PostgreSQLDialect`)
Identifier quoting	`[name]`	`"name"`
Schema-qualified	`[schema].[object]`	`schema."object"`
Boolean literals	`1` / `0`	`true` / `false`
Current UTC time	`GETUTCDATE()`	`(NOW() AT TIME ZONE 'UTC')`
New UUID	`NEWID()`	`gen_random_uuid()`
UUID PK default	`NEWSEQUENTIALID()`	`gen_random_uuid()`
Auto-increment	`IDENTITY(1,1)`	`GENERATED ALWAYS AS IDENTITY`
Pagination (no offset)	`SELECT TOP 10 ...`	`... LIMIT 10`
Pagination (with offset)	`OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY`	`LIMIT 10 OFFSET 20`
Return inserted	`OUTPUT INSERTED.*`	`RETURNING *`
Scope identity	`SCOPE_IDENTITY()`	`lastval()`
Row count	`@@ROWCOUNT`	`ROW_COUNT` (GET DIAGNOSTICS)
Concatenation	`+`	`\|\|`
Parameters	`@p0`, `@p1`, …	`$1`, `$2`, …
Batch separator	`GO`	(none)
Conditional	`IIF(cond, t, f)`	`CASE WHEN cond THEN t ELSE f END`
Recursive CTE	`WITH`	`WITH RECURSIVE`
Procedure call	`EXEC [s].[name] @p0`	`SELECT * FROM s."name"($1)`
CREATE OR REPLACE	Not supported	FUNCTION, VIEW, PROCEDURE
Full-text search	`CONTAINS([col], term)`	`col @@ plainto_tsquery(...)`
JSON extract	`JSON_VALUE(col, 'path')`	`col->>'path'`
String split	`STRING_SPLIT(val, delim)`	`unnest(string_to_array(val, delim))`
Cast to text	`CAST(x AS NVARCHAR(MAX))`	`CAST(x AS TEXT)`
Cast to UUID	`CAST(x AS UNIQUEIDENTIFIER)`	`CAST(x AS UUID)`
Object existence	`IF OBJECT_ID(...) IS NOT NULL`	`SELECT EXISTS (... pg_catalog ...)`
Comments/descriptions	`sp_addextendedproperty`	`COMMENT ON ...`
Grants	`GRANT ... ON [s].[o] TO [r]`	`GRANT ... ON s."o" TO "r"`

Installation

npm install @memberjunction/sql-dialect

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

License

Business Source License 1.1 — see LICENSE for details.