> ## 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.

# Plan

The `plan` command generates a migration plan to apply a desired schema state to a target database schema. It compares the desired state (from a file) with the current state of a specific schema and shows what changes would be applied.

## Overview

The plan command follows infrastructure-as-code principles similar to Terraform:

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. Connect to the target database and analyze current state of the specified schema
4. Compare the two states
5. Generate a detailed migration plan with proper dependency ordering
6. Display the plan without making any changes

By default, pgschema uses an embedded PostgreSQL instance to validate your desired state SQL. Cross-schema foreign keys to unmanaged tables (for example Supabase `auth.users`) work with the default embedded instance when you [ignore those schemas](/cli/ignore). For PostgreSQL extensions, or when referenced tables are not on the target, see [External Plan Database](/cli/plan-db).

## Basic Usage

```bash theme={null}
# Generate plan to apply schema.sql to the target database
pgschema plan --host localhost --db myapp --user postgres --password mypassword --file schema.sql

# Plan with specific schema
pgschema plan --host localhost --db myapp --user postgres --password mypassword --schema myschema --file schema.sql

# Generate JSON output for automation
pgschema plan --host localhost --db myapp --user postgres --password mypassword --file schema.sql --output-json stdout

# Generate SQL migration script
pgschema plan --host localhost --db myapp --user postgres --password mypassword --file schema.sql --output-sql stdout

# Save multiple output formats simultaneously
pgschema plan --host localhost --db myapp --user postgres --password mypassword --file schema.sql \
  --output-human plan.txt --output-json plan.json --output-sql migration.sql

# Disable colored output (useful for scripts or CI/CD)
pgschema plan --host localhost --db myapp --user postgres --password mypassword --file schema.sql --no-color
```

## Connection Options

<ParamField path="--host" type="string" default="localhost">
  Database server host (env: PGHOST)
</ParamField>

<ParamField path="--port" type="integer" default="5432">
  Database server port (env: PGPORT)
</ParamField>

<ParamField path="--db" type="string" required>
  Database name (required) (env: PGDATABASE)
</ParamField>

<ParamField path="--user" type="string" required>
  Database user name (required) (env: PGUSER)
</ParamField>

<ParamField path="--password" type="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):

  <CodeGroup>
    ```bash .env File (Recommended) theme={null}
    # Create .env file with:
    # PGHOST=localhost
    # PGPORT=5432
    # PGDATABASE=myapp
    # PGUSER=postgres
    # PGPASSWORD=mypassword

    pgschema plan --file schema.sql
    ```

    ```bash Environment Variable theme={null}
    PGPASSWORD=mypassword pgschema plan \
      --host localhost \
      --db myapp \
      --user postgres \
      --file schema.sql
    ```

    ```bash Command Line Flag theme={null}
    pgschema plan \
      --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 plan \
      --host localhost \
      --db myapp \
      --user postgres \
      --file schema.sql
    ```
  </CodeGroup>

  **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.
</ParamField>

<ParamField path="--sslmode" type="string" default="prefer">
  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`).
</ParamField>

<ParamField path="--schema" type="string" default="public">
  Schema name to target for comparison
</ParamField>

## Plan Database Options

By default, the plan command uses an embedded PostgreSQL instance to validate your desired state SQL. Cross-schema FKs to ignored tables are stubbed from the target automatically; extensions and other edge cases may need an external database. See [External Plan Database](/cli/plan-db) for complete documentation.

## Plan Options

<ParamField path="--file" type="string" required>
  Path to desired state SQL schema file
</ParamField>

<ParamField path="--output-human" type="string">
  Output human-readable format to stdout or file path

  Examples:

  * `--output-human stdout` - Display to terminal
  * `--output-human plan.txt` - Save to file
</ParamField>

<ParamField path="--output-json" type="string">
  Output JSON format to stdout or file path

  This JSON format is the same format accepted by the [apply command](/cli/apply) for executing migration plans.

  Examples:

  * `--output-json stdout` - Display to terminal
  * `--output-json plan.json` - Save to file
</ParamField>

<ParamField path="--output-sql" type="string">
  Output SQL format to stdout or file path

  Examples:

  * `--output-sql stdout` - Display to terminal
  * `--output-sql migration.sql` - Save to file
</ParamField>

<ParamField path="--no-color" type="boolean" default="false">
  Disable colored output for human format when writing to stdout

  This is useful for:

  * Scripts and automation that need to parse output
  * CI/CD environments that don't support color codes
  * Redirecting output to files where color codes are unwanted

  Note: This flag only affects human format output to stdout. File output and JSON/SQL formats are never colored.
</ParamField>

## Ignoring Objects

You can exclude specific database objects from migration planning using a `.pgschemaignore` file. See [Ignore (.pgschemaignore)](/cli/ignore) for complete documentation.

## Examples

### Default Human-Readable Output

```bash theme={null}
pgschema plan --host localhost --db myapp --user postgres --file schema.sql
```

```
Plan: 2 to add, 1 to modify, 1 to drop.

Summary by type:
  tables: 2 to add, 1 to modify, 1 to drop
  indexes: 1 to add
  functions: 1 to add

Tables:
  + users
  + posts
  ~ products
    + discount_rate (column)
    - old_price (column)
  - legacy_data

Indexes:
  + idx_users_email

Functions:
  + update_timestamp()

Transaction: true

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

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE
);

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

CREATE INDEX idx_users_email ON users(email);

CREATE FUNCTION update_timestamp() RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

ALTER TABLE products ADD COLUMN discount_rate numeric(5,2);

ALTER TABLE products DROP COLUMN old_price;

DROP TABLE legacy_data;
```

### JSON Output for Automation

```bash theme={null}
pgschema plan \
  --host localhost \
  --db myapp \
  --user postgres \
  --file schema.sql \
  --output-json stdout
```

```json theme={null}
{
  "version": "1.0.0",
  "pgschema_version": "1.0.0", 
  "created_at": "2025-01-15T10:30:00Z",
  "source_fingerprint": {
    "hash": "abc123def456..."
  },
  "diffs": [
    {
      "sql": "CREATE TABLE IF NOT EXISTS users (\n    id SERIAL PRIMARY KEY,\n    name VARCHAR(255) NOT NULL,\n    email VARCHAR(255) UNIQUE\n);",
      "type": "table",
      "operation": "create",
      "path": "public.users",
      "source": {
        "schema": "public",
        "name": "users",
        "type": "BASE_TABLE",
        "columns": [
          {
            "name": "id",
            "position": 1,
            "data_type": "integer",
            "is_nullable": false,
            "default_value": "nextval('users_id_seq'::regclass)"
          },
          {
            "name": "name",
            "position": 2,
            "data_type": "character varying",
            "is_nullable": false,
            "character_maximum_length": 255
          }
        ],
        "constraints": {
          "users_pkey": {
            "schema": "public",
            "table": "users",
            "name": "users_pkey",
            "type": "PRIMARY_KEY",
            "columns": [
              {
                "name": "id",
                "position": 1
              }
            ]
          }
        }
      },
      "can_run_in_transaction": true
    }
  ]
}
```

### SQL Migration Script

```bash theme={null}
pgschema plan \
  --host localhost \
  --db myapp \
  --user postgres \
  --file schema.sql \
  --output-sql stdout
```

```sql theme={null}
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE
);

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

CREATE INDEX idx_users_email ON users(email);

CREATE FUNCTION update_timestamp() RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

ALTER TABLE products ADD COLUMN discount_rate numeric(5,2);

ALTER TABLE products DROP COLUMN old_price;

DROP TABLE legacy_data;
```

### Plan for Specific Schema

```bash theme={null}
pgschema plan \
  --host localhost \
  --db multi_tenant \
  --user postgres \
  --schema tenant1 \
  --file tenant_schema.sql
```

## Use Cases

### Pre-deployment Validation

```bash theme={null}
# Check what changes will be applied before deployment
pgschema plan \
  --host prod-db \
  --db myapp \
  --user readonly \
  --file new_schema.sql

# If plan looks good, proceed with apply
pgschema apply \
  --host prod-db \
  --db myapp \
  --user deployer \
  --file new_schema.sql
```

### CI/CD Integration

```yaml theme={null}
# GitHub Actions example
- name: Validate Schema Changes
  run: |
    pgschema plan \
      --host ${{ secrets.DB_HOST }} \
      --db ${{ secrets.DB_NAME }} \
      --user ${{ secrets.DB_USER }} \
      --file schema/proposed.sql \
      --output-json plan.json \
      --no-color
    
    # Check if there are destructive changes
    if jq -e '.summary.to_destroy > 0' plan.json; then
      echo "Warning: Destructive changes detected!"
      exit 1
    fi
```

### Change Tracking

```bash theme={null}
# Generate plan and save for audit
DATE=$(date +%Y%m%d_%H%M%S)
pgschema plan \
  --host localhost \
  --db myapp \
  --user postgres \
  --file schema.sql \
  --output-sql "migrations/plan_${DATE}.sql"
```

### Multiple Output Formats

You can generate multiple output formats simultaneously:

```bash theme={null}
# Save all formats for comprehensive documentation
pgschema plan \
  --host localhost \
  --db myapp \
  --user postgres \
  --file schema.sql \
  --output-human plan.txt \
  --output-json plan.json \
  --output-sql migration.sql

# Display human-readable format and save JSON for automation
pgschema plan \
  --host localhost \
  --db myapp \
  --user postgres \
  --file schema.sql \
  --output-human stdout \
  --output-json automation/plan.json \
  --no-color
```

Note: Only one output format can use `stdout`. If no output flags are specified, the command defaults to human-readable output to stdout with colors enabled.

## Comparison Direction

The plan command is **unidirectional**: it always plans changes from the current state (database) to the desired state (file).

```
Current State (Database) → Desired State (File)
```

This ensures:

* Consistent infrastructure-as-code workflow
* Clear source of truth (the file)
* Predictable change direction

## Include Directive Support

The plan command supports include directives in schema files, allowing you to organize your schema across multiple files:

```sql theme={null}
-- main.sql
\i tables/users.sql
\i tables/posts.sql
\i functions/triggers.sql
```

The include processor will resolve all includes relative to the directory containing the main schema file.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.