CI/CD Integration

pgfence runs in GitHub Actions, GitLab CI, or any other CI system through the CLI that ships in the npm package.

GitHub Actions

yaml

- uses: actions/checkout@v4

- uses: actions/setup-node@v4
  with:
    node-version: 20

- name: Check migration safety
  run: npx @flvmnt/pgfence analyze --ci --max-risk medium migrations/*.sql

GitHub PR Comments

yaml

- name: Analyze migrations
  run: |
    npx @flvmnt/pgfence analyze --output github migrations/*.sql > pgfence-report.md

- name: Comment on PR
  uses: marocchino/sticky-pull-request-comment@v2
  with:
    path: pgfence-report.md

GitHub Code Scanning (SARIF)

Upload pgfence findings to GitHub Code Scanning for inline annotations directly on the pull request diff.

yaml

- name: Analyze migrations
  run: npx @flvmnt/pgfence analyze --output sarif migrations/*.sql > pgfence.sarif
- name: Upload to GitHub Code Scanning
  uses: github/codeql-action/upload-sarif@v3
  with:
    sarif_file: pgfence.sarif

CRITICAL and HIGH findings appear as errors; MEDIUM as warnings. Since 0.8.0 a file that produced no SQL statements also uploads as an error, under the rule pgfence-no-statements (see Exit code 2), so a red code scanning result does not always mean a risky statement. Requires GitHub Advanced Security (included on all public repos and GitHub Enterprise).

GitLab CI

yaml

migration-safety:
  stage: test
  script:
    - npx @flvmnt/pgfence analyze --ci --max-risk medium migrations/*.sql

gitlab-codequality:
  stage: test
  script:
    - npx @flvmnt/pgfence analyze --output gitlab migrations/*.sql > gl-code-quality-report.json
  artifacts:
    when: always
    reports:
      codequality: gl-code-quality-report.json

The report job has no --ci, so it does not fail on risk. It can still exit 2 if the run analyzed no SQL at all (see Exit code 2). pgfence finishes writing the report before it exits, so the file on disk is complete either way, but artifacts:when defaults to on_success and would discard it on that exit. when: always above is what keeps the report you need in order to diagnose the failure.

Any Other Runner

bash

npx @flvmnt/pgfence analyze --ci --max-risk medium migrations/*.sql

When the CI risk gate blocks, pgfence prints one line to stderr about the hosted required check. Suppress that line with --no-cloud-hint or PGFENCE_CLOUD_HINT=0. This changes only the hint, never the report or exit code.

Exit Code 2: Nothing Was Analyzed

When a run reads your files and produces no statements at all across all of them, neither analyzed nor unanalyzable, pgfence writes this to stderr and exits 2:

text

pgfence error: nothing was analyzed.

pgfence read 1 file and found 0 SQL statements in them, so no safety
check ran. This is a failure, not a pass. Exiting 0 here would report "your
migrations are safe" about migrations that were never checked.

Exit 2 is not a risk gate failure. It means pgfence could not do its job, so raising --max-risk will not help. Exit 1 is the risk gate: findings above your threshold. Exit 2 is pgfence telling you it checked nothing.

Before 0.8.0 this same run exited 0 and printed Coverage: 100% over files it had found no SQL in. Separately, versions 0.5.0 through 0.7.0 exited 0 with no output at all when launched through npx or node_modules/.bin, which is how most CI jobs launch it. So if this job went red the first time you ran 0.8.0, the job is not newly broken: it is reporting something about your pipeline that it could not report before.

1. The glob matched the wrong files

Print the list your workflow actually passes in, on the runner, not locally:

bash

ls -l migrations/*.sql

If the glob matches nothing and your shell passes the pattern through literally, pgfence reports a different error at the same exit code: pgfence error: ENOENT: no such file or directory. That one is the glob, not the contents.

2. The files contain no SQL

An empty file, or a file with nothing but comments, analyzes to zero statements. In CI this usually means a revert, a placeholder, or a migration whose body was left for later got picked up by the glob. Open the files named in the error and check.

3. The format is wrong for an ORM migration

This one is a near miss rather than a cause: it produces a different failure, and the distinction matters when you are reading the error.

TypeORM, Knex, Kysely and Sequelize migrations are read by an extractor that pulls SQL out of the query calls inside up(). Run one file with the format named explicitly to see what the extractor finds:

bash

pgfence analyze --format typeorm db/migrations/1700000000000-AddIndex.ts

A migration whose up() has no recognized query call does not produce exit 2. Since 0.8.0 the extractor reports that file as [UNANALYZABLE] at Coverage: 0% and the run exits 0. That case is failed by --unknown block rather than by this gate, and only together with --ci: the flag on its own still exits 0.

Intentional no-op migrations

If a migration really is a no-op, say so in SQL, so it is analyzed and recorded rather than looking like a mistake:

sql

-- no-op: reverts 20260101_add_foo
SELECT 1;

There is no flag that turns the gate off, and that is deliberate. A flag meaning "a run with zero statements passes" produces the same exit 0 as the failure this gate exists to catch, which is a job reporting safe migrations it never read. Writing the no-op as SQL keeps the file inside coverage and leaves the decision visible in the migration itself.

Per-file loops have no middle ground

bash

git diff --name-only origin/main | xargs -n1 pgfence analyze --ci

xargs -n1 makes every single file its own whole run. A comment-only revert is then a run that analyzed nothing, and it fails that job on its own, with no opt out. Either pass the whole list to one run (drop -n1), which exits 2 only when the entire batch produced no SQL, or write the no-op as SQL as above.

One empty file among real migrations

A run where some files have SQL and some do not is not this error, and the exit code is unaffected. It is still reported, and where depends on the output format:

--output cli marks the file in the report body as [NO STATEMENTS], distinct from [UNANALYZABLE]. --output github renders the same state as :grey_question: NO STATEMENTS, without the brackets.
--output json, --output sarif and --output gitlab keep stdout byte clean and write one line to stderr: pgfence: 1 of 2 files produced no SQL statements and was not checked: ...
--output json also lists them under coverage.filesWithNoStatements. Machine consumers should gate on coverage.analyzedNothing, not on coveragePercent, which is null rather than 100 when there were no statements at all.

The uploaded artifacts do not follow the exit code here. The file is reported as pgfence-no-statements at SARIF level error and GitLab severity major, so a pipeline that gates on the uploaded artifact can go red while pgfence itself exited 0. That is on purpose: a default GitHub code scanning gate does not fail on a warning, and a file that was never checked must not upload as a clean run.