Handle frequent Database Migrations in Agentic Workflows

Handling database migrations manually becomes difficult when multiple developers or agents are working in parallel. Someone may update the application code but forget to update the database, or a schema change may be made directly in a shared environment and never added to the repository.

One way to make this process safer is to treat database migrations as code and validate them in the CI pipeline.

You can feed your agent the Skill to figure this out by itself.
In this post, I will use two tools:

• Goose to create, track, and apply migrations.
• Atlas to compare the expected schema with the schema in a live database.

Goose is responsible for applying migrations. Atlas is used here for schema inspection and comparison. We also see the various ways to mitigate schema conflicts gracefully.

The basic idea

The workflow has two parts:

1. When deploying the application, run Goose before deploying the new application version.
2. When opening a pull request, run the migrations against a temporary database and check for schema drift.

This gives us an automated check before merging and an automated migration step during deployment.

Step 1: Install Goose and Atlas

Install Goose using Go:

go install github.com/pressly/goose/v3/cmd/goose@v3.27.1

Install Atlas on macOS or Linux:

curl -sSf https://atlasgo.sh | sh

Step 2: Create a migration

Create a migrations directory in the repository. For example:

db/
└── migrations/
    ├── 00001_create_accounts.sql
    └── 00002_add_account_status.sql

Each migration has a version number in its filename. Goose uses this version to decide which migrations have already been applied.

Here is a sample migration:

-- 00001_create_accounts.sql

-- +goose Up
CREATE TABLE IF NOT EXISTS accounts (
    id BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- +goose Down
DROP TABLE IF EXISTS accounts;

The Up section contains the change that should be applied. The Down section contains the rollback.

For example, a migration that adds a table and an index might look like this:

-- +goose Up
CREATE TABLE IF NOT EXISTS account_events (
    id BIGSERIAL PRIMARY KEY,
    account_id BIGINT NOT NULL,
    event_type TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_account_events_account_id
    ON account_events(account_id);

-- +goose Down
DROP TABLE IF EXISTS account_events;

Once a migration has been applied to a shared environment, do not edit it. Add a new migration instead. Editing an old migration can make a new database and an existing database end up with different schemas.

Step 3: Run migrations locally

Set the connection string for the database you want to migrate:

export DATABASE_URL="postgres://user:password@localhost:5432/app_db?sslmode=disable"

Step 4: Apply migrations during deployment

Migrations should run before the application is deployed. This ensures that the database is ready for the application version that depends on the new schema. Try to

Here is a generic GitHub Actions example:

name: Deploy application

on:
  push:
    branches: [main]
    paths:
      - "db/migrations/**/*.sql"
      - "src/**"
      - "Dockerfile"

concurrency:
  group: database-deploy-production
  cancel-in-progress: false

jobs:
  deploy:
    runs-on: ubuntu-latest

    steps:
      - name: Checkout repository
        uses: actions/checkout@v4

      - name: Setup Go
        uses: actions/setup-go@v5
        with:
          go-version: "1.23"

      - name: Install Goose
        run: go install github.com/pressly/goose/v3/cmd/goose@v3.27.1

      - name: Add Goose to PATH
        run: echo "$(go env GOPATH)/bin" >> "$GITHUB_PATH"

      - name: Apply database migrations
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
        run: |
          goose -dir ./db/migrations postgres "$DATABASE_URL" status
          goose -dir ./db/migrations postgres "$DATABASE_URL" up

      - name: Build and deploy application
        run: |
          # Add your build and deployment commands here.
          # For example: docker build, docker push, or kubectl apply.

The database URL should be stored as a GitHub secret. For separate environments, use separate secrets such as DEV_DATABASE_URL, STAGING_DATABASE_URL, and PROD_DATABASE_URL.

The concurrency setting is important. It prevents two deployments targeting the same database from running migrations at the same time.

You can either use goose up on every deployment or check whether a new migration file was added in the current commit. Avoid deleting or modifying existing migrations.

Step 5: Check for schema drift in pull requests

Applying migrations during deployment solves one problem, but it does not tell us whether someone changed the database manually.

For a pull request, the CI workflow can:

1. Start a temporary PostgreSQL database.
2. Apply all repository migrations to that database.
3. Inspect the live database and compare it with the expected schema using Atlas.
4. Fail the pull request if the difference represents an untracked database change.

A simplified workflow looks like this:

name: Schema migration check

on:
  pull_request:
    branches: [main]
    paths:
      - "db/migrations/**/*.sql"
      - "schema/**"

jobs:
  schema-check:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: app_test
        ports:
          - 5432:5432
        options: >
          --health-cmd "pg_isready -U postgres -d app_test"
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - name: Checkout repository
        uses: actions/checkout@v4

      - name: Setup Go
        uses: actions/setup-go@v5
        with:
          go-version: "1.23"

      - name: Install Goose
        run: go install github.com/pressly/goose/v3/cmd/goose@v3.27.1

      - name: Add Goose to PATH
        run: echo "$(go env GOPATH)/bin" >> "$GITHUB_PATH"

      - name: Install Atlas
        run: curl -sSf https://atlasgo.sh | sh

      - name: Apply repository migrations
        env:
          TEST_DATABASE_URL: postgres://postgres:postgres@localhost:5432/app_test?sslmode=disable
        run: |
          goose -dir ./db/migrations postgres "$TEST_DATABASE_URL" up

      - name: Compare schemas
        env:
          LIVE_DATABASE_URL: ${{ secrets.DATABASE_URL }}
          TEST_DATABASE_URL: postgres://postgres:postgres@localhost:5432/app_test?sslmode=disable
        run: |
          atlas schema diff \
            --from "$LIVE_DATABASE_URL" \
            --to "$TEST_DATABASE_URL" \
            --dev-url "docker://postgres/16" \
            --format '{{ sql . }}'

The last step prints the SQL required to move one schema to the required stage.

Perhaps, the most important part of this workflow is deciding on a strategy to manage the migrations. Your choices -

1. Apply the newer migrations as-is. You ensure that no database changes take place manually which might fail the current migration.
2. You cant ensure this. In that case read on further...

Handling pending migrations correctly

There is one important detail with schema checks.

If the pull request contains a new migration, the live database is expected to be different from the repository's latest schema. That difference alone is not drift; it may simply mean that the new migration has not been deployed yet.

The safer check compares three states:

Live database
        ↓
Repository schema at the live migration version

This comparison must be empty. A difference here means that the live database contains a change that is not represented by the migrations.

The workflow can then compare:

Live database
        ↓
Repository schema including the pending PR migrations

This comparison may contain expected changes from the pull request and should not automatically block the merge.

This is why a production-ready drift workflow usually creates more than one temporary database: one represents the schema at the live migration version, and another represents the schema after applying all migrations from the pull request.

Making migrations safe for rolling deployments

Migration success is not the only concern. During a rolling deployment, old and new application versions may run at the same time. Schema changes should therefore be backward-compatible whenever possible.

Common expand-and-contract approach:

1. Add the new column or table.
2. Deploy code that supports both the old and new schema.
3. Backfill existing data if required.
4. Switch the application to the new schema.
5. Remove the old column or table in a later migration.

For example, renaming a column and immediately deploying code that only understands the new name can break requests handled by an older application instance. Splitting the change across multiple deployments reduces that risk.

What each part is responsible for

Goose remains the source of truth for migration history. Atlas is the safety check that makes untracked changes visible.

What the CI check tells us

Conclusion

With this setup, database changes become part of the normal development workflow. Goose tracks and applies the migrations, while Atlas makes schema differences visible in pull requests.

This does not remove the need to review migrations, but it removes a large amount of manual work and makes it much harder for application code and database schema to drift apart.

References

• Goose documentation
• Atlas schema diff documentation
• Atlas schema inspection documentation