> ## Documentation Index
> Fetch the complete documentation index at: https://www.pgschema.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Apply
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](/cli/plan-db) 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
```bash theme={null}
# 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
Database server host (env: PGHOST)
Database server port (env: PGPORT)
Database name (required) (env: PGDATABASE)
Database user name (required) (env: PGUSER)
Database password (optional, can also use PGPASSWORD env var or .pgpass file)
You can provide the password in multiple ways (in order of precedence):
```bash .env File (Recommended) theme={null}
# Create .env file with:
# PGHOST=localhost
# PGPORT=5432
# PGDATABASE=myapp
# PGUSER=postgres
# PGPASSWORD=mypassword
pgschema apply --file schema.sql
```
```bash Environment Variable theme={null}
PGPASSWORD=mypassword pgschema apply \
--host localhost \
--db myapp \
--user postgres \
--file schema.sql
```
```bash Command Line Flag theme={null}
pgschema apply \
--host localhost \
--db myapp \
--user postgres \
--password mypassword \
--file schema.sql
```
```bash .pgpass File theme={null}
# Create ~/.pgpass file with:
# hostname:port:database:username:password
# localhost:5432:myapp:postgres:mypassword
# Make sure file has correct permissions
chmod 600 ~/.pgpass
pgschema apply \
--host localhost \
--db myapp \
--user postgres \
--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)](/cli/dotenv) for detailed configuration options.
SSL mode for database connection (env: PGSSLMODE)
Valid values: `disable`, `allow`, `prefer`, `require`, `verify-ca`, `verify-full`
For `verify-ca` and `verify-full` modes, you can configure certificate paths using standard PostgreSQL environment variables (`PGSSLROOTCERT`, `PGSSLCERT`, `PGSSLKEY`).
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](/cli/plan-db) for complete documentation.
**Note**: These options only apply when using `--file` mode. When using `--plan` mode, the plan has already been generated.
## Apply Options
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.
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`.
Apply changes without prompting for approval
Useful for automated deployments and CI/CD pipelines.
Disable colored output in the plan display
Useful for scripts, CI/CD environments, or terminals that don't support colors.
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](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT).
Application name for database connection (visible in pg\_stat\_activity) (env: PGAPPNAME)
See [PostgreSQL application\_name documentation](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-APPLICATION-NAME).
## Ignoring Objects
You can exclude specific database objects from schema application using a `.pgschemaignore` file. See [Ignore (.pgschemaignore)](/cli/ignore) for complete documentation.
## Examples
### File Mode (Generate and Apply)
```bash theme={null}
# 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)
```bash theme={null}
# 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
```bash theme={null}
pgschema apply \
--host prod-db \
--db myapp \
--user deployer \
--file schema.sql \
--auto-approve \
--no-color
```
### With Lock Timeout
```bash theme={null}
# Prevent blocking on long-running locks
pgschema apply \
--host localhost \
--db myapp \
--user postgres \
--file schema.sql \
--lock-timeout "30s"
```
### Custom Application Name
```bash theme={null}
pgschema apply \
--host localhost \
--db myapp \
--user postgres \
--file schema.sql \
--application-name "pgschema-deployment-v1.1.0"
```
```sql theme={null}
-- 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:
```bash theme={null}
# 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.
```