IBM Db2 Dialect

$ npm install ts-prorm-orm ibm_db

$ pnpm add ts-prorm-orm ibm_db

$ yarn add ts-prorm-orm ibm_db

$ bun add ts-prorm-orm ibm_db

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

export const db = new Prorm({
  dialect: 'db2',
  host: 'localhost',
  database: 'SAMPLE',
  username: 'db2inst1',
  password: process.env.DB_PASSWORD,
});

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

export const db = new Prorm({
  dialect: 'db2',
  url: 'db2://db2inst1:secret@localhost:50000/SAMPLE',
});

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 });
  // 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 { Prorm } from 'ts-prorm-orm';

const db = new Prorm({
  dialect: 'db2',
  host: 'db2host',
  port: 50000, // Db2 default; omit to use the injected default of 50000
  username: 'db2inst1',
  password: 'secret',
  database: 'SAMPLE',
});

DATABASE=SAMPLE;HOSTNAME=db2host;PORT=50000;PROTOCOL=TCPIP;UID=db2inst1;PWD=secret;

const db = new Prorm({
  dialect: 'db2',
  dialectOptions: {
    connectionString:
      'DATABASE=SAMPLE;HOSTNAME=db2host;PORT=50000;PROTOCOL=TCPIP;UID=u;PWD=p;',
  },
});

db.dialect.buildLimitOffset(10, 20);

db.dialect.buildSelectQuery({
  tableName: 'Users',
  where: { active: true },
  order: [['created_at', 'DESC']],
  limit: 10,
  offset: 20,
});

await db.dialect.createTable('Users', {
  id: { type: 'INTEGER', autoIncrement: true, allowNull: false, primaryKey: true },
  name: { type: 'STRING' },
});

await db.dialect.createSequence({
  name: 'order_seq',
  startWith: 1000,
  incrementBy: 1,
  cache: 20,
});

const next = await db.dialect.nextSequenceValue('order_seq');

db.dialect.buildInsertQuery(
  'Users',
  { name: 'Ada', email: 'a@x.io' },
  { returning: ['id', 'name'] },
);

db.dialect.buildDeleteQuery('Users', { id: 1 }, { returning: true });

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

Db2 does not support a RETURNING clause on MERGE. Re-fetch the row after the upsert instead.