Teradata Dialect

Reference for the teradata SQL dialect (Teradata Vantage).

Source: teradata
Tests: teradata

Overview

Teradata Vantage is not a PostgreSQL or MySQL fork — its own proprietary
engine, wire protocol and SQL dialect. But the shape of that SQL where the
ORM spends most of its time (double-quoted identifiers, '...' strings with
doubled apostrophes, WHERE/GROUP BY/HAVING/JOIN as PostgreSQL's
builders already emit them, and a MERGE statement for upserts) is ANSI and
matches PostgreSQL closely enough that TeradataDialect extends
PostgresDialect and reuses its where/order/join/CTE builders — every value
still leaves those builders as a $n placeholder with its value beside it.
Nothing about the wire is PostgreSQL's, though, so the whole connection
layer, escaping, data types, pagination, table DDL and every non-relational
PostgreSQL feature is replaced or refused.

Quick start

Install

$ npm install ts-prorm-orm teradatasql

$ pnpm add ts-prorm-orm teradatasql

$ yarn add ts-prorm-orm teradatasql

$ bun add ts-prorm-orm teradatasql

Connect

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'teradata',
  host: 'td.example.com',
  database: 'sales',
  username: 'dbc',
  password: process.env.TD_PASSWORD,
});

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'teradata',
  url: 'teradata://dbc:secret@td.example.com:1025/sales',
});

A URL is folded into host, port, database, username and password before the dialect is built; any of those passed alongside it wins.

Define a model

import { DataTypes } from 'ts-prorm-orm';

const User = db.define('User', {
  id: { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  email: { type: DataTypes.STRING(255), allowNull: false, unique: true },
  name: { type: DataTypes.STRING(100) },
});

await db.sync(); // CREATE TABLE — see Generated SQL below

[!WARNING] db.sync() cannot create this table
Teradata does not support CREATE TABLE IF NOT EXISTS. Check DBC.TablesV (or catch error 3803, "table already exists") before creating.

Query

import { Op } from 'ts-prorm-orm';

await User.create({ email: 'ada@example.com', name: 'Ada' });

const page = await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

Generated SQL

Each block is the statement the model call above it hands the dialect's query(), captured with the driver stubbed out. The model layer inlines the values.

Insert, update and delete

await User.create({ email: 'ada@example.com', name: 'Ada' });
await User.update({ name: 'Grace' }, { where: { email: 'ada@example.com' } });
await User.destroy({ where: { id: 1 } });

-- insert
INSERT INTO "users" ("email", "name") VALUES ('ada@example.com', 'Ada');

-- update
UPDATE "users" SET "name" = 'Grace', "updatedAt" = TIMESTAMP '2026-09-27 05:01:42.230000' WHERE "email" = 'ada@example.com';

-- delete
DELETE FROM "users" WHERE "id" = 1;

Upsert

await User.upsert({ id: 1, email: 'ada@example.com', name: 'Ada Lovelace' });

MERGE INTO "users" AS tgt USING (SELECT 1 AS "id", 'ada@example.com' AS "email", 'Ada Lovelace' AS "name") AS src ON tgt."id" = src."id" WHEN MATCHED THEN UPDATE SET "email" = src."email", "name" = src."name" WHEN NOT MATCHED THEN INSERT ("id", "email", "name") VALUES (src."id", src."email", src."name");

upsert() also reads the row by primary key before and after the write, to return the instance.

Pagination

await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

SELECT * FROM "users" WHERE "name" LIKE 'A%' QUALIFY ROW_NUMBER() OVER (ORDER BY "id" ASC) BETWEEN 41 AND 60 ORDER BY "id" ASC;

JSON

[!NOTE] No JSON operator in the WHERE builder
DataTypes.JSON() maps to JSON, but this dialect's WHERE builder refuses Op.contains on it: Teradata does not support the @> / <@ containment operators. The condition used a PostgreSQL-only operator. On Teradata, LIKE on a NOT CASESPECIFIC column is already case-insensitive; JSON values are read with JSONExtractValue.

Transaction

await db.transaction(async (t) => {
  await User.create({ email: 'grace@example.com', name: 'Grace' }, { transaction: t });
  await User.update({ name: 'Grace Hopper' }, { where: { email: 'grace@example.com' }, transaction: t });
});

INSERT INTO "users" ("email", "name") VALUES ('grace@example.com', 'Grace');
UPDATE "users" SET "name" = 'Grace Hopper', "updatedAt" = TIMESTAMP '2026-09-27 05:01:42.230000' WHERE "email" = 'grace@example.com';
-- driver: commit() (teradatasql)

[!WARNING] No savepoints
Nested transactions need savepoints, and teradata has none. Run the work in a single transaction, or restructure it so nothing nests.

Table sampling

sample on the select builder reads a random fraction of the table, for estimates over large tables:

const sample = db.getDialectInstance()!.buildSelectQuery({
  tableName: 'users',
  sample: { method: 'BERNOULLI', percent: 10 },
});
await db.query(sample.sql);

SELECT * FROM "users" SAMPLE 0.1;

Type mapping for the portable descriptors: STRING → VARCHAR(255), auto-increment INTEGER → INTEGER, BOOLEAN → BYTEINT, DATE → TIMESTAMP(6), UUID → CHAR(36), JSON → JSON. The type comparison lines the major engines up side by side.

Connection

import { Prorm, SqlDialect } from 'ts-prorm-orm';

const orm = new Prorm({
  dialect: SqlDialect.Teradata, // or the string 'teradata'
  host: 'td.example',
  // port defaults to 1025
  username: 'dbc',
  password: process.env.TD_PASSWORD,
  database: 'sales',
  dialectOptions: { tmode: 'ANSI', logmech: 'LDAP' },
});

Or construct the dialect directly:

const d = new Prorm({
  dialect: 'teradata',
  host: 'td.example',
  username: 'dbc',
});
await d.connect();

The teradatasql module is loaded lazily with require() on the first
connect(); if it is not installed, connect() throws with an install
hint (npm install teradatasql, 64-bit Node.js 18.20+).

The driver follows the DB-API shape: connect(params) → connection,
con.cursor() → cursor, cur.execute(sql, params) with ? markers,
cur.fetchall() → row arrays, cur.description → column names. query()
rewrites the builders' $n (or :name) placeholders outside string literals
to ? markers and hands the values to the driver as real bind parameters —
nothing is inlined on the wire:

const { sql, values } = d.buildSelectQuery({ tableName: 'users', where: { name: "O'Brien", active: true } });
await d.query(sql, { replacements: values });
// executed: 'SELECT * FROM "users" WHERE "name" = ? AND "active" = ?', params: ["O'Brien", 1]

getDatabaseVersion() reads DBC.DBCInfoV (InfoKey = 'VERSION').

What differs from Postgres

TeradataDialect extends PostgresDialect (postgres) and
overrides 109 of its methods (Object.getOwnPropertyNames(TeradataDialect.prototype),
computed by the scratch test); everything else is inherited unchanged.

Escaping

Values compiled to text (not bound) are escaped with Teradata literal rules —
booleans are 1/0 (no BOOLEAN type), dates are typed TIMESTAMP '...'
literals in UTC, and bytes are a '...'XB hex literal:

d.escape("it's");                              // 'it''s'
d.escape(true);                                 // 1
d.escape(new Date(Date.UTC(2024, 4, 6, 7, 8, 9, 10)));
// TIMESTAMP '2024-05-06 07:08:09.010000'
d.escape(Buffer.from([0xde, 0xad]));            // 'DEAD'XB
d.escape([1, 2]);                               // throws: array literals

Type mapping

Types with no Teradata counterpart (ARRAY, HSTORE, ...) throw naming the
type rather than emit something Teradata rejects.

CREATE TABLE

buildCreateTableSql is pure and synchronous, and covers Teradata's table
kinds, distribution and partitioning:

d.buildCreateTableSql('sales', {
  id: { type: { key: 'BIGINT' }, allowNull: false },
  d: { type: { key: 'DATEONLY' }, allowNull: false },
}, {
  tableKind: 'SET',
  fallback: false,
  primaryIndex: ['id'],
  uniquePrimaryIndex: true,
  partitionByRange: {
    column: 'd', start: '2024-01-01', end: new Date(Date.UTC(2024, 11, 31)),
    each: { value: 1, unit: 'MONTH' }, noRange: true, unknown: true,
  },
});

CREATE SET TABLE "sales", NO FALLBACK ("id" BIGINT NOT NULL, "d" DATE NOT NULL)
  UNIQUE PRIMARY INDEX ("id")
  PARTITION BY RANGE_N("d" BETWEEN DATE '2024-01-01' AND DATE '2024-12-31'
    EACH INTERVAL '1' MONTH, NO RANGE, UNKNOWN)

• SET vs MULTISET — defaults to MULTISET: a SET table silently
  discards duplicate rows, which is not what an ORM insert means.
• PRIMARY INDEX (...) / UNIQUE PRIMARY INDEX / NO PRIMARY INDEX — the
  hash-distribution key across AMPs. NO PRIMARY INDEX requires MULTISET
  and cannot combine with a primary index.
• PARTITION BY RANGE_N(...) — partitionByRange only; a raw
  partitionBy string is refused in favor of it.
• VOLATILE / GLOBAL TEMPORARY — mutually exclusive; both default their
  ON COMMIT to PRESERVE ROWS (Teradata's own default, DELETE ROWS, would
  empty the table at the end of every auto-committed statement).
• [NO] FALLBACK.
• A PRIMARY KEY / UNIQUE column must be NOT NULL — UNIQUE on a
  nullable column throws rather than silently changing meaning.
• Identity columns use GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 NO CYCLE) instead of SERIAL.

createTable also runs each options.indexes entry through
buildCreateIndexSql and, if given, a COMMENT ON TABLE.

dropTable, showTables, describeTable

No DROP TABLE IF EXISTS — dropTable({ ifExists: true }) first checks
DBC.TablesV:

await d.dropTable('gone', { ifExists: true });
// SELECT 1 AS present FROM DBC.TablesV WHERE DatabaseName = DATABASE AND TableName = ?

showTables() / showViews() query DBC.TablesV by TableKind;
describeTable() reads DBC.ColumnsV and DBC.IndicesV.

Secondary indexes

Teradata puts the columns before ON:

d.buildCreateIndexSql('t', { name: 'ix', fields: ['a', 'b'], unique: true });
// CREATE UNIQUE INDEX "ix" ("a", "b") ON "t"

addIndex/createIndex refuse using (access methods), a non-empty where
(partial indexes), include columns, and concurrently — Teradata secondary
indexes have one kind and take their own locks.

Databases (Teradata's schemas)

Teradata has no schemas — a database/user is carved out of a parent's PERM
space:

d.buildCreateDatabaseSql({ name: 'app', from: 'dbc', permBytes: 1e9, spoolBytes: 5e8, fallback: false });
// CREATE DATABASE "app" FROM "dbc" AS PERMANENT = 1000000000, SPOOL = 500000000, NO FALLBACK

createSchema() throws and points at buildCreateDatabaseSql; dropSchema()
maps to DROP DATABASE (refusing cascade — run DELETE DATABASE first);
showAllSchemas()/listSchemas() read DBC.DatabasesV.

SELECT: TOP, QUALIFY, SAMPLE, LOCKING

Teradata has no LIMIT/OFFSET. A limit alone becomes SELECT TOP n; an
offset is expressed with QUALIFY ROW_NUMBER() OVER (ORDER BY ...), which
needs an order to be deterministic:

d.buildSelectQuery({ tableName: 't', limit: 10, order: [['id', 'ASC']] }).sql;
// SELECT TOP 10 * FROM "t" ORDER BY "id" ASC

d.buildSelectQuery({ tableName: 't', where: { a: 1 }, limit: 10, offset: 20, order: [['id', 'DESC']] }).sql;
// SELECT * FROM "t" WHERE "a" = $1 QUALIFY ROW_NUMBER() OVER (ORDER BY "id" DESC)
//   BETWEEN 21 AND 30 ORDER BY "id" DESC

d.buildSelectQuery({ tableName: 't', offset: 5, order: [['id', 'ASC']] }).sql;
// ... QUALIFY ROW_NUMBER() OVER (ORDER BY "id" ASC) > 5

An offset without an order, or a limit together with DISTINCT, throws by
name instead of reaching the parser (TOP cannot combine with DISTINCT, and
QUALIFY numbers rows before DISTINCT removes duplicates).

SAMPLE — Teradata's own row sampling, via teradataSample:

d.buildSelectQuery({ tableName: 't', teradataSample: { rows: 100 } }).sql;
// SELECT * FROM "t" SAMPLE 100

d.buildSelectQuery({
  tableName: 't',
  teradataSample: { fraction: 0.25, withReplacement: true, randomizedAllocation: true },
  order: [['id', 'ASC']],
}).sql;
// SELECT * FROM "t" SAMPLE WITH REPLACEMENT RANDOMIZED ALLOCATION 0.25 ORDER BY "id" ASC

The shared sample option (PostgreSQL TABLESAMPLE BERNOULLI) is translated
to a SAMPLE fraction:

d.buildSelectQuery({ tableName: 't', sample: { method: 'BERNOULLI', percent: 10 } }).sql;
// SELECT * FROM "t" SAMPLE 0.1

TABLESAMPLE SYSTEM (block sampling) and a REPEATABLE seed have no
Teradata equivalent and throw. SAMPLE and pagination (TOP/QUALIFY)
cannot be combined — sample in a derived table first, then page it — and
neither can combine with a UNION built by this dialect.

LOCKING ... FOR <level> — the request modifier for dirty reads, via
locking:

d.buildSelectQuery({ tableName: 't', locking: 'ACCESS', limit: 1 }).sql;
// LOCKING TABLE "t" FOR ACCESS SELECT TOP 1 * FROM "t"

locking must be one of ACCESS/READ/WRITE/EXCLUSIVE.
buildSelectQuery({ lock: true }) (PostgreSQL's FOR UPDATE/FOR SHARE)
throws — Teradata has no row-level lock clause.

Where-clause operators

The inherited PostgreSQL where-builder can emit operators Teradata does not
have (ILIKE, ->/->>/#>, @>/<@, &&, :: casts, ~/~*
regexes). buildWhereClause checks the built SQL (outside string literals and
quoted identifiers) and throws naming the operator:

d.buildWhereClause({ name: { [Op.iLike]: '%a%' } });
// throws: Teradata does not support ILIKE (case-insensitive LIKE)

In the default TERA session mode, plain LIKE on a NOT CASESPECIFIC
column is already case-insensitive, which is why ILIKE is unneeded rather
than reimplemented.

Writes

Insert — refuses RETURNING:

d.buildInsertQuery('t', { a: 1, b: new Literal('CURRENT_TIMESTAMP') });
// { sql: 'INSERT INTO "t" ("a", "b") VALUES ($1, CURRENT_TIMESTAMP)', values: [1] }

Upsert → MERGE — Teradata has no ON CONFLICT; buildUpsertQuery
(and buildInsertQuery({ upsert: true })) build a MERGE. conflictFields
is required (the ON condition must fix the target's primary index with
equality) and its columns are never updated:

d.buildUpsertQuery('t', { id: 1, name: 'x' }, { conflictFields: ['id'] });

MERGE INTO "t" AS tgt USING (SELECT $1 AS "id", $2 AS "name") AS src ON tgt."id" = src."id"
WHEN MATCHED THEN UPDATE SET "name" = src."name"
WHEN NOT MATCHED THEN INSERT ("id", "name") VALUES (src."id", src."name")

Update / delete — refuse UPDATE ... LIMIT and DELETE ... LIMIT
(narrow the WHERE clause instead); truncate maps to Teradata's fast-path
DELETE FROM t ALL:

d.buildDeleteQuery('t', {}, { truncate: true }).sql; // DELETE FROM "t" ALL

Bulk insert — one parameterized INSERT executed with a batch of row
arrays bound to ? markers in one round trip (Teradata has no multi-row
VALUES list); a Literal value in any row throws, since every row shares
one set of markers:

await d.bulkInsert('t', [{ a: 1, b: true }, { a: 2, b: false }]);
// executed: 'INSERT INTO "t" ("a", "b") VALUES (?, ?)', params: [[1, 1], [2, 0]]

Transactions

A dedicated connection with con.autocommit = false, finished with
con.commit()/con.rollback(). Only READ UNCOMMITTED and SERIALIZABLE
isolation levels are offered; anything else throws.

const tx = await d.startTransaction({ isolationLevel: 'READ UNCOMMITTED' });
// SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Teradata has no savepoints — createSavepointSQL/releaseSavepointSQL/
rollbackToSavepointSQL all throw.

ALTER TABLE

addColumn, removeColumn, renameColumn (ALTER TABLE ... RENAME x TO y), and renameTable (RENAME TABLE old TO new) work as expected.
changeColumn throws: Teradata cannot retype a column in general — ALTER TABLE ... ADD on an existing column can only widen a VARCHAR, change a
title/format, or toggle a few attributes, not change the type — so the
suggested path is add a new column, copy the data, drop the old one, rename.

Refused, by name

Caveats / not-yet-verified

• All SQL above is asserted as strings in tests/dialects/teradata.test.ts
  against a mocked teradatasql module (jest.mock('teradatasql', ..., { virtual: true })); nothing here has been run against a live Vantage
  instance in this suite.
• queryStream has no server-side cursor to lean on — teradatasql exposes
  only fetchall/fetchone on an executed request — so it fetches the full
  result and then emits rows one by one; memory use is the whole result set.
• Triggers and stored procedures exist on Teradata but use their own syntax
  (inline trigger bodies, Teradata SPL); the inherited PostgreSQL/PL-pgSQL
  builders would emit SQL Teradata cannot parse, so both throw rather than
  guess.
• hasSequence() and getExtensions() answer false/[] without a server
  round trip, since Teradata has neither.

Capabilities

Computed from the code — capabilitiesOf() (src/transaction-support.ts),
whereSupportOf() (src/query-builders/expression-render.ts),
sqlObjectCapabilitiesOf() (src/sql-objects/capabilities.ts) and the
builders — by the scratch test described in the
capability matrix.

Related reading

• All dialects
• PostgreSQL dialect — everything inherited
• Teradata Node.js driver
• Teradata SQL documentation — SQL Data Definition Language / SQL Data Manipulation Language