Home

prisma-extension-timescaledb

Type-safe TimescaleDB / TigerData time-series support for Prisma — reset-safe migrations, hypertables, continuous aggregates, and typed query helpers.

Prisma can't model TimescaleDB features in its schema language, and the naive setup breaks on prisma migrate reset / migrate dev. This package fixes that with:

• 🧱 Hypertables & continuous aggregates from /// schema annotations
• 🧹 Retention policies — drop old chunks automatically
• 🗜️ Columnstore compression — compress old chunks automatically (TimescaleDB hypercore)
• ♻️ Reset-safe migrations — survive prisma migrate reset (proven on real TimescaleDB)
• 🔎 Typed timeBucket(...) queries with result-row inference, compile-time column checks, gap-filling, and Toolkit hyperfunctions (percentiles, counters, OHLC, …)
• 🛟 Generator-optional — the client extension works from a manual config too

Scope: hypertables, continuous aggregates, retention & compression, reset-safe migrations, typed query helpers. Vector / BM25 are out of scope for now.

Documentation

Page	What's there
Setup	Requirements, prisma.config.ts, the generate → migrate flow, shadow database
Hypertables	@timescale.hypertable, chunk interval, space partitioning, chunk skipping
Continuous aggregates	@timescale.continuousAggregate, refresh, real-time & hierarchical caggs
Retention & compression	@timescale.retention / @timescale.compression + runtime policies
timeBucket queries	where, relation filters, groupBy, orderBy/limit, gap-filling, first/last, time zones
Aggregates & hyperfunctions	Every aggregate function, exact output (as), and the Toolkit hyperfunctions
$timescale management	Refresh, chunk inspect/compress/drop, resize, background jobs
Annotation reference	Every @timescale.* annotation + interval grammar
Without the generator	Manual config, @@map/@map, multiple schemas
Troubleshooting	Common errors and current limitations

Requirements

• Prisma 7 (prisma and @prisma/client >=7.0.0) — relies on the v7 prisma.config.ts + prisma-client generator model.
• TimescaleDB-capable PostgreSQL for both your database and the Prisma shadow database (see Setup). Locally, the timescale/timescaledb image works. Compression needs TimescaleDB ≥ 2.18; the Toolkit hyperfunctions need the timescaledb_toolkit extension (the timescale/timescaledb-ha image, or Tiger Cloud).
• A Prisma driver adapter at runtime (e.g. @prisma/adapter-pg).

Install

npm install prisma-extension-timescaledb
npm install -D prisma @prisma/client
npm install @prisma/adapter-pg            # or your preferred driver adapter

Quick start

// schema.prisma
generator client {
  provider        = "prisma-client"
  output          = "./client"
  previewFeatures = ["views"]
}

generator timescaledb {
  provider = "prisma-extension-timescaledb"   // emits reset-safe migrations + a typed registry
  output   = "./timescale"
}

datasource db {
  provider = "postgresql"
}

/// @timescale.hypertable(column: "time", chunkInterval: "1 day")
model SensorReading {
  time        DateTime
  deviceId    Int
  temperature Float

  @@id([deviceId, time])
  @@index([deviceId, time])
}

/// @timescale.continuousAggregate(source: "SensorReading", bucket: "1 hour", timeColumn: "time", refresh: { startOffset: "1 month", endOffset: "1 hour", scheduleInterval: "1 hour" })
view SensorHourly {
  bucket   DateTime /// @timescale.bucket
  deviceId Int      /// @timescale.groupBy
  avgTemp  Float    /// @timescale.aggregate(fn: "avg", column: "temperature")

  @@unique([deviceId, bucket])   // Prisma 7 disallows @@id on views
}

npx prisma migrate dev --create-only --name init   # your normal CREATE TABLE
npx prisma generate                                # emits the timescale migrations + registry
npx prisma migrate deploy                          # applies everything, in the right order

import { PrismaClient } from "./client/client.js";
import { PrismaPg } from "@prisma/adapter-pg";
import { timescaledb } from "prisma-extension-timescaledb";
import { registry } from "./timescale/index.js";

const prisma = new PrismaClient({
  adapter: new PrismaPg({ connectionString: process.env.DATABASE_URL }),
}).$extends(timescaledb(registry));

const rows = await prisma.sensorReading.timeBucket({
  bucket: "1 hour",
  range: { start, end },
  groupBy: ["deviceId"],
  aggregate: { avgTemp: { avg: "temperature" } },
});
// rows: Array<{ bucket: Date; deviceId: number; avgTemp: number }>

Next: Setup for the full install/migration flow, or jump to timeBucket queries.

Examples

• NestJS — a runnable app wiring hypertables, continuous aggregates and timeBucket queries end to end: prisma-extension-timescaledb-nestjs-example.

---

MIT © Krister Johansson