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?

CodePudding user response：

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.

2. 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.
3. 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();
}