TypeORM index creation

Question

Is it possible to create an unique index with certain ascending/descending fields so that the outcome would be something like this?

CREATE UNIQUE INDEX "IDX_article_version_latest" ON "public"."article" ("uuid" ASC, "version" DESC, "updatedAt" DESC)

Answer 1

Unfortunately it's not supported yet. There is an open issue.

The only workaround is to follow the documentation:

TypeORM does not support some index options and definitions because of a lot of different database specifics and multiple issues with getting information about exist database indices and synchronizing them automatically. In such cases you should create index manually (for example in the migrations) with any index signature you want. To make TypeORM ignore these indices during synchronization use synchronize: false option on @Index decorator.

In the Article class add the @Index decorator and disable synchronization to avoid deletion on the next schema sync:

@Index("IDX_article_version_latest", { synchronize: false })
/* ...*/
class Article { /*...*/ }

Create the Migration:

export class IndexArticleVersionLatest implements MigrationInterface {
    async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`CREATE UNIQUE INDEX "IDX_article_version_latest" ON "public"."article" ("uuid" ASC, "version" DESC, "updatedAt" DESC)`);
    }

    async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`DROP INDEX IF EXISTS "IDX_article_version_latest"`);
    }
}

Remember to register the migration(s) in the TypeORM configuration. Refer to the documentation