How to create an SQL-server catalog using TypeORM in migration?

Serhii Pemakhov 0
Viewed 27

I'm trying to set up a full-text search in the following environment: Node.js, Nest.js, TypeORM, and Microsoft SQL database.

The migration I'm trying to run:

import { MigrationInterface, QueryRunner } from 'typeorm';

export default class addFullTextIndexToAttachmentComments1663750544577 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`--sql
      CREATE FULLTEXT CATALOG AttachmentComment
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`--sql
      DROP FULLTEXT CATALOG AttachmentComment
    `);
  }
}

The error I get:

QueryFailedError: Error: CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.

As I can see, typeorm queryrunner runs a transaction, but creating a catalog is not allowed inside the transaction. Is there a way to suppress transaction in typeorm queryrunner, or maybe there can be another solution?

database-migration typeorm sql-server node.js
1 Answers

It seems that I've finally found the solution.

It consists of three simple steps:

  1. In ormconfig.ts (js) add the following option:
    migrationsTransactionMode: 'each'

By default, it is 'all'. 'none' is also available. In general, with 'none' it already works, but I don't want to turn all transactions off. I'm not sure that replacing default 'all' with 'each' is really needed, but seems like it makes sense.

  1. In a migration file, inside the function 'up' and 'down' commit the transaction using queryRunner's built in method. Run the query that is impossible to run inside the user's transaction.
  2. Start a new transaction. Without starting a new transaction in the end there will be another error.
public async up(queryRunner: QueryRunner): Promise<void> {
  await queryRunner.commitTransaction();

  await queryRunner.query(`--sql
    // your query
  `);

  await queryRunner.startTransaction();
}
Serhii Pemakhov0
Related