# pgmold Tool Usage Guide for Agents
Quick reference for using pgmold as a tool in automated workflows.
## Command Reference
### Core Commands
```bash
# Preview changes (safe, read-only)
pgmold plan --schema sql:schema.sql --database db:postgres://localhost/mydb
# Apply changes
pgmold apply --schema sql:schema.sql --database db:postgres://localhost/mydb
# Apply with destructive operations
pgmold apply --schema sql:schema.sql --database db:postgres://localhost/mydb --allow-destructive
# Export database schema
pgmold dump db:postgres://localhost/mydb > schema.sql
# Check for drift
pgmold drift --schema sql:schema.sql --database db:postgres://localhost/mydb
# Compare two schemas
pgmold diff --from sql:old.sql --to sql:new.sql
```
### Connection String Format
```
db:postgres://user:password@host:port/database
```
Examples:
```bash
db:postgres://postgres:secret@localhost:5432/myapp
db:postgres://user@localhost/testdb
db:$DATABASE_URL # Use environment variable
```
### Schema Providers
```bash
# SQL file
--schema sql:schema.sql
# SQL directory (all .sql files recursively)
--schema sql:./schema/
# Glob pattern
--schema "sql:schema/**/*.sql"
# Drizzle ORM
--schema drizzle:drizzle.config.ts
# Multiple sources
--schema sql:base.sql --schema drizzle:drizzle.config.ts
```
## Common Scenarios
### 1. Check if Schema Needs Updates
```bash
# Returns exit code 0 if no changes needed, 1 if changes needed
pgmold drift --schema sql:schema.sql --database db:postgres://localhost/mydb
echo $? # Check exit code
```
### 2. Preview Changes Before Applying
```bash
# Show what would change
PLAN=$(pgmold plan --schema sql:schema.sql --database db:postgres://localhost/mydb)
# Check if plan is empty
else
echo "Changes needed:"
echo "$PLAN"
fi
```
### 3. Apply Changes Safely
```bash
# Preview first
pgmold plan --schema sql:schema.sql --database db:postgres://localhost/mydb
# Apply if plan looks good
pgmold apply --schema sql:schema.sql --database db:postgres://localhost/mydb --allow-destructive
```
### 4. Export Current Database Schema
```bash
# Single file
pgmold dump db:postgres://localhost/mydb > current_schema.sql
# Split by object type
pgmold dump db:postgres://localhost/mydb --split -o schema/
```
### 5. Work with Multiple Schemas
```bash
# Specify target schemas (comma-separated)
pgmold plan \
--schema sql:schema.sql \
--database db:postgres://localhost/mydb \
--target-schemas public,auth,api
```
### 6. Filter Objects
```bash
# By name pattern
pgmold plan \
--schema sql:schema.sql \
--database db:postgres://localhost/mydb \
--include 'api_*' \
--exclude '_internal*'
# By object type
pgmold plan \
--schema sql:schema.sql \
--database db:postgres://localhost/mydb \
--exclude-types policies,triggers
# Available types: extensions, tables, enums, domains, functions, views,
# triggers, sequences, partitions, policies, indexes,
# foreignkeys, checkconstraints
```
### 7. Generate Migration File
```bash
# Auto-numbered migration file
pgmold migrate generate \
--schema sql:schema/ \
--database db:postgres://localhost/mydb \
--migrations ./migrations \
--name "add_user_email"
# Creates: migrations/0001_add_user_email.sql
```
### 8. JSON Output for Parsing
```bash
# Get JSON output
pgmold plan \
--schema sql:schema.sql \
--database db:postgres://localhost/mydb \
--json > plan.json
# Parse with jq
cat plan.json | jq -r '.operations[].sql' # Extract SQL
```
## Error Handling
### Common Errors
#### 1. Destructive Operations Blocked
```
Error: Migration contains destructive operations (use --allow-destructive)
```
**Fix:** Add `--allow-destructive` flag if you understand the impact.
#### 2. Connection Failed
```
Error: Database error: connection refused
```
**Fix:**
- Check PostgreSQL is running
- Verify connection string format: `db:postgres://user:pass@host:port/database`
- Ensure database exists
#### 3. Schema Provider Not Found
```
Error: Unknown schema provider: schema.sql
```
**Fix:** Add provider prefix: `sql:schema.sql` or `drizzle:config.ts`
#### 4. Circular Dependency
```
Error: Circular foreign key dependency: table_a <-> table_b
```
**Fix:** Check schema for circular foreign keys. Use deferrable constraints or break the cycle.
## Automation Patterns
### Bash: Safe Apply with Confirmation
```bash
#!/bin/bash
set -e
SCHEMA="sql:schema.sql"
DB="db:postgres://localhost/mydb"
# Check for changes
if ! pgmold drift --schema "$SCHEMA" --database "$DB" > /dev/null 2>&1; then
# Show plan
echo "Changes needed:"
pgmold plan --schema "$SCHEMA" --database "$DB"
# Confirm
read -p "Apply? (yes/no) " -r
if [[ $REPLY == "yes" ]]; then
pgmold apply --schema "$SCHEMA" --database "$DB" --allow-destructive
fi
else
echo "No changes needed"
fi
```
### CI: Validate on Pull Request
```yaml
# .github/workflows/validate-schema.yml
name: Validate Schema
on: [pull_request]
jobs:
validate:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- name: Install pgmold
run: cargo install pgmold
- name: Check schema is valid
run: |
pgmold plan \
--schema sql:schema.sql \
--database db:${{ secrets.TEST_DB_URL }}
```
### CI: Detect Drift
```yaml
# .github/workflows/drift-check.yml
name: Schema Drift Check
on:
schedule:
- cron: '0 8 * * *' # Daily at 8am
jobs:
drift:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- name: Check for drift
uses: fmguerreiro/pgmold/.github/actions/drift-check@main
with:
schema: 'sql:schema/'
database: ${{ secrets.DATABASE_URL }}
fail-on-drift: 'true'
```
### Pre-commit Hook
```bash
#!/bin/bash
# .git/hooks/pre-commit
# Check syntax is valid
if ! pgmold lint --schema sql:schema.sql; then
echo "Schema validation failed"
exit 1
fi
fi
```
## Exit Codes
| 0 | Success / No drift |
| 1 | Error / Drift detected |
| 2 | Invalid arguments |
## Tips for Agents
1. **Always preview before applying**: Use `plan` before `apply`
2. **Check exit codes**: `drift` command returns non-zero if drift detected
3. **Use JSON for parsing**: Add `--json` flag for structured output
4. **Filter when possible**: Use `--exclude-types` to skip irrelevant objects
5. **Multi-schema databases**: Always specify `--target-schemas` explicitly
6. **Destructive operations**: Default is safe (blocks drops). Add `--allow-destructive` only when needed.
## Quick Troubleshooting
### How do I...
**Check if database matches schema?**
```bash
pgmold drift --schema sql:schema.sql --database db:postgres://localhost/mydb
```
**See what would change without applying?**
```bash
pgmold plan --schema sql:schema.sql --database db:postgres://localhost/mydb
```
**Apply changes to multiple schemas?**
```bash
pgmold apply \
--schema sql:schema.sql \
--database db:postgres://localhost/mydb \
--target-schemas public,auth,api
```
**Export current database to SQL?**
```bash
pgmold dump db:postgres://localhost/mydb > schema.sql
```
**Skip certain object types?**
```bash
pgmold plan \
--schema sql:schema.sql \
--database db:postgres://localhost/mydb \
--exclude-types policies,triggers
```
**Generate a migration file?**
```bash
pgmold migrate generate \
--schema sql:schema/ \
--database db:postgres://localhost/mydb \
--migrations ./migrations \
--name "my_change"
```
## JSON Output Schema
```json
{
"operations": [
{
"type": "CreateTable",
"schema": "public",
"table": "users",
"sql": "CREATE TABLE \"public\".\"users\" (...)"
},
{
"type": "AddColumn",
"schema": "public",
"table": "orders",
"column": "user_id",
"sql": "ALTER TABLE \"public\".\"orders\" ADD COLUMN \"user_id\" INTEGER"
}
],
"destructive": false,
"warnings": []
}
```
Parse with `jq`:
```bash
# Count operations
# Check if destructive
jq '.destructive' plan.json
# Extract SQL statements
jq -r '.operations[].sql' plan.json
# Filter by operation type
