PGPM Workspaces

Create and manage pgpm workspaces for modular PostgreSQL development. Workspaces bring npm-style modularity to database development.

When to Apply

Use this skill when:

• Starting a new modular database project
• Creating a pgpm workspace structure
• Initializing database modules
• Setting up a pnpm monorepo for database packages

Quick Start

Create a Workspace

pgpm init workspace

Enter workspace name when prompted:

? Enter workspace name: my-database-project

This creates a complete pnpm monorepo:

my-database-project/
├── docker-compose.yml
├── pgpm.json
├── lerna.json
├── LICENSE
├── Makefile
├── package.json
├── packages/
├── pnpm-workspace.yaml
├── README.md
└── tsconfig.json

Install Dependencies

cd my-database-project
pnpm install

Create a Module

Inside the workspace:

pgpm init

Enter module details:

? Enter module name: pets
? Select extensions: uuid-ossp, plpgsql

This creates:

packages/pets/
├── pets.control
├── pgpm.plan
├── deploy/
├── revert/
└── verify/

Workspace vs Module

Workspace: Top-level directory containing your entire project. Has pgpm.json and packages/ directory. Like an npm project root.

Module: Self-contained database package inside the workspace. Has its own pgpm.plan, .control file, and migration directories. Like an individual npm package.

Key Files

pgpm.json (Workspace Config)

{
  "packages": ["packages/*"]
}

Points pgpm to your modules directory.

module.control (Module Metadata)

# pets.control
comment = 'Pet adoption module'
default_version = '0.0.1'
requires = 'uuid-ossp,plpgsql'

Declares module name, description, version, and dependencies.

pgpm.plan (Migration Plan)

%syntax-version=1.0.0
%project=pets
%uri=pets

schemas/pets 2025-11-14T00:00:00Z Author <author@example.com>
schemas/pets/tables/pets [schemas/pets] 2025-11-14T00:00:00Z Author <author@example.com>

Tracks all changes in deployment order.

Common Commands

Environment Setup

Before deploying, ensure PostgreSQL is running and connection variables are loaded.

See pgpm-docker skill for starting PostgreSQL and pgpm-env skill for loading environment variables.

# Verify connection
psql -c "SELECT version();"

# Bootstrap database users (run once)
pgpm admin-users bootstrap --yes

Deploy a Module

cd packages/pets
pgpm deploy --database pets_dev --createdb --yes

pgpm:

1. Creates the database if needed
2. Resolves dependencies
3. Deploys changes in order
4. Tracks deployment in pgpm_migrate schema

Module Structure Best Practices

Organize changes hierarchically:

deploy/
└── schemas/
    └── app/
        ├── schema.sql
        ├── tables/
        │   └── users.sql
        ├── functions/
        │   └── create_user.sql
        └── triggers/
            └── updated_at.sql

Use nested paths:

pgpm add schemas/app/schema
pgpm add schemas/app/tables/users --requires schemas/app/schema
pgpm add schemas/app/functions/create_user --requires schemas/app/tables/users

Troubleshooting

References

• Related skill: pgpm-docker for Docker management
• Related skill: pgpm-env for environment configuration
• Related skill: pgpm-changes for authoring database changes