@memberjunction/sql-dialect

@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"

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

ISC