pgmold

https://pgmold.dev/docs
Admin
pgmold is a PostgreSQL schema-as-code tool that enables defining schemas in native SQL, diffing...
Tokens:10,236
Snippets:114
Trust Score:2.4
Update:1 month ago
Show doc for...
Tokens:
Raw
### Verify pgmold installation with --version

Source: https://pgmold.dev/docs/getting-started/installation

Prints the installed pgmold version to verify the installation succeeded.

```bash
pgmold --version
```

--------------------------------

### Install pgmold from source (Git)

Source: https://pgmold.dev/docs/getting-started/installation

Builds and installs pgmold directly from the Git repository at https://github.com/fmguerreiro/pgmold. Use this to get the latest development version.

```bash
cargo install --git https://github.com/fmguerreiro/pgmold
```

--------------------------------

### Install pgmold from crates.io

Source: https://pgmold.dev/docs/getting-started/installation

Installs the pgmold binary from crates.io using Cargo. Run this command in a terminal with Cargo installed.

```bash
cargo install pgmold
```

--------------------------------

### Install pgmold provider with required_providers

Source: https://pgmold.dev/docs/reference/terraform

Declares the pgmold provider source and version constraint, then instantiates the provider with an empty configuration block.

```hcl
terraform {
  required_providers {
    pgmold = {
      source  = "fmguerreiro/pgmold"
      version = "~> 0.3"
    }
  }
}


provider "pgmold" {}

```

--------------------------------

### Full GitHub Actions workflow example

Source: https://pgmold.dev/docs/guides/ci-cd

Complete workflow that runs the drift-check action on pull requests. Requires `pull-requests: write` permission to post comments. Uses `actions/checkout@v4`.

```yaml
name: Schema Check
on:
  pull_request:


jobs:
  schema-ci:
    runs-on: ubuntu-latest
    permissions:
      pull-requests: write
      contents: read
    steps:
      - uses: actions/checkout@v4
      - uses: fmguerreiro/pgmold/.github/actions/drift-check@main
        with:
          schema: sql:schema/
          database: db:${{ secrets.DATABASE_URL }}
          target-schemas: public,auth
```

--------------------------------

### CLI drift detection JSON output

Source: https://pgmold.dev/docs/guides/ci-cd

Example JSON output from the `pgmold drift --json` command. Shows drift detection results including fingerprints and differences.

```json
{
  "has_drift": true,
  "expected_fingerprint": "abc123...",
  "actual_fingerprint": "def456...",
  "differences": ["AddColumn { schema: \"public\", table: \"users\", ... }"]
}
```

--------------------------------

### Include objects by name pattern

Source: https://pgmold.dev/docs/guides/filtering

Use --include with a pattern to include only objects matching the pattern. Multiple --include flags can be used. The example includes objects matching 'api_*' and 'users'.

```bash
# Include only objects matching patterns
pgmold plan -s sql:schema.sql -d postgres://localhost/mydb \
  --include 'api_*' --include 'users'
```

--------------------------------

### Include only specific object types

Source: https://pgmold.dev/docs/guides/filtering

Use --include-types with a comma-separated list to compare only the specified object types. The example compares only tables and functions.

```bash
# Only compare tables and functions
pgmold plan -s sql:schema.sql -d postgres://localhost/mydb \
  --include-types tables,functions
```

--------------------------------

### Exclude object types from comparison

Source: https://pgmold.dev/docs/guides/filtering

Use --exclude-types with a comma-separated list to exclude specific object types from comparison. The example excludes extensions.

```bash
# Exclude extensions from comparison
pgmold plan -s sql:schema.sql -d postgres://localhost/mydb \
  --exclude-types extensions
```

--------------------------------

### Combine name and type filters

Source: https://pgmold.dev/docs/guides/filtering

Combine --include-types, --include, and --exclude to filter by both object type and name pattern. The example compares only functions matching 'api_*' while excluding those matching '_*'.

```bash
# Compare only functions matching 'api_*', excluding internal ones
pgmold plan -s sql:schema.sql -d postgres://localhost/mydb \
  --include-types functions \
  --include 'api_*' \
  --exclude '_*'
```

--------------------------------

### Exclude objects by name pattern

Source: https://pgmold.dev/docs/guides/filtering

Use --exclude with a pattern to exclude objects matching the pattern. Multiple --exclude flags can be used. The example excludes objects matching '_*' and 'pg_*'.

```bash
# Exclude objects matching patterns
pgmold plan -s sql:schema.sql -d postgres://localhost/mydb \
  --exclude '_*' --exclude 'pg_*'
```

--------------------------------

### Exclude nested types within tables - policies

Source: https://pgmold.dev/docs/guides/filtering

Use --exclude-types with nested type names to exclude nested objects within tables. The example excludes RLS policies from comparison.

```bash
# Compare tables without RLS policies
pgmold plan -s sql:schema.sql -d postgres://localhost/mydb \
  --exclude-types policies
```

--------------------------------

### Exclude multiple nested types within tables

Source: https://pgmold.dev/docs/guides/filtering

Use --exclude-types with a comma-separated list of nested types to exclude multiple nested objects. The example excludes policies, indexes, foreign keys, and check constraints, comparing only table structure.

```bash
# Compare only table structure (no indexes, constraints, or policies)
pgmold plan -s sql:schema.sql -d postgres://localhost/mydb \
  --exclude-types policies,indexes,foreignkeys,checkconstraints
```

--------------------------------

### See what would change with pgmold plan

Source: https://pgmold.dev/docs/getting-started/quick-start

Compares the SQL file against the live database and shows the migration plan without applying changes.

```bash
pgmold plan -s sql:schema.sql -d postgres://localhost/mydb
```

--------------------------------

### Plan and apply schema changes with pgmold

Source: https://pgmold.dev/docs

Shows the two core pgmold commands: plan to preview changes and apply to execute the migration. Both use the same source and database connection flags.

```bash
# See what would change
pgmold plan -s sql:schema.sql -d postgres://localhost/mydb
```

```bash
# Apply the migration
pgmold apply -s sql:schema.sql -d postgres://localhost/mydb
```

--------------------------------

### Create a baseline with pgmold dump

Source: https://pgmold.dev/docs/guides/adopting

Creates a baseline from a live database. The first command exports the full schema; the second restricts to specific schemas using --target-schemas; the third uses --split to generate separate files for extensions, types, sequences, tables, functions, views, triggers, and policies.

```bash
# Export current database schema to SQL
pgmold dump -d postgres://localhost/mydb -o schema/baseline.sql


# For specific schemas only
pgmold dump -d postgres://localhost/mydb --target-schemas public,auth -o schema/baseline.sql


# Split into multiple files by object type
pgmold dump -d postgres://localhost/mydb --split -o schema/
```

--------------------------------

### Create a schema file (schema.sql)

Source: https://pgmold.dev/docs/getting-started/quick-start

Defines a users table with an id, email, and created_at column. This is the schema file that pgmold compares against the live database.

```sql
-- schema.sql
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT now()
);
```

--------------------------------

### Apply the migration with pgmold apply

Source: https://pgmold.dev/docs/getting-started/quick-start

Applies the migration. All operations run in a single transaction; if anything fails, nothing changes.

```bash
pgmold apply -s sql:schema.sql -d postgres://localhost/mydb
```

--------------------------------

### Recommended directory structure for schema files

Source: https://pgmold.dev/docs/guides/multi-file-schemas

Recommended directory structure for organizing schema files. Shows a layout with enums, tables, and functions directories, each containing SQL files with specific purposes.

```text
schema/
├── enums.sql           # CREATE TYPE statements
├── tables/
│   ├── users.sql       # users table + indexes
│   └── posts.sql       # posts table + foreign keys
└── functions/
    └── triggers.sql    # stored procedures

```

--------------------------------

### pgmold describe command

Source: https://pgmold.dev/docs/reference/cli

Outputs JSON describing available commands, object types, providers, and filters. The optional COMMAND argument narrows the description to a specific command such as plan or apply. No database connection is required.

```bash
pgmold describe
pgmold describe plan

```

--------------------------------

### pgmold dump

Source: https://pgmold.dev/docs/reference/cli

Export a live database schema to SQL.

```APIDOC
## pgmold dump

### Description
Export a live database schema to SQL.

### Command
pgmold

### Options
- `-d, --database <URL>` - PostgreSQL connection string
- `-o, --output <PATH>` - Output file or directory
- `--split` - Split into separate files by object type
- `--target-schemas <LIST>` - Comma-separated PostgreSQL schemas (default: `public`)
- `--include <PATTERN>` - Include objects matching glob pattern (repeatable)
- `--exclude <PATTERN>` - Exclude objects matching glob pattern (repeatable)
- `--include-types <TYPES>` - Include only these object types (comma-separated)
- `--exclude-types <TYPES>` - Exclude these object types (comma-separated)
- `--include-extension-objects` - Include objects owned by extensions
- `--json` - JSON output (includes SQL content and metadata)

### Example
```bash
pgmold dump -d postgres://localhost/mydb -o schema.sql
```

```

--------------------------------

### pgmold dump: export database schema

Source: https://pgmold.dev/docs/reference/cli

Exports a live database schema to SQL. Use -d/--database and -o/--output. Supports --split, --target-schemas, --include/--exclude, --include-types/--exclude-types, --include-extension-objects, and --json.

```shell
pgmold dump -d postgres://localhost/mydb -o schema.sql
```

--------------------------------

### pgmold diff: compare two schema sources

Source: https://pgmold.dev/docs/reference/cli

Compares two schema sources. Use --from and --to to specify the source and target schemas. Add --json for JSON output.

```shell
pgmold diff --from sql:old.sql --to sql:new.sql
```

--------------------------------

### Generate migration files with pgmold migrate and diff

Source: https://pgmold.dev/docs/guides/adopting

Integrates pgmold with an existing migration system. The migrate command auto-detects the next migration number and generates a numbered file; the diff command manually captures output to a file. Use pgmold for diffing while keeping your existing migration runner.

```bash
# Generate a numbered migration file automatically
pgmold migrate \
  -s sql:schema/ \
  -d postgres://localhost/mydb \
  --migrations ./migrations \
  --name "add_email_column"
# Creates: migrations/0044_add_email_column.sql


# Or manually capture output
pgmold diff --from sql:current.sql --to sql:schema/ > migrations/0044_my_change.sql
```

--------------------------------

### Create pgmold_migration resource

Source: https://pgmold.dev/docs/reference/terraform

Generates numbered migration files instead of applying directly. Uses a Flyway-style prefix 'V' and outputs to the specified directory.

```hcl
resource "pgmold_migration" "app" {
  schema_file  = "${path.module}/schema.sql"
  database_url = var.database_url
  output_dir   = "${path.module}/migrations"
  prefix       = "V"  # Flyway-style prefix
}

```

--------------------------------

### pgmold apply

Source: https://pgmold.dev/docs/reference/cli

Applies migrations to the database. Supports dry-run, destructive operation control, validation, and JSON output.

```APIDOC
## pgmold apply

### Description
Apply migrations to the database.

### Usage
```
pgmold apply -s sql:schema.sql -d postgres://localhost/mydb
```

### Flags
- `-s, --schema <SOURCE>` - Schema source (repeatable)
- `-d, --database <URL>` - PostgreSQL connection string
- `--allow-destructive` - Allow DROP and other destructive operations
- `--dry-run` - Preview SQL without executing
- `--target-schemas <LIST>` - Comma-separated PostgreSQL schemas (default: `public`)
- `--include <PATTERN>` - Include objects matching glob pattern (repeatable)
- `--exclude <PATTERN>` - Exclude objects matching glob pattern (repeatable)
- `--include-types <TYPES>` - Include only these object types (comma-separated)
- `--exclude-types <TYPES>` - Exclude these object types (comma-separated)
- `--include-extension-objects` - Include objects owned by extensions
- `--validate <URL>` - Validate migration against a temporary database first
- `--manage-ownership` - Include ownership management (`ALTER ... OWNER TO`)
- `--no-manage-grants` - Disable grant/revoke management
- `--exclude-grants-for-role <ROLE>` - Exclude grants for a specific role (repeatable)
- `-v, --verbose` - Log each statement execution and result
- `--json` - JSON output
```

--------------------------------

### Combine SQL and Drizzle sources

Source: https://pgmold.dev/docs/guides/drizzle

Combine SQL files and Drizzle schemas. Both sources are merged into a single desired schema before diffing against the database.

```bash
pgmold plan \
  --schema sql:base.sql \
  --schema drizzle:drizzle.config.ts \
  --database db:postgres://localhost/mydb
```

--------------------------------

### Use Drizzle schema as source

Source: https://pgmold.dev/docs/guides/drizzle

Use a Drizzle ORM config file as the schema source. pgmold runs `drizzle-kit export` internally to extract the SQL DDL from the Drizzle config.

```bash
pgmold plan \
  --schema drizzle:drizzle.config.ts \
  --database db:postgres://localhost/mydb
```

--------------------------------

### Load SQL files from a directory recursively

Source: https://pgmold.dev/docs/guides/multi-file-schemas

Loads all SQL files from a directory recursively using the sql: prefix. The -s flag specifies the source directory and -d provides the database connection string.

```bash
# Load all SQL files from a directory (recursive)
pgmold apply -s sql:./schema/ -d postgres://localhost/mydb

```

--------------------------------

### Run tests against a specific PostgreSQL version with PGMOLD_TEST_PG_VERSION

Source: https://pgmold.dev/docs/reference/compatibility

Set the PGMOLD_TEST_PG_VERSION environment variable to a PostgreSQL version tag (e.g., 14-alpine) and run the full test suite with all features. Requires Docker to be running because tests use testcontainers.

```bash
PGMOLD_TEST_PG_VERSION=14-alpine cargo test --all-features --test '*'
```

--------------------------------

### GitHub Action drift-check usage

Source: https://pgmold.dev/docs/guides/ci-cd

Basic usage of the pgmold GitHub Action for drift checking. Requires `schema` and `database` inputs. The action posts a migration plan as a PR comment and checks for drift.

```yaml
- uses: fmguerreiro/pgmold/.github/actions/drift-check@main
  with:
    schema: sql:schema/
    database: db:${{ secrets.DATABASE_URL }}
    target-schemas: public,auth
```

--------------------------------

### pgmold migrate: generate numbered migration file

Source: https://pgmold.dev/docs/reference/cli

Generates a numbered migration file. The -s flag specifies the schema source (repeatable), -d the connection string, -m the migrations directory, and -n the migration name. Auto-detects the next migration number in the output directory.

```bash
pgmold migrate \
  -s sql:schema/ \
  -d postgres://localhost/mydb \
  --migrations ./migrations \
  --name "add_email_column"
```

--------------------------------

### Use glob pattern to select SQL files

Source: https://pgmold.dev/docs/guides/multi-file-schemas

Uses a glob pattern to select SQL files. The pattern is quoted to prevent shell expansion, and the sql: prefix indicates the source type.

```bash
pgmold apply -s "sql:schema/**/*.sql" -d postgres://localhost/mydb

```

--------------------------------

### pgmold diff

Source: https://pgmold.dev/docs/reference/cli

Compares two schema sources.

```APIDOC
## pgmold diff

### Description
Compare two schema sources.

### Command
pgmold diff --from <SOURCE> --to <SOURCE>

### Options
- `--from <SOURCE>` - Source schema
- `--to <SOURCE>` - Target schema
- `--json` - JSON output

### Example
```bash
pgmold diff --from sql:old.sql --to sql:new.sql
```

```

--------------------------------

### pgmold plan

Source: https://pgmold.dev/docs/reference/cli

Generates a migration plan without applying it. Supports schema sources, filtering, validation, and JSON output.

```APIDOC
## pgmold plan

### Description
Generate a migration plan without applying it to the database.

### Usage
```
pgmold plan -s sql:schema.sql -d postgres://localhost/mydb
```

### Flags
- `-s, --schema <SOURCE>` - Schema source (repeatable). Prefix: `sql:` or `drizzle:`
- `-d, --database <URL>` - PostgreSQL connection string
- `--target-schemas <LIST>` - Comma-separated PostgreSQL schemas (default: `public`)
- `--include <PATTERN>` - Include objects matching glob pattern (repeatable)
- `--exclude <PATTERN>` - Exclude objects matching glob pattern (repeatable)
- `--include-types <TYPES>` - Include only these object types (comma-separated)
- `--exclude-types <TYPES>` - Exclude these object types (comma-separated)
- `--include-extension-objects` - Include objects owned by extensions
- `--reverse` - Generate rollback plan (reverse direction)
- `--zero-downtime` - Generate expand/contract phased migration plan
- `--validate <URL>` - Validate migration against a temporary database first
- `--manage-ownership` - Include ownership management (`ALTER ... OWNER TO`)
- `--no-manage-grants` - Disable grant/revoke management
- `--exclude-grants-for-role <ROLE>` - Exclude grants for a specific role (repeatable)
- `--json` - JSON output
```

--------------------------------

### Detect drift with pgmold drift --json

Source: https://pgmold.dev/docs/getting-started/quick-start

Detects drift between the database and schema files. Returns exit code 1 if drift is found; use in CI.

```bash
pgmold drift -s sql:schema.sql -d postgres://localhost/mydb --json
```

--------------------------------

### pgmold lint: validate schema against live database

Source: https://pgmold.dev/docs/reference/cli

Validates a schema against a live PostgreSQL database. The -s flag specifies the schema source (repeatable) and -d provides the connection string. Use --target-schemas to limit to specific schemas (default: public) and --json for JSON output.

```bash
pgmold lint -s sql:schema.sql -d postgres://localhost/mydb
```

--------------------------------

### Specify multiple sources with repeated -s flags

Source: https://pgmold.dev/docs/guides/multi-file-schemas

Specifies multiple sources by repeating the -s flag. Each source can be a single file or a glob pattern.

```bash
pgmold apply -s sql:types.sql -s "sql:tables/*.sql" -d postgres://localhost/mydb

```

--------------------------------

### CLI drift detection command

Source: https://pgmold.dev/docs/guides/ci-cd

Runs drift detection locally or in custom CI. Uses the `drift` command with `-s` for schema source and `-d` for database connection. The `--json` flag outputs JSON. Exit code is non-zero when drift is detected.

```bash
pgmold drift -s sql:schema/ -d postgres://localhost/mydb --json
```

--------------------------------

### Production mode blocks table drops

Source: https://pgmold.dev/docs/guides/safety

Set PGMOLD_PROD=1 for production mode, which blocks table drops entirely, even with --allow-destructive.

```bash
PGMOLD_PROD=1 pgmold apply -s sql:schema.sql -d postgres://localhost/prod
```

--------------------------------

### pgmold drift

Source: https://pgmold.dev/docs/reference/cli

Detect schema drift between SQL files and a live database. Returns exit code 1 if drift is detected.

```APIDOC
## pgmold drift

### Description
Detect schema drift between SQL files and a live database. Returns exit code 1 if drift is detected.

### Command
pgmold

### Options
- `-s, --schema <SOURCE>` - Schema source (repeatable)
- `-d, --database <URL>` - PostgreSQL connection string
- `--target-schemas <LIST>` - Comma-separated PostgreSQL schemas (default: `public`)
- `--json` - JSON output

### Example
```bash
pgmold drift -s sql:schema.sql -d postgres://localhost/mydb --json
```

```

--------------------------------

### Create pgmold_schema resource

Source: https://pgmold.dev/docs/reference/terraform

Defines a pgmold_schema resource that applies a SQL schema file to a PostgreSQL database. The allow_destructive flag is set to false to prevent DROP operations.

```hcl
resource "pgmold_schema" "app" {
  schema_file       = "${path.module}/schema.sql"
  database_url      = var.database_url
  allow_destructive = false
}

```

--------------------------------

### CI check: pgmold plan without --allow-destructive

Source: https://pgmold.dev/docs/reference/limitations

Run CI without --allow-destructive to catch unintended renames. pgmold refuses the plan and surfaces the drop + add for review. The error message is 'destructive operations present (use --allow-destructive)'.

```shell
pgmold plan -s sql:schema/ -d $DATABASE_URL
# Error: destructive operations present (use --allow-destructive)
```

--------------------------------

### Include extension objects in dump

Source: https://pgmold.dev/docs/guides/filtering

Use --include-extension-objects with pgmold dump to include objects owned by extensions (e.g., PostGIS functions, pg_trgm operators) in the output. By default, these are excluded from diffs.

```bash
# Include extension objects if needed
pgmold dump -d postgres://localhost/mydb --include-extension-objects -o full_schema.sql
```

--------------------------------

### Apply with destructive operation blocking

Source: https://pgmold.dev/docs/guides/safety

By default, pgmold blocks destructive operations like DROP TABLE, DROP COLUMN, and DROP ENUM. This command will fail if the plan includes any drops. Use --allow-destructive to explicitly opt in.

```bash
# This will fail if the plan includes any drops
pgmold apply -s sql:schema.sql -d postgres://localhost/mydb


# Explicitly opt in to destructive operations
pgmold apply -s sql:schema.sql -d postgres://localhost/mydb --allow-destructive
```

--------------------------------

### pgmold rename detection output: ALTER TABLE ADD/DROP COLUMN

Source: https://pgmold.dev/docs/reference/limitations

pgmold emits this drop + add pair when a column is renamed from entity_id to supplier_id. The DROP COLUMN ... CASCADE destroys the column data and cascades to dependent indexes, constraints, and views. Requires --allow-destructive to apply.

```sql
ALTER TABLE orders ADD COLUMN supplier_id UUID;
ALTER TABLE orders DROP COLUMN entity_id CASCADE;
```

--------------------------------

### Workaround: ALTER TABLE RENAME COLUMN

Source: https://pgmold.dev/docs/reference/limitations

Workaround step 1: apply the rename directly to the database. This is instant in PostgreSQL (catalog-only, no data movement). The same approach works for RENAME TABLE, RENAME INDEX, and RENAME CONSTRAINT.

```sql
ALTER TABLE orders RENAME COLUMN entity_id TO supplier_id;
```
Tokens:
Raw