Data Sources

Supported data sources for the PostgresCompare CLI

The PostgresCompare CLI can compare schemas from a variety of sources, providing flexibility for different workflows, from local development to CI/CD pipelines. Data sources are specified as positional arguments to commands like pgc diff, pgc script, and pgc report.

Live Database

The most common data source is a live PostgreSQL database, specified using a standard connection URI.

postgres://user:pass@host:5432/dbname
postgresql://user:pass@host:5432/dbname

Environment Variable Expansion

Connection URIs support environment variable expansion, so you can avoid hardcoding credentials:

pgc diff "postgres://${PROD_USER}:${PROD_PASS}@prod-host/db" @staging

Environment

Environments are named aliases for connection URIs, making them easier to manage and reference. You can manage them with the pgc env command or define them in a pgc.yaml configuration file.

@production
production

The @ prefix is optional — @production and production both resolve to the same environment. This makes the CLI fully compatible with PowerShell, where @ is the splatting operator.

# These are equivalent
pgc diff @production @staging
pgc diff production staging

Environments are stored in ~/.pgc/environments.json. When both a pgc.yaml file and ~/.pgc/environments.json define the same name, the user-level environment from ~/.pgc/environments.json takes precedence.

JSON Snapshot

A JSON snapshot is a file that contains a complete representation of a database schema at a specific point in time. It’s useful for offline comparisons, keeping a history of your schema, or as a known-good baseline. Snapshots are created with the pgc snapshot command.

Any file ending in .json is treated as a snapshot:

path/to/snapshot.json

# Create a snapshot
pgc snapshot @production -o prod-snapshot.json

# Compare the snapshot to a development database
pgc diff prod-snapshot.json @development

SQL File

You can use a single .sql file containing DDL (Data Definition Language) statements as a data source. PostgresCompare parses the file using a PostgreSQL grammar to construct a model of the schema.

Any file ending in .sql is treated as a SQL file source:

path/to/schema.sql

Supported Formats

  • Plain SQL pg_dump output — the most common format, generated by pg_dump --format=plain.
  • Binary pg_dump format — automatically detected via PGDMP magic bytes and converted to plain SQL using pg_restore. Requires pg_restore to be on your PATH.
  • Hand-written DDL — CREATE TABLE, CREATE VIEW, CREATE FUNCTION, etc.

psql metacommands (\connect, \encoding, \copy, \pset, etc.) are automatically stripped before parsing.

pgc diff schema.sql @production

SQL Folder

For projects where the schema is split across multiple files, you can specify a directory as a data source. The CLI reads and merges all .sql files within that directory in alphabetical order for deterministic results. Each file’s content is prefixed with a source comment.

A path ending in / or pointing to a directory is treated as a folder source:

path/to/sql_scripts/

pgc diff ./db/migrations/ @production

Git Reference

PostgresCompare can read a SQL file directly from any git reference (branch, tag, commit hash, or relative reference) in your repository. This is extremely powerful for CI/CD pipelines and for comparing against historical versions of your schema without checking out the code.

The format is git:<ref>:<path-to-file>:

git:main:schema.sql
git:v1.2.0:db/structure.sql
git:a1b2c3d:schema.sql
git:HEAD~1:schema.sql

Under the hood, this uses git show <ref>:<path> to retrieve the file content.

# Compare production against the schema in the main branch
pgc diff @production git:main:schema.sql

# Compare schema between two releases
pgc diff git:v1.0.0:schema.sql git:v1.1.0:schema.sql

# Compare the current schema against the previous commit
pgc diff git:HEAD:schema.sql git:HEAD~1:schema.sql

Mixing Data Sources

All data sources are interchangeable. You can compare any combination:

# Live database vs. SQL file
pgc diff postgres://localhost/mydb schema.sql

# Snapshot vs. Git reference
pgc diff prod-snapshot.json git:main:schema.sql

# Two Git references
pgc diff git:v1.0:schema.sql git:v2.0:schema.sql

# Environment vs. SQL folder
pgc diff @production ./db/schema/

The only exception is pgc snapshot, which requires a live database connection as its source.