Cloud SQL for PostgreSQL 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: 'cloudsqlpostgres',
  database: 'app',
  username: 'app',
  password: process.env.DB_PASSWORD,
  dialectOptions: {
    cloudSql: { instanceConnectionName: 'my-project:us-central1:my-instance' },
  },
});

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) },
  meta: { type: DataTypes.JSONB() },
});

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: 'cloudsqlpostgres',
  database: 'app',
  username: 'app',
  password: process.env.DB_PASSWORD,
  dialectOptions: {
    cloudSql: {
      instanceConnectionName: 'my-project:us-central1:my-instance',
      ipType: 'PRIVATE', // 'PUBLIC' (default) | 'PRIVATE' | 'PSC'
    },
  },
});

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

export const db = new Prorm({
  dialect: 'cloudsqlpostgres',
  database: 'app',
  username: 'app-sa@my-project.iam', // the IAM principal; no password
  dialectOptions: {
    cloudSql: {
      instanceConnectionName: 'my-project:us-central1:my-instance',
      iam: true, // automatic IAM database authentication
    },
  },
});

import { Prorm } from 'ts-prorm-orm';
import * as fs from 'fs';

export const db = new Prorm({
  dialect: 'cloudsqlpostgres',
  host: '10.0.0.5',
  database: 'app',
  username: 'app',
  password: process.env.DB_PASSWORD,
  dialectOptions: {
    cloudSql: {
      mode: 'direct',
      instanceConnectionName: 'my-project:us-central1:my-instance',
      serverCa: fs.readFileSync('server-ca.pem', 'utf8'),
    },
  },
});

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

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

await User.findAll({ where: { meta: { [Op.contains]: { plan: 'pro' } } } });

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(() => {});
});

await db.transaction(async (t) => {
  await User.findAll({ where: { name: 'Ada' }, lock: { level: 'UPDATE', skipLocked: true }, transaction: t });
});

const db = new Prorm({
  dialect: 'cloudsqlpostgres',
  database: 'app',
  username: 'app',
  password: process.env.DB_PASSWORD,
  dialectOptions: {
    cloudSql: {
      instanceConnectionName: 'my-project:us-central1:my-instance',
      ipType: 'PRIVATE', // 'PUBLIC' (default) | 'PRIVATE' | 'PSC'
    },
  },
});

import * as fs from 'fs';

const db = new Prorm({
  dialect: 'cloudsqlpostgres',
  host: '10.0.0.5',
  database: 'app',
  username: 'app',
  password: process.env.DB_PASSWORD,
  dialectOptions: {
    cloudSql: {
      serverCa: fs.readFileSync('server-ca.pem', 'utf8'),
      // per-instance CA: name the instance so its certificate can be checked
      mode: 'direct',
      instanceConnectionName: 'my-project:us-central1:my-instance',
      // or, shared / customer-managed CA: the DNS name in the certificate's SAN
      // serverName: '1a23b4cd5e67.1a2b345c6d27.us-central1.sql.goog',
      // clientCert, clientKey: for ssl_mode TRUSTED_CLIENT_CERTIFICATE_REQUIRED
    },
  },
});

const db = new Prorm({
  dialect: 'cloudsqlpostgres',
  database: 'app',
  dialectOptions: {
    cloudSql: {
      instanceConnectionName: 'p:us-central1:i',
      iam: true,
      iamPrincipal: 'app@p.iam.gserviceaccount.com', // → user 'app@p.iam'
    },
  },
});