Tracking PostgreSQL Schema Changes with Git — Declarative DB GitOps Powered by pg-delta

Have you ever run an ALTER TABLE on production late at night, only to watch your service grind to a halt as ACCESS EXCLUSIVE LOCK kicked in? I have. And I've felt the frustration of having to dig through Slack DMs and Jira tickets just to figure out who made a change, when, and why. Code has its full history in Git — so why does the DB schema live in some murky gray zone?

In this post, we'll explore a declarative GitOps workflow for managing DB schemas the same way we manage code, centered on pg-delta developed by Supabase. Instead of the "run these SQL statements in this order" approach of tools like Flyway or Liquibase, you simply declare the desired final state — "this DB should look like this" — and let the tool calculate the rest. The key idea is that the delta between the current DB and the declared state is computed automatically, applying the same process to your DB that you already use for code: from SQL generation all the way through review.

We'll cover a GitHub Actions CI pipeline integration example, situational criteria for choosing between pgschema and Atlas, and an honest look at the gotchas you absolutely need to know about in production.

Core Concepts

Imperative vs. Declarative — Sounds Familiar

Traditional tools like Flyway or Liquibase follow the imperative approach. You instruct them: "run these SQL files in this order" — V001__create_users.sql, V002__add_email_column.sql, and so on. As history accumulates, you have to walk through dozens of files in sequence just to understand what the current schema looks like.

The declarative approach is different. You write only the desired final state in a single schema.sql file — "this DB should have this table structure right now" — and the tool compares it against the current DB state and automatically generates the necessary DDL.

Declarative schema management means defining the desired final DB state in Git, and letting the tool compute the delta from the current state to automatically generate migrations.

GitOps is an operational paradigm that makes Git the single source of truth for every change. You're probably already familiar with this for infrastructure and app deployments — the idea here is to apply it to DB schemas as well. Think of it as treating schema changes like code changes: PR → Review → Merge → Apply.

Schema Drift refers to the phenomenon where DB schemas across development, staging, and production environments gradually diverge from each other. Think of it as the DB equivalent of "it worked on my machine."

The Three Tools Covered in This Post

Before diving into examples, let's briefly introduce the tools that appear throughout.

It may not be immediately obvious which of the three to use. For now, just keep this in mind: "pg-delta is the core diff engine in Supabase, while pgschema and Atlas are separate tools that offer the same declarative paradigm without Supabase." The right choice will become clearer as you see each example in action.

Why pg-delta Is Better Than migra

pg-delta is a core package within Supabase's pg-toolbelt monorepo, replacing the Python-based migra that previously filled this role. Its purpose is clear: a diff engine that compares two PostgreSQL schema states and automatically generates the correct migration DDL.

In brief, pg-delta parses both the "desired state" (as declared in schema.sql) and the "current state" (the live DB), performs an AST-level comparison, and derives the DDL needed to bridge the gap. Issues that plagued migra — such as misinterpreting RENAME as DROP + CREATE, or failing to handle PostgreSQL-specific DDL like ALTER SEQUENCE — have been addressed in pg-delta. If you've ever nearly lost data because migra handled a column rename incorrectly, you'll know exactly what this means.

Within the Supabase CLI, pg-delta can be activated in two slightly different ways:

# Keep the existing migration file workflow, but swap the diff engine to pg-delta
# (Use this when you want to stick with the V001__, V002__ file approach
#  but improve the quality of generated output)
supabase db pull --diff-engine pg-delta
 
# Switch to a fully declarative workflow
# (Use this when moving from migration files to managing a single schema.sql)
supabase db pull --use-pg-delta

If you're just getting started, --diff-engine pg-delta is a good entry point — swap only the engine while keeping your existing workflow intact.

Practical Application

The three examples in this section are independent. Feel free to start with whichever is closest to your environment.

Supabase + GitHub Actions: Auto-Posting Schema Change Plans to PRs

If you're already on a Supabase project, this is the most natural starting point. Keep declarative schema files in a supabase/schemas/ directory, and set up a workflow that automatically posts the change plan as a PR comment whenever a PR is opened.

For this to work, you'll need SUPABASE_ACCESS_TOKEN and SUPABASE_PROJECT_REF set in your repository Secrets. Add them under GitHub repository Settings → Secrets and variables → Actions.

# .github/workflows/schema-check.yml
name: Schema Plan
on:
  pull_request:
    paths:
      - 'supabase/schemas/**'
 
jobs:
  plan:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
 
      - name: Setup Supabase CLI
        uses: supabase/setup-cli@v1
 
      - name: Link Supabase project
        run: supabase link --project-ref ${{ secrets.SUPABASE_PROJECT_REF }}
        env:
          SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}
 
      - name: Generate schema diff
        run: |
          supabase db diff --use-pg-delta --linked --schema public > migration_plan.sql
 
      - name: Comment plan on PR
        uses: actions/github-script@v7
        with:
          script: |
            const plan = require('fs').readFileSync('migration_plan.sql', 'utf8')
            if (!plan.trim()) return
            github.rest.issues.createComment({
              issue_number: context.issue.number,
              owner: context.repo.owner,
              repo: context.repo.repo,
              body: `## 스키마 변경 계획\n\`\`\`sql\n${plan}\n\`\`\``
            })

The heart of this pipeline is supabase db diff --use-pg-delta. pg-delta compares the current connected DB state against the modified schema file, automatically generates migration SQL, and posts that output as a PR comment.

Team members can review both the code changes and the schema change plan together in the same PR. Having "why is this column being added?" discussed in the same space as the code review has a surprisingly large impact on team culture.

pgschema: Introducing a Declarative Workflow to Vanilla PostgreSQL Without Supabase

If your team isn't using Supabase, pgschema is a great alternative. It's a Go-based CLI that performs pure state comparison and applies changes with no migration tracking table and no separate server required.

This example reflects information as of the time of writing (May 2025). For the PostgreSQL versions supported by pgschema, we recommend checking the official repository.

# 1. Dump: Extract the current DB schema to a SQL file (once initially, or to capture external changes)
pgschema dump --url $DATABASE_URL > schema.sql
 
# 2. Plan: Compare the modified schema.sql against the live DB and generate migration DDL
pgschema plan --url $DATABASE_URL --schema schema.sql --output plan.json
 
# 3. Apply: Apply the validated plan
# ⚠️ --auto-approve is for CI environments only. Locally, review the plan contents before applying.
pgschema apply --plan plan.json --auto-approve

At first I thought, "what's the point of plan.json — why the extra step?" But there's a safety mechanism hidden inside: fingerprint verification. The DB state at the time the plan was generated is recorded within it, so if another team member modifies the schema between plan and apply, the conflict is detected and the apply is blocked. This is an important safety net when multiple teams are modifying the same DB concurrently.

Atlas Kubernetes Operator: Including DB Schemas in Your GitOps Pipeline

This section is for teams already running Kubernetes and ArgoCD. If that's not your environment, feel free to skim this section.

If you're running Kubernetes with ArgoCD, the Atlas Operator provides the most native integration. Declare your schema as a Kubernetes resource, and ArgoCD continuously synchronizes the Git state with the actual DB.

It is strongly recommended to store DB connection credentials in a Kubernetes Secret and reference them from there. Hardcoding connection strings directly in YAML risks exposing credentials in Git.

# db-credentials-secret.yaml (managed as a separate Secret resource)
apiVersion: v1
kind: Secret
metadata:
  name: db-credentials
type: Opaque
stringData:
  # In production, always use sslmode=require or verify-full
  url: "postgres://user:pass@postgres:5432/app?sslmode=require"

# atlas-schema.yaml
apiVersion: db.atlasgo.io/v1alpha1
kind: AtlasSchema
metadata:
  name: app-schema
spec:
  credentials:
    secretKeyRef:
      name: db-credentials
      key: url
  schema:
    sql: |
      CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        email TEXT UNIQUE NOT NULL
      );
      CREATE TABLE posts (
        id SERIAL PRIMARY KEY,
        user_id INT REFERENCES users(id),
        title TEXT NOT NULL
      );

Commit these files to Git, and ArgoCD will detect the change and apply it to the DB via the Atlas Operator. This lets you manage both infrastructure (Kubernetes resources) and schema (DB state) through the same GitOps pipeline.

Pros and Cons

To be honest, the most painful thing early on was rollbacks. When you revert in Git, the code comes back — but data from a dropped column does not. A lot of people get blindsided by this, so I'd rather you go in with a clear picture of the downsides alongside the benefits.

Advantages

Disadvantages and Caveats

ACCESS EXCLUSIVE LOCK is the most restrictive lock PostgreSQL places on a table when executing certain DDL statements. While this lock is held, all reads and writes to the table are queued. Auto-generated migrations may include statements that trigger this lock, so it is essential to verify before applying to production.

The Most Common Mistakes in Practice

1. Connecting auto-apply without destructive change detection: Wiring apply directly to the CI pipeline without a plan step risks running a DROP COLUMN by accident. The recommended pattern is to expose the plan in the PR and require manual approval before applying.

2. Deploying to production without a rollback plan: Having a GitOps pipeline does not mean DB rollbacks are handled automatically. Changes like dropping a column cannot be undone by Git revert alone — data does not come back. It's worth preparing a backup snapshot strategy before any schema apply.

3. Mixing old Flyway/Liquibase files with the new approach: Running both legacy migration files and the declarative workflow during a transition will cause conflicts. Set a clear cutover point and use only the declarative approach after that. Dumping the schema state at the cutover point and using it as the baseline makes for a clean starting line.

Closing Thoughts

Managing your DB schema in Git means the whole team can treat schema changes with the same confidence they treat code changes. pg-delta serves as the core diff engine for this workflow in Supabase environments, while pgschema and Atlas extend the same paradigm to suit different environments.

Three steps you can take right now:

1. Extract a current DB snapshot: If you're on Supabase, start with supabase db pull --use-pg-delta --schema-only. If you're running PostgreSQL directly without Supabase, try pgschema dump --url $DATABASE_URL > schema.sql. Either way, extracting your current DB schema to a file is the right starting point.

2. Wire up the PR comment pipeline: Copy the GitHub Actions workflow from the Supabase example above into .github/workflows/schema-check.yml in your repository. You'll immediately see the experience of having the schema changes automatically posted as SQL comments every time you open a PR.

3. Add destructive change detection rules: If you're using Atlas, use the Schema Rules feature to configure CI to automatically block migrations containing DROP TABLE or DROP COLUMN. This single safeguard alone can dramatically reduce accidents caused by human error.

References

• pg-toolbelt | Supabase GitHub
• Declarative Database Schemas | Supabase Official Docs
• pgschema GitOps Workflow | pgschema.com
• pgschema | GitHub
• GitOps for Databases Part 1 | Atlas Blog
• Schema Rules: Guarding Your Database with Atlas | Atlas Blog
• The Hard Truth about GitOps and DB Rollbacks | Atlas Blog
• pg-schema-diff | Stripe GitHub
• pgschema GitHub Actions Example | GitHub
• Manage PostgreSQL Clusters with GitOps using CloudNativePG and ArgoCD | Proventa