Skip to main content
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.
1

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
2

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
3

Approval and Merge

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

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.23'
      
      - name: Install pgschema
        run: go install github.com/pgschema/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

​
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.23
  script:
    - go install github.com/pgschema/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.23
  needs: ["pgschema-plan"]
  dependencies:
    - pgschema-plan
  
  script:
    - go install github.com/pgschema/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.23'
          
          - script: |
              go install github.com/pgschema/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.23'
                
                - script: |
                    go install github.com/pgschema/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'
⌘I