What nx affected database migrations are and how detection works

In this pattern, each database schema lives in its own Nx project, such as db-billing or db-identity. Each project holds its migration files and a migrate target that calls your migration tool. When CI runs nx affected -t migrate, Nx compares a base commit to the head commit. It maps each changed file to the project that owns it, then walks the project graph to add every project that depends on a changed one. Only those projects run migrate.

 git diff base..head
   └─ libs/db-billing/migrations/0042_add_invoice_status.sql
                │
                ▼
  ┌──────────────┐        ┌──────────────┐
  │ db-billing   │◄───────│ billing-api  │   affected
  │ (migrate)    │        └──────────────┘
  └──────┬───────┘
         │ runs against
         ▼
  billing DB: _migrations history table   ← authority on what's applied

  ┌──────────────┐        ┌──────────────┐
  │ db-identity  │◄───────│ auth-api     │   not affected, skipped
  └──────┬───────┘        └──────────────┘
         ▼
  identity DB: _migrations history table

Two separate mechanisms are at work here.

  • Project-level affected detection decides whether a project is in scope. It works from file ownership and graph edges.
  • Target inputs decide how a task is hashed for caching. They control whether a cached result can be replayed. They do not control which projects count as affected.

A migration changes a live database, so a cache hit replaying "success" without touching that database is a real risk.

Affected detection is a CI optimization that saves you from running migrations against every database on every commit. It does not record what has been applied. The migration tool's history table in each database is the authority. If detection misses a project, or a base SHA is wrong, running the tool again is still safe, because it checks that table and applies only the pending migrations.

 git diff base..head
   └─ libs/db-billing/migrations/0042_add_invoice_status.sql
           │  changed file mapped to its owning project
           ▼
  ┌──────────────────┐   depends on   ┌──────────────┐
  │ db-billing       │◄───────────────│ billing-api  │  AFFECTED
  │ target: migrate  │                └──────────────┘  (via graph walk)
  └────────┬─────────┘
           │  nx affected -t migrate runs the migration tool
           ▼
  ┌──────────────────────────────┐
  │ billing DB                   │
  │  _migrations history table   │  ← authority on what's applied
  └──────────────────────────────┘

  ┌──────────────────┐   depends on   ┌──────────────┐
  │ db-identity      │◄───────────────│ auth-api     │  NOT AFFECTED
  │ target: migrate  │                └──────────────┘  (skipped)
  └────────┬─────────┘
           │  migrate not run in this pipeline
           ▼
  ┌──────────────────────────────┐
  │ identity DB                  │
  │  _migrations history table   │  ← still authority on its state
  └──────────────────────────────┘

  Affected scope = file ownership + graph edges.
  Target inputs only drive task hashing and caching.

A changed migration file marks db-billing and its dependent billing-api as affected, so only the billing database is migrated, while each database's own history table remains the record of what has actually been applied.

Who should use it, and when to avoid it

This approach pays off in monorepos with several independent databases or schemas: a billing service with its own Postgres database, an inventory service with another, a reporting schema owned by a separate team. When every pull request runs every migration job, CI time grows with the number of schemas, not with the size of the change. Each job also needs credentials, a network path to a database, and a passing status, so an unrelated flaky connection can block a merge that touched none of those schemas. Scoping migrations to affected projects ties both the cost and the failure surface to what actually changed.

When to avoid it

  • One or two databases. If a full migration run finishes quickly and rarely fails, running everything on every change is simpler. The tool already skips migrations it has applied, so the only savings would be job startup time. You would be adding project boundaries and graph configuration to save very little.
  • Teams that won't maintain the structure. Detection is only accurate if every schema lives in its own Nx project, migration files stay inside that project's root, and new schemas follow the same layout. If people routinely put migrations in shared folders or skip project setup, the graph will be wrong and nobody will notice.
  • Shared root inputs. If each migrate target depends on the lockfile, a shared Prisma generator config, or a common environment file, a single dependency bump marks every schema as affected. You would pay for the extra configuration and still run everything.

Modeling one schema per Nx project with a migrate target

The fix is structural: give every schema its own Nx project. A library such as libs/db/orders owns prisma/schema.prisma and the prisma/migrations directory next to it. A change to the orders schema then touches only that project's files, so nx affected flags orders and nothing else. With a single shared libs/db project holding every schema, any edit to any model marks the whole project affected, and every database migrates on every change.

In project.json, define a named input such as schema that lists only {projectRoot}/prisma/schema.prisma and {projectRoot}/prisma/migrations/**/*. Point the migrate target at it so README edits or test changes don't trigger it. The target uses nx:run-commands and passes --schema explicitly, because Prisma's default schema lookup assumes one schema per package and will pick the wrong file or none. Set cache: false on this target. A migration changes a live database, and Nx must not skip it just because the inputs match an earlier run against a different environment.

Command Where Behavior
prisma migrate deploy CI, production Applies pending migrations only. Never creates migrations or resets data.
prisma migrate dev Local Generates new migrations and uses a shadow database. May prompt to reset.

Put prisma generate in a separate generate target and leave it cacheable. Its output depends only on the schema. Set an explicit generator output path inside the project and declare that path as the target's outputs, so Nx can restore it from cache. Then add generate to build's dependsOn. Builds stay fast and reproducible, while migrations remain an explicit step with side effects that you run on purpose.

// libs/db/orders/project.json (Nx accepts comments in project.json)
{
  "name": "db-orders",
  "root": "libs/db/orders",
  "projectType": "library",
  // Only the schema and its migrations count as inputs for DB targets
  "namedInputs": {
    "schema": [
      "{projectRoot}/prisma/schema.prisma",
      "{projectRoot}/prisma/migrations/**/*"
    ]
  },
  "targets": {
    // Side effect against a live database: never cached, run on purpose
    "migrate": {
      "executor": "nx:run-commands",
      "cache": false,
      "inputs": ["schema"],
      "options": {
        "command": "prisma migrate deploy --schema=libs/db/orders/prisma/schema.prisma"
      }
    },
    // Pure function of the schema: safe to cache and restore
    // Requires generator output = "../src/generated/client" in schema.prisma
    "generate": {
      "executor": "nx:run-commands",
      "cache": true,
      "inputs": ["{projectRoot}/prisma/schema.prisma"],
      "outputs": ["{projectRoot}/src/generated/client"],
      "options": {
        "command": "prisma generate --schema=libs/db/orders/prisma/schema.prisma"
      }
    },
    // Builds depend on the generated client, never on migrations
    "build": {
      "dependsOn": ["generate"]
    }
  }
}

A project.json for libs/db/orders with an uncached migrate target scoped to its schema and migrations, and a cacheable generate target that build depends on.

Wiring the dependency graph and execution order

Services should depend on schema projects, not the other way around. Nx propagates "affected" downstream: when a schema project changes, every project that depends on it is marked affected. When a service changes, its upstream schema projects are not. Most services already create this edge by importing the generated client from the schema project's library. Where no import exists, for example a worker that talks to the database through raw SQL, declare the edge with implicitDependencies on the service. The result is that a schema change re-runs downstream service tests, while a service-only change never triggers a migration, because services have no migrate target.

Ordering between schemas

Sometimes one schema must migrate before another. Common cases are a cross-schema foreign key, or a shared extensions schema that installs types or functions other schemas use. Make the dependent schema project depend on the prerequisite one, then give the migrate target dependsOn: ["^migrate"]. Nx runs the dependency's migrate first, even when that dependency is not itself affected, so every migrate command must be a safe no-op when nothing is pending.

Parallelism

Nx runs independent tasks concurrently by default. Several migrations against one database server can then contend for locks, exhaust connection limits, or hold DDL locks that block each other. If your schemas share a server, run the migrate target with --parallel=1, or serialize only that target.

Err toward over-declaring

An extra dependency edge costs a few redundant no-op runs. A missing edge is worse. Ordering then depends on scheduling and on what changed in each commit. A migration can run before its prerequisite in one environment and succeed in another. These failures appear only intermittently, which makes them hard to reproduce. When in doubt, add the edge.

# libs/db-extensions: shared types/functions that other schemas use.
# libs/db-billing: has a cross-schema FK, so it must migrate after db-extensions.
cat > libs/db-billing/project.json <<'EOF'
{
  "name": "db-billing",
  "implicitDependencies": ["db-extensions"],
  "targets": {
    "migrate": {
      "executor": "nx:run-commands",
      "dependsOn": ["^migrate"],
      "options": { "command": "./tools/migrate.sh billing" }
    }
  }
}
EOF

# apps/worker-reports uses raw SQL and imports no generated client,
# so declare the edge by hand. It has no migrate target, so changing
# only this worker never triggers a migration.
cat > apps/worker-reports/project.json <<'EOF'
{
  "name": "worker-reports",
  "implicitDependencies": ["db-billing"]
}
EOF

# Confirm the edges before you trust 'affected'.
npx nx show project db-billing --json

# Schemas share one server, so run migrations one at a time.
# tools/migrate.sh must exit 0 when no migrations are pending, because
# ^migrate also runs db-extensions when only db-billing changed.
npx nx affected -t migrate --parallel=1

# Tests can still run in parallel. They include downstream services.
npx nx affected -t test

This example adds an implicit dependency so a raw-SQL worker becomes affected by schema changes, uses dependsOn "^migrate" so the extensions schema migrates first, and runs only affected migrations one at a time against a shared server.

Running nx affected -t migrate in CI with safe base SHAs

In CI, never let Nx infer the comparison range. Pass both ends explicitly: nx affected -t migrate --base=$LAST_MIGRATED_SHA --head=$GITHUB_SHA (or your CI's equivalent commit variable). The head is the commit being deployed. The base needs more care.

A common default is HEAD~1 or the tip of main, and both are unsafe for migrations. Suppose commit A adds a migration to the billing schema, and that pipeline fails at an unrelated step or is cancelled. Commit B then deploys cleanly with HEAD~1 as its base. Nx compares B against A, sees no change in billing, and never runs A's migration. Later runs will not catch it either, because each one only looks one commit back.

The base must be the last commit that was successfully migrated in that specific environment. Staging and production usually sit at different commits, so store one SHA per environment. A small key-value entry, a CI variable or a row in an ops table all work. Read it before the migrate step and write the new head only after every affected migrate target exits zero. If the step fails, leave the stored value alone so the next run retries the same range.

Add a separate check for schema files edited without a matching migration. Run prisma migrate diff from the migrations directory to the current schema file with --exit-code. The command exits non-zero when the two differ, which fails the build before deploy. Run it per schema project, as an affected target of its own.

#!/usr/bin/env bash
# CI deploy step: migrate only schemas changed since this environment's last successful migration.
set -euo pipefail

ENV_NAME="${DEPLOY_ENV:?set to staging or production}"
HEAD_SHA="${GITHUB_SHA:?}"   # the commit being deployed

# Base = last commit successfully migrated in THIS environment, not HEAD~1 or main's tip.
BASE_SHA=$(psql "$OPS_DATABASE_URL" -tA -v ON_ERROR_STOP=1 -v env="$ENV_NAME" <<'SQL'
SELECT sha FROM migration_state WHERE env = :'env';
SQL
)

# The checkout must include full history (fetch-depth: 0) so the base commit exists locally.
git cat-file -e "${BASE_SHA}^{commit}"

# Drift gate: each schema project defines a migrate-check target that runs, e.g.:
#   prisma migrate diff --from-migrations ./prisma/migrations \
#     --to-schema-datamodel ./prisma/schema.prisma \
#     --shadow-database-url "$SHADOW_DATABASE_URL" --exit-code
# A non-zero exit means schema.prisma was edited without a matching migration.
npx nx affected -t migrate-check --base="$BASE_SHA" --head="$HEAD_SHA"

# Apply migrations for affected schema projects only.
npx nx affected -t migrate --base="$BASE_SHA" --head="$HEAD_SHA"

# Reached only if every migrate target exited zero (set -e). On failure the stored
# SHA stays put, so the next run retries the same range instead of skipping it.
psql "$OPS_DATABASE_URL" -v ON_ERROR_STOP=1 -v env="$ENV_NAME" -v sha="$HEAD_SHA" <<'SQL'
UPDATE migration_state
   SET sha = :'sha', migrated_at = now()
 WHERE env = :'env';
SQL

A CI script reads the last migrated SHA for the target environment, fails on schema drift via a per-project prisma migrate diff target, runs nx affected -t migrate with explicit --base and --head, and records the new head only after every migration succeeds.

Keeping a safety net and avoiding common failure modes

Affected detection answers "which schemas changed in this diff," not "which databases are behind." Those questions diverge more often than you'd expect, so pair affected runs with a full sweep: run nx run-many -t migrate across every schema project on each production deploy, or on a schedule. This is cheap because tools like prisma migrate deploy apply only pending migrations and exit cleanly when there are none. The sweep turns a missed migration into a short delay instead of a silent gap.

Failure modes to watch

  • Environment drift. If a deploy to staging was skipped or rolled back, the next affected run compares against a newer base SHA and never sees the migrations that environment missed. Only a full sweep, or a check of each database's migration history table, catches this.
  • Cached success. If migrate is cacheable, Nx can replay a previous successful result without contacting the database. The output looks identical to a real run. Set cache: false on the target, because the database's state is an input Nx cannot hash.
  • Over-broad inputs. If the target's inputs include shared config, the root lockfile, or a generic default named input, a dependency bump marks every schema as affected. Scope inputs to the schema file and migrations directory.
  • Irreversibility. Affected runs control which migrations execute, not whether they are safe. Dropping or renaming a column still requires expand/contract. Add the new shape, deploy services that handle both shapes, backfill the data, then remove the old shape in a later release once no running service depends on it.

Alternatives and impact on cost and engineering time

Affected-only migrations are one of several ways to scope schema changes. The main differences are how accurately each approach detects a change and how easily the record of applied migrations drifts from what is actually deployed.

Approach Accuracy of change detection Setup effort Drift risk
Run all migrations every pipeline Not needed; everything runs Low Low, but slow and noisy
Migrate from each service's deploy Tied to deploy, not to schema Low per service High when services share a schema
Path-filtered CI jobs without Nx Only as good as hand-written globs Medium Medium; globs miss shared libraries
nx affected with a migrate target Follows the project graph Medium to high Low if base SHAs are tracked

The cost is real. You add a project.json per schema and a migrate target with consistent inputs. You also need someone to own the base SHA bookkeeping and conventions for where migrations live. New schemas that skip these conventions fall out of the graph and silently stop being detected.

The savings come from not running migrations that cannot have changed. That gives shorter pipelines, fewer unrelated migration jobs in each review, and an obvious owner per schema.

Illustrative estimate (assumed numbers, not a benchmark). Assume 12 schemas, 3 minutes per migrate job, 2 schemas affected per change on average, and 40 pipelines per day.

Minutes saved per day = (total schemas − affected schemas) × minutes per job × pipelines per day = (12 − 2) × 3 × 40 = 1,200 runner-minutes.

Plug in your own counts. If the affected share stays close to the total, the setup cost is probably not worth paying.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to nx affected database migrations, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.