# **pgpm — a Postgres Package Manager**
**A modern CLI for modular PostgreSQL development.**
`pgpm` is a focused command-line tool for PostgreSQL database migrations and package management. It provides the core functionality for managing database schemas, migrations, and module dependencies.
## ✨ Features
- 📦 **Postgres Module System** — Reusable, composable database packages with dependency management, per-module plans, and versioned releases
- 🔄 **Deterministic Migration Engine** — Version-controlled, plan-driven deployments with rollback support and idempotent execution enforced by dependency and validation safeguards.
- 📊 **Recursive Module Resolution** — Recursively resolves database package dependencies (just like npm) from plan files or SQL headers, producing a reproducible cross-module migration graph.
- 🏷️ **Tag-Aware Versioning** - Deploy to @tags, resolve tags to changes, and reference tags across modules for coordinated releases
- 🐘 **Portable Postgres Development** — Rely on standard SQL migrations for a workflow that runs anywhere Postgres does.
- 🚀 **Turnkey Module-First Workspaces** — `pgpm init` delivers a ready-to-code Postgres workspace with CI/CD, Docker, end-to-end testing, and modern TS tooling.
## 🚀 Quick Start
### Install & Setup
```bash
# Install pgpm globally
npm install -g pgpm
# Start local Postgres (via Docker) and export env vars
pgpm docker start
eval "$(pgpm env)"
```
> **Tip:** Already running Postgres? Skip the Docker step and just export your PG* vars.
---
### Create a Workspace and Install a Package
```bash
# 1. Create a workspace
pgpm init workspace
cd my-app
# 2. Create your first module
pgpm init
cd packages/your-module
# 3. Install a package
pgpm install @pgpm/faker
# 4. Deploy everything
pgpm deploy --createdb --database mydb1
psql -d mydb1 -c "SELECT faker.city('MI');"
> Ann Arbor
```
## 🛠️ Commands
Here are some useful commands for reference:
### Getting Started
- `pgpm init` - Initialize a new module
- `pgpm init workspace` - Initialize a new workspace
- `pgpm init --template ` - Initialize using a full template path (e.g., `pnpm/module`)
- `pgpm init -w` - Create a workspace first, then create the module inside it
### Development Setup
- `pgpm docker start` - Start PostgreSQL container (via Docker)
- `pgpm docker stop` - Stop PostgreSQL container
- `pgpm env` - Print PostgreSQL environment variables for shell export
### Database Operations
- `pgpm deploy` - Deploy database changes and migrations
- `pgpm verify` - Verify database state matches expected migrations
- `pgpm revert` - Safely revert database changes
### Migration Management
- `pgpm migrate` - Comprehensive migration management
- `pgpm migrate init` - Initialize migration tracking
- `pgpm migrate status` - Check migration status
- `pgpm migrate list` - List all changes
- `pgpm migrate deps` - Show change dependencies
### Module Management
- `pgpm install` - Install database modules as dependencies
- `pgpm upgrade-modules` - Upgrade installed modules to latest versions
- `pgpm extension` - Interactively manage module dependencies
- `pgpm tag` - Version your changes with tags
### Packaging and Distribution
- `pgpm plan` - Generate deployment plans for your modules
- `pgpm package` - Package your module for distribution
### Testing
- `pgpm test-packages` - Run integration tests on all modules in a workspace
### Utilities
- `pgpm add` - Add a new database change
- `pgpm remove` - Remove a database change
- `pgpm export` - Export migrations from existing databases
- `pgpm clear` - Clear database state
- `pgpm kill` - Clean up database connections
- `pgpm analyze` - Analyze database structure
- `pgpm rename` - Rename database changes
- `pgpm admin-users` - Manage admin users
- `pgpm cache clean` - Clear cached template repos used by `pgpm init`
- `pgpm update` - Install the latest pgpm version from npm
## 💡 Common Workflows
### Starting a New Project and Adding a Change
```bash
# 1. Create workspace
pgpm init workspace
cd my-app
# 2. Create your first module
pgpm init
cd packages/new-module
# 3. Add some SQL migrations to sql/ directory
pgpm add some_change
# 4. Deploy to database
pgpm deploy --createdb
```
## 🧰 Templates, Caching, and Updates
- `pgpm init` now scaffolds workspaces/modules from `https://github.com/constructive-io/pgpm-boilerplates.git` using `create-gen-app` with a one-week cache (stored under `~/.pgpm/cache/repos`). Override with `--repo`, `--from-branch`, and `--template`, or use a local template path.
- Run `pgpm cache clean` to wipe the cached boilerplates if you need a fresh pull.
- The CLI performs a lightweight npm version check at most once per week (skipped in CI or when `PGPM_SKIP_UPDATE_CHECK` is set). Use `pgpm update` to upgrade to the latest release.
### Working with Existing Projects
```bash
# 1. Navigate to your module
cd packages/your-module
# 2. Install a package
pgpm install @pgpm/faker
# 3. Deploy all installed modules
pgpm deploy --createdb --database mydb1
psql -d mydb1 -c "SELECT faker.city('MI');"
> Ann Arbor
```
### Testing a pgpm module in a workspace
```bash
# 1. Install workspace dependencies
pnpm install
# 2. Enter the packages/
cd packages/yourmodule
# 3. Test the module in watch mode
pnpm test:watch
```
### Database Operations
#### `pgpm deploy`
Deploy your database changes and migrations.
```bash
# Deploy to selected database
pgpm deploy
# Create database if it doesn't exist
pgpm deploy --createdb
# Deploy specific package to a tag
pgpm deploy --package mypackage --to @v1.0.0
# Fast deployment without transactions
pgpm deploy --fast --no-tx
```
#### `pgpm verify`
Verify your database state matches expected migrations.
```bash
# Verify current state
pgpm verify
# Verify specific package
pgpm verify --package mypackage
```
#### `pgpm revert`
Safely revert database changes.
```bash
# Revert latest changes
pgpm revert
# Revert to specific tag
pgpm revert --to @v1.0.0
```
### Migration Management
#### `pgpm migrate`
Comprehensive migration management.
```bash
# Initialize migration tracking
pgpm migrate init
# Check migration status
pgpm migrate status
# List all changes
pgpm migrate list
# Show change dependencies
pgpm migrate deps
```
### Module Management
#### `pgpm install`
Install pgpm modules as dependencies.
```bash
# Install single package
pgpm install @pgpm/base32
# Install multiple packages
pgpm install @pgpm/base32 @pgpm/faker
```
#### `pgpm upgrade-modules`
Upgrade installed pgpm modules to their latest versions from npm.
```bash
# Interactive selection of modules to upgrade
pgpm upgrade-modules
# Upgrade all installed modules without prompting
pgpm upgrade-modules --all
# Preview available upgrades without making changes
pgpm upgrade-modules --dry-run
# Upgrade specific modules
pgpm upgrade-modules --modules @pgpm/base32,@pgpm/faker
# Upgrade modules across all packages in the workspace
pgpm upgrade-modules --workspace --all
```
**Options:**
- `--all` - Upgrade all modules without prompting
- `--dry-run` - Show what would be upgraded without making changes
- `--modules ` - Comma-separated list of specific modules to upgrade
- `--workspace` - Upgrade modules across all packages in the workspace
- `--cwd ` - Working directory (default: current directory)
#### `pgpm extension`
Interactively manage module dependencies.
```bash
pgpm extension
```
#### `pgpm tag`
Version your changes with tags.
```bash
# Tag latest change
pgpm tag v1.0.0
# Tag with comment
pgpm tag v1.0.0 --comment "Initial release"
# Tag specific change
pgpm tag v1.1.0 --package mypackage --changeName my-change
```
### Packaging and Distribution
#### `pgpm plan`
Generate deployment plans for your modules.
```bash
pgpm plan
```
#### `pgpm package`
Package your module for distribution.
```bash
# Package with defaults
pgpm package
# Package without deployment plan
pgpm package --no-plan
```
### Utilities
#### `pgpm dump`
Dump a postgres database to a sql file.
```bash
# dump to default timestamped file
pgpm dump --database mydb
# interactive mode (prompts for database)
pgpm dump
# dump to specific file
pgpm dump --database mydb --out ./backup.sql
# dump from a specific working directory
pgpm dump --database mydb --cwd ./packages/my-module
# dump with pruning
# useful for creating test fixtures or development snapshots
pgpm dump --database mydb --database-id
```
#### `pgpm export`
Export migrations from existing databases.
```bash
pgpm export
```
#### `pgpm kill`
Clean up database connections and optionally drop databases.
```bash
# Kill connections and drop databases
pgpm kill
# Only kill connections
pgpm kill --no-drop
```
### Testing
#### `pgpm test-packages`
Run integration tests on the modules in a workspace. Creates a temporary database per tested module, deploys, and optionally runs verify/revert/deploy cycles.
By default only the **minimal covering set** is tested: the modules nothing else in the
workspace requires. Testing one of those deploys its whole dependency closure in
dependency order, so every module is still exercised — once per covering module instead
of once per module. Excluding a covering module promotes whatever it alone covered.
The property the default trades away is standalone deployability — that a module's own
`requires` is complete rather than satisfied by a sibling in the same database. Use
`--force-all` to get it back.
```bash
# Test the minimal covering set (deploy only)
pgpm test-packages
# Test every module in its own database
pgpm test-packages --force-all
# Run full deploy/verify/revert/deploy cycle
pgpm test-packages --full-cycle
# Continue testing all packages even after failures
pgpm test-packages --continue-on-fail
# Exclude specific modules
pgpm test-packages --exclude my-module,another-module
# Combine options
pgpm test-packages --full-cycle --continue-on-fail --exclude legacy-module
```
**Options:**
- `--force-all` - Test every module in its own database instead of the minimal covering set
- `--full-cycle` - Run full deploy/verify/revert/deploy cycle (default: deploy only)
- `--continue-on-fail` - Continue testing all packages even after failures (default: stop on first failure)
- `--exclude ` - Comma-separated module names to exclude
- `--cwd ` - Working directory (default: current directory)
**Notes:**
- Discovers modules from workspace `pgpm.json` configuration
- Creates isolated test databases (`test_`) for each module
- Automatically cleans up test databases after each test
- Uses internal APIs for deploy/verify/revert operations
## ⚙️ Configuration
### Environment Variables
`pgpm` uses standard PostgreSQL environment variables (`PGHOST`, `PGPORT`, `PGDATABASE`, `PGUSER`, `PGPASSWORD`).
**Quick setup** (recommended):
```bash
eval "$(pgpm env)"
```
**Manual setup** (if you prefer):
```bash
export PGHOST=localhost
export PGPORT=5432
export PGDATABASE=myapp
export PGUSER=postgres
export PGPASSWORD=password
```
**Supabase local development:**
```bash
eval "$(pgpm env --supabase)"
```
## Getting Help
### Command Help
```bash
# Global help
pgpm --help
# Command-specific help
pgpm deploy --help
pgpm tag -h
```
### Common Options
Most commands support these global options:
- `--help, -h` - Show help information
- `--version, -v` - Show version information
- `--cwd ` - Set working directory
## AI Agent Skills
Install the pgpm skill for AI coding agents (Devin, Claude Code, Cursor, Copilot):
```bash
npx skills add https://github.com/constructive-io/constructive --skill pgpm
```
For the full Constructive platform skills (security, blueprints, codegen, billing, etc.):
```bash
npx skills add constructive-io/constructive-skills
```