QuestDB Dialect

export class QuestDBDialect extends PostgresDialect {
  readonly name = 'questdb';

  constructor(config: PostgresDialectOptions = {}) {
    // QuestDB's PostgreSQL-wire (pgwire) port defaults to 8812.
    super({ port: 8812, ...config });
  }
  // ...
}

$ 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: 'questdb',
  host: 'localhost',
  database: 'qdb',
  username: 'admin',
  password: process.env.QUESTDB_PASSWORD,
});

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

export const db = new Prorm({
  dialect: 'questdb',
  url: 'questdb://admin:quest@localhost:8812/qdb',
});

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.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 });
  await User.update({ name: 'Grace Hopper' }, { where: { email: 'grace@example.com' }, transaction: t });
});

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

const prorm = new Prorm({
  dialect: 'questdb',
  host: 'localhost',
  port: 8812,          // optional — defaults to 8812 for QuestDB
  database: 'qdb',
  username: 'admin',
  password: 'quest',
});

await prorm.connect();

CREATE TABLE [IF NOT EXISTS] name (cols...)
  [TIMESTAMP(designated) PARTITION BY UNIT [WAL|BYPASS WAL]]
  [DEDUP UPSERT KEYS(...)]

dialect.buildCreateTableSQL(
  'trades',
  {
    ts: { type: DataTypes.DATE() },
    price: { type: DataTypes.DOUBLE() },
  },
  { timestamp: 'ts', partitionBy: 'DAY' },
);

dialect.buildCreateTableSQL('trades', {
  ticker: { type: DataTypes.STRING(), symbol: true, symbolCapacity: 256, symbolCache: true },
});

dialect.buildCreateTableSQL(
  'wal_trades',
  { ts: { type: DataTypes.DATE() } },
  { timestamp: 'ts', partitionBy: 'HOUR', wal: true },
);

dialect.buildCreateTableSQL(
  'nowal_trades',
  { ts: { type: DataTypes.DATE() } },
  { timestamp: 'ts', partitionBy: 'HOUR', wal: false },
);

dialect.buildCreateTableSQL(
  'trades',
  {
    ts: { type: DataTypes.DATE() },
    ticker: { type: DataTypes.STRING(), symbol: true },
  },
  { timestamp: 'ts', partitionBy: 'DAY', wal: true, dedupUpsertKeys: ['ts', 'ticker'] },
);

dialect.getDataTypeSql(DataTypes.BIGINT());  // 'LONG'
dialect.getDataTypeSql(DataTypes.INTEGER()); // 'INT'
dialect.getDataTypeSql(DataTypes.DOUBLE());  // 'DOUBLE'
dialect.getDataTypeSql(DataTypes.FLOAT());   // 'FLOAT'
dialect.getDataTypeSql(DataTypes.STRING());  // 'STRING'
dialect.getDataTypeSql(DataTypes.DATE());    // 'TIMESTAMP'
dialect.getDataTypeSql(DataTypes.BOOLEAN()); // 'BOOLEAN'
dialect.getDataTypeSql(DataTypes.UUID());    // 'UUID'
dialect.getDataTypeSql(DataTypes.BLOB());    // 'BINARY'
dialect.getDataTypeSql('SYMBOL');            // 'SYMBOL'
dialect.getDataTypeSql('DATETIME');          // 'TIMESTAMP'
dialect.getDataTypeSql('BIGINT');            // 'LONG'

dialect.buildCreateTableSQL('quotes', {
  id: { type: DataTypes.BIGINT(), primaryKey: true, autoIncrement: true },
  symbol_id: {
    type: DataTypes.BIGINT(),
    unique: true,
    references: { table: 'symbols', field: 'id' },
  },
});

dialect.buildInsertQuery('trades', {
  ts: '2024-01-01T00:00:00Z',
  ticker: 'AAPL',
  price: 100.5,
});

dialect.buildUpdateQuery('trades', { price: 200 }, { ticker: 'AAPL' }, { limit: 5, returning: true });

dialect.buildDeleteQuery('trades', {}, { truncate: true }).sql; // 'TRUNCATE TABLE "trades"'
dialect.buildDeleteQuery('trades', { ticker: 'AAPL' });          // throws

dialect.buildLimitOffset(10);      // ' LIMIT 10'
dialect.buildLimitOffset(10, 20);  // ' LIMIT 20, 30'
dialect.buildLimitOffset(undefined, 20); // ''

dialect.buildAddColumnSQL('trades', 'volume', { type: DataTypes.BIGINT(), allowNull: false, defaultValue: 0 });
// ALTER TABLE "trades" ADD COLUMN "volume" LONG

dialect.buildAddColumnSQL('trades', 'ticker', { type: DataTypes.STRING(), symbol: true, symbolCapacity: 128, symbolCache: true });
// ALTER TABLE "trades" ADD COLUMN "ticker" SYMBOL CAPACITY 128 CACHE

dialect.buildAddIndexSQL('trades', 'ticker');       // ALTER TABLE "trades" ALTER COLUMN "ticker" ADD INDEX
dialect.buildAddIndexSQL('trades', 'ticker', 512);  // ALTER TABLE "trades" ALTER COLUMN "ticker" ADD INDEX CAPACITY 512
dialect.buildDropIndexSQL('trades', 'ticker');      // ALTER TABLE "trades" ALTER COLUMN "ticker" DROP INDEX

dialect.buildRenameTableSQL('old_trades', 'trades'); // RENAME TABLE "old_trades" TO "trades"

QuestDB has no row-level locking, so `lock` cannot be honoured. Remove the `lock` option; a lock the caller believes it holds but does not is worse than an error.

Cannot render WHERE condition at tags.$contains for questdb: 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.