TiDB Dialect

TiDB Dialect Overview TiDB is a distributed, MySQL-wire-protocol-compatible NewSQL database: it speaks the MySQL client/server protocol and understands almost all of MySQL's SQL grammar, while storing data in a horizontally scalable, distributed key-value layer (TiKV). Because of that wire and grammar compatibility, this ORM's TiDBDialect extends MySQLDialect and reuses MySQL's mysql2 driver verbatim — connection pooling, identifier escaping, query execution, and most DDL/DML builders are inherited unchanged. Only the genuine TiDB grammar differences are overridden or added. Key facts (from src/dialects/tidb/index.ts): Dialect name is 'tidb'. The underlying driver library stays mysql2 (that is the actual driver in use). Default SQL port is 4000 (MySQL's is 3306). The constructor injects 4000 as a default that an explicit port still overrides: super({ port: 4000, ...config }). Storage engines: TiDB parses but effectively ignores MySQL storage-engine clauses (ENGINE=InnoDB). The engine table option is still emitted for MySQL source compatibility but has no effect on TiDB. Connection Notes: The top-level Prorm option is username (mapped internally to the mysql2 driver's user field). If port is omitted, the dialect defaults it to 4000. Extra mysql2 driver options (e.g. TLS) can be passed through dialectOptions. TiDB-specific features AUTORANDOM primary keys TiDB offers AUTORANDOM as an alternative to AUTOINCREMENT for BIGINT primary keys. It scatters generated IDs across the key space to avoid the write hot-spotting that a monotonic AUTOINCREMENT key causes on a range-partitioned distributed store. A column flag autoRandom controls it (autoRandom takes precedence over autoIncrement when both are set): autoRandom: true emits a bare AUTORANDOM. autoRandom: <number> emits AUTORANDOM(n), where n is the shard-bit count. With an explicit shard-bit count (autoRandom: 5): When autoRandom is absent, ordinary AUTOINCREMENT is emitted: SHARDROWIDBITS / PRESPLITREGIONS table options For tables that have no integer primary key (or a non-clustered PK), TiDB can shard the implicit tidbrowid and pre-split the table into multiple regions at creation time so write load is distributed immediately. Both are TiDB extensions and are not valid MySQL syntax. shardRowIdBits: n → SHARDROWIDBITS = n preSplitRegions: n → PRESPLITREGIONS = n SPLIT TABLE helper TiDB's SPLIT TABLE statement pre-splits a table's (or an index's) key range into N regions, distributing write load before data arrives. It is not standard MySQL, so it is exposed as dedicated helpers: buildSplitTableSql (pure / synchronous, returns the SQL string) and splitTable (builds and executes). TiDBSplitTableOptions: between: [lower, upper] — inclusive lower/upper bounds. Each element is emitted inside (...); pass an array for composite keys (comma-joined). regions: number — number of regions to split into. index?: string — when set, splits that index's key range (SPLIT TABLE t INDEX idx BETWEEN ...) instead of the table's row range. To split an index's range, pass index: To build and execute in one call: ALTER TABLE ADD/DROP CONSTRAINT The base MySQLDialect throws "addConstraint is not supported", but TiDB fully supports altering a table to add and drop PRIMARY KEY / UNIQUE / FOREIGN KEY / CHECK constraints, so TiDBDialect implements them (buildAddConstraintSql, buildDropConstraintSql, addConstraint, removeConstraint). Dropping uses the correct clause per constraint type: Inherited MySQL behavior TiDB is MySQL-wire compatible, so standard CRUD and most DDL are inherited from MySQLDialect and behave exactly as documented for the MySQL dialect. Verified in tests/dialects/tidb-crud.test.ts: Identifiers are backtick-quoted (escapeId('mycol') → mycol , quoteTable('sch.tbl') → sch.tbl ). Upsert — TiDB-compatible VALUES(col) form This is the one CRUD divergence. MySQL 8.0.19 introduced the row-alias INSERT form (INSERT ... VALUES (...) AS newvals ON DUPLICATE KEY UPDATE col = newvals.col), which the base MySQLDialect emits. TiDB rejects that row-alias syntax, so TiDBDialect overrides buildInsertQuery (with options.upsert) and buildUpsertQuery to emit the classic, TiDB-compatible VALUES(col) function form instead: updateOnDuplicate restricts the update set: TiDB, like MySQL, has no RETURNING clause — passing returning to buildUpsertQuery throws: Type mapping Type mapping delegates to the MySQL mapping (getDataTypeSql) for everything TiDB shares. The following are verified in tests/dialects/tidb-crud.test.ts: ORM type descriptor | Generated SQL | ------------------------------------------- | ----------------- | { key: 'STRING' } | VARCHAR(255) | { key: 'STRING', length: 100 } | VARCHAR(100) | { key: 'INTEGER' } | INT | { key: 'INTEGER', length: 8 } | BIGINT | { key: 'BIGINT' } | BIGINT | { key: 'BIGINT', unsigned: true } | BIGINT UNSIGNED | { key: 'BOOLEAN' } | TINYINT(1) | { key: 'DECIMAL', precision: 12, scale: 4 } | DECIMAL(12,4) | { key: 'DATE' } | DATETIME | { key: 'DATEONLY' } | DATE | { key: 'JSON' } | JSON | { key: 'JSONB' } | JSON | { key: 'UUID' } | CHAR(36) | { key: 'ENUM', values: ['a', 'b'] } | ENUM('a','b') | 'TEXT' (raw string) | TEXT | Rejected: spatial / GIS types TiDB implements no spatial data types, so getDataTypeSql rejects them up front (rather than letting a real TiDB server fail with an opaque parse error). The following keys throw an error matching /spatial/i: GEOMETRY, GEOGRAPHY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION. Caveats / not-yet-verified SQL generation is verified; live execution is not. All the SQL shown here is produced and asserted by the pure/synchronous build builders in tests/dialects/tidb.test.ts and tests/dialects/tidb-crud.test.ts, which run without any database connection. The actual runtime behavior of these statements against a live TiDB cluster (e.g. that SPLIT TABLE, AUTORANDOM, SHARDROWIDBITS, and PRESPLITREGIONS execute and produce the intended region splits) has not been exercised end-to-end in this test suite and would require a running TiDB server to confirm. ENGINE= is emitted but ignored. The engine table option is passed through for MySQL source compatibility; TiDB parses but ignores it, since all data lives in TiKV. SHARDROWIDBITS / AUTORANDOM interaction. These options target distinct table shapes on a live cluster (SHARDROWIDBITS applies to tables without a clustered integer PK; AUTORANDOM applies to a BIGINT PK). This dialect emits whatever is requested and does not validate that combination — a real TiDB server enforces the actual constraints. Inherited MySQL builders are assumed to behave as in the MySQL dialect; the TiDB CRUD tests re-verify the common ones (INSERT/SELECT/UPDATE/DELETE) to guard against regressions, but the full MySQL surface is documented under the MySQL dialect. Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL