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).
SQL type names this dialect uses for boolean columns.
SQL type names this dialect uses for fixed-precision currency columns.
SQL type names this dialect uses for date / time / timestamp columns.
Default ORDER BY expression for paging when no user-specified ORDER BY exists. SQL Server: '(SELECT NULL)', PostgreSQL: '1'
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 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.
PostgreSQL has no in-row row-size limit (TOAST stores oversized variable-length values
out-of-line), so MaxInRowSizeBytes stays null (inherited). It does enforce a
hard 1600-column-per-table cap.
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.
Returns the dialect name used by node-sql-parser for AST parsing. SQL Server: 'TransactSQL', PostgreSQL: 'PostgresQL', etc.
The platform key identifying this dialect.
SQL type names this dialect uses for variable-length character / text columns.
Returns the data type mapping for this dialect.
SQL type names this dialect uses for UUID / uniqueidentifier columns.
Returns the ADD COLUMN clause for ALTER TABLE. SQL Server: ADD [colName] type NULL DEFAULT ... PostgreSQL: ADD COLUMN "colName" type NULL DEFAULT ...
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.
PostgreSQL folds unquoted identifiers to lowercase, so the physical schema an
unquoted CREATE SCHEMA __mj_BizAppsCommon produces is __mj_bizappscommon.
Canonicalize to that lowercase form so the engine's quoted operations target the
same physical schema as the app's (typically unquoted) migration DDL.
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)
PostgreSQL's n-ary null-coalescing is also COALESCE. Same form as
the two-arg IsNull since PG has no ISNULL to differentiate from.
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.
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())
PostgreSQL strict typing requires an explicit ::UUID cast when
comparing the empty-GUID sentinel against a UUID-typed column.
Without it, PG raises "operator does not exist: uuid = text".
PostgreSQL-specific Flyway escape. PostgreSQL string concatenation uses
|| (not +), and TEXT has no length cap — so a simple split with ||
suffices and no cast-to-MAX dance is needed (unlike SQL Server, which
silently truncates NVARCHAR(N) + NVARCHAR(M) past 4,000 chars).
PostgreSQL string literals don't take an N prefix either; everything
is already Unicode.
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
PostgreSQL FK-graph query for cascade planning — reads pg_catalog.pg_constraint
(contype = 'f'). Returns one row per FK column (via unnest(conkey, confkey), which
preserves column pairing/order), with childNullable (NOT attnotnull) and colCount
(array_length(conkey, 1), for composite exclusion). Column aliases and semantics MATCH
the SQL Server variant so a single caller parses both. relname/attname preserve the
quoted mixed-case identifiers MJ creates on PG, so they feed straight into QuoteIdentifier.
Both parent + child are filtered to schema; the schema is embedded as a literal.
Returns DDL to create a full-text index. SQL Server: FULLTEXT CATALOG + FULLTEXT INDEX PostgreSQL: tsvector column + GIN index
Optional_catalog: 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 }.
PostgreSQL has no ISNULL keyword; the standard is COALESCE (which
SQL Server also supports). PG generated SPs/functions emit COALESCE
everywhere a null-coalescing wrap is needed.
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()
PostgreSQL functions use the DEFAULT <value> clause for parameter defaults.
Returns a parameter placeholder for the given index. SQL Server: @p0, @p1, ... PostgreSQL: $1, $2, ...
PostgreSQL function parameters use a p_<flat lowercase> convention
(no @-prefix syntax in PG). This matches the baseline-ported SP names
(which lowercased SQL Server's PascalCase parameter names without
separators — e.g. @CompanyID → p_companyid) and the runtime
PostgreSQLDataProvider, which calls procs with p_${field.Name.toLowerCase()}.
Earlier this used a snake_case transform (p_company_id), which
produced functions the runtime could never invoke. Underscores already
in the input (e.g. the _Clear companion suffix) are preserved.
Returns the SQL syntax for calling a stored procedure or function. SQL Server: EXEC [schema].[name] @p0,
PostgreSQL: SELECT * FROM schema.name($1, $2)
PostgreSQL folds unquoted identifiers to lowercase, which would turn
AS EntityName into the result column entityname. Quoting the
alias preserves the requested casing for callers that key off the
column name (e.g. when consuming results into a TypeScript object
with a PascalCase property).
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 —
this is concrete in the base class so callers don't reinvent the
value.replace(/'/g, "''") pattern. Subclasses may override if a
future dialect needs a different 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"
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[]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 — but NEVER inside a PostgreSQL dollar-quoted block
($$ … $$ or $tag$ … $tag$), whose body legitimately contains
;+newline (DO blocks, PL/pgSQL function bodies, the integration
view-drop guard $mj_dropviews$). A naive split(/;\s*\n/g) tears
those apart. Outside dollar blocks the boundary semantics mirror the
base split(/;\s*\n/g) (a ; then optional inline whitespace then a
newline). Each returned statement ends with ;.
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.