Set up a TimescaleDB hypertable with Prisma

Photo by Andrey Novik on Unsplash

A montho ago, I was trying to set up TimescaleDB on NodeJS + Prisma. There was a tutorial on TimescaleDB site, but it used TypeORM to which I didn’t want. Then I found this guide, it was incredibly helpful, but I discovered some little mistakes. After I spended a whole day. I finally managed to create a hypertable.
Today, I was trying to create a hypertable again, and my time was fully taken up with errors. After I put them right. I just realised that I knew all the pitfalls beforehand, but I forgot all about them! So I decided to write this tutorial.

Caveat

According to Jan Piotrowski — head of engineering at Prisma, “Prisma does not support TimescaleDB officially, and these very manual steps make it work but of course there still is the danger that unrelated things go wrong.”

1. On PostgresSQL, and TimescaleDB, Anything inside ""(double quotes) are are case-sensitive, and anything inside ''(single quotes) are considered converted to lower case.
2. When you create a hypertable with the command SELECT create_hypertable('[table]', '[field]');, it will create an index, if an index is already exists, the creation fails. [source]
   So remove index first, then create a unique key because Prisma needs at lease one unique key or an index, and then create a hypertable.
3. As far as I know, the command SELECT create_hypertable('[table]', '[field]'); accepts table name as case-sensitive, but not for the field name. please consider naming the field name that you want to use as the hypertable key in lower case.

Tutorial

1.Initialise a NodeJS project and install prisma.

mkdir timescale-prisma && cd timescale-prisma
npm init -y
npm install prisma typescript ts-node @types/node --save-dev

2. Create Prisma schema.

npx prisma init

This command created a new directory called prisma which contains a file named schema.prisma and a .env file in the root of the project.

3. Edit the .env file to point to your database.

DATABASE_URL=”postgresql://johndoe:randompassword@localhost:5432/mydb”

The above are:

• username — johndoe
• password — randompassword
• host URL — localhost
• port — 5432 (default)
• database name — mydb

4. Then edit schema.prisma. It may looks like below.

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
model Node {
  id        Int      @id @default(autoincrement())
  nodeName  String

  log       Log[]
}
model Log {
  id           Int      @id @default(autoincrement())
  loggedAt     DateTime @default(now())
  temperature  Float

  node         Node     @relation(fields: [nodeId], references: [id])
  nodeId       Int
}

You need to modify it a little.

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
model Node {
  id        Int      @id @default(autoincrement())
  nodeName  String

  log       Log[]
}
model Log {
  id           Int      @default(autoincrement())
  logged_at    DateTime @default(now())
  temperature  Float

  node         Node     @relation(fields: [nodeId], references: [id])
  nodeId       Int
  @@unique([id,  logged_at])
}

As per the caveat, I rename the loggedAt field to logged_at and removed @id from the id field of Log,
then add @@unique([id, logged_at]) as a unique key.

The reason I combine id and logged_at together, because I’m not curtain that the time stamp alone will be unique, since the data from other nodes may arrive at the same time

5. Generate .sql file by migration;--create-only means you don’t want to make a change on the database yet.

npx prisma migrate dev --create-only

The prompt will ask for the migration name, you can type any. In this case, I enter init.

$ npx prisma migrate dev --create-only
Environment variables loaded from .env
Prisma schema loaded from db\schema.prisma
Datasource “db”: PostgreSQL database “mydb”, schema “public” at “localhost:5432”
√ Enter a name for the new migration: … init
Prisma Migrate created the following migration without applying it 20210831083903_init

You will see migration.sql in the folder 20210831083903_init under Prisma/migrations/.

-- CreateTable
CREATE TABLE "Node" (
    "id" SERIAL NOT NULL,
    "nodeName" TEXT NOT NULL,

    PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "Log" (
    "id" SERIAL NOT NULL,
    "logged_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "temperature" DOUBLE PRECISION NOT NULL,
);

-- CreateIndex
CREATE UNIQUE INDEX "Log.id_logged_at_unique" ON "Log"("id", "logged_at");

-- AddForeignKey
ALTER TABLE "Log" ADD FOREIGN KEY ("nodeId") REFERENCES "Node"("id") ON DELETE CASCADE ON UPDATE CASCADE;

5. Insert CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE; at the first line to make sure that TimescaleDB extension is installed.
Add SELECT create_hypertable('"Log"', 'logged_at'); at the last line to create hypertable on Log table.

Note1: If your table name contains upper-case letters, you need to put them between "" as per above, but if your table name is log the command would be SELECT create_hypertable('log', 'logged_at');.
Note2: It seems the field which we use to create hypertable, must be lower case. As I have tried SELECT create_hypertable('"Log"', '"loggedAt"') but got an error.

Finally, your migration.sql will look like:

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
-- CreateTable
CREATE TABLE "Node" (
    "id" SERIAL NOT NULL,
    "nodeName" TEXT NOT NULL,

    PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "Log" (
    "id" SERIAL NOT NULL,
    "logged_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "temperature" DOUBLE PRECISION NOT NULL,
);

-- CreateIndex
CREATE UNIQUE INDEX "Log.id_logged_at_unique" ON "Log"("id", "logged_at");

-- AddForeignKey
ALTER TABLE "Log" ADD FOREIGN KEY ("nodeId") REFERENCES "Node"("id") ON DELETE CASCADE ON UPDATE CASCADE;
SELECT create_hypertable('"Log"', 'logged_at');

6. Then apply the sql command to the database.

npx prisma migrate dev

Prompt will ask for migration name again, I enter hypertable.

$ npx prisma migrate dev
Environment variables loaded from .env
Prisma schema loaded from db\schema.prisma
Datasource “db”: PostgreSQL database “mydb”, schema “public” at “localhost:5432”
The following migration(s) have been applied:
migrations/
 └─ 20210831083903_hypertable/
 └─ migration.sql
√ Enter a name for the new migration: …hypertable
The following migration(s) have been created and applied from new schema changes:
migrations/
 └─ 20210831083958_hypertable/
 └─ migration.sql
Your database is now in sync with your schema.
✔ Generated Prisma Client (2.30.2) to .\node_modules\@prisma\client in 181ms

Done! Now your hypertable is ready. Please see TimescaleDB document how to insert row into it and execute a query.