TimescaleDB Dialect

const dialect = new TimescaleDBDialect({});
dialect.name;    // 'timescaledb'
dialect.library; // 'pg'

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

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

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

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 orm = new Prorm({
  dialect: 'timescaledb',
  host: 'localhost',
  port: 5432,            // default; TimescaleDB runs inside PostgreSQL
  database: 'metrics',
  username: 'postgres',
  password: 'secret',
});

dialect.createExtensionSQL();
// CREATE EXTENSION IF NOT EXISTS "timescaledb"

dialect.createExtensionSQL('timescaledb_toolkit');
// CREATE EXTENSION IF NOT EXISTS "timescaledb_toolkit"

await dialect.createExtension('timescaledb', { ifNotExists: true });
// runs: CREATE EXTENSION IF NOT EXISTS "timescaledb"

dialect.createHypertable('conditions', 'time');

dialect.createHypertable('metrics', 'ts', { chunkTimeInterval: '1 hour' });

dialect.createHypertable('metrics', 'ts', {
  chunkTimeInterval: '1 day',
  ifNotExists: true,
  migrateData: true,
});

dialect.createHypertable('events', 'seq', { chunkTimeInterval: 86400000 });

dialect.enableCompression('conditions');

dialect.enableCompression('conditions', { segmentBy: 'device_id', orderBy: 'time DESC' });

dialect.enableCompression('conditions', {
  segmentBy: ['device_id', 'location'],
  orderBy: ['time DESC', 'device_id'],
});

dialect.addCompressionPolicy('conditions', '30 days');

dialect.addCompressionPolicy('events', 1000000);

dialect.addRetentionPolicy('conditions', '90 days');

dialect.addRetentionPolicy('events', 5000000);

dialect.createContinuousAggregate(
  'daily_avg',
  "SELECT time_bucket('1 day', time) AS bucket, avg(temperature) FROM conditions GROUP BY bucket",
);

dialect.buildInsertQuery('readings', { device_id: 'sensor-1', temperature: 21.5 });
// sql:    INSERT INTO "readings" ("device_id", "temperature") VALUES ($1, $2)
// values: ['sensor-1', 21.5]

dialect.buildSelectQuery({ tableName: 'readings' });
// SELECT * FROM "readings"

dialect.buildUpdateQuery('readings', { temperature: 25 }, { device_id: 'sensor-1' });
// sql:    UPDATE "readings" SET "temperature" = $1 ... WHERE "device_id" = $...
// values: [25, 'sensor-1']

dialect.buildDeleteQuery('readings', { device_id: 'x' });
// sql:    DELETE FROM "readings" WHERE "device_id" = $1
// values: ['x']

dialect.buildUpsertQuery('readings', { device_id: 'a', temperature: 5 }, { conflictFields: ['device_id'] });
// INSERT INTO "readings" ... ON CONFLICT ("device_id") DO UPDATE SET "temperature" = excluded."temperature"

await dialect.createTable('conditions', { /* ... */ }, { ifNotExists: true });
// CREATE TABLE IF NOT EXISTS "conditions" (... "id" SERIAL ... "time" ...)

await dialect.dropTable('conditions', { ifExists: true, cascade: true });
// DROP TABLE IF EXISTS "conditions" CASCADE

await dialect.addColumn('conditions', 'humidity', { type: 'DOUBLE PRECISION' });
// ALTER TABLE "conditions" ADD COLUMN "humidity" ...

await dialect.removeColumn('conditions', 'humidity');
// ALTER TABLE "conditions" DROP COLUMN "humidity"

await dialect.addIndex('conditions', 'idx_device', ['device_id'], { unique: true });
// CREATE UNIQUE INDEX "idx_device" ON "conditions" ("device_id")

await dialect.removeIndex('conditions', 'idx_device');
// DROP INDEX "idx_device"

await dialect.addForeignKey('readings', 'device_id', 'devices', 'id');
// ALTER TABLE "readings" ADD CONSTRAINT ... FOREIGN KEY ("device_id") REFERENCES "devices"("id")

dialect.getDataTypeSql({ key: 'INTEGER', autoIncrement: true }); // 'SERIAL'
dialect.getDataTypeSql({ key: 'DATE' });                         // 'TIMESTAMP'
dialect.getDataTypeSql({ key: 'BOOLEAN' });                      // 'BOOLEAN'