Skip to main content
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. Apply the desired state SQL to a temporary PostgreSQL instance (embedded by default, or external via --plan-* flags)
  3. Compare it with the current database state of the target schema
  4. Generate a migration plan with proper dependency ordering
  5. Display the plan for review
  6. Apply the changes (with optional confirmation and safety checks)
By default, File Mode uses an embedded PostgreSQL instance to validate the desired state. For schemas using PostgreSQL extensions or cross-schema references, you can use an external database instead via the --plan-* flags. See External Plan Database for details.

​
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 (env: PGHOST)
​
--port
integer
default:"5432"
Database server port (env: PGPORT)
​
--db
string
required
Database name (required) (env: PGDATABASE)
​
--user
string
required
Database user name (required) (env: PGUSER)
​
--password
string
Database password (optional, can also use PGPASSWORD env var or .pgpass file)You can provide the password in multiple ways (in order of precedence):
# Create .env file with:
# PGHOST=localhost
# PGPORT=5432
# PGDATABASE=myapp
# PGUSER=postgres
# PGPASSWORD=mypassword

pgschema apply --file schema.sql
Password Resolution Order:
  1. Command line --password flag (highest priority)
  2. PGPASSWORD environment variable
  3. .pgpass file in user’s home directory
  4. PostgreSQL will prompt for password if none found
See dotenv (.env) for detailed configuration options.
​
--schema
string
default:"public"
Schema name to apply changes to

​
Plan Database Options

When using File Mode (--file), the apply command generates a plan internally using a temporary PostgreSQL instance. By default, this uses embedded PostgreSQL. For schemas that require PostgreSQL extensions or have cross-schema references, you can provide an external database. See External Plan Database for complete documentation. Note: These options only apply when using --file mode. When using --plan mode, the plan has already been generated.

​
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) (env: PGAPPNAME)See PostgreSQL application_name documentation.

​
Ignoring Objects

You can exclude specific database objects from schema application using a .pgschemaignore file. See Ignore (.pgschemaignore) for complete 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.