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

bash

pgpm init workspace

Enter workspace name when prompted:

? Enter workspace name: my-database-project

This creates a complete pnpm monorepo:

text

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

Install Dependencies

bash

cd my-database-project
pnpm install

Create a Module

Inside the workspace:

bash

pgpm init

Enter module details:

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

This creates:

text

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)

json

{
  "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

Command	Description
`pgpm init workspace`	Create new workspace
`pgpm init`	Create new module in workspace
`pgpm add <change>`	Add a database change
`pgpm deploy`	Deploy module to database
`pgpm verify`	Verify deployment
`pgpm revert`	Rollback changes

Environment Setup

Before deploying, configure PostgreSQL connection:

bash

# Start PostgreSQL via Docker
pgpm docker start

# Load environment variables
eval "$(pgpm env)"

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

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

Deploy a Module

bash

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

pgpm:

•Creates the database if needed
•Resolves dependencies
•Deploys changes in order
•Tracks deployment in pgpm_migrate schema

Module Structure Best Practices

Organize changes hierarchically:

text

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

Use nested paths:

bash

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

Issue	Solution
"Cannot connect to Docker"	Start Docker Desktop first
"PGHOST not set"	Run `eval "$(pgpm env)"`
"Connection refused"	Run `pgpm docker start`
Module not found	Ensure you're inside a workspace with `pgpm.json`

References

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