Google Cloud Spanner Dialect

Google Cloud Spanner Dialect Overview Google Cloud Spanner is a fully-managed, horizontally-scalable, strongly-consistent distributed SQL database. Unlike the other distributed dialects in this codebase (cockroachdb is Postgres-wire, tidb is MySQL-wire), Spanner is not wire-compatible with an existing engine, so this dialect is a standalone Dialect implementation built on the official @google-cloud/spanner driver rather than an extension of another dialect. Key facts (from src/dialects/spanner/index.ts): Dialect name is 'spanner'; the driver library is @google-cloud/spanner. This dialect targets GoogleSQL, Spanner's native SQL dialect (backtick-quoted identifiers, STRING(n)/INT64 types, @pN named parameters). It does not target Spanner's PostgreSQL interface — connect() fails fast if the database was provisioned with databaseDialect: POSTGRESQL (use the postgres dialect for those). See GoogleSQL vs the PostgreSQL interface. There is no local SQL port. The dialect authenticates via Google Application Default Credentials (ADC) or an explicit service-account key and addresses a database by project / instance / database IDs, not host/port. Connection Spanner-specific options are passed through dialectOptions: For the Spanner emulator, pass emulator: true (defaults apiEndpoint to localhost:9010) alongside skipDialectCheck: true. SpannerDialectOptions (from the source): instanceId / databaseId — required; identify the Spanner database. projectId — GCP project; falls back to the ADC default project when omitted. keyFilename / credentials — explicit service-account auth. When both are omitted, the driver uses GOOGLEAPPLICATIONCREDENTIALS / ambient ADC (GCE, GKE, Cloud Run metadata server). apiEndpoint / emulator — override the endpoint / target the local emulator. queryOptions — forwarded to every database.run() (e.g. optimizer version). skipDialectCheck: true — skip the automatic GoogleSQL-dialect assertion done on connect(). Distributed SQL & consistency model Spanner shards data across servers by primary-key ranges and rebalances splits automatically — there is no user-facing PARTITION BY (all the createPartitionedTable/createPartition/attachPartition methods throw). Interleaved tables (below) are the closest Spanner-native tool for physical co-location, but they solve co-location, not logical partitioning. Every read-write transaction is externally consistent (TrueTime-ordered, effectively stronger than SERIALIZABLE) by construction. There is no isolation-level spectrum: startTransaction() accepts options.isolationLevel for interface parity but ignores it with a warning rather than pretending to map it. GoogleSQL vs the PostgreSQL interface Spanner exposes two SQL dialects. This implementation emits GoogleSQL only. On connect() it calls assertGoogleSqlDialect(), which queries the Database Admin API and throws an actionable error if the database is in POSTGRESQL mode. Pass skipDialectCheck: true to bypass this, or call it manually: Spanner-specific features Auto-generated keys — hotspot avoidance Spanner's docs explicitly discourage monotonically-increasing primary keys: they concentrate every write on the single split holding the tail of the key range (a hotspot). To keep the shared autoIncrement flag meaningful without reproducing that anti-pattern, an autoIncrement column is mapped to a random, well-distributed UUID (and a warning is logged): Note GoogleSQL declares the primary key after the column-list parentheses, not inline. A table with no primary key throws — mark a column primaryKey: true or pass options.primaryKey: ['col', ...]. Bit-reversed sequences — ordered-but-distributed keys When you need a numeric key that is roughly ordered over time but still spread across the keyspace, use a bit-reversed sequence instead of a plain counter: Interleaved tables INTERLEAVE IN PARENT physically co-locates a child table's rows with their parent row (e.g. Orders under Customers) so point look-ups and joins across the relationship stay within one storage split. The child's primary key must include the parent's primary key as a prefix: Covering (STORING) indexes and TTL addIndex's include option maps to Spanner's STORING (...) clause — a covering index that duplicates listed columns so a lookup avoids a join back to the base table. Row-TTL is expressed via a table-level row deletion policy: Spanner has no arbitrary partial-index WHERE predicate; passing where to addIndex logs a warning and is ignored (the closest analogue is NULLFILTERED). Writes — SQL DML vs the Mutation API Spanner offers two write paths, and this dialect exposes both: 1. SQL DML (INSERT/UPDATE/DELETE) — always run inside a read-write transaction. query() transparently wraps single DML statements in an auto-committing transaction, since Spanner has no autocommit single-statement DML path. 2. The Mutation API — the recommended, primary path for bulk/blind writes: it skips SQL parsing/planning and batches into one commit. Upsert — INSERT OR UPDATE INTO Spanner has no ON CONFLICT clause. The SQL upsert fallback emits GoogleSQL's INSERT OR UPDATE INTO: Only the OR UPDATE variant is wired up (GoogleSQL also has OR IGNORE / OR REPLACE, but there is no conflictAction option to select them). Prefer mutationUpsert() over this SQL form for bulk writes. GoogleSQL DML has no RETURNING clause — passing returning to buildInsertQuery/buildUpdateQuery/buildDeleteQuery logs a warning and is ignored. UPDATE and DELETE always require a WHERE (an always-true WHERE TRUE is emitted when none is given; truncate maps to DELETE FROM t WHERE TRUE since there is no TRUNCATE). Transactions startTransaction/commitTransaction/rollbackTransaction bridge the driver's callback-based database.runTransaction() into the imperative shape used elsewhere. Contention surfaces as ABORTED (gRPC code 10); use isRetryableError() to drive a retry loop (the driver also retries internally): Read-only transactions (strong or stale/bounded-staleness reads) are a distinct concept and are exposed separately, not through startTransaction(): Spanner has no SAVEPOINT — the savepoint builders all throw. Type mapping getDataTypeSql maps ORM type descriptors to GoogleSQL types: ORM type descriptor | Generated SQL | --------------------------------------- | ------------------------- | { key: 'STRING' } / { key: 'CHAR' } | STRING(255) (or length) | { key: 'TEXT' } | STRING(MAX) | { key: 'INTEGER' } / 'BIGINT' | INT64 | { key: 'FLOAT' } / 'DOUBLE' | FLOAT64 | { key: 'DECIMAL' } | NUMERIC | { key: 'BOOLEAN' } | BOOL | { key: 'DATE' } | TIMESTAMP | { key: 'DATEONLY' } | DATE | { key: 'BLOB' } | BYTES(MAX) (or length) | { key: 'UUID' } | STRING(36) | { key: 'JSON' } / 'JSONB' | JSON | { key: 'ENUM', values: [...] } | STRING(MAX) CHECK (col IN (...)) | { key: 'ARRAY', type: <t> } | ARRAY<t> | TIME, SET, RANGE, INET, CIDR, MACADDR, and HSTORE have no native equivalent and fall back to STRING(MAX) (HSTORE → JSON) with a warning. GEOMETRY throws — Spanner has no built-in geometry/geography type. Not supported / caveats Methods that throw, because the underlying concept has no GoogleSQL analogue: Schemas / namespaces — createSchema/dropSchema throw; GoogleSQL has no CREATE SCHEMA. quoteTable(name, schema) ignores schema with a warning. Databases via SQL — created/dropped through the Admin API (instance.createDatabase() / database.delete()), not DDL. Extensions, Foreign Data Wrappers, row-level security, stored procedures / procedural SQL — all throw; these are Postgres/relational concepts Spanner lacks. Move procedural logic to the application layer. CREATE USER / GRANT ... TO <user> — Spanner access is governed by Cloud IAM plus GoogleSQL database roles. CREATE ROLE / DROP ROLE / GRANT ROLE ... TO ROLE builders exist; per-user grants throw. RENAME TABLE / RENAME COLUMN — not supported; add a new column/table, backfill, drop the old one. Primary keys are fixed at CREATE TABLE time (addConstraint with PRIMARY KEY throws). DROP TABLE/DROP VIEW/DROP INDEX have no CASCADE — drop dependent interleaved child tables and indexes explicitly first. Other behavioral notes: escape() and Date values. escape() has no column-type context, so a JS Date is always emitted as a TIMESTAMP literal. GoogleSQL will reject that for a DATE (DATEONLY) column. Pass a 'YYYY-MM-DD' string to DATEONLY columns instead of a Date. OFFSET requires LIMIT. GoogleSQL has no bare OFFSET n; when only an offset is given, a very large LIMIT is emitted to satisfy the grammar. Backups are an Admin API concept (createBackup/listBackups/getBackup/ deleteBackup/restoreDatabase), not BACKUP DATABASE DDL; these helpers await the long-running operations to completion. Window functions and (recursive) CTEs are standard GoogleSQL syntax and pass straight through (buildWindowFunction, buildRecursiveCteQuery). Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL