Vertica dialect

expect(dialect).toBeInstanceOf(PostgresDialect);
expect(dialect.library).toBe('pg'); // reuses the pg wire
expect(dialect.name).toBe('vertica');

$ 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: 'vertica',
  host: 'localhost',
  database: 'VMart',
  username: 'dbadmin',
  password: process.env.DB_PASSWORD,
});

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

export const db = new Prorm({
  dialect: 'vertica',
  url: 'vertica://dbadmin:secret@localhost:5433/VMart',
});

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 prorm = new Prorm({
  dialect: 'vertica',
  host: process.env.DB_HOST || 'localhost',
  port: Number(process.env.DB_PORT) || 5433, // Vertica default; may be omitted
  database: process.env.DB_NAME || 'VMart',
  username: process.env.DB_USER || 'dbadmin',
  password: process.env.DB_PASSWORD || '',
  logging: console.log,
});

dialect.buildCreateTableSql(
  'events',
  {
    id:      { type: DataTypes.INTEGER(), autoIncrement: true, primaryKey: true },
    user_id: { type: DataTypes.INTEGER() },
    payload: { type: DataTypes.TEXT() },
  },
  { orderBy: ['user_id', 'id'], segmentedBy: 'user_id' },
);

dialect.buildCreateTableSql(
  'facts',
  {
    id:     { type: DataTypes.INTEGER(), autoIncrement: true, primaryKey: true },
    note:   { type: DataTypes.TEXT() },
    amount: { type: DataTypes.INTEGER(), allowNull: false, defaultValue: 0 },
  },
  { ifNotExists: true, orderBy: 'id', unsegmented: true },
);

dialect.buildCreateTableSql(
  'metrics',
  { ts: { type: DataTypes.DATE() } },
  { partitionByExpression: "date_trunc('month', ts)" },
);

dialect.buildCreateProjectionSql({
  name: 'events_p1',
  table: 'events',
  columns: ['id', 'user_id'],
  orderBy: 'user_id',
  segmentedBy: 'user_id',
});

dialect.buildCreateProjectionSql({
  name: 'facts_super',
  table: 'facts',
  orderBy: ['id'],
  unsegmented: true,
});

dialect.getDataTypeSql(DataTypes.TEXT());    // 'LONG VARCHAR'
dialect.getDataTypeSql(DataTypes.BLOB());    // 'LONG VARBINARY'
dialect.getDataTypeSql(DataTypes.BOOLEAN()); // 'BOOLEAN'
dialect.getDataTypeSql(DataTypes.INTEGER()); // 'INTEGER' (delegated)

const bigAuto: any = DataTypes.BIGINT();
bigAuto.autoIncrement = true;
dialect.getDataTypeSql(bigAuto);             // 'IDENTITY'

dialect.buildUpsertQuery('users', { id: 1 }, {}); // throws /conflictFields/

dialect.buildUpsertQuery(
  'users',
  { id: 1, name: 'Ann', email: 'a@x.io' },
  { conflictFields: ['id'] },
);

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

dialect.buildInsertQuery('users', { id: 1, name: 'Ann' }, { returning: true });
// RETURNING is stripped → INSERT INTO "users" ("id", "name") VALUES ($1, $2)

dialect.buildDeleteQuery('users', { id: 5 });
// DELETE FROM "users" WHERE "id" = $1

dialect.buildUpdateQuery('users', { name: 'Bo' }, { id: 1 });
// UPDATE "users" SET "name" = $1 WHERE ...

dialect.buildCreateTableSql('orders', {
  id:      { type: DataTypes.INTEGER(), primaryKey: true },
  user_id: { type: DataTypes.INTEGER(), references: { table: 'users', field: 'id' } },
});
// ... "user_id" INTEGER REFERENCES "users"("id")

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

Vertica 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 vertica: 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.