Skip to content

SQL Server

This page describes how ts-sql-query integrates with SQL Server, including dialect-specific behavior, configuration options, and available features. It covers the proper setup of a SQL Server connection, guidelines for connection management, and advanced behaviors such as UUID handling.

Info

To configure the database dialect, extend the appropriate database connection class when defining your connection. You must choose the correct database type to ensure that the generated SQL queries follow the dialect expected by that database.

Do not share connections between requests

A ts-sql-query connection object — along with the query runner instances passed to its constructor — represents a dedicated connection to the database.

Therefore, you must not share the same connection object between concurrent HTTP requests. Instead, create a new connection object for each request, along with its own query runners.

Even if the query runner internally uses a connection pool, the ts-sql-query connection still represents a single active connection, acquired from the pool. It must be treated as such and never reused across requests.

Usage Example

import { SqlServerConnection } from "ts-sql-query/connections/SqlServerConnection";

class DBConnection extends SqlServerConnection<'DBConnection'> { }

Warning

An empty string will be treated as a null value; if you need to allow empty string set the allowEmptyString property to true in the connection object.

Tip

SQL Server does not have a native boolean data type; ts-sql-query assumes that the boolean is represented by a bit where 0 is false, and 1 is true. All conversions are made automatically by ts-sql-query. In case you need a different way to represent a boolean, see Custom booleans values for more information.

Compatibility version

The compatibilityVersion property declares the minimum SQL Server version the generated SQL must support, encoded as the integer major * 1_000_000 + minor * 1_000 + patch — e.g. 16_000_000 for SQL Server 2022 (whose internal version is 16.0). The default is Number.POSITIVE_INFINITY (latest).

Recognized breakpoints:

  • compatibilityVersion >= 16_000_000 (SQL Server 2022): minValue(...) / maxValue(...) emit the native LEAST(a, b) / GREATEST(a, b) functions added in SQL Server 2022, instead of a IIF(a < b, a, b) emulation that evaluates each argument twice.
  • compatibilityVersion >= 17_000_000 (SQL Server 2025):
    • aggregateAsArray and aggregateAsArrayOfOneColumn emit the native JSON_ARRAYAGG / JSON_OBJECT aggregates instead of a string_agg/string_escape-based emulation. The aggregateAsArrayDistinct / aggregateAsArrayOfOneColumnDistinct variants always use the emulation regardless of compatibilityVersion, because JSON_ARRAYAGG does not accept DISTINCT.
    • substringToEnd(...) / substrToEnd(...) emit substring(x, start + 1) (relying on the now-optional length argument) instead of substring(x, start + 1, len(x) - start).
    • currentDate() emits the native CURRENT_DATE keyword introduced in SQL Server 2025, which returns a date value. On earlier versions it emits cast(getdate() as date) — also a proper date, matching the currentDate() public API contract (the previous implementation emitted getdate(), which returns a datetime with the time portion).

On older SQL Server versions, set compatibilityVersion to your actual version so the right emulation is chosen automatically. It is recommended to keep this value in sync with your real database version so future ts-sql-query releases that gate additional features on it pick the right behavior automatically.

import { SqlServerConnection } from "ts-sql-query/connections/SqlServerConnection";

class DBConnection extends SqlServerConnection<'DBConnection'> {
    protected override compatibilityVersion = 16_000_000
}

UUID management

In SQL Server, UUIDs are stored in columns of type uniqueidentifier, which preserve values in uppercase. If you prefer to convert them to lowercase during projection, you can override the transformValueFromDB method as shown below:

import { SqlServerConnection } from "ts-sql-query/connections/SqlServerConnection";

class DBConnection extends SqlServerConnection<'DBConnection'> { 
    protected override transformValueFromDB(value: unknown, type: string): unknown {
        const result = super.transformValueFromDB(value, type);
        if (result && type === 'uuid') {
            return (result as string).toLowerCase();
        }
        return result;
    }
}

Tip

If you use Prisma, this is done automatically.

Generating UUIDs

Prefer UUID v7 over UUID v4 — v7 keeps the rest of the supported databases time-ordered on the primary-key index. SQL Server's uniqueidentifier is the exception: it uses a non-byte-order comparison that ignores the leading bytes, so the chronological ordering of v7 is not preserved inside the index. Uniqueness and cross-database identifier portability are still preserved. SQL Server has no server-side v7 generator, but provides NEWID() (random) and NEWSEQUENTIALID() (which produces GUIDs that match uniqueidentifier's sort order — useful as a column DEFAULT if you accept a SQL Server-specific format instead of RFC 9562 v7). See the column types page for more context.