YugabyteDB Dialect

YugabyteDB Dialect Overview YugabyteDB is a distributed SQL database whose YSQL API is forked from the PostgreSQL source. As a result it speaks the PostgreSQL wire protocol and is largely SQL-compatible with PostgreSQL. This ORM implements the dialect by extending the Postgres dialect rather than reimplementing it: YugabyteDBDialect extends PostgresDialect — connection handling, parameter binding, transactions, and most query building are inherited unchanged. It reuses the same pg driver (library === 'pg', inherited from PostgresDialect). The dialect identifies itself as name === 'yugabytedb'. The default port is 5433 (YugabyteDB's YSQL server port), not Postgres's 5432. An explicit port in your config still wins. On top of the inherited Postgres behavior, this dialect layers YugabyteDB's distributed-table DDL: tablet splitting, colocation, hash/range primary keys, and tablegroups. Source: src/dialects/yugabytedb/index.ts. Base: src/dialects/postgres/index.ts. Connection Connection options are identical to the Postgres dialect (YugabyteDBDialectOptions extends PostgresDialectOptions); the only difference is the 5433 port default applied in the constructor. You can also construct the dialect directly: YugabyteDB-specific features These are surfaced through an extended options type, YugabyteTableOptions extends TableOptions, accepted by buildCreateTableSQL() (synchronous, connection-free) and createTable(). createTable() only takes the custom DDL path when at least one YugabyteDB option is present (splitIntoTablets, splitAtValues, colocated, colocation, tablegroup, or a table-level primaryKey); otherwise it falls back to the inherited Postgres createTable(). Identifiers are double-quoted (inherited Postgres escapeId). All SQL below is the exact output of buildCreateTableSQL() / buildCreateTableGroupSQL(). SPLIT INTO n TABLETS Presplit a hash-sharded table into n tablets with splitIntoTablets: SPLIT AT VALUES Presplit a range-sharded table at explicit boundaries with splitAtValues. Each inner array is one split point's column value(s); numbers pass through, strings are single-quoted and escaped: Colocated / colocation tables Small tables can be co-located onto the database's colocation tablet to avoid the overhead of a tablet-per-table. Use colocated (older keyword) or colocation (newer keyword). If both are supplied, colocation wins. Both accept false as well, emitting WITH (colocated = false) / WITH (colocation = false). HASH-sharded / range-sharded primary keys Unlike Postgres, a YugabyteDB primary-key column carries a sharding/sort directive: HASH (hash-sharded — meaningful on leading PK columns), or ASC / DESC (range-sharded). Supply a table-level primaryKey as an array of { name, order? } columns: Notes: The order is optional; a column with no order is emitted with no directive. When a table-level primaryKey directive is present, any column-level primaryKey: true flags are stripped so the statement never emits two PRIMARY KEY clauses. A PRIMARY KEY constraint passed via options.constraints is likewise skipped when a PK clause was already produced. If no table-level primaryKey is given, columns flagged with column-level primaryKey: true are collected into a table-level PRIMARY KEY (...) clause with no sharding directive (as seen in the SPLIT INTO example above). Tablegroups CREATE TABLEGROUP groups tables that should share tablets. buildCreateTableGroupSQL() builds the statement synchronously; createTableGroup() runs it. A table joins a tablegroup via the tablegroup option. Related helpers: Clause ordering When several YugabyteDB clauses apply to one CREATE TABLE, they are emitted in this fixed order after the column list: colocation WITH (...), then TABLEGROUP, then SPLIT INTO ... TABLETS, then SPLIT AT VALUES (...). Inherited Postgres behavior Everything not listed above comes straight from PostgresDialect, unchanged: CRUD — INSERT / SELECT / UPDATE / DELETE query building, parameter binding ($1, $2, …), and result handling. Upsert — buildUpsertQuery() emits INSERT INTO ... VALUES (...) ON CONFLICT (...) DO UPDATE SET col = excluded.col. conflictFields is required (PostgreSQL cannot infer the conflict target), and an optional RETURNING clause is supported. For example, with conflictFields: ['id'] the generated SQL is ... ON CONFLICT ("id") DO UPDATE SET .... DDL — the non-YugabyteDB createTable() path, dropTable(), index creation, comments, unique keys, and sequence start values are inherited. When a YugabyteDB-specific option forces the custom DDL path, the follow-up statements (unique keys, table comment, initialAutoIncrement sequence start, and secondary indexes) are re-emitted to match the base dialect's behavior. Identifier quoting — double quotes ("col"), inherited from the Postgres escapeId ("foo" with embedded " doubled to ""). Auto-increment — an autoIncrement INTEGER column is emitted as SERIAL, matching the Postgres/CockroachDB convention. Type mapping Column types are resolved by the inherited Postgres getDataTypeSql(). The main ORM type → SQL mappings: ORM type | SQL type | ----------- | ------------------------------------------ | STRING | VARCHAR(length) (default VARCHAR(255)) | CHAR | CHAR(length) (default CHAR(1)) | TEXT | TEXT (or VARCHAR(length) for a sized value) | INTEGER | INTEGER (SERIAL when autoIncrement) | BIGINT | BIGINT (BIGSERIAL when autoIncrement)| FLOAT | REAL | DOUBLE | DOUBLE PRECISION | DECIMAL | DECIMAL(precision, scale) (default 10,0)| BOOLEAN | BOOLEAN | DATE | TIMESTAMP (or TIMESTAMP(precision)) | DATEONLY | DATE | TIME | TIME (or TIME(precision)) | BLOB | BYTEA | ENUM | ENUM('a','b',...) | JSON | JSON | JSONB | JSONB | UUID | UUID | HSTORE | HSTORE | VECTOR | VECTOR(dimensions) (requires pgvector) | INET | INET | CIDR | CIDR | MACADDR | MACADDR | ARRAY | <baseType>[] | Additional notes from the base dialect: The cross-dialect pseudo-type DATETIME (used by the model-sync layer for createdAt / updatedAt / deletedAt) is normalized to TIMESTAMP. A raw string type is passed through as-is. An unknown/object type with no matching key falls back to VARCHAR(255). Caveats CREATE INDEX ... CONCURRENTLY addIndex() is overridden to handle YugabyteDB's CONCURRENTLY divergence. In YugabyteDB, index backfill is already online by default, and CONCURRENTLY carries different semantics/restrictions — notably it cannot run inside a transaction block. Mirroring the CockroachDB dialect's approach, a requested concurrently flag is ignored (with a console.warn) rather than emitted, so the generated SQL stays safe to run in the common transactional case. All other index-building behavior is inherited from the Postgres dialect unchanged. System catalogs YugabyteDB emulates the PostgreSQL pgcatalog for compatibility, but a handful of columns/values reflect its distributed storage rather than a single-node heap. Any inherited Postgres feature that introspects pgcatalog internals may therefore behave slightly differently against a live YugabyteDB cluster. Verification status SQL generation is verified. The CREATE TABLE / CREATE TABLEGROUP grammar (tablet splitting, colocation, hash/range primary keys, tablegroups) is covered by connection-free unit tests in tests/dialects/yugabytedb.test.ts, and every SQL snippet in this document is the exact output of the corresponding builder method. Runtime behavior is not verified here. There is no CRUD/integration test against a live YugabyteDB cluster (no yugabytedb-crud.test.ts exists). Executing these statements against a real cluster — and the inherited Postgres runtime behavior over the YSQL wire — has not been exercised in this repository's test suite. Validate against a live YugabyteDB deployment before relying on runtime semantics. Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL