GitOps - pgschema, Declarative Schema Migration for Postgres

pgschema integrates seamlessly with GitOps workflows, providing a declarative approach to database schema management that fits naturally into your existing CI/CD pipelines. The typical workflow follows a review-then-apply pattern that ensures all schema changes are properly reviewed before being applied to production.

Development Phase

Developers modify schema files in their feature branch
Schema changes are committed alongside application code
The desired state is expressed declaratively in SQL files

Pull/Merge Request Review

When a PR/MR is opened, CI automatically runs pgschema plan
The plan output shows exactly what changes will be applied
The plan is posted as a comment on the PR/MR for review
Reviewers can see:
- What tables, indexes, functions will be added/modified/dropped
- The exact SQL statements that will be executed
- Whether changes can run in a transaction
- Any potentially destructive operations

Approval and Merge

Once reviewers approve the schema changes
The PR/MR is merged to the target branch (e.g., main, production)

Deployment Phase

After merge, CD pipeline automatically runs pgschema apply
The apply command uses the reviewed plan to execute migrations
Schema changes are applied to the target database
The deployment includes safety checks for concurrent modifications

GitHub Actions

GitHub Actions Example Repository

Complete example repository with workflows and sample PRs

Single-file Schema Workflow

Plan GitHub Actions

Generates migration plan on pull requests

Apply GitHub Actions

Applies migrations after merge

Example PR

Live example with plan comments

Modular Multi-file Schema Workflow

Plan GitHub Actions

Handles modular schema files

Apply GitHub Actions

Applies modular migrations

Example PR

Multi-file schema changes

Example GitHub Actions Workflows

Plan Workflow
Apply Workflow

.github/workflows/pgschema-plan.yml

name: Schema Migration Plan

on:
  pull_request:
    paths:
      - 'schema/schema.sql'

jobs:
  plan:
    runs-on: ubuntu-latest
    
    steps:
      - uses: actions/checkout@v4
        with:
          fetch-depth: 0
      
      - uses: actions/setup-go@v5
        with:
          go-version: '1.24'
      
      - name: Install pgschema
        run: go install github.com/pgplex/pgschema@latest
      
      - name: Generate migration plan
        env:
          PGPASSWORD: ${{ secrets.DB_PASSWORD }}
        run: |
          pgschema plan \
            --host ${{ secrets.DB_HOST }} \
            --port ${{ secrets.DB_PORT }} \
            --db ${{ secrets.DB_NAME }} \
            --user ${{ secrets.DB_USER }} \
            --file schema/schema.sql \
            --output-json plan.json \
            --output-human plan.txt
      
      - name: Comment PR with plan
        uses: actions/github-script@v7
        with:
          script: |
            const fs = require('fs');
            const planContent = fs.readFileSync('plan.txt', 'utf8');
            
            // Find existing comment
            const { data: comments } = await github.rest.issues.listComments({
              owner: context.repo.owner,
              repo: context.repo.repo,
              issue_number: context.issue.number,
            });
            
            const botComment = comments.find(comment => 
              comment.user.type === 'Bot' && 
              comment.body.includes('## Migration Plan')
            );
            
            const body = `## Migration Plan\n\`\`\`sql\n${planContent}\n\`\`\``;
            
            if (botComment) {
              await github.rest.issues.updateComment({
                owner: context.repo.owner,
                repo: context.repo.repo,
                comment_id: botComment.id,
                body
              });
            } else {
              await github.rest.issues.createComment({
                owner: context.repo.owner,
                repo: context.repo.repo,
                issue_number: context.issue.number,
                body
              });
            }
      
      - name: Upload plan artifact
        uses: actions/upload-artifact@v4
        with:
          name: pgschema-plan-${{ github.event.pull_request.number }}
          path: plan.json
          retention-days: 30

.github/workflows/pgschema-apply.yml

name: Apply Schema Migration

on:
  pull_request:
    types: [closed]
    branches:
      - main
    paths:
      - 'schema/schema.sql'

jobs:
  apply:
    runs-on: ubuntu-latest
    if: github.event.pull_request.merged == true
    environment: production
    
    steps:
      - uses: actions/checkout@v4
      
      - uses: actions/setup-go@v5
        with:
          go-version: '1.24'
      
      - name: Install pgschema
        run: go install github.com/pgplex/pgschema@latest
      
      - name: Download plan artifact
        uses: dawidd6/action-download-artifact@v6
        with:
          workflow: pgschema-plan.yml
          pr: ${{ github.event.pull_request.number }}
          name: pgschema-plan-${{ github.event.pull_request.number }}
          path: .
      
      - name: Validate plan exists
        run: |
          if [ ! -f "plan.json" ]; then
            echo "Error: plan.json not found"
            exit 1
          fi
      
      - name: Apply migration
        id: apply
        env:
          PGPASSWORD: ${{ secrets.DB_PASSWORD }}
        run: |
          echo "Applying migration plan from PR #${{ github.event.pull_request.number }}"
          
          # Capture apply output
          APPLY_OUTPUT=$(pgschema apply \
            --host ${{ secrets.DB_HOST }} \
            --port ${{ secrets.DB_PORT }} \
            --db ${{ secrets.DB_NAME }} \
            --user ${{ secrets.DB_USER }} \
            --plan plan.json \
            --auto-approve \
            2>&1)
          
          APPLY_EXIT_CODE=$?
          
          echo "Apply output:"
          echo "$APPLY_OUTPUT"
          
          # Set output for the next step
          echo "output<<EOF" >> $GITHUB_OUTPUT
          echo "$APPLY_OUTPUT" >> $GITHUB_OUTPUT
          echo "EOF" >> $GITHUB_OUTPUT
          
          exit $APPLY_EXIT_CODE
      
      - name: Comment on PR
        uses: actions/github-script@v7
        with:
          script: |
            await github.rest.issues.createComment({
              owner: context.repo.owner,
              repo: context.repo.repo,
              issue_number: context.issue.number,
              body: '✅ Schema migration has been successfully applied to production!'
            });

GitLab CI

Demonstrates a complete workflow for reviewing and applying schema changes through merge requests.

.gitlab-ci.yml

stages:
  - plan
  - apply

variables:
  PGPASSWORD: $DB_PASSWORD

# Plan job - runs on merge requests and main branch
pgschema-plan:
  stage: plan
  image: golang:1.24
  script:
    - go install github.com/pgplex/pgschema@latest
    - |
      pgschema plan \
        --host $DB_HOST \
        --port ${DB_PORT:-5432} \
        --db $DB_NAME \
        --user $DB_USER \
        --file schema/schema.sql \
        --output-json plan.json \
        --output-human plan.txt
    - cat plan.txt
    
    # Post plan as MR comment (only for merge requests)
    - |
      if [ -n "$CI_MERGE_REQUEST_IID" ]; then
        PLAN_CONTENT=$(cat plan.txt)
        curl --request POST \
          --header "PRIVATE-TOKEN: $CI_JOB_TOKEN" \
          --data-urlencode "body=## Migration Plan\n\`\`\`\n$PLAN_CONTENT\n\`\`\`" \
          "$CI_API_V4_URL/projects/$CI_PROJECT_ID/merge_requests/$CI_MERGE_REQUEST_IID/notes"
      fi
  
  artifacts:
    paths:
      - plan.json
      - plan.txt
    expire_in: 7 days
  
  rules:
    - if: '$CI_PIPELINE_SOURCE == "merge_request_event"'
      changes:
        - schema/**
    - if: '$CI_COMMIT_BRANCH == "main"'
      changes:
        - schema/**

# Apply job - manual trigger on main branch
pgschema-apply:
  stage: apply
  image: golang:1.24
  needs: ["pgschema-plan"]
  dependencies:
    - pgschema-plan
  
  script:
    - go install github.com/pgplex/pgschema@latest
    - |
      if [ ! -f plan.json ]; then
        echo "Error: plan.json not found"
        exit 1
      fi
    - |
      pgschema apply \
        --host $DB_HOST \
        --port ${DB_PORT:-5432} \
        --db $DB_NAME \
        --user $DB_USER \
        --plan plan.json \
        --auto-approve
    - echo "✅ Schema migration applied successfully"
  
  when: manual  # Require manual approval
  environment:
    name: production
  
  rules:
    - if: '$CI_COMMIT_BRANCH == "main"'
      changes:
        - schema/**

Azure Pipelines

Demonstrates a complete workflow for reviewing and applying schema changes through pull requests.

azure-pipelines.yml

trigger:
  branches:
    include:
      - main
  paths:
    include:
      - schema/*

pr:
  branches:
    include:
      - main
  paths:
    include:
      - schema/*

variables:
  PGPASSWORD: $(DB_PASSWORD)

stages:
  - stage: Plan
    displayName: 'Generate Migration Plan'
    jobs:
      - job: PlanJob
        displayName: 'Run pgschema plan'
        pool:
          vmImage: 'ubuntu-latest'
        
        steps:
          - task: GoTool@0
            inputs:
              version: '1.24'
          
          - script: |
              go install github.com/pgplex/pgschema@latest
            displayName: 'Install pgschema'
          
          - script: |
              pgschema plan \
                --host $(DB_HOST) \
                --port $(DB_PORT) \
                --db $(DB_NAME) \
                --user $(DB_USER) \
                --file schema/schema.sql \
                --output-json plan.json \
                --output-human plan.txt
            displayName: 'Generate plan'
          
          - script: cat plan.txt
            displayName: 'Display plan'
          
          # Post plan as PR comment (for PRs only)
          - task: GitHubComment@0
            condition: eq(variables['Build.Reason'], 'PullRequest')
            inputs:
              gitHubConnection: 'github-connection'
              repositoryName: '$(Build.Repository.Name)'
              comment: |
                ## Migration Plan
                <pre>$(cat plan.txt)</pre>
          
          - publish: plan.json
            artifact: migration-plan
            displayName: 'Publish plan artifact'

  - stage: Apply
    displayName: 'Apply Migration'
    dependsOn: Plan
    condition: and(succeeded(), eq(variables['Build.SourceBranch'], 'refs/heads/main'))
    jobs:
      - deployment: ApplyJob
        displayName: 'Apply pgschema migration'
        pool:
          vmImage: 'ubuntu-latest'
        environment: 'production'
        strategy:
          runOnce:
            deploy:
              steps:
                - task: GoTool@0
                  inputs:
                    version: '1.24'
                
                - script: |
                    go install github.com/pgplex/pgschema@latest
                  displayName: 'Install pgschema'
                
                - download: current
                  artifact: migration-plan
                  displayName: 'Download plan artifact'
                
                - script: |
                    if [ ! -f $(Pipeline.Workspace)/migration-plan/plan.json ]; then
                      echo "Error: plan.json not found"
                      exit 1
                    fi
                  displayName: 'Verify plan exists'
                
                - script: |
                    pgschema apply \
                      --host $(DB_HOST) \
                      --port $(DB_PORT) \
                      --db $(DB_NAME) \
                      --user $(DB_USER) \
                      --plan $(Pipeline.Workspace)/migration-plan/plan.json \
                      --auto-approve
                  displayName: 'Apply migration'
                
                - script: echo "✅ Schema migration applied successfully"
                  displayName: 'Report success'

Documentation Index

​GitHub Actions

GitHub Actions Example Repository

Plan GitHub Actions

Apply GitHub Actions

Example PR

Plan GitHub Actions

Apply GitHub Actions

Example PR

​GitLab CI

​Azure Pipelines

GitHub Actions

GitLab CI

Azure Pipelines