NestJS‑TypeORM Integration: Should Migrations Execute Inside Automatic Transactions?

NestJS should not ship a built-in MigrationService to wrap migrations in transactions because TypeORM already handles this behavior by default. The assumption that migrations execute outside a transaction is generally incorrect for standard database configurations, provided the database supports transactional DDL.

How TypeORM Handles Migration Transactions

By default, TypeORM uses a migrationsTransactionMode setting set to each. This means every individual migration's up method is executed within its own database transaction. If any SQL statement within that migration file fails, TypeORM automatically rolls back the transaction for that specific migration, ensuring the database schema remains in its state prior to that migration starting.

The Trade-offs of Automatic Wrapping

While a high-level NestJS wrapper might reduce boilerplate, there are significant trade-offs to consider:

• Database Limitations: In databases like MySQL or Oracle, DDL statements often cause an implicit commit. In these cases, no amount of application-level wrapping can roll back a CREATE TABLE statement once it has executed.
• Complexity Overhead: Migrations often require custom QueryRunner logic. An automated wrapper might conflict with manual transaction management when used for complex data transformations, leading to nested transaction errors or "transaction already started" exceptions.
• Locking Behavior: Wrapping multiple migrations in a single transaction (migrationsTransactionMode: 'all') can cause long-held table locks, potentially blocking the application's startup sequence or deployment cycles.

Implementation Best Practices

To ensure consistency in a NestJS environment without unnecessary wrappers, follow these steps:

1. Verify Configuration: Ensure your DataSource options do not have migrationsTransactionMode set to none unless explicitly required.
2. Use the Provided QueryRunner: Always use the queryRunner passed into the up and down methods rather than creating a new connection. This ensures your statements are part of the transaction managed by the runner.
3. Handle Non-Transactional DDL: If using a database that does not support transactional DDL (like MySQL), manually implement "compensation logic" in the down method or use pre-checks to ensure idempotency.

// Example of a safe migration implementation
export async up(queryRunner: QueryRunner): Promise {
    // The queryRunner is already inside a transaction started by TypeORM
    await queryRunner.query(`CREATE TABLE "user" ("id" int PRIMARY KEY, "name" varchar)`);
n    // If an error occurs here, TypeORM rolls back the table creation in PostgreSQL
}

To provide a more specific recommendation, are you using a database that supports transactional DDL (like PostgreSQL) or one that performs implicit commits on DDL (like MySQL)?