Creating Primary Keys in TimescaleDB with TypeORM

Using TypeORM¹ with TimescaleDB² hypertables requires some additional work besides the usual operations needed to use a PostgreSQL data source.

This is because:

• TypeORM requires that each entity has a primary key (in Timescale this is quite optional);
• Timescale requies the primary key to contain the partition key (usually the time range column).

To make this work, you can do the following:

• Add an auto-generated ID column to the hyper-table;
• Create a composite primary key on both the ID as well as the hypertable’s partition key.

This will satisfiy both TypeORM’s need to have a unique primary key across the whole table, as well as Timescale’s requirement that the primary key contains the table’s partition key.

Example

In Ping.ts (entity definition file):

@Entity('pings')
export class Ping {
    @PrimaryGeneratedColumn()
    id!: number;

    @CreateDateColumn({ primary: true })
    startedAt: Date = new Date();

    // ...other columns...
}

In the TypeORM migration file:

// Make sure the primary key contains all needed columns.
await queryRunner.query(`CREATE TABLE "pings" ("id" SERIAL NOT NULL, "started_at" TIMESTAMP NOT NULL DEFAULT now(), ...other columns..., CONSTRAINT "PK_005b7a421c5dcdf04f030f2ab92" PRIMARY KEY ("id", "started_at"))`);

// Create hypertable.
await queryRunner.query(`SELECT create_hypertable('pings', by_range('started_at'));`)

P.S. In case you’re wondering why the SQL code uses snake_case while the TypeScript code uses camelCase, this is achieved using the typeorm-naming-strategies package.

1. This article presumes you are familiar with the TypeORM package. ↩︎
2. This article presumes you are familiar with the Timescale extension for the PostgreSQL DB. ↩︎