pgschema

pgschema Overview

pgschema is a CLI tool that implements a Terraform-style declarative schema migration workflow for PostgreSQL. Instead of writing manual migration files, you define your desired schema state and let the tool reconcile the database to match that state.

The pgschema workflow consists of four main stages:

1. Dump: Export an existing Postgres schema into a developer-friendly format.
2. Edit: Modify the exported schema files to represent your desired target state.
3. Plan: Compare your desired state (the files) against the current database state to generate a migration plan.
4. Apply: Execute the migration with features like concurrent change detection, transaction-adaptive execution, and lock timeout control.

If a table is ignored in the [tables] section, its structure is unmanaged. However, triggers defined on that table can still be managed by pgschema if the trigger name itself is not ignored.

Example: If external_users is ignored, but a trigger on_data_change is defined on it, pgschema will still manage the trigger lifecycle.

# .pgschemaignore
[tables]
patterns = ["external_*"]

-- schema.sql
-- The table is ignored, but the trigger is managed
CREATE TRIGGER on_data_change
  AFTER INSERT ON external_users
  FOR EACH ROW
  EXECUTE FUNCTION sync_data();

When pgschema generates migration SQL, it follows a specific canonical format to ensure readability and consistency:

• Empty ENUMs: Generated on a single line: CREATE TYPE name AS ENUM ();.
• Populated ENUMs: Generated using a multi-line format where each value is on its own indented line, and there is no trailing comma after the last value.
• Composite Types: Generated on a single line with attributes separated by commas.

Modification Behavior

• ENUMs: Can be modified using ALTER TYPE type_name ADD VALUE 'new_value' AFTER 'existing_value';.
• Composite Types: Currently, modifications to composite types require dropping and recreating the type. This is due to PostgreSQL's limited ALTER TYPE support for composite types and the complexity of managing dependencies during in-place modifications.

The ir package provides strongly-typed Go representations of PostgreSQL objects. When you build an IR, you can access the following types:

• Tables: Includes Columns, Constraints, Indexes, Triggers, and RLSPolicy (if RLSEnabled is true).
• Views: Includes Definition and Columns.
• Functions: Includes Arguments ([]*Parameter), Returns, Language, and Body.
• Procedures: Includes parameters and language.
• Types: Enums, composites, and domains.
• Sequences: Start, increment, and min/max values.

The tool automatically utilizes PostgreSQL's non-blocking features to minimize downtime.

• Concurrent Indexing: Index additions automatically use CREATE INDEX CONCURRENTLY to avoid blocking table writes.
• Progress Monitoring: The pgschema:wait directive can be used to block migration execution while polling the database to monitor the progress of long-running operations (like index creation) and automatically continuing once they complete.

-- Example of generated concurrent index creation
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email_status ON users (email, status DESC);

pgschema provides optimized documentation files for AI-assisted workflows following the llms.txt standard:

• llms.txt: A concise, machine-readable summary of capabilities.
• llms-full.txt: The complete documentation in a single file optimized for LLM context windows.

These are ideal for inclusion in RAG pipelines, agent tool definitions, or system prompts.

Unlike other declarative tools that require a temporary "shadow" or "dev" database to compute differences, pgschema uses an Intermediate Representation (IR) system to perform direct comparisons. This eliminates the need for extra infrastructure, complex setup, or special permissions for temporary database creation.

The workflow follows these steps:

1. SQL Parsing: Your desired state SQL files are parsed into a normalized IR.
2. Database Introspection: The target database is introspected via Postgres system catalogs (e.g., pg_class, pg_attribute) and converted into the same IR format.
3. Direct Comparison: The diff engine compares the two IR structures directly.
4. Migration Generation: The differences are converted into ordered DDL statements to transform the current state into the desired state.

When pgschema generates migration SQL for views, it follows a specific canonical format to ensure efficient and safe updates.

For creating or modifying views, it always uses CREATE OR REPLACE VIEW. This allows for seamless updates to view definitions without needing to drop dependent objects. For deletions, it uses DROP VIEW IF EXISTS ... CASCADE to ensure all dependencies are handled.

Key characteristics:

• Uses CREATE OR REPLACE VIEW for all creation and modifications.
• Includes schema qualification where necessary.
• Preserves the original SELECT statement formatting.
• Uses CASCADE during DROP operations.

-- Creation/Modification format
CREATE OR REPLACE VIEW [schema.]view_name AS
select_statement;

-- Drop format
DROP VIEW IF EXISTS view_name CASCADE;

Unlike regular views, PostgreSQL materialized views do not support CREATE OR REPLACE. To change the definition of a materialized view, the view must be dropped and recreated.

pgschema behavior:

• pgschema automatically handles the drop/create cycle during migration planning when a change to a materialized view's definition is detected.
• For DROP operations, pgschema uses the syntax: DROP MATERIALIZED VIEW IF EXISTS view_name;.

pgschema handles migrations using the following logic:

1. Transactional Changes: For most changes, pgschema executes them within a single transaction. If a failure occurs, the changes will roll back, leaving the database in its previous state.
2. Non-Transactional Changes: For statements that cannot run in a transaction (such as CREATE INDEX CONCURRENTLY), each statement runs in its own transaction.
3. Partial Application: If a migration contains non-transactional DDL statements and a later statement fails, partial application may occur.

If a migration fails, review the error message, fix the issue in your schema file, and attempt the migration again.