Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 42 ยท Data and Persistence

Transactions and Migrations

Learn how to keep NestJS data consistent: database transactions in services, the unit of work, a safe migration workflow, seeding and detecting schema drift.

Two problems appear as soon as an application stores real data. The first is consistency: an operation such as "move 50 coins from Ada to Grace" changes two rows, and if the server crashes after the first change the money has vanished. A transaction makes a group of changes all-or-nothing. The second is change over time: your tables will need new columns and indexes, and production data cannot be thrown away and recreated. Migrations are versioned scripts that evolve the schema in a controlled way.

This lesson builds on Connecting to a Database and Repositories, Entities and Relations. The Nest and ORM code is read-only, because it needs a database server and npm packages. The runnable parts use node:sqlite (built into Node 24) as a stand-in to show real commits, rollbacks and a small migration runner. They are not Nest or an ORM, and they say so where it matters.

What a transaction guarantees

A transaction follows the classic ACID promises:

  • Atomic: all statements take effect, or none do.
  • Consistent: constraints (foreign keys, CHECK, UNIQUE) hold when it commits.
  • Isolated: concurrent transactions do not see each other's half-finished work (how strictly depends on the isolation level).
  • Durable: after commit, the data survives a crash.

You begin one, run your statements, and then either COMMIT or ROLLBACK. If any step throws, you roll back, and the database returns to the state before the transaction started.

A transaction in four steps

BEGIN Start a unit of work.
Statements Change several rows.
COMMIT Everything becomes permanent.
ROLLBACK On any error, undo it all.

Without a transaction, a failure leaves half the work done.

1 / 2

Transactions in a Nest service

The easy mistake is to open a transaction and then call an injected repository inside it. The injected repository was created with the connection's default entity manager, so its queries run outside your transaction. With TypeORM, you do the work through the manager the transaction gives you:

TypeScript
import { Injectable } from '@nestjs/common';
import { DataSource } from 'typeorm';

@Injectable()
export class TransfersService {
  constructor(private readonly dataSource: DataSource) {}

  async transfer(fromId: number, toId: number, amount: number) {
    await this.dataSource.transaction(async (manager) => {
      const from = await manager.findOneByOrFail(Account, { id: fromId });
      const to = await manager.findOneByOrFail(Account, { id: toId });

      if (from.balance < amount) {
        throw new BadRequestException('Insufficient funds');
      }

      from.balance -= amount;
      to.balance += amount;
      await manager.save([from, to]);
    });
  }
}

dataSource.transaction() commits when the callback finishes and rolls back if it throws, and the error propagates so that an exception filter can turn it into a response. When you need finer control, a QueryRunner offers explicit startTransaction(), commitTransaction(), rollbackTransaction() and release(), and you must always release it in a finally block.

The other libraries have the same shape. Prisma has prisma.$transaction(async (tx) => { ... }), where tx is the client you use inside (there is also a form that takes an array of operations). MikroORM has em.transactional(async (em) => { ... }), and because of its unit of work, a single flush() writes all tracked changes in one transaction.

Practical rules for services:

  • Keep transactions short. They hold locks. Never call a slow external API (payments, email) inside one.
  • One transaction per business operation, opened in the service layer, not in controllers or repositories, because the service knows where the operation begins and ends.
  • Pass the transaction handle (manager, tx) to everything that must take part. A repository method that should join the transaction needs to accept it.
  • Expect conflicts. Under concurrency, a transaction can fail with a deadlock or serialization error. Retrying the whole transaction is often the right response.
  • For "check then update" logic, protect against races with row locks (SELECT ... FOR UPDATE), atomic updates (UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?) or optimistic version columns.

Stand-in: an all-or-nothing transfer

The first stand-in runs the same transfer twice on an in-memory SQLite database. The first time it uses no transaction, and a failure between the two updates leaves the data inconsistent. The second wraps both updates in BEGIN and COMMIT, with ROLLBACK on error. Nest and TypeORM are not involved; transaction() is a small helper that does what dataSource.transaction() does.

Node.js Without and with a transaction

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Without the transaction 50 coins disappeared (the total dropped from 120 to 70). With it, the crash left everything as before, and the overdraft was stopped by the database's own CHECK constraint and rolled back. Constraints and transactions work together: the constraint says what is never allowed, the transaction makes sure a failure cannot leave a half-done state.

Migrations

A migration is a script with an up step (apply the change) and a down step (reverse it), stored in version control and run in order. The database keeps a small table recording which migrations ran, so each one runs exactly once per environment. The workflow with TypeORM looks like this (commands are read-only here; the exact CLI setup depends on your project and TypeORM version):

Terminal
# create a migration from the difference between entities and the database
npx typeorm migration:generate src/migrations/AddUserBio -d src/data-source.ts

# apply pending migrations
npx typeorm migration:run -d src/data-source.ts

# undo the most recent one
npx typeorm migration:revert -d src/data-source.ts
TypeScript
export class AddUserBio1735000000000 implements MigrationInterface {
  async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`ALTER TABLE "user" ADD "bio" text`);
  }

  async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`ALTER TABLE "user" DROP COLUMN "bio"`);
  }
}

Prisma uses prisma migrate dev while developing (it creates and applies SQL files) and prisma migrate deploy in CI and production, which only applies the committed files. The principles are the same everywhere:

  • Never edit a migration that has already run anywhere shared. Add a new one.
  • Review generated SQL. A column rename can be generated as drop-and-add, which loses data.
  • Make risky changes in steps (expand and contract): add the new column, deploy code that writes both, backfill, switch reads, and only later drop the old column. This keeps old and new app versions working during a rolling deploy.
  • Run migrations as a deploy step, once, not at the start of every app instance, or several instances race.

Seeding

A seed puts initial data in place: roles, a default admin in development, reference tables. Make seeds idempotent (running them twice changes nothing), for example with INSERT ... ON CONFLICT DO NOTHING, so they are safe to run on every deploy. Keep development sample data separate from reference data that production needs.

Schema drift

Drift means the real database no longer matches what your migrations and entities say, usually because someone changed it by hand or synchronize: true was used somewhere. Drift makes the next migration fail or, worse, succeed incorrectly. Prevent it by never hand-editing production schemas, never mixing synchronize with migrations, and by running migrations from scratch in CI against an empty database to prove that the history is complete.

Stand-in: a tiny migration runner

The second stand-in is a miniature migration tool. It keeps a schema_migrations table with each migration's name and a checksum, applies pending ones in order inside transactions, runs an idempotent seed, and detects when an already-applied migration file was edited (a form of drift). Real tools do more, but this is the core loop.

Node.js Migrations, seeding and drift detection

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Each migration runs once, and rerunning the tool does nothing. Seeding twice leaves a single row. The broken third migration fails inside its transaction, so SQLite's ALTER TABLE is rolled back along with it (SQLite supports transactional DDL; some databases, such as MySQL, do not, which is one more reason to keep migrations small). Finally, editing 002_add_bio after it ran is caught by the checksum, which is the same job that migration tools do to protect you from drift.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Inside dataSource.transaction(async (manager) => { ... }), why should you use manager instead of an injected repository?

    The transaction is bound to the manager passed to the callback. Queries made through other managers or repositories do not take part in it, so they would not be rolled back.
  2. 2. A transaction throws an error halfway through. What should happen to the changes already made inside it?

    Atomicity means all-or-nothing. On error the transaction is rolled back and the error propagates to the caller.
  3. 3. What is the rule for a migration that has already been applied in a shared environment?

    Other environments already ran the old version. Editing it causes drift, so corrections go into a new migration.
  4. 4. Why should a seed script be idempotent?

    Idempotent seeds, such as INSERT ... ON CONFLICT DO NOTHING, are safe to run many times.

Summary

  • A transaction makes several changes atomic: commit them all, or roll back on any error.
  • In Nest services open the transaction around one business operation and use the handle it provides (manager, tx) for every query that must take part; keep it short and never call slow external services inside.
  • Constraints and transactions work together to keep data consistent; race-prone check-then-update logic needs locks or atomic updates.
  • Migrations are versioned up/down scripts recorded in a history table; never edit applied ones, review generated SQL, and change risky columns in expand-and-contract steps.
  • Make seeds idempotent, never use synchronize in production, and guard against schema drift with checksums and a from-scratch run in CI.

Next, secure your API with passwords and tokens in Authentication with JWT.