feat: single-file schema example #17
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: pgschema Apply - Single File | |
| on: | |
| pull_request: | |
| types: [closed] | |
| branches: | |
| - main | |
| paths: | |
| - "singlefile/**" | |
| - ".github/workflows/pgschema-singlefile-apply.yml" | |
| permissions: | |
| contents: read | |
| pull-requests: write | |
| jobs: | |
| pgschema-apply-single: | |
| runs-on: ubuntu-latest | |
| if: github.event.pull_request.merged == true | |
| env: | |
| PGPASSWORD: postgres | |
| services: | |
| postgres: | |
| image: postgres:17 | |
| env: | |
| POSTGRES_PASSWORD: postgres | |
| POSTGRES_USER: postgres | |
| POSTGRES_DB: testdb | |
| options: >- | |
| --health-cmd pg_isready | |
| --health-interval 10s | |
| --health-timeout 5s | |
| --health-retries 5 | |
| ports: | |
| - 5432:5432 | |
| steps: | |
| - name: Checkout code | |
| uses: actions/checkout@v4 | |
| - name: Setup Go | |
| uses: actions/setup-go@v5 | |
| with: | |
| go-version: "stable" | |
| - name: Install pgschema | |
| run: go install github.com/pgschema/pgschema@latest | |
| - name: Download plan.json artifact | |
| uses: dawidd6/action-download-artifact@v6 | |
| with: | |
| workflow: pgschema-singlefile-plan.yml | |
| pr: ${{ github.event.pull_request.number }} | |
| name: pgschema-singlefile-plan-${{ github.event.pull_request.number }} | |
| path: . | |
| - name: Validate plan.json | |
| run: | | |
| if [ ! -f "plan.json" ]; then | |
| echo "❌ Error: plan.json not found. Make sure the plan workflow completed successfully." | |
| exit 1 | |
| fi | |
| echo "✅ plan.json found and ready for apply" | |
| echo "Plan file size: $(stat -f%z plan.json 2>/dev/null || stat -c%s plan.json) bytes" | |
| - name: Load baseline schema | |
| run: | | |
| echo "::group::Loading baseline schema to emulate remote database" | |
| PGPASSWORD=postgres psql -h localhost -p 5432 -U postgres -d testdb -f baseline.sql | |
| echo "::endgroup::" | |
| - name: Run pgschema apply | |
| id: apply | |
| run: | | |
| echo "::group::Applying schema changes using plan.json" | |
| echo "Running pgschema apply with pre-generated plan..." | |
| # Enable detailed error reporting | |
| set -x # Show commands as they execute | |
| # Run pgschema apply using the plan.json file | |
| APPLY_OUTPUT=$(pgschema apply \ | |
| --debug \ | |
| --host localhost \ | |
| --port 5432 \ | |
| --db testdb \ | |
| --user postgres \ | |
| --plan plan.json \ | |
| --lock-timeout "30s" \ | |
| --application-name "pgschema-github-action-apply" \ | |
| --auto-approve \ | |
| 2>&1) | |
| APPLY_EXIT_CODE=$? | |
| set +x # Disable command tracing | |
| echo "Apply exit code: $APPLY_EXIT_CODE" | |
| echo "Apply output:" | |
| echo "$APPLY_OUTPUT" | |
| echo "::endgroup::" | |
| # Set outputs for potential future use | |
| echo "output<<EOF" >> $GITHUB_OUTPUT | |
| echo "$APPLY_OUTPUT" >> $GITHUB_OUTPUT | |
| echo "EOF" >> $GITHUB_OUTPUT | |
| echo "exit_code=$APPLY_EXIT_CODE" >> $GITHUB_OUTPUT | |
| # Exit with the same code as pgschema | |
| exit $APPLY_EXIT_CODE | |
| - name: Report Success | |
| if: success() | |
| run: | | |
| echo "✅ Schema changes applied successfully!" | |
| echo "" | |
| echo "Applied to database: testdb" | |
| echo "Application name: pgschema-github-action-apply" | |
| echo "Lock timeout: 30s" | |
| - name: Report Failure | |
| if: failure() | |
| run: | | |
| echo "❌ Failed to apply schema changes!" | |
| echo "" | |
| echo "Please check the logs above for details." | |
| - name: Comment on PR with migration results | |
| if: always() | |
| uses: actions/github-script@v7 | |
| with: | |
| script: | | |
| // Get the apply output from the previous step | |
| const applyOutput = `${{ steps.apply.outputs.output }}` || 'No output captured'; | |
| // Determine if migration was successful based on job outcome | |
| const wasSuccessful = `${{ job.status }}` === 'success'; | |
| let commentBody; | |
| if (wasSuccessful) { | |
| commentBody = `## ✅ Schema Changes Applied Successfully! | |
| 📋 **Applied using plan:** \`pgschema-singlefile-plan-${{ github.event.pull_request.number }}\` | |
| <details> | |
| <summary>📋 Applied Changes</summary> | |
| \`\`\` | |
| ${applyOutput} | |
| \`\`\` | |
| </details> | |
| **Database:** testdb | |
| --- | |
| *This comment was automatically generated by the [pgschema](https://www.pgschema.com) Single File Apply workflow.*`; | |
| } else { | |
| commentBody = `## ❌ Schema Migration Failed! | |
| The single-file schema migration failed after merging this PR using plan \`pgschema-singlefile-plan-${{ github.event.pull_request.number }}\`. | |
| This could indicate: | |
| - Database schema changed between plan and apply (fingerprint mismatch) | |
| - Invalid plan.json file | |
| - Database connectivity issues | |
| Please review the error details below: | |
| <details> | |
| <summary>🔍 Error Details</summary> | |
| \`\`\` | |
| ${applyOutput} | |
| \`\`\` | |
| </details> | |
| **Database:** testdb | |
| --- | |
| *This comment was automatically generated by the [pgschema](https://www.pgschema.com) Single File Apply workflow.*`; | |
| } | |
| // Try to find existing comment | |
| const { data: comments } = await github.rest.issues.listComments({ | |
| ...context.repo, | |
| issue_number: context.issue.number, | |
| }); | |
| const botComment = comments.find(comment => | |
| comment.user.type === 'Bot' && | |
| comment.body.includes('pgschema Single File Apply workflow') | |
| ); | |
| if (botComment) { | |
| // Update existing comment | |
| await github.rest.issues.updateComment({ | |
| ...context.repo, | |
| comment_id: botComment.id, | |
| body: commentBody | |
| }); | |
| } else { | |
| // Create new comment | |
| await github.rest.issues.createComment({ | |
| ...context.repo, | |
| issue_number: context.issue.number, | |
| body: commentBody | |
| }); | |
| } |