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

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

export const db = new Prorm({
  dialect: 'firebird',
  url: 'firebird://SYSDBA:secret@localhost:3050/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) },
});

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 orm = new Prorm({
  dialect: 'firebird',
  host: 'localhost',
  port: 3050, // default for the Firebird dialect; may be omitted
  database: 'app',
  username: 'sysdba',
  password: 'masterkey',
});

import { FirebirdDialect, createFirebirdDialect } from 'prorm/dialects/firebird';

const dialect = new FirebirdDialect({ host: 'localhost', database: 'app' });
// or:
const dialect2 = createFirebirdDialect({ host: 'localhost', database: 'app' });

await User.findAll({ where: { name: 'Ada' }, lock: 'UPDATE', transaction: t });

Firebird does not support SKIP LOCKED with a UPDATE lock. Remove lock.skipLocked, or use a lock level that supports it.

Cannot render WHERE condition at tags.$contains for firebird: the operator '$contains' is not implemented by its WHERE builder. The statement was not sent, because running it without the condition would match rows it should not.

dialect.buildSelectQuery({ tableName: 'users', limit: 10, offset: 20 });

dialect.buildSelectQuery({ tableName: 'users', limit: 5 });

dialect.buildSelectQuery({
  tableName: 'users',
  limit: 3,
  cte: [{ name: 'recent', query: 'SELECT id FROM logs' }],
});
// The main SELECT gets `FIRST 3`; the CTE's inner SELECT does not.

dialect.buildUpdateQuery('users', { name: 'Bob' }, { active: true }, { limit: 5 });
// ... ROWS 5   (no LIMIT)

dialect.buildDeleteQuery('users', { active: false }, { limit: 3 });
// ... ROWS 3   (no LIMIT)

dialect.getDataTypeSql({ key: 'INTEGER', autoIncrement: true });
// "INTEGER GENERATED BY DEFAULT AS IDENTITY"

dialect.getIdentityColumnSql('id', { primaryKey: true });
// '"id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY'

dialect.getIdentityColumnSql('id', { type: 'BIGINT' });
// '"id" BIGINT GENERATED BY DEFAULT AS IDENTITY'

dialect.buildCreateTableSQL('users', {
  id: { type: { key: 'INTEGER' }, autoIncrement: true, primaryKey: true },
  name: { type: { key: 'STRING', length: 100 }, allowNull: false },
});

dialect.createGenerator('order_gen');
// 'CREATE SEQUENCE "order_gen"'

dialect.buildUpsertQuery('users', { id: 1, name: 'Ann' }, { conflictFields: ['id'] });

dialect.buildUpsertQuery('users', { id: 1, name: 'Ann' });

dialect.buildUpsertQuery('users', { id: 1, name: 'Ann' },
  { conflictFields: ['id'], returning: ['id'] });
// ... MATCHING ("id") RETURNING "id"

dialect.buildInsertQuery('users', { id: 1, name: 'Ann' },
  { upsert: true, conflictFields: ['id'] });
// UPDATE OR INSERT INTO "users" (...) VALUES (...) MATCHING ("id")

dialect.buildInsertQuery('users', { name: 'Ann', age: 30 });
// INSERT INTO "users" ("name", "age") VALUES ($1, $2)

dialect.buildInsertQuery('users', { name: 'Ann', created_at: new Literal('CURRENT_TIMESTAMP') });
// INSERT INTO "users" ("name", "created_at") VALUES ($1, CURRENT_TIMESTAMP)

await dialect.addColumn('users', 'nickname', { type: { key: 'STRING', length: 50 } });
// ALTER TABLE "users" ADD "nickname" VARCHAR(50)      (not ADD COLUMN)

await dialect.removeColumn('users', 'nickname');
// ALTER TABLE "users" DROP "nickname"                 (not DROP COLUMN)