Timescale DB Support

[all2pie]

Feature Description

Please add support for Timescale db.

If it is already supported then please add it in the documentation.

Is is currently supported by sequelize:

Timescale Db

Relevant Database Driver

DB Type	Relevant
aurora-data-api	no
aurora-data-api-pg	no
better-sqlite3	no
cockroachdb	no
cordova	no
expo	no
mongodb	no
mysql	no
nativescript	no
oracle	no
postgres	yes
react-native	no
sap	no
sqlite	no
sqlite-abstract	no
sqljs	no
sqlserver	no

Are you willing to resolve this issue by submitting a Pull Request?

• ✖️ Yes, I have the time, and I know how to start.
• ✖️ Yes, I have the time, but I don't know how to start. I would need guidance.
• ✖️ No, I don’t have the time, but I can support (using donations) development.
• ✅ No, I don’t have the time and I’m okay to wait for the community / maintainers to resolve this issue.

[iam4x]

TimescaleDB is just an extension of PostgreSQL which is already supported by TypeORM. Just follow the docs for PostgreSQL :+1:

[all2pie]

@iam4x I know that and i have successfully connected to it but i do not use migrations so i need someway to create hyper-tables and also it would be good to have support for Timescale function, but i can do raw queries for that so that wont be an issue.

[paltaa]

You can create hypertables in typeOrm:

import { MigrationInterface, QueryRunner } from 'typeorm';

export class TradesInit1640705305983 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`CREATE TABLE IF NOT EXISTS trades(
            id              serial,
            mongo_id        varchar(24) NOT NULL,
            date            TIMESTAMPTZ NOT NULL,
            symbol          varchar(30) NOT NULL,
            price           BIGINT NOT NULL,
            amount          BIGINT NOT NULL,
            total_cost      BIGINT NOT NULL
        );`);
    await queryRunner.query(
      `CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;`,
    );
    await queryRunner.query(`SELECT create_hypertable('trades', 'date');`);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP TABLE trades;`);
    await queryRunner.query(`DROP EXTENSION timescaledb;`);
  }
}

[danfield73]

What I think we need is Hypertable options available at the Entity level.

Perhaps a simple option within Entity to mark as a hypertable... we'd also need to consider other options (Compression, Partitioning options etc).

Hyperfunction could be implemented as SQL with queryRunner for now - open to look at other implementation options.

[akindo]

@paltaa The matching entity for the trades table would result in an error however:

ERROR: Entity "trades does not have a primary column. Primary column is required to have in all your entities. Use @PrimaryColumn decorator to add a primary column to your entity.

if I have a TypeORM entity I want to convert to a hyper table, there's a conflict of interest. TypeORM requires entities to have a primary key. Hyper tables can have a primary key, but that primary key must be part of the partition key: UNIQUE and PRIMARY constraints must include the partitioning key.

In my case, I must have an ID column annotated with PrimaryGeneratedColumn(), while the hyper table is created on a timestamp field. This means I have to manually edit the generated migration in order to remove the primary key on ID, which might cause problems down the line, as the TypeORM entity states it has a primary key, but the created table doesn't.

[Nosfistis]

I guess hypertables can only be resolved with custom queries.

[matte0080]

I guess hypertables can only be resolved with custom queries.

Can you give me an example?

[matte0080]

Some news about this issue/request?

[wiatrM]

Any updates? It would be great to add this to TypeORM

[treecon]

Any updates? @akindo is right about the primary key issue. Working with both TypORM and TimescaleDB seems like a serious hassle. It would be great if TypeORM could provide support for Hypertables.

[thuantruong-kite]

any updates?