Whether the platform allows ORDER BY inside CTE definitions without TOP, OFFSET, or FOR XML. SQL Server: false (ORDER BY in CTEs is illegal without TOP/OFFSET/FOR XML) PostgreSQL: true (ORDER BY in CTEs is always legal)
SQL type names this dialect uses for binary blob columns (image, bytea, varbinary).
Driver binding rules.
SQL type names this dialect uses for boolean columns.
Functions SQL a caller supplied (ad-hoc SQL, TestQuerySQL, query specs, client RunView
clauses) may not call, lower case; a trailing * matches every name with that prefix. They
run SQL passed as a string, read files or other databases, act on the server, or pause the
query, so neither the check of which tables the SQL reads nor a read-only transaction stops
them.
SQL type names this dialect uses for fixed-precision currency columns.
SQL type names this dialect uses for date / time / timestamp columns.
DDL, stored-procedure, migration, introspection and transaction SQL.
Default ORDER BY expression for paging when no user-specified ORDER BY exists. SQL Server: '(SELECT NULL)', PostgreSQL: '1'
Human-readable name for this dialect, as it should appear to a person — or to an LLM being told which SQL to write.
Abstract on purpose: the name is dialect knowledge, so a new dialect declares it once here rather than every prompt/UI keeping its own map.
Subset of StringTypeNames that represents fixed-width /
space-padded character types. SQL Server char/nchar and PostgreSQL
char/bpchar/character (without varying) all right-pad stored
values with spaces up to the declared length and return that padding
in result sets. Variable-width types (varchar, nvarchar, text,
etc.) do not.
Used by BaseEntity to rtrim padding on load so dirty-checks and
downstream consumers see the logical value, not the storage form.
SQL type names this dialect uses for floating-point / decimal columns.
sql-formatter ships PostgreSQL under the postgresql language key.
Statement grammar facts. A frozen plain object.
SQL type names this dialect uses for integer columns (int, bigint, smallint, …).
SQL type names this dialect uses for interval / duration columns.
SQL type names this dialect uses for JSON / XML structured columns.
The dialect key, which for a platform dialect is its PlatformKey.
How the dialect's text splits into tokens. A frozen plain object.
Hard maximum number of columns per table, or null when effectively unbounded for our purposes.
SQL Server: 1024. PostgreSQL: 1600. Default: null.
Maximum bytes that can be stored in-row for a single row, or null when the dialect has no
in-row size limit. SQL Server enforces a hard ~8060-byte in-row limit; PostgreSQL has none
(TOAST stores oversized variable-length values out-of-line). Consumed by schema materialization
to avoid emitting a table whose minimum in-row footprint can never satisfy an INSERT.
Default: null (no in-row size limit).
Maximum string length usable in an INDEX KEY column (PRIMARY KEY / UNIQUE constraint) on this dialect. A PK string column wider than this can't be indexed, so callers (e.g. the integration schema builder) cap PK string columns at this value. Default: no practical declare-time cap — a dialect overrides it where one exists. SQL Server's 900-byte index-key limit = 450 NVARCHAR chars; PostgreSQL enforces its (larger) btree limit on the stored value at write time, not the declared length, so it keeps the no-cap default.
SQL type names this dialect uses for network address columns (inet, cidr, …).
Returns the dialect's literal representation of NULL as it would
appear in generated SQL (e.g. as the result of a default-value
formatter). Both SQL Server and PostgreSQL use the bare keyword
NULL, but a future dialect could differ — codegen comparisons
should route through this rather than hard-coding the string.
Whether skipping rows for a page needs an ORDER BY (OFFSET … FETCH does on SQL Server).
When true, a page taken from a derived table is ordered by DefaultPagingOrderBy.
SQL Server: true, PostgreSQL: false
Returns the dialect name used by node-sql-parser for AST parsing. SQL Server: 'TransactSQL', PostgreSQL: 'PostgresQL', etc.
The platform key identifying this dialect.
A syntax briefing for this dialect, composed ENTIRELY from this class's own primitives — nothing here is a hand-maintained per-dialect prose table. Implementing the dialect is therefore sufficient to get correct guidance; there is no second list to remember to update.
Consumed by the _SQL_DIALECT_RULES system placeholder, which makes it
available to EVERY AI prompt template without any template having to be
registered anywhere. That is the point: the set of SQL-generating
templates is a predicate ("does it ask for SQL?"), never a list of IDs.
The keyword that opens a statement's trailing query-hint clause, such as OPTION (…);
null when the platform has none. SQL Server: 'OPTION', PostgreSQL: null
The ORDER BY expression for paging a set operation (UNION / INTERSECT / EXCEPT) or a
SELECT DISTINCT that has no ORDER BY of its own, on a platform that only accepts select-list
items there. null when DefaultPagingOrderBy is accepted for those shapes too.
SQL Server: '1' (the first column), PostgreSQL: null
SQL type names this dialect uses for variable-length character / text columns.
Whether dollar-quoted string literals ($$…$$, $tag$…$tag$) exist.
PostgreSQL: true, SQL Server: false
Whether E'…' string literals, in which a backslash escapes the next character, exist.
PostgreSQL: true, SQL Server: false
Returns the data type mapping for this dialect.
SQL type-name sets.
Upper-case keywords that start a new statement wherever they appear as a bare word at the top level of another one, because the platform needs no separator between statements; empty or absent when statements must be separated. SQL Server: the statement keywords; PostgreSQL: none.
SQL type names this dialect uses for UUID / uniqueidentifier columns.
Writes SQL fragments in this dialect.
Returns the ADD COLUMN clause for ALTER TABLE. SQL Server: ADD [colName] type NULL DEFAULT ... PostgreSQL: ADD COLUMN "colName" type NULL DEFAULT ...
Wraps a single DML statement so the number of rows it affected comes back as a one-row,
one-column result set — the portable way to read an affected-row count without relying on a
driver-specific rowsAffected field.
SQL Server appends SELECT @@ROWCOUNT. PostgreSQL has no @@ROWCOUNT at all, so it wraps
the statement in a data-modifying CTE and counts the RETURNING rows.
dmlStatement must be exactly one INSERT/UPDATE/DELETE and carry no trailing semicolon:
the PostgreSQL form is a CTE, which cannot wrap a statement batch.
Returns ALTER COLUMN clause(s) for type/nullability changes. SQL Server: ALTER TABLE t ALTER COLUMN [col] newType NULL/NOT NULL; PostgreSQL: ALTER TABLE t ALTER COLUMN "col" TYPE newType, ALTER COLUMN "col" SET/DROP NOT NULL;
Wraps a list of statements in ONE all-or-nothing transaction batch for this platform, ready to
run as a single ExecuteSQL(script) call. Owns the platform-specific session/transaction setup
so callers don't sniff PlatformKey themselves:
SET QUOTED_IDENTIFIER ON / SET ANSI_NULLS ON (required for UPDATE/DELETE
against tables with filtered / computed-column indexes or indexed views — Msg 1934 otherwise)
SET XACT_ABORT ON (any failure rolls the whole batch back) + BEGIN/COMMIT TRANSACTION.BEGIN … COMMIT — no session pragmas (QUOTED_IDENTIFIER/ANSI_NULLS
are SQL-Server concepts) and PostgreSQL already aborts the whole transaction on any error.Returns an empty string for an empty statement list (nothing to run).
individual SQL statements (each WITHOUT a trailing ;)
Returns the auto-increment PK expression for DDL. SQL Server: IDENTITY(1,1), PostgreSQL: GENERATED ALWAYS AS IDENTITY
Returns the batch separator for the platform. SQL Server: "GO", PostgreSQL: "" (none needed)
Returns a boolean literal for the platform. SQL Server: "1"/"0", PostgreSQL: "true"/"false"
Returns the SQL type token for a boolean parameter in a stored-procedure /
function signature. Used by codegen when emitting tolerant-SP _Clear
companion parameters and other boolean-typed params.
SQL Server: bit (BIT type, 0/1)
PostgreSQL: boolean (BOOLEAN type, TRUE/FALSE)
Hardcoding bit everywhere worked on SQL Server but produced sprocs
that PG rejected as operator does not exist: boolean = integer when
the generated CASE compared a bit-declared parameter with = 1.
The value to bind for a boolean query parameter. SQL Server: 1 / 0 (BIT),
PostgreSQL: true / false.
Canonicalizes a SCHEMA name to the form the platform actually stores when
the name appears UNQUOTED in DDL. PostgreSQL folds unquoted identifiers to
lowercase, so __mj_BizAppsCommon physically becomes __mj_bizappscommon;
SQL Server is case-insensitive and stores the name as-given. Routing every
schema CREATE/DROP/USE through this keeps the engine's (quoted) operations
aligned with what unquoted migration DDL produces — so a mixed-case schema
name can't split into two physical schemas (one quoted-mixed, one folded-lowercase).
Cap a column type if it cannot be used in a UNIQUE/index constraint. SQL Server: NVARCHAR(MAX) → NVARCHAR(450) (MAX columns cannot be indexed). PostgreSQL: no-op (TEXT can be indexed). Override in platform-specific dialects.
Returns a CAST to a bounded-width string type. Used when the result
needs to be comparable against an indexed column (SQL Server cannot
compare/index NVARCHAR(MAX)) or against a fixed-width text column
such as MJ's RecordID (NVARCHAR(450) on SQL Server).
Implemented by composing ResolveAbstractType({ type: 'string', maxLength }),
which dialects already supply — SQL Server emits NVARCHAR(N) and
PostgreSQL emits VARCHAR(N). Defaults to MJ's standard 450-char
width to match the cap on indexable string columns in SQL Server.
Returns a CAST-to-text expression. SQL Server: CAST(expr AS NVARCHAR(MAX)), PostgreSQL: CAST(expr AS TEXT)
Returns a CAST-to-UUID expression. SQL Server: CAST(expr AS UNIQUEIDENTIFIER), PostgreSQL: CAST(expr AS UUID)
Returns the platform's n-ary null-coalescing expression. SQL Server and
PostgreSQL both spell this COALESCE(...); the dialect's writer owns
the spelling.
Returns description metadata SQL for a column. SQL Server: EXEC sp_addextendedproperty with
CommentOnColumn that is safe to re-run. Default returns the standard statement (PostgreSQL COMMENT ON COLUMN already includes its terminator and is idempotent). SQL Server overrides to guard sp_addextendedproperty.
Returns a COMMENT ON statement (PostgreSQL) or sp_addextendedproperty (SQL Server).
CommentOnObject that is safe to re-run (no error if the description already exists). Default returns the standard statement terminated with ';' — adequate where the comment statement is inherently idempotent (PostgreSQL COMMENT ON replaces in place). SQL Server overrides to guard sp_addextendedproperty with a fn_listextendedproperty check.
Returns the string concatenation operator. SQL Server: "+", PostgreSQL: "||"
Returns a conditional IF/ELSE block in platform-appropriate procedural SQL. SQL Server: IF (condition) BEGIN thenSQL END ELSE BEGIN elseSQL END PostgreSQL: DO $$ BEGIN IF condition THEN thenSQL; ELSE elseSQL; END IF; END $$;
SQL boolean condition
SQL to execute when condition is true
OptionalelseSQL: stringOptional SQL to execute when condition is false
Whether the platform supports CREATE OR REPLACE for a given object type. PostgreSQL supports it for FUNCTION, VIEW. SQL Server does not.
SQL that marks a nested transaction savepoint. SQL Server:
SAVE TRANSACTION name. PostgreSQL: SAVEPOINT name.
Returns CREATE SCHEMA IF NOT EXISTS SQL. SQL Server: IF NOT EXISTS (...) EXEC('CREATE SCHEMA [name]'); GO PostgreSQL: CREATE SCHEMA IF NOT EXISTS "name";
A full CREATE TABLE that runs only if the table is absent, as a SINGLE statement.
Default (PostgreSQL et al.): CREATE TABLE IF NOT EXISTS <fullTable> (...);
SQL Server overrides with an IF OBJECT_ID(...) IS NULL guard (it has no native
CREATE TABLE IF NOT EXISTS).
Already-quoted schema.table identifier
The column/constraint lines that go between the parentheses
Returns a full CREATE TABLE wrapped in a "create if not exists" guard. SQL Server: IF NOT EXISTS (sys.tables check) BEGIN CREATE TABLE ... END; PostgreSQL: CREATE TABLE IF NOT EXISTS ...;
Schema name
Table name
The column definitions (everything between the parentheses)
Returns the current UTC timestamp expression. SQL Server: GETUTCDATE(), PostgreSQL: NOW() AT TIME ZONE 'UTC'
Returns a date/time arithmetic expression. SQL Server: DATEADD(MINUTE, 30, GETUTCDATE()) PostgreSQL: (NOW() AT TIME ZONE 'UTC') + INTERVAL '30 minutes'
Time unit
Number of units to add (can be negative)
Base timestamp expression (e.g., from CurrentTimestampUTC())
Returns the empty-GUID sentinel literal
(00000000-0000-0000-0000-000000000000) formatted for use in a
CASE-comparison expression. The base class returns the literal as a
plain quoted string — SQL Server's implicit conversion accepts that
directly. Dialects with strict typing (PostgreSQL) override to add
the explicit cast their grammar requires.
Splits ${...} inside SQL string literals so Flyway doesn't treat
them as placeholders. Each ${name} becomes a concat-split that
round-trips back to ${name} at execution but contains no adjacent
${ in the file text. Form is platform-specific (concat operator,
truncation rules) — see each dialect's implementation.
Escapes the characters a LIKE pattern treats specially, so text matches literally with
the platform's default escape rules (no ESCAPE clause). Quotes are not escaped here; the
caller still doubles them for the string literal.
SQL Server: [, % and _ become [[], [%] and [_]. PostgreSQL: \, % and _
are escaped with a backslash.
Minimum in-row byte footprint of a column of the given raw SQL type. Off-row-capable variable-length / LOB types contribute only their in-row pointer; fixed-length types contribute their full size. Only meaningful for dialects that report a MaxInRowSizeBytes. Default: 24 (a conservative off-row-pointer floor).
Returns SQL to check if a database object exists.
"TABLE", "VIEW", "FUNCTION", "PROCEDURE", "TRIGGER"
Schema name
Object name
Returns the platform's fallback type for unknown/unmapped abstract types. SQL Server: NVARCHAR(MAX) PostgreSQL: TEXT
Returns a ready-to-run (no bind parameters) query that enumerates the foreign-key graph of a single schema, for FK-cascade planning (e.g. Open-App metadata teardown).
Unlike SchemaIntrospectionQueries.listForeignKeys (a placeholder template), this
embeds the schema literal directly (via QuoteStringLiteral) so it can be executed
with ExecuteSQL(sql) on any provider without a dialect-specific bind convention. It also
returns two extra columns the cascade planner needs:
childNullable — whether the referencing (child) column allows NULL, which decides
UPDATE … SET NULL (nullable) vs. DELETE + recurse (NOT NULL).colCount — the number of columns in the FK constraint, so a caller can exclude
composite FKs (colCount > 1).Every row is one FK column. Column aliases are IDENTICAL across dialects:
parentTable, parentRefCol, childTable, childCol, childNullable, fkName, colCount
(both the referenced/parent and referencing/child tables are within schema).
the schema whose intra-schema FK edges to enumerate (e.g. __mj)
Returns DDL to create a full-text index. SQL Server: FULLTEXT CATALOG + FULLTEXT INDEX PostgreSQL: tsvector column + GIN index
Optionalcatalog: stringReturns a full-text search predicate expression. SQL Server: CONTAINS(column, searchTerm) PostgreSQL: column @@ plainto_tsquery('english', searchTerm)
Returns an IIF/CASE equivalent expression. SQL Server: IIF(condition, trueVal, falseVal) PostgreSQL: CASE WHEN condition THEN trueVal ELSE falseVal END
Returns true if the given error represents an infrastructure-level connection failure (timeout, refused, pool closed, etc.) as opposed to a query-level error (bad SQL, constraint violation).
Each dialect implements this using its driver's structured error types:
error.name === 'ConnectionError'ErrorUsed by GenericDatabaseProvider to re-throw connection errors from
RunView/RunQuery instead of swallowing them into { Success: false }.
Returns the platform's two-argument null-coalescing expression.
SQL Server: ISNULL(expr, fallback), PostgreSQL: COALESCE(expr, fallback).
The dialect's writer declares the keyword.
Returns true if value is the dialect's representation of a NULL
literal in generated SQL. Comparison is case-insensitive after
trimming whitespace. Subclasses may override if a dialect uses a
non-keyword form (none currently do).
Returns a JSON value extraction expression. SQL Server: JSON_VALUE(column, path) PostgreSQL: column->>'path' or jsonb_extract_path_text
Returns pagination SQL fragments. SQL Server uses TOP (prefix) or OFFSET/FETCH (suffix). PostgreSQL uses LIMIT/OFFSET (suffix).
Optionaloffset: numberWraps an expression in the dialect's lowercase function — used for case-insensitive comparison when authoring filters that must work on both SS (default case-insensitive collation) and PG (case-sensitive).
Both SQL Server and PostgreSQL implement LOWER() per the ANSI SQL
standard, so the default returns LOWER(${expr}). A subclass would
override only for an exotic dialect (e.g. one that exposes lc() or
needs a CAST first).
Use this instead of hardcoding LOWER(...) in callers — keeps the
dialect-aware SQL surface in one place per the SQLDialect contract.
Convenience: maps a SQL Server type to this dialect's equivalent.
Optionallength: numberOptionalprecision: numberOptionalscale: numberReturns a new UUID generation expression. SQL Server: NEWID(), PostgreSQL: gen_random_uuid()
Returns the default-value clause appended to a parameter declaration.
SQL Server: = NULL (or = 0, etc.), PostgreSQL: DEFAULT NULL.
value should be a SQL literal already formatted by the caller (e.g.
the dialect's NullLiteral, a quoted string, a numeric literal).
Returns a parameter placeholder for the given index. SQL Server: @p0, @p1, ... PostgreSQL: $1, $2, ...
Returns the parameter-reference syntax used by this dialect's
stored-procedure / function bodies.
SQL Server: @MyName, PostgreSQL functions: p_my_name.
Codegen should call this rather than hard-coding '@' + name.
Returns the SQL syntax for calling a stored procedure or function. SQL Server: EXEC [schema].[name] @p0,
PostgreSQL: SELECT * FROM schema.name($1, $2)
Quotes a column alias used in SELECT ... AS <alias>. SQL Server is
case-insensitive and accepts a bare identifier; PostgreSQL folds
unquoted identifiers to lowercase, so we must quote the alias when the
caller cares about preserving case (e.g. matching a TypeScript property
name in the result row).
Quotes a database identifier (table name, column name, etc.). SQL Server: [name], PostgreSQL: "name"
Produces a schema-qualified object reference. SQL Server: [schema].[object], PostgreSQL: schema."object"
Quotes a value as a SQL string literal. Both SQL Server and PostgreSQL
use single quotes with '' doubling to escape internal apostrophes.
Use this instead of reinventing the value.replace(/'/g, "''") pattern;
the dialect's writer owns the escape rule.
Returns a non-fatal signal/notice statement detectable in CLI output. Used for signaling conditions (e.g., "lock held") without aborting the script. SQL Server: RAISERROR('message', 16, 1) — appears in sqlcmd stdout PostgreSQL: RAISE NOTICE 'message' — appears in psql stderr
Note: For PostgreSQL, this must be used inside a ConditionalBlock (DO $$ context).
Signal message to emit (used for detection in CLI output)
Returns the recursive CTE syntax keyword. SQL Server: "WITH", PostgreSQL: "WITH RECURSIVE"
SQL that discards a savepoint after a nested commit, or null when the
platform has no release (SQL Server savepoints live until the outer TX
ends). PostgreSQL: RELEASE SAVEPOINT name.
Resolves an abstract schema field type to a concrete SQL type string. Used by SchemaEngine for DDL generation from platform-agnostic definitions. SQL Server: 'string' -> 'NVARCHAR(255)', 'boolean' -> 'BIT' PostgreSQL: 'string' -> 'VARCHAR(255)', 'boolean' -> 'BOOLEAN'
Returns the clause used to get inserted values back. SQL Server: OUTPUT INSERTED.col1, INSERTED.col2 PostgreSQL: RETURNING col1, col2
Optionalcolumns: string[]SQL that undoes work after a savepoint. SQL Server:
ROLLBACK TRANSACTION name. PostgreSQL: ROLLBACK TO SAVEPOINT name.
Returns the row count variable/expression for the last statement. SQL Server: @@ROWCOUNT, PostgreSQL: (via GET DIAGNOSTICS or FOUND)
Returns platform-specific schema introspection queries.
Returns the expression to get the last inserted identity value. SQL Server: SCOPE_IDENTITY(), PostgreSQL: lastval()
Splits an oversized SQL batch into individual statements on ;+EOL
boundaries so a caller (e.g. the RSU migration executor) can re-group
them into smaller chunks that each fit under a client request timeout.
Base implementation = the naive split(/;\s*\n/g) semantics: split on a
; followed by optional inline whitespace and a newline, trim each
fragment, drop empties, and ensure each returned statement ends with ;.
This is correct for SQL Server (no dollar-quoted blocks).
PostgreSQL overrides this with a dollar-quote-aware split that never
tears apart DO $$ … $$ / $tag$ … $tag$ blocks whose bodies
legitimately contain ;+newline.
The prefix a string literal holding text needs so the database keeps every character, or
'' when a plain literal does. SQL Server: N when the text has characters outside ASCII,
which a plain literal would lose to the database code page. PostgreSQL: always ''.
Returns a STRING_SPLIT or equivalent expression. SQL Server: STRING_SPLIT(value, delimiter) PostgreSQL: string_to_array(value, delimiter) or regexp_split_to_table
Returns the default expression for a UUID primary key. SQL Server: NEWSEQUENTIALID(), PostgreSQL: gen_random_uuid()
PostgreSQL dialect implementation. Uses "double-quote" identifiers, LIMIT/OFFSET pagination, native BOOLEAN, PL/pgSQL functions.