Aurora DSQL Dialect

$ npm install ts-prorm-orm @aws-sdk/dsql-signer

$ pnpm add ts-prorm-orm @aws-sdk/dsql-signer

$ yarn add ts-prorm-orm @aws-sdk/dsql-signer

$ bun add ts-prorm-orm @aws-sdk/dsql-signer

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

export const db = new Prorm({
  dialect: 'auroradsql',
  host: 'abc123.dsql.us-east-1.on.aws', // region is read from the host
  username: 'admin', // no password: an IAM token is minted per connection
});

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

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

export const db = new Prorm({
  dialect: 'auroradsql',
  host: 'abc123.dsql.us-east-1.on.aws',
  username: 'admin', // admin gets getDbConnectAdminAuthToken()
});

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

declare function fetchTokenFromVault(): Promise<string>;

export const db = new Prorm({
  dialect: 'auroradsql',
  host: 'abc123.dsql.us-east-1.on.aws',
  username: 'app_role',
  dialectOptions: {
    tokenProvider: async () => fetchTokenFromVault(), // takes precedence over the signer
  },
});

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 });
  // A nested transaction is a SAVEPOINT; its failure undoes only its own work.
  await db
    .transaction(async (sp) => {
      await User.destroy({ where: { id: 1 }, transaction: sp });
      throw new Error('keep the row');
    }, { transaction: t })
    .catch(() => {});
});

import { AuroraDSQLDialect } from 'prorm/dialects/auroradsql';

const dialect = new AuroraDSQLDialect({
  host: 'abc123.dsql.us-east-1.on.aws', // region is parsed from the host
  username: 'admin',                     // the default
});
await dialect.connect();

AuroraDSQLDialect.isRetryableError(err); // true for 40001 / OC000 / OC001
AuroraDSQLDialect.occConflictCode(err);  // 'OC000' | 'OC001' | null

await dialect.runTransaction(async (tx) => {
  // may run more than once — keep it idempotent apart from its DB effects
}, { maxRetries: 5, baseDelayMs: 20 });

dialect.buildCreateTableSql('orders', {
  id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
}, { initialAutoIncrement: 1000 });
// CREATE TABLE "orders" ("id" BIGINT GENERATED BY DEFAULT AS IDENTITY
//   (CACHE 65536 START WITH 1000) PRIMARY KEY)

dialect.buildCreateTableSql('t', { id: dialect.uuidPrimaryKey() });
// CREATE TABLE "t" ("id" UUID DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY)

dialect.buildAddForeignKeySql('items', 'order_id', 'orders', 'id');
// ALTER TABLE "items" ADD CONSTRAINT "items_order_id_fkey" FOREIGN KEY ("order_id")
//   REFERENCES "orders" ("id") NOT VALID
dialect.buildValidateConstraintSql('items', 'items_order_id_fkey');
// ALTER TABLE ASYNC "items" VALIDATE CONSTRAINT "items_order_id_fkey"

const jobId = await dialect.createIndexAsync('orders', {
  name: 'orders_customer_idx',
  fields: ['customer_id', { column: 'created_at', nulls: 'LAST' }],
  include: ['status'],
  where: { status: 'open' },
});
// CREATE INDEX ASYNC "orders_customer_idx" ON "orders" ("customer_id",
//   "created_at" NULLS LAST) INCLUDE ("status") WHERE "status" = 'open'

await dialect.waitForJob(jobId);  // CALL sys.wait_for_job($1)
await dialect.getJob(jobId);      // the sys.jobs row

dialect.buildCreateSequenceSql({ name: 'order_no', cache: 65536, startWith: 101 });
// CREATE SEQUENCE "order_no" CACHE 65536 START WITH 101