The apply command applies database schema changes to a target database schema. You can either provide a desired state file to generate and apply a plan, or execute a pre-generated plan file directly.

​
Overview

The apply command supports two execution modes:

​
File Mode (Generate and Apply)

  1. Read the desired state from a SQL file (with include directive support)
  2. Compare it with the current database state of the target schema
  3. Generate a migration plan with proper dependency ordering
  4. Display the plan for review
  5. Apply the changes (with optional confirmation and safety checks)

​
Plan Mode (Execute Pre-generated Plan)

  1. Load a pre-generated plan from JSON file
  2. Validate plan version compatibility and schema fingerprints
  3. Display the plan for review
  4. Apply the changes (with optional confirmation and safety checks)

​
Basic Usage

# File mode: Generate and apply plan from desired state file
pgschema apply --host localhost --db myapp --user postgres --password mypassword --file schema.sql

# Plan mode: Execute pre-generated plan
pgschema apply --host localhost --db myapp --user postgres --password mypassword --plan plan.json

# Auto-approve mode for CI/CD (no confirmation prompt)
pgschema apply --host localhost --db myapp --user postgres --password mypassword --file schema.sql --auto-approve

​
Connection Options

​
--host
string
default:"localhost"
Database server host
​
--port
integer
default:"5432"
Database server port
​
--db
string
required
Database name
​
--user
string
required
Database user name
​
--password
string
Database password (can also use PGPASSWORD environment variable)You can provide the password in two ways:
PGPASSWORD=mypassword pgschema apply \
--host localhost \
--db myapp \
--user postgres \
--file schema.sql
​
--schema
string
default:"public"
Schema name to apply changes to

​
Apply Options

​
--file
string
Path to desired state SQL schema file (mutually exclusive with —plan)Used in File Mode to generate and apply a plan from the desired state.
​
--plan
string
Path to pre-generated plan JSON file (mutually exclusive with —file)Used in Plan Mode to execute a plan that was previously generated with pgschema plan --output-json.
​
--auto-approve
boolean
default:"false"
Apply changes without prompting for approvalUseful for automated deployments and CI/CD pipelines.
​
--no-color
boolean
default:"false"
Disable colored output in the plan displayUseful for scripts, CI/CD environments, or terminals that don’t support colors.
​
--lock-timeout
string
Maximum time to wait for database locks (e.g., ’30s’, ‘5m’, ‘1h’)If not specified, uses PostgreSQL’s default behavior (wait indefinitely). See PostgreSQL lock_timeout documentation.
​
--application-name
string
default:"pgschema"
Application name for database connection (visible in pg_stat_activity)See PostgreSQL application_name documentation.

​
Examples

​
File Mode (Generate and Apply)

# Interactive mode - shows plan and prompts for confirmation
pgschema apply --host localhost --db myapp --user postgres --file desired_schema.sql
This will:
  1. Generate a migration plan by comparing the desired state with current database
  2. Display the plan with colored output
  3. Prompt: “Do you want to apply these changes? (yes/no):”
  4. Wait for confirmation before proceeding
  5. Apply changes using transactions where possible
Example output: 
Plan: 2 to add, 1 to modify.

Summary by type:
  tables: 1 to add, 1 to modify
  indexes: 1 to add

Tables:
  + posts
  ~ users
    + email (column)

Indexes:
  + idx_users_email

Transaction: true

DDL to be executed:
--------------------------------------------------

CREATE TABLE IF NOT EXISTS posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    user_id INTEGER REFERENCES users(id)
);

ALTER TABLE users ADD COLUMN email VARCHAR(255) UNIQUE;

CREATE INDEX idx_users_email ON users(email);

Do you want to apply these changes? (yes/no):

​
Plan Mode (Execute Pre-generated Plan)

# First, generate a plan
pgschema plan --host localhost --db myapp --user postgres --file schema.sql --output-json plan.json

# Then apply the pre-generated plan
pgschema apply --host localhost --db myapp --user postgres --plan plan.json
Benefits of plan mode:
  • Separation of concerns: Generate plans in one environment, apply in another
  • Review process: Plans can be reviewed before deployment
  • Repeatability: Same plan can be applied to multiple environments
  • Version control: Plans can be stored and versioned
  • Rollback preparation: Generate rollback plans before applying changes

​
Auto-approve for CI/CD

pgschema apply \
  --host prod-db \
  --db myapp \
  --user deployer \
  --file schema.sql \
  --auto-approve \
  --no-color

​
With Lock Timeout

# Prevent blocking on long-running locks
pgschema apply \
  --host localhost \
  --db myapp \
  --user postgres \
  --file schema.sql \
  --lock-timeout "30s"

​
Custom Application Name

pgschema apply \
  --host localhost \
  --db myapp \
  --user postgres \
  --file schema.sql \
  --application-name "pgschema-deployment-v1.1.0"

-- Monitor active pgschema connections:
SELECT application_name, state, query_start, query 
FROM pg_stat_activity 
WHERE application_name LIKE 'pgschema%';

​
Safety Features

​
Schema Fingerprint Validation

When using plan mode, pgschema validates that the database schema hasn’t changed since the plan was generated: 
# Generate plan
pgschema plan --host localhost --db myapp --user postgres --file schema.sql --output-json plan.json

# Database schema changes here...

# Apply plan - will detect changes and fail
pgschema apply --host localhost --db myapp --user postgres --plan plan.json
If schema changes are detected: 
Error: schema fingerprint mismatch detected - the database schema has changed since the plan was generated.

Expected fingerprint: abc123...
Current fingerprint:  def456...
Difference: Table 'users' was modified

To resolve this issue:
1. Regenerate the plan with current database state: pgschema plan ...
2. Review the new plan to ensure it's still correct
3. Apply the new plan: pgschema apply ...

​
Version Compatibility

Plans include version information to ensure compatibility:
  • pgschema version: Must match the version used to generate the plan
  • Plan format version: Must be supported by the current pgschema version

​
Transaction Handling

pgschema automatically determines whether changes can run in a transaction:
  • Transactional mode (default): All changes run in a single transaction with automatic rollback on failure
  • Non-transactional mode: Some operations (like CREATE INDEX CONCURRENTLY) run outside transactions
Transaction: true    # Changes will run in a transaction
Transaction: false   # Some changes cannot run in a transaction

​
No-op Detection

If no changes are needed, pgschema skips execution: 
No changes to apply. Database schema is already up to date.