pgpm Workspaces: A New Way to Organize Postgres Projects

Database development shouldn't feel like you're stuck in 2005. When you want to build a new feature, you shouldn't spend hours configuring migration tools, manually tracking dependencies, or wondering if your schema changes will deploy in the right order. pgpm workspaces solve this by treating database schemas as composable modules—just like npm packages for your frontend code.

The Problem: Monolithic Database Development

Traditional database development treats your entire schema as one giant migration chain. You write SQL files, number them sequentially, and hope they run in order. When you want to reuse a schema across projects, you copy-paste SQL files and lose all version history. When modules depend on each other, you manually track what needs to deploy first.

This doesn't scale. Frontend developers expect modularity—they install packages with npm install, compose applications from reusable components, and let the package manager handle dependency resolution. Database development deserves the same sophistication.

pgpm Workspaces: npm for Postgres

pgpm workspaces bring npm-style modularity to Postgres. Instead of monolithic migration scripts, you build focused modules that declare their dependencies explicitly. Instead of manual deployment orchestration, you get automatic dependency resolution.

A pgpm workspace is a pnpm monorepo containing multiple database modules. Each module is self-contained with its own migrations, dependencies, and version tags. When you deploy, pgpm discovers all modules, resolves the dependency graph, and deploys everything in the correct order.

Workspace vs Module: Understanding the Distinction

Workspace: The top-level directory containing your entire project. It has a pgpm.json configuration file and a packages/ directory where modules live. Think of it like your npm project root with package.json.

Module: A self-contained database package inside the workspace. Each module has its own pgpm.plan file, .control file, and deploy/, revert/, verify/ directories. Think of it like an individual npm package.

Example structure:

my-workspace/              # Workspace root
├── pgpm.json         # Workspace config
├── packages/             # Modules directory
│   ├── users/           # Module: user management
│   │   ├── pgpm.plan
│   │   ├── users.control
│   │   ├── deploy/
│   │   ├── revert/
│   │   └── verify/
│   └── organizations/   # Module: org management
│       ├── pgpm.plan
│       ├── organizations.control
│       ├── deploy/
│       ├── revert/
│       └── verify/

How Modules Declare Dependencies

Modules declare dependencies in their .control file, similar to package.json:

# organizations.control
comment = 'Organization management module'
default_version = '0.0.1'
requires = 'users,uuid-ossp'

This tells pgpm that the organizations module depends on the users module and the uuid-ossp Postgres extension. When you deploy organizations, pgpm automatically deploys users first.

Automatic Dependency Resolution

When you run pgpm deploy, pgpm:

1. Scans your workspace for all modules (by finding .control files)
2. Builds a dependency graph from the requires fields
3. Resolves the topological order (which modules must deploy first)
4. Deploys modules in the correct sequence
5. Tracks what's been deployed in the pgpm_migrate schema

You never manually specify deployment order. pgpm handles the orchestration automatically.

Cross-Module References

Modules can reference changes from other modules in their dependency declarations. If your organizations module needs a specific change from the users module, you can declare it:

-- Deploy tables/memberships
-- requires: users:tables/users

CREATE TABLE organizations.memberships (
  user_id UUID REFERENCES users(id),
  organization_id UUID REFERENCES organizations(id)
);

The -- requires: users:tables/users comment tells pgpm that this change depends on the tables/users change from the users module. pgpm ensures users:tables/users deploys before this change.

Benefits of Modular Development

• Composability - Build reusable modules that work across projects
• Explicit Dependencies - Dependencies are declared in code, not documentation
• Transactional Safety - All deployments happen in transactions with automatic rollback
• Fast Iteration - TypeScript-based engine deploys complex schemas in milliseconds
• Cross-Project Reuse - Publish modules to npm and install them with pgpm install

What's Next

In the next lesson, we'll initialize your first pgpm workspace. You'll see how to scaffold the directory structure, configure the workspace, and prepare for module development. Then we'll create your first module and deploy it to Postgres.

The future of database development is modular, composable, and fast. Let's build it together.