Google Cloud Spanner Dialect

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

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

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

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

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

export const db = new Prorm({
  dialect: 'spanner',
  dialectOptions: {
    projectId: 'my-project',
    instanceId: 'prod-instance',
    databaseId: 'app-db',
  },
});

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

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

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

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

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

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

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

const db = new Prorm({
  dialect: 'spanner',
  dialectOptions: {
    projectId: 'my-gcp-project',   // omit to use ADC's default project
    instanceId: 'prod-instance',   // required
    databaseId: 'app-db',          // required
    // keyFilename: '/path/to/sa-key.json', // else falls back to ADC
  },
});

await db.connect();

await dialect.assertGoogleSqlDialect(); // throws if the DB is PostgreSQL-interface

await db.dialect.createTable('users', {
  id: { type: 'UUID', primaryKey: true, autoIncrement: true },
  name: { type: 'STRING', length: 100 },
});

await db.dialect.createBitReversedSequence('order_seq', { startWithCounter: 1 });

const id = await db.dialect.nextBitReversedSequenceValue('order_seq');
// or, as a column default / SQL fragment:
db.dialect.nextBitReversedSequenceValueSql('order_seq');
// => GET_NEXT_SEQUENCE_VALUE(SEQUENCE `order_seq`)

await db.dialect.createTable('Customers', {
  CustomerId: { type: 'STRING', length: 36, primaryKey: true },
  Name: { type: 'STRING', length: 200 },
});

await db.dialect.createInterleavedTable(
  'Orders',
  'Customers', // parent
  {
    CustomerId: { type: 'STRING', length: 36 },
    OrderId: { type: 'STRING', length: 36 },
    Total: { type: 'NUMBER' },
  },
  { primaryKey: ['CustomerId', 'OrderId'], onDeleteCascade: true },
);

await db.dialect.addIndex('Orders', 'idx_orders_total', ['Total'], {
  include: ['OrderId'], // STORING (`OrderId`)
});

await db.dialect.createTable('Events', {
  Id: { type: 'UUID', primaryKey: true, autoIncrement: true },
  CreatedAt: { type: 'DATE' }, // TIMESTAMP
}, {
  rowDeletionPolicy: { column: 'CreatedAt', olderThanDays: 30 },
});
// ... ROW DELETION POLICY (OLDER_THAN(`CreatedAt`, INTERVAL 30 DAY))

// Mutation API (recommended for bulk / blind writes)
await db.dialect.mutationInsert('users', [{ id: 'a', name: 'Ada' }]);
await db.dialect.mutationUpdate('users', [{ id: 'a', name: 'Ada L.' }]);
await db.dialect.mutationUpsert('users', [{ id: 'a', name: 'Ada' }]); // insert-or-update by PK
await db.dialect.mutationDelete('users', ['a']);                      // delete by PK
await db.dialect.bulkInsert('users', records);                        // uses mutationInsert

db.dialect.buildUpsertQuery('kv', { k: 'a', v: 1 });

const tx = await db.dialect.startTransaction();
try {
  const spannerTx = (tx as any).spannerTransaction;
  await spannerTx.runUpdate({ sql: 'UPDATE `users` SET `name` = @p1 WHERE `id` = @p2', params: { p1: 'Ada', p2: 'a' } });
  await db.dialect.commitTransaction(tx);
} catch (err) {
  await db.dialect.rollbackTransaction(tx);
  if (db.dialect.isRetryableError(err)) { /* retry */ }
}

const rows = await db.dialect.runReadOnlyTransaction(
  (run) => run('SELECT * FROM `users` WHERE `id` = @id', { id: 'a' }),
  { exactStaleness: { seconds: 15 } }, // lower-latency stale read
);