Spanner (PostgreSQL interface) Dialect

$ npm install ts-prorm-orm pg

$ pnpm add ts-prorm-orm pg

$ yarn add ts-prorm-orm pg

$ bun add ts-prorm-orm pg

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

export const db = new Prorm({
  dialect: 'spannerpg',
  host: 'localhost', // PGAdapter
  database: 'my-database',
});

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

# sidecar: java -jar pgadapter.jar -p my-project -i my-instance -d my-database
docker run -d -p 5432:5432 \
  -v $HOME/key.json:/key.json \
  gcr.io/cloud-spanner-pg-adapter/pgadapter \
  -p my-project -i my-instance -d my-database -c /key.json -x

const d = new SpannerPostgresDialect({ database: 'my-database' });
// host 'localhost', port 5432; username/password are not used for authorization

db.dialect.buildCreateTableStatements(
  'albums',
  {
    singer_id: { type: DataTypes.BIGINT, primaryKey: true },
    album_id: { type: DataTypes.BIGINT, primaryKey: true, autoIncrement: true },
    title: { type: DataTypes.STRING(200), unique: true },
    created_at: { type: DataTypes.DATE },
  },
  {
    interleave: { parent: 'singers', onDelete: 'CASCADE' },
    ttl: { days: 30, column: 'created_at' },
  },
);
// CREATE TABLE "albums" ("singer_id" bigint NOT NULL,
//   "album_id" bigint GENERATED BY DEFAULT AS IDENTITY (BIT_REVERSED_POSITIVE) NOT NULL,
//   "title" varchar(200), "created_at" timestamptz, PRIMARY KEY ("singer_id", "album_id"))
//   INTERLEAVE IN PARENT "singers" ON DELETE CASCADE TTL INTERVAL '30 days' ON "created_at"
// CREATE UNIQUE INDEX "albums_title_key" ON "albums" ("title")

db.dialect.buildCreateIndexSql('albums', 'by_title', ['singer_id', 'title'], {
  include: ['year'],
  interleaveIn: 'singers',
  where: { title: { [Op.ne]: null } },
});
// CREATE INDEX "by_title" ON "albums" ("singer_id", "title") INCLUDE ("year")
//   INTERLEAVE IN "singers" WHERE "title" IS NOT NULL

db.dialect.buildCreateSequenceSql({ name: 'ids', skipRange: [1, 1000], startCounterWith: 50 });
// CREATE SEQUENCE "ids" BIT_REVERSED_POSITIVE SKIP RANGE 1 1000 START COUNTER WITH 50
await db.dialect.nextSequenceValue('ids'); // SELECT nextval('ids')