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: 'postgres',
  host: 'localhost',
  database: 'app',
  username: 'app',
  password: process.env.DB_PASSWORD,
});

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

export const db = new Prorm({
  dialect: 'postgres',
  url: 'postgres://app:secret@localhost:5432/app',
});

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

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

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

const db = new Prorm({
  dialect: 'postgres',
  host: '127.0.0.1',
  port: 5432, // default 5432 when omitted
  username: 'postgres',
  password: 'secret',
  database: 'app',
});

const db = new Prorm({
  dialect: 'postgres',
  host: 'db.example.com',
  username: 'app',
  password: 'secret',
  database: 'app',
  dialectOptions: {
    ssl: { rejectUnauthorized: false }, // pg `ssl` — object or boolean
    max: 40, // pool size
    idleTimeoutMillis: 10000,
    connectionTimeoutMillis: 5000,
    statementTimeout: 30000, // pg `statement_timeout`
    queryTimeout: 30000, // pg `query_timeout`
  },
});

await db.dialect.query('SET search_path TO billing, public');

const { sql, values } = db.dialect.buildInsertQuery('users', {
  name: 'Ada',
  age: 36,
});
// sql:    INSERT INTO "users" ("name", "age") VALUES ($1, $2)
// values: ['Ada', 36]

const { sql, values } = db.dialect.buildInsertQuery(
  'users',
  { name: 'Grace', email: 'grace@example.com' },
  { returning: ['id', 'created_at'] },
);
// INSERT INTO "users" ("name", "email") VALUES ($1, $2) RETURNING "id", "created_at"

db.dialect.buildDeleteQuery('sessions', { userId: 7 }, { returning: true });
// DELETE FROM "sessions" WHERE "userId" = $1 RETURNING *

const { sql, values } = db.dialect.buildUpsertQuery(
  'kv',
  { k: 'theme', v: 'dark' },
  { conflictFields: ['k'] },
);
// INSERT INTO "kv" ("k", "v") VALUES ($1, $2)
//   ON CONFLICT ("k") DO UPDATE SET "k" = excluded."k", "v" = excluded."v"

db.dialect.buildUpsertQuery(
  'users',
  { id: 1, name: 'Ada', seen_at: new Date() },
  { conflictFields: ['id'], updateOnDuplicate: ['seen_at'], returning: ['id'] },
);
// INSERT INTO "users" ("id", "name", "seen_at") VALUES ($1, $2, $3)
//   ON CONFLICT ("id") DO UPDATE SET "seen_at" = excluded."seen_at" RETURNING "id"

db.dialect.escape(['red', 'green', 'blue']);
// ARRAY["red", "green", "blue"]

// rows whose "tags" array contains both 'sql' and 'db'
const frag = db.dialect.buildArrayContains('tags', ['sql', 'db']);
const rows = await db.dialect.query(`SELECT * FROM "posts" WHERE ${frag}`);

// data #> '{profile,age}' > $2
db.dialect.buildJsonPathQuery('data', '{profile,age}', 30, '>');
// { sql: '"data" #> $1 > $2', values: ['{profile,age}', 30] }

// documents whose JSONB "meta" contains {"active": true}
const q = db.dialect.buildJsonPathQuery('meta', '', { active: true }, '@>');
// q.sql:    "meta" @> $1::jsonb
// q.values: [{ active: true }]
const rows = await db.dialect.query(
  `SELECT * FROM "docs" WHERE ${q.sql}`,
  { replacements: q.values },
);