Google BigQuery Dialect

Overview

BigQuery is Google Cloud's serverless
columnar data warehouse. It has no servers to size, no connections, and no
sessions: every statement is submitted as an independent REST job, billed by
the bytes it scans.

Property	Value
Dialect name	`bigquery`
Extends	nothing — implements `Dialect` directly
Driver	`@google-cloud/bigquery`
Cloud SDK	`@google-cloud/bigquery`
Default port	none — Google API
Local runtime	none — hosted service only

That model breaks most of what the OLTP dialects in this codebase assume, so
BigQueryDialect (src/dialects/bigquery/index.ts) is a standalone Dialect
implementation rather than a subclass of anything. It speaks GoogleSQL — the
same language family as Cloud Spanner — through the official
@google-cloud/bigquery driver.

What is genuinely different, and what it means for generated SQL:

Backtick identifiers, and a backtick cannot be escaped inside one, so
escapeId() throws on one instead of emitting something injectable.

dataset.table naming. A "schema" is a dataset; a "database" is a GCP
project. CREATE DATABASE throws.

No enforced constraints. PRIMARY KEY and FOREIGN KEY exist only as
NOT ENFORCED optimizer hints. UNIQUE and CHECK do not exist at all, and
every path that would emit one throws.

No indexes. Partitioning and clustering replace them; addIndex,
createIndex, removeIndex and dropIndex all throw. CREATE SEARCH INDEX
and CREATE VECTOR INDEX are real and are exposed under their own names.

MERGE for upserts. There is no ON CONFLICT.

No RETURNING. DML returns an affected-row count and nothing else.

UPDATE/DELETE require a WHERE — WHERE TRUE is emitted when none is
given.

Bare UNION is a syntax error. GoogleSQL demands UNION ALL /
UNION DISTINCT, EXCEPT DISTINCT, INTERSECT DISTINCT.

SELECT * EXCEPT (...) makes attributes.exclude expressible directly.

String literals use backslash escapes (\'), not doubling — 'it''s'
parses as two adjacent literals in GoogleSQL and fails.

Transactions cannot span statements, so startTransaction() throws.

Key facts (from the source):

Dialect name is 'bigquery'; the driver library is
'@google-cloud/bigquery', required lazily inside connect().

Pagination is LIMIT n [OFFSET m]; OFFSET is a modifier on LIMIT, so a
standalone offset emits LIMIT 9223372036854775807 OFFSET m.

Quick start

Install

$ npm install ts-prorm-orm @google-cloud/bigquery

$ pnpm add ts-prorm-orm @google-cloud/bigquery

$ yarn add ts-prorm-orm @google-cloud/bigquery

$ bun add ts-prorm-orm @google-cloud/bigquery

Connect

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

export const db = new Prorm({
  dialect: 'bigquery',
  dialectOptions: {
    projectId: 'my-project',
    dataset: 'analytics',
    location: 'US',
  },
});

Define a model

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

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

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

The key has no autoIncrement: the engine has no sequence or identity column the dialect will emit, so the application supplies id. email is not declared unique: the engine has no enforced UNIQUE constraint. timestamps: false leaves out createdAt/updatedAt: with them, sync() gives each a DATETIME DEFAULT CURRENT_DATETIME() NOT NULL column.

Query

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

await User.create({ id: 1, 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.

Create table

db.sync() for the model in the Quick start:

CREATE TABLE

Insert, update and delete

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

Upsert

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

MERGE statement

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,
});

JSON

A dotted key reads a field of the JSON column:

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

await User.findAll({ where: { 'meta.plan': 'pro' } });

Transaction

[!CAUTION] No transactions
startTransaction() is not supported for the bigquery dialect: a BigQuery transaction exists only inside a single multi-statement script job. Each query() is an independent job, so a BEGIN TRANSACTION issued by itself would neither hold a transaction open nor be committable by a later call. Use runTransactionScript([sql, sql, …]), which submits BEGIN TRANSACTION / … / COMMIT TRANSACTION as one job.

Table sampling

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

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

Type mapping for the portable descriptors: STRING → STRING, auto-increment INTEGER → INT64, BOOLEAN → BOOL, DATE → TIMESTAMP, UUID → STRING, JSON → JSON. The type comparison lines the major engines up side by side.

Connection

There is no host or port. Authentication is Google Application Default
Credentials (ADC) unless keyFilename / credentials are given.

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

const db = new Prorm({
  dialect: 'bigquery',
  dialectOptions: {
    projectId: 'my-project',   // billing project; falls back to the ADC default
    dataset: 'analytics',      // default dataset for unqualified table names
    location: 'US',            // must match the dataset's location
    maximumBytesBilled: 10 * 1024 ** 3, // guard rail: fail a runaway scan
  },
});

await db.connect();

BigQueryDialectOptions:

projectId — project jobs are billed to. dataProjectId overrides the
project used to qualify table names when the data lives elsewhere.

dataset — default dataset (database is accepted as an alias). Set on every
job as defaultDataset and used by quoteTable().

location — job location; must match the dataset's.

keyFilename / credentials — explicit service-account auth; otherwise ADC.

apiEndpoint, clientOptions — forwarded to the BigQuery constructor.

qualifyTablesWithProject — emit `project`.`dataset`.`table`. Off by
default, so cross-project reads are not broken by a hardcoded billing project.

maximumBytesBilled — fail a job that would scan more than this. On BigQuery
a runaway query costs money rather than time; this is worth setting.

jobTimeoutMs, labels — applied to every job (labels for cost attribution).

validateOnConnect (default true) — runs a zero-byte SELECT 1 so auth and
permission failures surface at connect() rather than mid-migration.

wrapIntegers — how the driver returns INT64 values that exceed
Number.MAX_SAFE_INTEGER.

disconnect() clears the client; there is no socket to close, because each
statement already completed as its own job.

Tables: partitioning and clustering instead of indexes

await db.dialect.createTable(
  'events',
  {
    event_id: { type: 'UUID', primaryKey: true, autoIncrement: true },
    ts:       { type: 'DATE' },        // → TIMESTAMP
    user_id:  { type: 'STRING' },
    payload:  { type: 'JSON' },
  },
  {
    partition: { type: 'time', column: 'ts', columnType: 'TIMESTAMP', granularity: 'DAY' },
    clusterBy: ['user_id'],
    requirePartitionFilter: true,
    description: 'raw events',
  },
);

Notes:

The primary key is written at table level with NOT ENFORCED. BigQuery
rejects one declared without it, and a composite key can only be written that
way. Nothing enforces uniqueness.

autoIncrement on a STRING/UUID column becomes DEFAULT GENERATE_UUID() (a
random key, not a counter) and warns. On a numeric column it throws —
BigQuery has no IDENTITY, no AUTO_INCREMENT and no sequences, so quietly
turning an INT64 key into a string one would be worse than failing.

clusterBy takes at most four columns; a fifth throws.

require_partition_filter rejects queries that do not filter on the partition
column — the most effective guard against a surprise five-figure scan.

Partition specs: { type: 'ingestion' } (_PARTITIONDATE), { type: 'time' }
(DATE_TRUNC / TIMESTAMP_TRUNC / DATETIME_TRUNC chosen from columnType;
daily on a DATE column is the bare column),

{ type: 'range', column, start,
end, interval }

(RANGE_BUCKET(GENERATE_ARRAY(...))), or
{ type: 'expression' } for a hand-written one.

createPartitionedTable, createPartition, attachPartition,
detachPartition and dropPartition all throw: BigQuery partitions are
implicit and are never named, declared, attached or detached.

Search and vector indexes

await db.dialect.createSearchIndex('logs', 'idx_logs', ['message']);
// CREATE SEARCH INDEX `idx_logs` ON `analytics`.`logs`(`message`)

await db.dialect.createVectorIndex('docs', 'idx_vec', 'embedding', { distanceType: 'COSINE' });
// CREATE VECTOR INDEX `idx_vec` ON `analytics`.`docs`(`embedding`)
//   OPTIONS(index_type = 'IVF', distance_type = 'COSINE')

createFulltextIndex() routes to CREATE SEARCH INDEX, which is the honest
equivalent — it is what makes SEARCH(table, 'term') fast. showIndexes()
reports search indexes from INFORMATION_SCHEMA.SEARCH_INDEXES.

Writes

db.dialect.buildInsertQuery('users', { id: 'a', name: 'Ada' });
// INSERT INTO `analytics`.`users` (`id`, `name`) VALUES (?, ?)

db.dialect.buildUpdateQuery('users', { name: 'Ada L.' }, {});
// UPDATE `analytics`.`users` SET `name` = ? WHERE TRUE

RETURNING throws on INSERT, UPDATE, DELETE and MERGE. Re-SELECT
if you need the row back.

limit on UPDATE/DELETE throws. GoogleSQL has no such clause, and
ignoring the option would change more rows than the caller asked for.

truncate: true maps to TRUNCATE TABLE, which BigQuery does have.

bulkInsert() emits one multi-row INSERT, padding a ragged record set with
NULL so the column list stays consistent. BigQuery meters DML per table,
so a high-rate load should use streamInsert() (the tabledata.insertAll
API, which bypasses the DML quota at the cost of streaming-buffer semantics)
or a load job.

Upsert — `MERGE`

db.dialect.buildUpsertQuery('users', { id: 'a', name: 'Ada' }, { conflictFields: ['id'] });

conflictFields is required. There is no unique constraint to infer it
from, and matching on every column would never find an existing row — every
upsert would insert a duplicate.

Reads

db.dialect.buildSelectQuery({
  tableName: 'users',
  attributes: { exclude: ['password'] },
});
// SELECT * EXCEPT (`password`) FROM `analytics`.`users`

QUALIFY is supported as a raw expression on the select options, for
filtering on a window function without a wrapping subquery.

union / unionType translate to GoogleSQL's set operators: 'UNION' becomes
UNION DISTINCT, 'EXCEPT' becomes EXCEPT DISTINCT, 'INTERSECT' becomes
INTERSECT DISTINCT. Each operand is parenthesised, because GoogleSQL rejects
a set-operation input carrying its own ORDER BY/LIMIT otherwise.

TABLESAMPLE SYSTEM (n PERCENT) is supported; BERNOULLI and
REPEATABLE(seed) throw — BigQuery has neither.

lock (SELECT … FOR UPDATE) throws: BigQuery uses snapshot isolation and
has no row locks.

distinctOn throws, pointing at the
QUALIFY ROW_NUMBER() OVER (PARTITION BY …) = 1 idiom that does the same job.

Operators without a GoogleSQL spelling are translated rather than passed
through: $iLike → LOWER(col) LIKE LOWER(?), $startsWith/$endsWith →
STARTS_WITH/ENDS_WITH, $substring → STRPOS(...) > 0, $regexp →
REGEXP_CONTAINS.

An empty $in compiles to FALSE and an empty $notIn to TRUE, because
IN () is a syntax error.

order: random() renders RAND().

Transactions

BigQuery transactions live inside a single multi-statement script job.
BEGIN TRANSACTION submitted as a job of its own commits nothing and leaves
nothing open, so startTransaction(), commitTransaction() and
rollbackTransaction() all throw, as do the savepoint builders. Passing a
transaction to query() throws too.

await db.dialect.runTransactionScript([
  'DELETE FROM `analytics`.`staging` WHERE TRUE',
  'INSERT INTO `analytics`.`staging` SELECT * FROM `analytics`.`incoming`',
]);

Type mapping

ORM type descriptor	Generated SQL
`STRING` / `CHAR` / `TEXT` / `UUID`	`STRING`
`INTEGER` / `BIGINT` / `NUMBER`	`INT64`
`FLOAT` / `DOUBLE`	`FLOAT64`
`DECIMAL(p, s)`	`NUMERIC(p,s)`, or `BIGNUMERIC` when p>38 or s>9
`BOOLEAN`	`BOOL`
`DATE`	`TIMESTAMP`
`DATEONLY`	`DATE`
`TIME`	`TIME`
`BLOB`	`BYTES`
`JSON` / `JSONB` / `HSTORE`	`JSON`
`GEOMETRY` / `GEOGRAPHY`	`GEOGRAPHY`
`ARRAY(<t>)`	`ARRAY<t>`
`RANGE`	`RANGE<DATE\|DATETIME\|TIMESTAMP>`
`ENUM`	`STRING` (with a warning — the value set is not enforced anywhere)

Deliberate refusals in the type mapper:

ARRAY with no element type throws — GoogleSQL has no bare ARRAY.

ARRAY<ARRAY<…>> throws — BigQuery cannot nest repeated fields directly.

RANGE of anything but DATE/DATETIME/TIMESTAMP throws.

SET throws — mapping it to ARRAY<STRING> would silently drop both the
value whitelist and the deduplication SET implies.

No VARCHAR(n). BigQuery's STRING is unbounded and a declared length is
not part of the storage model, so STRING(100) in a model becomes STRING.
(BigQuery does accept the parameterized STRING(100), but that turns a length
the rest of this ORM treats as advisory into a hard insert-time rejection, so
the same model would behave differently here than everywhere else.)

Escaping caveats

escape() has no column-type context. A JS Date is always emitted as a
TIMESTAMP literal, which BigQuery rejects for a DATE column — pass a
'YYYY-MM-DD' string to DATEONLY columns. A JS array or object becomes a
JSON literal, not an ARRAY/STRUCT one; use arrayLiteral() /
structLiteral() (or a Literal) for repeated and struct columns.

escapeId() throws on a backtick, backslash, newline or NUL. There is no
escape for them inside a backtick-quoted identifier, so passing one through
would close the quote early.

Prefer passing values as positional ? replacements — those become real
BigQuery query parameters and never reach the SQL text.

Not supported / caveats

Methods that throw, because the concept has no BigQuery analogue:

Constraints — UNIQUE and CHECK anywhere (column, table constraint,
uniqueKeys); ON DELETE/ON UPDATE referential actions (an unenforced
foreign key can never honour them).

Indexes — addIndex, createIndex, removeIndex, dropIndex,
createSpatialIndex.

Partition DDL — createPartitionedTable and the partition
create/attach/detach/drop methods.

changeColumn — BigQuery's ALTER COLUMN only widens a type, sets or
drops a default, drops NOT NULL, and sets a description; it cannot narrow a
type or add NOT NULL. Use setColumnDataType(), setColumnDefault(),
dropColumnDefault(), dropColumnNotNull(), or rebuild with
CREATE OR REPLACE TABLE … AS SELECT. Adding a NOT NULL column with no
default also throws — existing rows would have no value for it.

CASCADE on DROP TABLE / DROP VIEW (BigQuery has none;

DROP SCHEMA
… CASCADE

is supported).

Transactions and savepoints — see above.

CREATE DATABASE / DROP DATABASE — a project, not SQL.

Extensions and Foreign Data Wrappers — Postgres concepts. External data
is read through external tables and BigLake connections.

changeOwner, SQL users and SQL roles — identity is Cloud IAM.
buildGrantQuery/buildRevokeQuery throw because BigQuery's GRANT takes
IAM roles (roles/bigquery.dataViewer), which do not correspond one-to-one
with SQL privileges; use buildGrantRolesQuery() / buildRevokeRolesQuery()
for the real DCL.

What is implemented beyond the base interface: runTransactionScript(),
streamInsert(), createSearchIndex/dropSearchIndex,
createVectorIndex/dropVectorIndex, buildCreateTableAsSelect(),
setTableOptions(), materialized views (including
CALL BQ.REFRESH_MATERIALIZED_VIEW(...)), row access policies for row-level
security, stored procedures (CREATE PROCEDURE … BEGIN … END, CALL),
buildSafeCast(), arrayLiteral(), structLiteral() and buildJsonQuery()
(JSON_VALUE(col, '$.path')).

queryStream() uses the driver's createQueryStream() rather than the shared
LIMIT/OFFSET paginating helper: paging by re-running the query would pay for
the whole scan once per page and, without an ORDER BY, would not guarantee
stable pages.

Verification status

SQL-generation verified. tests/dialects/bigquery.test.ts (87 assertions,
no connection opened) pins:

Backtick quoting, the identifier rejections, and dataset / project.dataset
/ already-dotted table paths.

Backslash string escaping (explicitly not '' doubling), typed TIMESTAMP
/ FROM_BASE64 / JSON literals, NaN/Infinity spellings, and the
arrayLiteral/structLiteral helpers.

The full type-mapping table plus every type-level refusal.

CREATE TABLE clause order, table-level PRIMARY KEY … NOT ENFORCED,
composite keys, unenforced foreign keys, the autoIncrement split, the
clustering cap, and the UNIQUE/CHECK/index/uniqueKeys refusals.

Every partition expression form, and the partition-DDL refusals.

ALTER TABLE grammar (ADD/DROP/RENAME COLUMN, the four supported
ALTER COLUMN edits) and the changeColumn refusal.

Index refusals, CREATE SEARCH INDEX, CREATE VECTOR INDEX.

WHERE operator translation, the empty-IN constants, ORDER BY shapes,
RAND(), and that a standalone OFFSET never appears without a LIMIT.

SELECT assembly, * EXCEPT (...), QUALIFY, CTEs, TABLESAMPLE, and that
no bare UNION is emitted.

INSERT/bulk INSERT/UPDATE/DELETE/TRUNCATE/MERGE shapes and
parameter order, and the RETURNING and LIMIT refusals.

Transaction refusals and the multi-statement script builder.

Dataset, view, materialized-view, procedure and row-access-policy DDL.

The IAM-shaped GRANT/REVOKE, and that the SQL-privilege form refuses.

Not verified against a live warehouse. No statement here has been executed
against a real BigQuery project. @google-cloud/bigquery is not installed in
this repo and there is no mock for it, so connect(), query(), queryStream(),
streamInsert() and the INFORMATION_SCHEMA introspection queries
(showTables, showViews, showConstraints, showIndexes, describeTable,
hasStoredProcedure) are unexercised — only the SQL they would submit is
reviewed, and the driver-shape assumptions (createQueryJob,
job.getQueryResults(), statistics.query.numDmlAffectedRows,
createQueryStream, dataset().table().insert()) are untested. Treat the SQL
as verified-by-shape and validate against your own project before relying on it
in production. Newer BigQuery surface area used here — unenforced key
constraints, column DEFAULTs, RENAME COLUMN, search and vector indexes — may
also need a current region/edition to be available.

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.

Capability	`bigquery`
Transactions atomic	yes
Savepoints	no — a nested `transaction()` throws
Isolation	up to `REPEATABLE READ`
Transaction modes	read-only no; deferrable no; constraint deferral no
RETURNING	refused — RETURNING on INSERT is not supported for the bigquery dialect: BigQuery DML returns an affected-row count and nothing else. Re-SELECT the rows after the stateme
Upsert	`MERGE INTO … WHEN MATCHED / WHEN NOT MATCHED`
JSON column	`JSON` (`JSONB` → `JSON`)
Vector column	`ARRAY<FLOAT64>`
Array column	`ARRAY<INT64>`
WHERE builder	38 operator keys; `$and`/`$or`/`$not`; `$exists`: yes; `$in` subquery: yes; expression operands: yes
SQL objects	family `bigquery`: triggers no; procedures yes; functions `expressionOnly`; materialized views native; sequences no; enums `check`; RLS no
Streaming	`queryStream()` present; `LISTEN` absent; `createExtension()` present

Testing

tests/dialects/bigquery.test.ts exercises the dialect with a mocked driver, so nothing touches a server.

All dialects — what each engine supports

Cloud Spanner — the other GoogleSQL dialect in this codebase

Data types — how each type maps to this engine

Querying — how a FindOptions becomes SQL

Google BigQuery Dialect

Overview#

Quick start#

Install#

Connect#

Define a model#

Query#

Generated SQL#

Create table#

Insert, update and delete#

Upsert#

Pagination#

JSON#

Transaction#

Table sampling#

Connection#

Tables: partitioning and clustering instead of indexes#

Search and vector indexes#

Writes#

Upsert — MERGE#

Reads#

Transactions#

Type mapping#

Escaping caveats#

Not supported / caveats#

Verification status#

Capabilities#

Testing#

Related reading#