Skip to content

MySQL

This page describes how ts-sql-query integrates with MySQL, including dialect-specific behavior, configuration options, and available features. It covers the proper setup of a MySQL 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 { MySqlConnection } from "ts-sql-query/connections/MySqlConnection";

class DBConnection extends MySqlConnection<'DBConnection'> { }

UUID strategies

ts-sql-query provides different strategies to handle UUID values in MySQL. These strategies control how UUID values are represented in JavaScript and stored in the database.

  • 'binary' (default strategy): UUIDs are treated as strings and stored using the native BINARY(16) column type via the UUID_TO_BIN / BIN_TO_UUID functions. This requires MySQL version 8 or higher.
  • 'string': UUIDs are treated as strings and stored in character-based columns such as CHAR(36), VARCHAR(36), or TEXT. This option can be used with older MySQL versions or when avoiding the BINARY type.

You can configure the strategy by overriding the uuidStrategy field in your connection class:

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

class DBConnection extends MySqlConnection<'DBConnection'> {
    protected override uuidStrategy = 'string' as const
}

Generating UUIDs

Prefer UUID v7 over UUID v4. With the 'binary' strategy on MySQL 8+, the bytes are stored in canonical order, so a UUID v7 keeps its chronological ordering on the primary-key index. MySQL has no server-side v7 generator (its built-in UUID() returns v1, which does not preserve sortability under the canonical byte order of the 'binary' strategy), so v7 must be generated in the application — the only exception to the general rule of preferring database-side generation that is laid out in the column types page.

Compatibility version

The compatibilityVersion property declares the minimum MySQL version the generated SQL must support, encoded as the integer major * 1_000_000 + minor * 1_000 + patch — e.g. 8_000_019 for MySQL 8.0.19, 5_007_000 for MySQL 5.7. The numeric separator _ is for readability only (8_000_019 === 8000019). The default is Number.POSITIVE_INFINITY (latest), so every supported feature is emitted.

Patch precision matters here because MySQL 8.0 has a continuous-delivery history: from 8.0.0 (April 2018) through 8.0.34 (July 2023), patch releases added new dialect features (CTE, LATERAL, the row alias for ON DUPLICATE KEY UPDATE, INTERSECT/EXCEPT, etc.). The Innovation Release model that started with 8.1.0 is cumulative with these features.

You can set this to your real database version (whatever it is) regardless of whether ts-sql-query currently uses it — extra granularity is harmless and future-proof.

Recognised breakpoints (with the default Number.POSITIVE_INFINITY every breakpoint below is enabled — the list reads as the bar you need to clear to keep each feature):

  • >= 8_000_019: target MySQL 8.0.19+. The row alias syntax INSERT ... AS _new_ ON DUPLICATE KEY UPDATE col = _new_.col is emitted to reference values being inserted (added in 8.0.19; the legacy VALUES(col) function reference was deprecated in 8.0.20).
  • >= 8_000_000: target MySQL 8.0+. The WITH clause is used and recursive queries are supported.
  • < 8_000_000: target MySQL 5. The WITH clause is not emitted — the inner query is inlined in the FROM instead — recursive queries throw at query-build time, and the legacy VALUES(col) reference is used inside ON DUPLICATE KEY UPDATE.
import { MySqlConnection } from "ts-sql-query/connections/MySqlConnection";

class DBConnection extends MySqlConnection<'DBConnection'> {
    protected override compatibilityVersion = 5_007_000
}