diff

The diff command compares your current database schema (JSON file from extract) with your desired schema (SQL files) and displays the differences. Use this to preview what changes will be made before generating migrations.

Usage

pgtofu diff [flags]

Flags

Flag	Description	Required
`--current`	Path to current schema JSON file (from `extract`)	Yes
`--desired`	Path to desired schema SQL file or directory	Yes
`--help`, `-h`	Help for diff	No

Examples

Basic Comparison

# Compare with a directory of SQL files
pgtofu diff --current current-schema.json --desired ./schema

# Compare with a single SQL file
pgtofu diff --current current-schema.json --desired schema.sql

Docker

docker run --rm \
  -v "$(pwd):/workspace" \
  -w /workspace \
  accented/pgtofu:latest diff \
  --current current-schema.json \
  --desired ./schema

Output Format

The diff command outputs a summary of detected changes grouped by severity:

Schema Comparison Summary
========================

Changes detected: 8

SAFE Changes (5):
  - ADD_TABLE: Add table: public.users
  - ADD_TABLE: Add table: public.orders
  - ADD_INDEX: Add index: idx_users_email on public.users
  - ADD_INDEX: Add index: idx_orders_user_id on public.orders
  - ADD_EXTENSION: Add extension: uuid-ossp

POTENTIALLY_BREAKING Changes (2):
  - DROP_INDEX: Drop index: idx_old_unused on public.legacy
  - MODIFY_COLUMN_DEFAULT: Change default for: public.users.status

BREAKING Changes (1):
  - DROP_TABLE: Drop table: public.deprecated_table

Total: 8 changes
  Safe: 5
  Potentially Breaking: 2
  Breaking: 1

Change Severities

pgtofu classifies all detected changes by their potential impact:

SAFE

Low risk - Additions and non-destructive changes

Adding tables, columns, indexes
Adding constraints
Creating views and functions

POTENTIALLY_BREAKING

Medium risk - May affect queries or applications

Dropping unused indexes
Changing defaults
Renaming objects

BREAKING

High risk - May cause data loss or downtime

Dropping tables or columns
Dropping constraints
Removing functions used by applications

DATA_MIGRATION_REQUIRED

Requires intervention - Cannot be automated

Changing column types incompatibly
Adding NOT NULL to existing columns without defaults
Complex schema transformations

Change Types

pgtofu detects over 40 different types of schema changes:

Table Changes

Change Type	Severity	Description
`ADD_TABLE`	SAFE	New table created
`DROP_TABLE`	BREAKING	Table removed
`MODIFY_TABLE_COMMENT`	SAFE	Table comment changed

Column Changes

Change Type	Severity	Description
`ADD_COLUMN`	SAFE	New column added
`DROP_COLUMN`	BREAKING	Column removed
`MODIFY_COLUMN_TYPE`	Varies	Column type changed
`MODIFY_COLUMN_NULLABILITY`	Varies	NULL/NOT NULL changed
`MODIFY_COLUMN_DEFAULT`	POTENTIALLY_BREAKING	Default value changed

Constraint Changes

Change Type	Severity	Description
`ADD_CONSTRAINT`	SAFE	New constraint added
`DROP_CONSTRAINT`	POTENTIALLY_BREAKING	Constraint removed
`MODIFY_CONSTRAINT`	POTENTIALLY_BREAKING	Constraint modified

Index Changes

Change Type	Severity	Description
`ADD_INDEX`	SAFE	New index created
`DROP_INDEX`	POTENTIALLY_BREAKING	Index removed
`MODIFY_INDEX`	POTENTIALLY_BREAKING	Index definition changed

View Changes

Change Type	Severity	Description
`ADD_VIEW`	SAFE	New view created
`DROP_VIEW`	POTENTIALLY_BREAKING	View removed
`MODIFY_VIEW`	POTENTIALLY_BREAKING	View definition changed

Function Changes

Change Type	Severity	Description
`ADD_FUNCTION`	SAFE	New function created
`DROP_FUNCTION`	POTENTIALLY_BREAKING	Function removed
`MODIFY_FUNCTION`	POTENTIALLY_BREAKING	Function body changed

TimescaleDB Changes

Change Type	Severity	Description
`ADD_HYPERTABLE`	SAFE	Table converted to hypertable
`ADD_COMPRESSION_POLICY`	SAFE	Compression policy added
`ADD_RETENTION_POLICY`	SAFE	Retention policy added
`ADD_CONTINUOUS_AGGREGATE`	SAFE	Continuous aggregate created

Type Compatibility

When comparing column types, pgtofu understands safe type widening:

Safe Widening (No Data Loss)

smallint → integer → bigint
VARCHAR(50) → VARCHAR(100)
NUMERIC(10,2) → NUMERIC(12,2)

Incompatible Changes (Data Migration Required)

bigint → smallint (narrowing)
TEXT → INTEGER (type change)
VARCHAR(100) → VARCHAR(50) (length reduction)

Desired Schema Format

The desired schema can be a single SQL file or a directory structure:

Single File

pgtofu diff --current current.json --desired schema.sql

Directory Structure (Recommended)

schema

extensions

types

tables

users.sql

orders.sql

views

functions

timescaledb

pgtofu automatically discovers and parses all .sql files in the directory tree.

Organize SQL files by type for maintainability. pgtofu resolves dependencies automatically regardless of file organization.

Troubleshooting

No changes detected

Ensure your SQL files use the same schema names as the database. By default, PostgreSQL uses the public schema.

Parser errors

Check that your SQL syntax is valid. pgtofu supports standard PostgreSQL syntax including:

Dollar-quoted strings
Quoted identifiers
Complex expressions

Unexpected changes

View normalization may detect changes due to formatting differences. Use --preview with generate to see exact SQL.

CLI Reference

Usage

Flags

Examples

Basic Comparison

Docker

Output Format

Change Severities

SAFE

POTENTIALLY_BREAKING

BREAKING

DATA_MIGRATION_REQUIRED

Change Types

Type Compatibility

Safe Widening (No Data Loss)

Incompatible Changes (Data Migration Required)

Desired Schema Format

Single File

Directory Structure (Recommended)

Troubleshooting

See Also

​Usage

​Flags

​Examples

​Basic Comparison

​Docker

​Output Format

​Change Severities

SAFE

POTENTIALLY_BREAKING

BREAKING

DATA_MIGRATION_REQUIRED

​Change Types

​Type Compatibility

​Safe Widening (No Data Loss)

​Incompatible Changes (Data Migration Required)

​Desired Schema Format

​Single File

​Directory Structure (Recommended)

​Troubleshooting

​See Also

Usage

Flags

Examples

Basic Comparison

Docker

Output Format

Change Severities

Change Types

Type Compatibility

Safe Widening (No Data Loss)

Incompatible Changes (Data Migration Required)

Desired Schema Format

Single File

Directory Structure (Recommended)

Troubleshooting

See Also