Nx Affected Database Migrations: Run Checks Only When Needed

Why default affected detection misses database migrations

nx affected decides what to run by mapping changed files between a base and head commit to the projects that own them. It then walks the project graph to include every project that depends on those projects. The graph comes mostly from static analysis of source imports, plus any dependencies you declare explicitly. That works well for application code. It breaks down for database migrations because migrations rarely participate in the import graph.

Plain .sql files import nothing, and nothing imports them. A typical db/migrations directory at the repository root often isn't inside any project's root. A change there may mark nothing as affected unless it happens to match a global input. Even if you wrap the directory in a project, Nx has no edges connecting it to the code that actually shapes the schema.

The reverse gap is subtler. Suppose a developer edits an ORM model or a schema definition file in a shared data-access library. Nx correctly marks that library and the services importing it as affected. The migration project is not one of those importers, so it stays untouched, and the check that would catch a missing or conflicting migration never runs. The dependency is real, but it lives in your team's conventions, not in your code.

This tutorial is for backend and platform engineers who run monorepo CI against a shared database schema. It applies when migration checks are slow enough that you can't afford to run them on every commit, but you also can't afford to skip them when the schema changes. The fix is to make that implicit relationship explicit in the graph.

Modeling migrations as a first-class Nx project

Make the relationship explicit by giving migrations their own node in the project graph. Create libs/db-migrations, move (or point at) the migration files there, and add a project.json that names the project db-migrations. Nx now treats a change to any file under that directory as a change to a real project, not as loose files that no project claims.

Targets that wrap your existing tool

Define three targets using the nx:run-commands executor, each calling the migration tool your team already uses:

• migrate:check applies pending migrations against a disposable database and confirms the result matches the expected schema.
• migrate:lint runs static checks on migration files, such as destructive operations or missing down steps.
• migrate:dry-run prints the SQL that would execute, without applying it.

Wrapping the tool rather than replacing it keeps your local workflow unchanged. Nx only decides when these commands run.

Dependencies in both directions

Nx cannot see that migrations relate to your ORM models, so declare it with implicitDependencies. Set it in two places:

• In db-migrations: list the libraries that own schema and model definitions. Editing a model then marks the migrations as affected.
• In each service: list db-migrations. A new migration then marks the services that rely on it as affected, so their integration tests run too.

The resulting graph, read as "depends on":

A change to db-models now propagates upward through db-migrations to both services.

Copy{
  "name": "db-migrations",
  "$schema": "../../node_modules/nx/schemas/project-schema.json",
  "projectType": "library",
  "sourceRoot": "libs/db-migrations",
  "implicitDependencies": ["db-models", "db-schema"],
  "targets": {
    "migrate:check": {
      "executor": "nx:run-commands",
      "options": {
        "command": "./scripts/migrate.sh check --database-url=postgres://ci:ci@localhost:5432/migrations_check",
        "cwd": "libs/db-migrations"
      }
    },
    "migrate:lint": {
      "executor": "nx:run-commands",
      "options": {
        "command": "./scripts/migrate.sh lint ./migrations",
        "cwd": "libs/db-migrations"
      }
    },
    "migrate:dry-run": {
      "executor": "nx:run-commands",
      "options": {
        "command": "./scripts/migrate.sh plan --database-url=postgres://ci:ci@localhost:5432/migrations_check",
        "cwd": "libs/db-migrations"
      }
    }
  },
  "tags": ["scope:database"]
}

libs/db-migrations/project.json: three nx:run-commands targets wrap the team's existing migration script, and implicitDependencies on db-models and db-schema make model changes mark migrations as affected. Each service adds "db-migrations" to its own implicitDependencies.

Configuring inputs, namedInputs and caching for migration targets

The project graph decides which projects are affected. Inputs decide what counts as a change inside them. For a migration target, define a named input in nx.json, for example migrationInputs. It should cover four things:

• the migrations directory
• the schema and model files
• the migration tool's config file
• any environment-independent settings that change generated SQL, such as naming conventions or the default schema

Then use negated patterns to exclude test files, fixtures and Markdown. A README edit should not trigger a migration rerun. Add the ^ form of the input so changes in db-models and db-schema also count.

Caching needs more thought. A check that only diffs generated SQL against committed migrations is deterministic, so caching it is safe. That holds only if the cache key covers everything that can change the outcome: tool and driver versions, relevant environment variables, and the target database engine.

A check that applies migrations to a real database is different. Its result depends on state that Nx cannot hash. Mark that target non-cacheable. A cached success only means the inputs matched an earlier run. It says nothing about the live database.

Copy{
  "namedInputs": {
    "migrationInputs": [
      "{projectRoot}/migrations/**/*",
      "{projectRoot}/src/schema/**/*",
      "{projectRoot}/src/models/**/*",
      "{projectRoot}/migration.config.json",
      "{projectRoot}/naming-conventions.json",
      "!{projectRoot}/**/*.spec.ts",
      "!{projectRoot}/**/fixtures/**",
      "!{projectRoot}/**/*.md"
    ]
  },
  "targetDefaults": {
    "migrations-check": {
      "cache": true,
      "inputs": [
        "migrationInputs",
        "^migrationInputs",
        { "externalDependencies": ["pg"] },
        { "env": "DB_ENGINE" },
        { "runtime": "node --version" }
      ]
    },
    "migrations-apply": {
      "cache": false,
      "inputs": ["migrationInputs", "^migrationInputs"]
    }
  }
}

An nx.json sketch: a migrationInputs named input that excludes tests, fixtures and Markdown; a cacheable SQL-diff check whose key adds dependency, driver, engine and runtime inputs; and a non-cacheable target that applies migrations to a live database.

What a migration check should do when it runs

When the check does run, give it a database nobody else has touched. Declare one as a service container in the CI job, using the same engine and major version as production. A shared staging database carries leftover state from earlier branches, and that state can hide a migration that only works because a table already exists.

Apply every migration from an empty schema, in order. This proves the full chain still replays cleanly. A new environment, or a disaster recovery rebuild, depends on that.

Next, compare the resulting schema against the source of truth: the ORM models or the declarative schema file. Most migration tools can generate a diff or report pending changes. Fail the job if the diff is not empty. That catches drift in both directions: a model change without a migration, and a hand-edited migration the models never learned about.

Check ordering as well. Two branches that each add a migration with the same sequence number, or with the same parent revision, will both pass on their own and conflict after merge. Run the check against the merge result, not the branch tip alone.

If your team supports rollbacks, apply the down migrations back to empty, then apply the up migrations again. This exposes down migrations that were never tested.

Be clear about what this check cannot tell you. An empty database has no rows. It will not show a lock held for minutes on a large table, a backfill that runs for hours, or a new constraint that existing production data violates. Those risks need review and rehearsal against realistic data volumes.

Copy# Runs only when Nx marks the migrations project as affected (gate not shown).
name: migration-check
on: pull_request
jobs:
  migrations:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16 # pin to the same engine and major version as production
        env:
          POSTGRES_USER: ci
          POSTGRES_PASSWORD: ci-only-password # throwaway, not a real secret
          POSTGRES_DB: app_ci
        ports: ["5432:5432"]
        options: >-
          --health-cmd "pg_isready -U ci"
          --health-interval 5s --health-timeout 5s --health-retries 10
    env:
      # Assumes alembic env.py reads the connection URL from DATABASE_URL
      DATABASE_URL: postgresql://ci:ci-only-password@localhost:5432/app_ci
    steps:
      # For pull_request events this checks out the merge result, not the branch tip
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.12"
      - run: pip install -r requirements.txt
      - name: Ordering - two migrations with the same parent create two heads
        run: test "$(alembic heads | wc -l)" -eq 1 || { alembic heads; exit 1; }
      - name: Replay the full chain on an empty database
        run: alembic upgrade head
      - name: Drift - fail if models and migrated schema differ
        run: alembic check
      - name: Round trip - exercise down migrations, then replay
        run: alembic downgrade base && alembic upgrade head
      # None of this sees production rows: locks, backfill time and
      # constraint violations on existing data need separate rehearsal.

A GitHub Actions job using Alembic that starts a throwaway Postgres service container. It rejects branching migration histories, replays every migration from empty, fails on schema drift with alembic check, and round-trips the down migrations.

Wiring nx affected into monorepo CI with correct base and head SHAs

For pull requests, set --head to the PR's head commit and --base to the merge-base of that commit and the target branch, computed with git merge-base. Do not use the target branch tip. If main has moved since the branch was cut, comparing against its tip counts other people's changes as yours. For pushes to main, set --base to the last commit on main where this pipeline succeeded, not HEAD~1. A push can contain several commits, and a failed run must not drop changes from the next comparison. Then run nx affected -t lint test build migrate:check with both SHAs passed explicitly.

A wrong base usually fails quietly. Nx gets a smaller diff, decides nothing relevant changed, and the job passes. Common causes:

• Shallow clones. The merge-base or the stored SHA is not in the checkout. Fetch enough history, or the full history, before computing anything.
• Force-pushes. The recorded "last good" SHA may have been rewritten away. Check that it exists with git cat-file -e. If it doesn't, fall back to a full run.
• Merge queues. The commit under test is a temporary merge that sits on top of other queued PRs. Use the queue's target tip as the base.
• First push of a branch. There is no previous SHA, and the event may report all zeros. Treat that as "run everything."

The safety net

Run nx run-many -t migrate:check on every merge to main and nightly. Two branches can each add a migration with the same sequence number. Each passes its own affected check, and they only conflict once both are merged. A full run also catches projects whose missing implicit dependencies kept them out of the graph.

Copy#!/usr/bin/env bash
# Inputs from the CI system (placeholder names): CI_EVENT, TARGET_BRANCH,
# PR_HEAD_SHA, LAST_GOOD_SHA (last main commit where this pipeline passed).
set -euo pipefail
ZERO=0000000000000000000000000000000000000000
TARGETS="lint,test,build,migrate:check"

# Shallow clones hide the merge-base and old SHAs, so fetch full history first.
if [ "$(git rev-parse --is-shallow-repository)" = "true" ]; then
  git fetch --unshallow origin
fi
git fetch origin "$TARGET_BRANCH"

HEAD_SHA="${PR_HEAD_SHA:-$(git rev-parse HEAD)}"
case "$CI_EVENT" in
  # Use the fork point, not the target tip, so other people's merges are not counted as ours.
  pull_request) BASE_SHA=$(git merge-base "origin/$TARGET_BRANCH" "$HEAD_SHA") ;;
  # The queue commit is a temporary merge; compare it against the target tip.
  merge_queue)  BASE_SHA=$(git rev-parse "origin/$TARGET_BRANCH") ;;
  # Last green commit, not HEAD~1: covers multi-commit pushes and earlier failed runs.
  push)         BASE_SHA="${LAST_GOOD_SHA:-}" ;;
  *)            BASE_SHA="" ;;  # schedule or unknown event: no base, run everything
esac

# Missing, all-zero, or rewritten-by-force-push base means "run everything", never "run nothing".
if [ -z "$BASE_SHA" ] || [ "$BASE_SHA" = "$ZERO" ] \
   || ! git cat-file -e "${BASE_SHA}^{commit}" 2>/dev/null; then
  echo "No usable base SHA; running full target set"
  npx nx run-many -t "$TARGETS"
else
  echo "Affected range: $BASE_SHA..$HEAD_SHA"
  npx nx affected -t "$TARGETS" --base="$BASE_SHA" --head="$HEAD_SHA"
fi

# Safety net: full migration check on every main push and nightly run, to catch
# cross-branch sequence-number clashes and projects missing from the graph.
if [ "$CI_EVENT" = "push" ] || [ "$CI_EVENT" = "schedule" ]; then
  npx nx run-many -t migrate:check
fi

A CI-agnostic shell step that picks the correct base and head SHAs for pull requests, merge queues and main pushes. It falls back to a full run when the base is missing or was rewritten, and always runs a full migrate:check on main and on the nightly schedule.

Verifying the project graph instead of trusting it

The affected configuration is only as good as the graph Nx builds from it, so test it directly rather than waiting for a missed migration in production to reveal a gap.

Three sample edits

On a throwaway branch, make one trivial change at a time and commit it, then run nx show projects --affected against your main branch as the base.

1. Touch a model file in a service that owns tables, for example by adding a comment to an entity definition. The migrations project should appear in the output alongside the service. If only the service shows up, the inputs you defined for migration targets are not picking up model changes.
2. Add an empty migration file. Every service listed as a consumer of the migrations project should appear. A missing service means its implicit dependency was never declared.
3. Edit the migration config, such as the connection or schema settings file. This should mark the migrations project affected. If it doesn't, the file is probably outside the project root or excluded by a named input.

Use nx graph to inspect the edges. It shows why a project is affected, which the flat list can't.

Why this goes stale

implicitDependencies are maintained by hand, and nothing fails when they drift. A new service that starts reading from the shared database, or a table whose ownership moves between teams, changes the real dependency without changing the graph. Make the three edits part of code review for any PR that adds a service or touches database ownership. Better still, script them as a CI test that asserts the expected project list, so drift fails a build instead of escaping silently.

When to avoid it, alternatives, and impact on cost and engineering time

Skip this setup when migration checks are cheap. If a repo has a handful of services and the check finishes in a minute or two, run it on every pull request. You give up a few CI minutes, and in return there is no project graph to model and no risk of a missed dependency silently skipping the check. The modeling only pays off when checks are slow (spinning up a database, replaying a long migration history) and most PRs never touch schema code.

Path filters are a reasonable middle ground. They cannot see that a change in a shared ORM library affects migrations unless someone keeps the glob list current by hand.

Illustrative estimate (assumed numbers, not a benchmark): 200 PRs per month and a 12-minute migration check.

Formula: monthly minutes = PRs × fraction affected × check minutes

• Always run: 200 × 1.0 × 12 = 2,400 minutes
• Scoped inputs, 20% of PRs affected: 200 × 0.2 × 12 = 480 minutes
• Overly broad inputs, where a widely shared config file is listed as an input and 70% of PRs are affected: 200 × 0.7 × 12 = 1,680 minutes

In the broad case most of the savings are gone, and you still carry the configuration burden. Compare the minutes you save with the engineering hours spent maintaining inputs and graph tests. If those hours cost more than the minutes, run the checks on every PR.

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.