pgschema GitHub Actions Example

This repository demonstrates how to use pgschema with GitHub Actions to implement the plan-review-apply workflow pattern for safe database schema migrations. It includes examples for both single-file and multi-file schema approaches.

Overview

Plan Workflows (Pull Requests)

• Single File Plan - Shows migration plan for single-file schemas
• Multi File Plan - Shows migration plan for multi-file schemas

Plan workflows automatically:

• Run pgschema plan --output-human stdout --output-json plan.json when a PR modifies schema files
• Generate both human-readable output for PR comments and plan.json artifact for deployment
• Post the migration plan as a comment on the PR for team review
• Upload plan.json as a GitHub artifact for the apply workflow
• Update the comment if the PR is synchronized with new changes

Apply Workflows (Merged Pull Requests)

• Single File Apply - Applies schema changes for single-file approach
• Multi File Apply - Applies schema changes for multi-file approach

Apply workflows automatically:

• Download the plan.json artifact generated during the plan phase using dawidd6/action-download-artifact
• Run pgschema apply --plan plan.json --auto-approve using the pre-approved plan
• Validate database fingerprint to ensure no concurrent schema changes occurred
• Apply changes to a test PostgreSQL 17 container
• Comment on the PR with success or failure results and detailed logs

Plan-Review-Apply Workflow Pattern

This implementation follows the pgschema plan-review-apply pattern for safe database migrations:

1. Plan Phase (Pull Request)

• Generates detailed migration plan with pgschema plan
• Creates both human-readable output and plan.json artifact
• Team reviews the proposed changes in PR comments
• Plan.json is stored as GitHub artifact for later use

2. Review Phase (Pull Request Review)

• Team examines the migration plan for correctness and safety
• Considers business impact and potential risks
• Approves or requests changes before merging

3. Apply Phase (Merge to Main)

• Downloads the exact plan.json that was reviewed using dawidd6/action-download-artifact
• Applies using pgschema apply --plan plan.json --auto-approve
• Fingerprint validation prevents concurrent schema changes
• Ensures exactly what was planned is what gets applied

Setup

PostgreSQL 17 Test Container Setup

The workflows automatically set up a PostgreSQL 17 test container and load the baseline schema to emulate a remote database with existing schema. The baseline.sql file contains a complete schema.

Workflow Behavior

Each workflow now:

1. Starts a clean PostgreSQL 17 container
2. Loads baseline.sql to simulate an existing remote database
3. Runs pgschema against this baseline to generate migration plans or apply changes
4. Reports results with detailed logging

This approach ensures that:

• Migration plans show realistic diffs against existing schema
• Apply operations work against a database with existing data structure
• Tests validate changes in a production-like environment
• Plans are generated and validated against the actual target database state
• Fingerprint validation catches any concurrent schema modifications

GitHub Secrets

No secrets required! Both workflows start a PostgreSQL 17 test container for demonstration purposes, making them fully self-contained.

Schema Organization

Single File Approach

• Place your complete schema in singlefile/schema.sql. This is generated by pgschema dump --host localhost --user postgres --password testpwd1 --db employee --file schema.sql.
• All tables, indexes, functions, and triggers in one file
• Plan workflow: .github/workflows/pgschema-singlefile-plan.yml
• Apply workflow: .github/workflows/pgschema-singlefile-apply.yml

Multi File Approach

• Place SQL files in the multifile/ directory. This is generated by pgschema dump --host localhost --user postgres --password testpwd1 --db employee --multi-file --file main.sql
• Uses main.sql as the entry point with psql \i directives
• Organize files by type in subdirectories (tables/, functions/, views/, etc.). Each file contains a specific database object
• Plan workflow: .github/workflows/pgschema-multifile-plan.yml
• Apply workflow: .github/workflows/pgschema-multifile-apply.yml

For more information, visit the pgschema documentation.