prorm API Reference
    Preparing search index...

    Class MSSQLDialect

    MSSQL dialect class that implements the Dialect interface

    Implements

    Index
    constructor
    name library
    connect disconnect getConnection isConnected queryStream query escape escapeId quoteIdentifier quoteTable getDatabaseVersion createTable disableSystemVersioning dropTemporalTable dropTable createPartitionedTable createPartition attachPartition detachPartition dropPartition addPartition createView dropView createMaterializedView refreshMaterializedView dropMaterializedView hasMaterializedView createStoredProcedure dropStoredProcedure dropProcedure createProcedure executeStoredProcedure hasStoredProcedure createTrigger dropTrigger hasTrigger createSequence dropSequence nextSequenceValue hasSequence listSequences createPolicy dropPolicy enableRLS disableRLS hasPolicy commentTable commentColumn createPartialIndex createExpressionIndex createIdentityColumn createComputedColumn addColumn removeColumn changeColumn showTables getTableStatus getCreateTable hasPartition showConstraints showIndexes showViews showMaterializedViews describeTable renameTable addIndex removeIndex createIndex dropIndex createColumnstoreIndex createConstraint dropConstraint startTransaction commitTransaction rollbackTransaction createSchema dropSchema showAllSchemas listSchemas getDataTypeSql buildWhereClause buildOrderClause buildLimitOffset buildInsertQuery insert buildUpdateQuery buildDeleteQuery buildSelectQuery buildUpsertQuery buildIncrementQuery buildJsonExtract buildCreateServerQuery buildAlterServerQuery buildDropServerQuery buildCreateUserMappingQuery buildAlterUserMappingQuery buildDropUserMappingQuery buildCreateForeignTableQuery buildDropForeignTableQuery buildImportForeignSchemaQuery getServersQuery createForeignDataWrapper dropForeignDataWrapper createForeignServer dropForeignServer createForeignTable createUserMapping dropUserMapping changeOwner addConstraint removeConstraint createSecurityPolicy dropSecurityPolicy bulkInsert addForeignKey renameColumn createFulltextIndex createSpatialIndex buildCreateUserQuery buildAlterUserQuery buildDropUserQuery getUsersQuery buildGrantQuery buildRevokeQuery buildShowGrantsQuery buildFlushPrivilegesQuery buildCreateRoleQuery buildDropRoleQuery buildGrantRoleQuery buildRevokeRoleQuery getRolesQuery createDatabaseSQL dropDatabaseSQL createSavepointSQL releaseSavepointSQL rollbackToSavepointSQL createExtension dropExtension getExtensions hasExtension buildJsonQuery replaceReplacements formatValue stDistance stWithin stContains stIntersects stDWithin stAsText stGeomFromText buildJsonTable
    name: "mssql" = 'mssql'

    The name of the dialect

    library: "mssql" = 'mssql'

    The database library or driver being used

    • Stream query results by paging through sql via repeated dialect-appropriate LIMIT/OFFSET queries (see createPaginatedQueryStream() in src/dialects/query-stream-helper.ts) instead of loading the whole result set into memory at once.

      Parameters

      • sql: string

        The SELECT statement to stream

      • Optionaloptions: StreamOptions

        Streaming options (batch size, backpressure watermark, model mapping)

      Returns Readable

    • Escape an identifier (table name, column name, etc.)

      Parameters

      • identifier: unknown

        The identifier to escape

      Returns string

    • Quote a table name

      Parameters

      • tableName: string

        The table name to quote

      • Optionalschema: string

        Optional schema to prefix the table with

      Returns string

    • Create a new table

      Parameters

      • tableName: string

        Name of the table

      • columns: Record<string, ColumnDefinition>

        Column definitions

      • Optionaloptions: MSSQLTableOptions

        Additional options

      Returns Promise<void>

    • Disable system-versioning on a temporal table. Required before the table (or its schema) can be dropped or structurally altered in ways incompatible with an active PERIOD FOR SYSTEM_TIME.

      Parameters

      • tableName: string
      • Optionalschema: string

      Returns Promise<void>

    • Drop a system-versioned temporal table: disables SYSTEM_VERSIONING on the current table, then drops the table itself. Pass dropHistoryTable/historyTableName to also drop the associated history table.

      Parameters

      • tableName: string
      • Optionaloptions: DropTableOptions & {
            schema?: string;
            dropHistoryTable?: boolean;
            historyTableName?: string;
        }

      Returns Promise<void>

    • Create a partitioned table (PostgreSQL 10+)

      Parameters

      • _tableName: string
      • _columns: Record<string, ColumnDefinition>
      • Optional_options: TableOptions & {
            partitionBy: {
                type: "range" | "list" | "hash";
                column: string | string[];
            };
            partitions?: {
                name: string;
                bound?: unknown;
                tablespace?: string;
                storageParameters?: Record<string, string | number>;
            }[];
        }

      Returns Promise<void>

    • Drop a partition (PostgreSQL 10+)

      Parameters

      • _partitionName: string
      • Optional_options: { ifExists?: boolean; cascade?: boolean }

      Returns Promise<void>

    • Add a partition to an existing partitioned table (MSSQL)

      Parameters

      • _tableName: string
      • _partitionName: string
      • _partitionSpec: { values?: string; forValues?: string }

      Returns Promise<void>

    • Create a database view

      Parameters

      • viewName: string

        Name of the view to create

      • query: string

        The SELECT query for the view

      • Optionaloptions: ViewOptions

        View options (e.g., replace if exists)

      Returns Promise<void>

    • Parameters

      • viewName: string

      Returns Promise<boolean>

    • Parameters

      • triggerName: string
      • tableName: string

      Returns Promise<boolean>

    • Parameters

      • sequenceName: string

      Returns Promise<number>

    • Parameters

      • sequenceName: string

      Returns Promise<boolean>

    • List all sequences in the database (MSSQL)

      Returns Promise<string[]>

      Array of sequence names

    • Enable row-level security on a table (MSSQL) Note: In SQL Server, RLS is automatically enabled when a security policy is created. This method can be used to explicitly enable RLS if needed.

      Parameters

      • tableName: string
      • Optionalschema: string

      Returns Promise<void>

    • Disable row-level security on a table (MSSQL) This drops all security policies associated with the table

      Parameters

      • tableName: string
      • Optionalschema: string

      Returns Promise<void>

    • Check if a security policy exists (MSSQL RLS)

      Parameters

      • policyName: string
      • tableName: string

      Returns Promise<boolean>

    • Parameters

      • tableName: string
      • comment: string

      Returns Promise<void>

    • Parameters

      • tableName: string
      • columnName: string
      • comment: string

      Returns Promise<void>

    • Parameters

      • tableName: string
      • indexName: string
      • fields: string[]
      • where: string
      • Optionaloptions: IndexOptions

      Returns Promise<void>

    • Parameters

      • tableName: string
      • indexName: string
      • expression: string
      • Optionaloptions: IndexOptions & { computedColumnName?: string; persisted?: boolean }

      Returns Promise<void>

    • Parameters

      • tableName: string
      • columnName: string
      • Optionaloptions: {
            startWith?: number;
            incrementBy?: number;
            minvalue?: number;
            maxvalue?: number;
            cycle?: boolean;
        }

      Returns Promise<void>

    • Parameters

      • tableName: string
      • columnName: string
      • expression: string
      • Optionaloptions: { persisted?: boolean; type?: string }

      Returns Promise<void>

    • Remove a column from a table

      Parameters

      • tableName: string

        Name of the table

      • columnName: string

        Name of the column to remove

      Returns Promise<void>

    • Get table status (MSSQL implementation)

      Parameters

      • OptionaltableName: string

      Returns Promise<Record<string, any>[]>

    • Get table create statement (MSSQL implementation)

      Parameters

      • tableName: string

      Returns Promise<string>

    • Check if a table has partitions (MSSQL implementation)

      Parameters

      • tableName: string

      Returns Promise<boolean>

    • Rename a table

      Parameters

      • oldName: string

        Current table name

      • newName: string

        New table name

      Returns Promise<void>

    • Add an index to a table

      Parameters

      • tableName: string

        Name of the table

      • indexName: string

        Name of the index

      • fields: string[]

        Fields to index

      • Optionaloptions: IndexOptions

        Index options

      Returns Promise<void>

    • Remove an index from a table

      Parameters

      • tableName: string

        Name of the table

      • indexName: string

        Name of the index

      Returns Promise<void>

    • Drop an index from a table

      Parameters

      • tableName: string

        Name of the table

      • indexName: string

        Name of the index

      • Optional_options: { ifExists?: boolean; cascade?: boolean }

      Returns Promise<void>

    • Create a SQL Server columnstore index — the storage format used for analytic/warehouse workloads.

      A clustered columnstore index (clustered: true, the default) converts the entire table to columnar storage and therefore takes NO column list — emitting one is a T-SQL syntax error, which is why the generic addIndex() (which always appends (cols)) cannot express it. A nonclustered columnstore index is layered on top of a rowstore table and DOES take an explicit column list.

      Parameters

      • tableName: string

        Table to build the columnstore index on

      • indexName: string

        Name for the index

      • Optionaloptions: {
            clustered?: boolean;
            columns?: string[];
            where?: string;
            compressionDelay?: number;
        }
        • Optionalclustered?: boolean

          Clustered (whole-table columnar) when true/omitted; nonclustered when false

        • Optionalcolumns?: string[]

          Columns for a nonclustered columnstore index (required when clustered is false; ignored when clustered)

        • Optionalwhere?: string

          Filtered-index predicate (nonclustered columnstore only, SQL Server 2016+)

        • OptionalcompressionDelay?: number

          COMPRESSION_DELAY = <n> MINUTES WITH option

      Returns Promise<void>

    • Build a LIMIT/OFFSET clause

      Parameters

      • Optionallimit: string | number

        Limit value

      • Optionaloffset: string | number

        Offset value

      • hasOrderBy: boolean = false

      Returns string

    • Build an INSERT query

      Parameters

      • tableName: string

        Table name

      • values: Record<string, unknown>

        Values to insert

      • Optionaloptions: InsertOptions

        Query options

      Returns { sql: string; values: unknown[] }

    • Execute a single-row INSERT, optionally toggling IDENTITY_INSERT for tables where an explicit value is being supplied for an IDENTITY column (see MSSQLInsertOptions.identityInsert). Unlike buildInsertQuery (which only builds SQL text), this method actually runs the query, which is required to bracket it with SET IDENTITY_INSERT ... ON/OFF.

      Parameters

      • tableName: string
      • values: Record<string, unknown>
      • Optionaloptions: MSSQLInsertOptions

      Returns Promise<QueryResult>

    • Build a SELECT query

      Parameters

      • options: MSSQLSelectOptions

        Select options

      Returns { sql: string; values: unknown[] }

    • Build an UPSERT query (insert or update on conflict)

      Parameters

      • tableName: string

        Table name

      • values: Record<string, unknown>

        Values to insert/update

      • Optionaloptions: UpsertQueryOptions

        Upsert options

      Returns { sql: string; values: unknown[] }

    • Build an increment query

      Parameters

      • tableName: string

        Table name

      • fields: string | string[] | Record<string, number>

        Fields to increment (field name or array of field names, or object with values)

      • where: WhereOptions

        Where clause

      • Optionaloptions: { by?: number }

        Query options (by: number)

      Returns { sql: string; values: unknown[] }

    • Parameters

      • column: string
      • path: string
      • asText: boolean = true

      Returns string

    • Parameters

      • _name: string
      • Optional_opts: { ifExists?: boolean; cascade?: boolean }

      Returns string

    • Parameters

      • _serverName: string
      • _user: string
      • Optional_opts: { ifExists?: boolean }

      Returns string

    • Parameters

      • _tableName: string
      • Optional_opts: { ifExists?: boolean; cascade?: boolean }

      Returns string

    • Create a foreign data wrapper (PostgreSQL)

      Parameters

      • _fdwName: string
      • Optional_options: { handler?: string }

      Returns Promise<void>

    • Drop a foreign data wrapper (PostgreSQL)

      Parameters

      • _fdwName: string
      • Optional_options: { ifExists?: boolean }

      Returns Promise<void>

    • Create a foreign server (PostgreSQL)

      Parameters

      • _serverName: string
      • _fdwName: string
      • Optional_options: { options?: Record<string, string>; ifNotExists?: boolean }

      Returns Promise<void>

    • Drop a foreign server (PostgreSQL)

      Parameters

      • _serverName: string
      • Optional_options: { ifExists?: boolean; cascade?: boolean }

      Returns Promise<void>

    • Create a foreign table (PostgreSQL)

      Parameters

      • _tableName: string
      • _columns: Record<string, { type: string }>
      • Optional_options: { serverName?: string; ifNotExists?: boolean }

      Returns Promise<void>

    • Create a user mapping for a foreign server

      Parameters

      • _serverName: string
      • _userName: string
      • Optional_options: { username?: string; password?: string }

      Returns Promise<void>

    • Drop a user mapping for a foreign server

      Parameters

      • _serverName: string
      • _userName: string
      • Optional_options: { ifExists?: boolean }

      Returns Promise<void>

    • Change the owner (authorization principal) of a table. SQL Server has no ALTER TABLE ... OWNER TO — object ownership is changed with ALTER AUTHORIZATION ON OBJECT::<table> TO <principal>, which is the T-SQL equivalent (pass SCHEMA OWNER as the principal to revert to the containing schema's owner).

      Parameters

      • newOwner: string
      • tableName: string

      Returns Promise<void>

    • Add a table constraint via ALTER TABLE ... ADD CONSTRAINT. Delegates to createConstraint/buildConstraintSql, mapping the references.fields array shape onto the references.field shape those helpers expect.

      Parameters

      • tableName: string
      • options: {
            type: "UNIQUE" | "PRIMARY KEY" | "FOREIGN KEY" | "CHECK";
            fields: string[];
            name?: string;
            references?: { table: string; fields: string[] };
            check?: string;
        }

      Returns Promise<void>

    • Drop a named table constraint via ALTER TABLE ... DROP CONSTRAINT.

      Parameters

      • tableName: string
      • constraintName: string

      Returns Promise<void>

    • Create a security policy (MSSQL Row-Level Security)

      Parameters

      • policyName: string
      • tableName: string
      • Optionaloptions: { predicate?: string; schema?: string }

      Returns Promise<void>

    • Drop a security policy (MSSQL Row-Level Security)

      Parameters

      • policyName: string
      • tableName: string

      Returns Promise<void>

    • Bulk insert records into a table.

      T-SQL caps a single INSERT ... VALUES (...), (...), ... statement at 1,000 rows (table-value constructor limit) and 2,100 parameters per batch. For batches that would exceed either limit, this splits the records into multiple INSERT statements, executed inside a single BEGIN TRANSACTION / COMMIT TRANSACTION so the whole call stays atomic (SQL Server nests transactions via @@TRANCOUNT, so this is safe even if the caller already has an outer transaction open — only the outermost COMMIT actually commits, and a ROLLBACK at any nesting level rolls back the entire transaction, mirroring the BEGIN/COMMIT/ROLLBACK TRANSACTION pattern already used by startTransaction/commitTransaction /rollbackTransaction on this dialect).

      Parameters

      • tableName: string
      • records: Record<string, any>[]
      • Optionaloptions: MSSQLBulkInsertOptions

      Returns Promise<QueryResult>

    • Add a foreign key to a table

      Parameters

      • tableName: string
      • columnName: string
      • referencedTableName: string
      • referencedColumnName: string
      • Optionaloptions: {
            name?: string;
            onDelete?: "CASCADE" | "RESTRICT" | "SET NULL" | "NO ACTION";
            onUpdate?: "CASCADE" | "RESTRICT" | "SET NULL" | "NO ACTION";
        }

      Returns Promise<void>

    • Rename a column

      Parameters

      • tableName: string
      • oldColumnName: string
      • newColumnName: string

      Returns Promise<void>

    • Create a fulltext index (MSSQL supports fulltext search).

      SQL Server fulltext indexes are always keyed off a pre-existing UNIQUE (usually the primary key) index on the table — keyIndexName must name that existing index, not the fulltext index itself (fulltext indexes don't have their own name in T-SQL). Fulltext indexes also always belong to a fulltext catalog; this method creates the catalog if it doesn't already exist before issuing CREATE FULLTEXT INDEX.

      Real syntax produced: CREATE FULLTEXT INDEX ON table (col1, col2) KEY INDEX ON

      Parameters

      • tableName: string
      • keyIndexName: string
      • fields: string[]
      • Optionaloptions: {
            parser?: string;
            comment?: string;
            catalogName?: string;
            createCatalog?: boolean;
        }
        • Optionalparser?: string
        • Optionalcomment?: string
        • OptionalcatalogName?: string

          Fulltext catalog to use/create. Defaults to 'default_fulltext_catalog'.

        • OptionalcreateCatalog?: boolean

          Set to false to skip catalog creation (e.g. it's already guaranteed to exist).

      Returns Promise<void>

    • Create a spatial index (MSSQL supports spatial indexes)

      Parameters

      • tableName: string
      • indexName: string
      • fields: string[]
      • Optionaloptions: { storage?: string; srid?: number }

      Returns Promise<void>

    • Build DROP USER and DROP LOGIN statements for MSSQL. Must drop USER first, then LOGIN (due to dependencies).

      Parameters

      • username: string
      • Optionaloptions: { ifExists?: boolean }

      Returns string

    • Build SHOW GRANTS query for MSSQL. Queries sys.database_permissions for database-level permissions.

      Parameters

      • username: string
      • Optional_host: string

      Returns string

    • Build DROP ROLE statement for MSSQL.

      Parameters

      • roleName: string
      • Optionaloptions: { ifExists?: boolean; cascade?: boolean }

      Returns string

    • Build GRANT role statement for MSSQL. Uses ALTER ROLE ADD MEMBER (MSSQL syntax).

      Parameters

      • role: string
      • to: string | string[]
      • Optionaloptions: { withAdminOption?: boolean }

      Returns string

    • Build REVOKE role statement for MSSQL. Uses ALTER ROLE DROP MEMBER (MSSQL syntax).

      Parameters

      • role: string
      • from: string | string[]
      • Optionaloptions: { cascade?: boolean }

      Returns string

    • Generate SQL for creating a database

      Parameters

      • options: {
            name: string;
            encoding?: string;
            lcCollate?: string;
            lcCtype?: string;
            template?: string;
            tablespace?: string;
            collate?: string;
            isTemplate?: boolean;
        }

        Database creation options

      Returns string

    • Generate SQL for creating a savepoint

      Parameters

      • Optionalname: string

        Savepoint name (optional, will be generated if not provided)

      Returns string

    • Create a PostgreSQL extension

      Parameters

      • _extensionName: string
      • Optional_options: unknown

      Returns Promise<void>

    • Drop a PostgreSQL extension

      Parameters

      • _extensionName: string
      • Optional_options: unknown

      Returns Promise<void>

    • Check if a PostgreSQL extension is installed

      Parameters

      • _extensionName: string

      Returns Promise<boolean>

      True if the extension is installed

    • Parameters

      • column: string
      • path: string
      • Optionalvalue: unknown
      • operator: string = '='

      Returns { sql: string; values: unknown[] }

    • Replace placeholders in SQL with actual values Supports both named (:param) and positional (?) placeholders

      Parameters

      • sql: string

        SQL string with placeholders

      • Optional_replacements: unknown[] | Record<string, unknown>

      Returns string

      SQL with placeholders replaced with escaped values

    • ST_Distance - calculate distance between two geometries (MSSQL)

      Parameters

      • geom1: string
      • geom2: string
      • Optionalsrid: number

      Returns string

    • ST_Within - check if geometry A is within geometry B (MSSQL)

      Parameters

      • geom1: string
      • geom2: string
      • Optionalsrid: number

      Returns string

    • ST_Contains - check if geometry A contains geometry B (MSSQL)

      Parameters

      • geom1: string
      • geom2: string
      • Optionalsrid: number

      Returns string

    • ST_Intersects - check if geometries intersect (MSSQL)

      Parameters

      • geom1: string
      • geom2: string
      • Optionalsrid: number

      Returns string

    • ST_DWithin - check if geometries are within a given distance (MSSQL)

      Parameters

      • geom1: string
      • geom2: string
      • distance: number
      • Optionalsrid: number

      Returns string

    • ST_AsText - convert geometry to text representation (MSSQL)

      Parameters

      • geom: string

      Returns string

    • ST_GeomFromText - create geometry from text (MSSQL)

      Parameters

      • wkt: string
      • Optionalsrid: number

      Returns string

    • Build an OPENJSON expression to shred a JSON document/array into relational rows. MSSQL has no JSON_TABLE function; OPENJSON(expr, path) WITH (...) is the real equivalent, using explicit JSON-path-per-column instead of Oracle/MySQL's COLUMNS(...) clause. Note: unlike JSON_TABLE, OPENJSON has no FOR ORDINALITY column concept, so columns flagged forOrdinality are not supported here. MSSQL: OPENJSON(expr, '$.path') WITH (name type '$.path', ...) AS alias

      Parameters

      • jsonExpression: string
      • rowPath: string
      • columns: { name: string; type?: string; path?: string; forOrdinality?: boolean }[]
      • alias: string

      Returns string

    Settings
    Member Visibility

    On This Page
    Constructors
    Properties
    Methods
    connectdisconnectgetConnectionisConnectedqueryStreamqueryescapeescapeIdquoteIdentifierquoteTablegetDatabaseVersioncreateTabledisableSystemVersioningdropTemporalTabledropTablecreatePartitionedTablecreatePartitionattachPartitiondetachPartitiondropPartitionaddPartitioncreateViewdropViewcreateMaterializedViewrefreshMaterializedViewdropMaterializedViewhasMaterializedViewcreateStoredProceduredropStoredProceduredropProcedurecreateProcedureexecuteStoredProcedurehasStoredProcedurecreateTriggerdropTriggerhasTriggercreateSequencedropSequencenextSequenceValuehasSequencelistSequencescreatePolicydropPolicyenableRLSdisableRLShasPolicycommentTablecommentColumncreatePartialIndexcreateExpressionIndexcreateIdentityColumncreateComputedColumnaddColumnremoveColumnchangeColumnshowTablesgetTableStatusgetCreateTablehasPartitionshowConstraintsshowIndexesshowViewsshowMaterializedViewsdescribeTablerenameTableaddIndexremoveIndexcreateIndexdropIndexcreateColumnstoreIndexcreateConstraintdropConstraintstartTransactioncommitTransactionrollbackTransactioncreateSchemadropSchemashowAllSchemaslistSchemasgetDataTypeSqlbuildWhereClausebuildOrderClausebuildLimitOffsetbuildInsertQueryinsertbuildUpdateQuerybuildDeleteQuerybuildSelectQuerybuildUpsertQuerybuildIncrementQuerybuildJsonExtractbuildCreateServerQuerybuildAlterServerQuerybuildDropServerQuerybuildCreateUserMappingQuerybuildAlterUserMappingQuerybuildDropUserMappingQuerybuildCreateForeignTableQuerybuildDropForeignTableQuerybuildImportForeignSchemaQuerygetServersQuerycreateForeignDataWrapperdropForeignDataWrappercreateForeignServerdropForeignServercreateForeignTablecreateUserMappingdropUserMappingchangeOwneraddConstraintremoveConstraintcreateSecurityPolicydropSecurityPolicybulkInsertaddForeignKeyrenameColumncreateFulltextIndexcreateSpatialIndexbuildCreateUserQuerybuildAlterUserQuerybuildDropUserQuerygetUsersQuerybuildGrantQuerybuildRevokeQuerybuildShowGrantsQuerybuildFlushPrivilegesQuerybuildCreateRoleQuerybuildDropRoleQuerybuildGrantRoleQuerybuildRevokeRoleQuerygetRolesQuerycreateDatabaseSQLdropDatabaseSQLcreateSavepointSQLreleaseSavepointSQLrollbackToSavepointSQLcreateExtensiondropExtensiongetExtensionshasExtensionbuildJsonQueryreplaceReplacementsformatValuestDistancestWithinstContainsstIntersectsstDWithinstAsTextstGeomFromTextbuildJsonTable