Publishing PGPM Modules (Constructive Standard)

Publish pgpm SQL modules to npm using pgpm package bundling and lerna for versioning. This covers the workflow for @pgpm/* scoped packages.

When to Apply

Use this skill when:

• Publishing SQL database modules to npm
• Bundling pgpm packages for distribution
• Managing @pgpm/* scoped packages
• Working with pgpm-modules or similar repositories

PGPM vs PNPM Workspaces

Workspace Structure

pgpm-modules/
├── .gitignore
├── lerna.json
├── package.json
├── packages/
│   ├── faker/
│   │   ├── deploy/
│   │   ├── revert/
│   │   ├── verify/
│   │   ├── package.json
│   │   ├── pgpm-faker.control
│   │   └── pgpm.plan
│   └── utils/
│       ├── deploy/
│       ├── revert/
│       ├── verify/
│       ├── package.json
│       ├── pgpm-utils.control
│       └── pgpm.plan
├── pgpm.json
├── pnpm-lock.yaml
└── pnpm-workspace.yaml

Configuration Files

pgpm.json

Points to packages containing SQL modules:

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

pnpm-workspace.yaml

Same packages directory:

packages:
  - packages/*

lerna.json (Fixed Versioning)

For pgpm modules, use fixed versioning so all modules release together:

{
  "$schema": "node_modules/lerna/schemas/lerna-schema.json",
  "version": "0.16.6",
  "npmClient": "pnpm"
}

Note: Unlike TypeScript packages which often use independent versioning, pgpm modules typically use fixed versioning because they’re tightly coupled.

Root package.json

{
  "name": "pgpm-modules",
  "version": "0.0.1",
  "private": true,
  "repository": {
    "type": "git",
    "url": "https://github.com/constructive-io/pgpm-modules"
  },
  "license": "MIT",
  "engines": {
    "node": ">=20"
  },
  "scripts": {
    "bundle": "pnpm -r bundle",
    "lint": "pnpm -r lint",
    "test": "pnpm -r test",
    "deps": "pnpm up -r -i -L"
  },
  "devDependencies": {
    "@types/jest": "^30.0.0",
    "jest": "^30.2.0",
    "lerna": "^8.2.3",
    "pgsql-test": "^2.18.6",
    "ts-jest": "^29.4.5",
    "typescript": "^5.9.3"
  }
}

Module Configuration

Module package.json

{
  "name": "@pgpm/faker",
  "version": "0.16.0",
  "description": "Fake data generation utilities for testing",
  "author": "Dan Lynch <pyramation@gmail.com>",
  "keywords": ["postgresql", "pgpm", "faker", "testing"],
  "publishConfig": {
    "access": "public"
  },
  "scripts": {
    "bundle": "pgpm package",
    "test": "jest",
    "test:watch": "jest --watch"
  },
  "dependencies": {
    "@pgpm/types": "workspace:*",
    "@pgpm/verify": "workspace:*"
  },
  "devDependencies": {
    "pgpm": "^1.3.0"
  },
  "repository": {
    "type": "git",
    "url": "https://github.com/constructive-io/pgpm-modules"
  }
}

Key differences from TypeScript packages:

• No publishConfig.directory — publishes from package root
• Uses pgpm package for bundling instead of makage
• Dependencies on other @pgpm/* modules use workspace:*

Module .control File

# pgpm-faker.control
comment = 'Fake data generation utilities'
default_version = '0.16.0'
requires = 'plpgsql,uuid-ossp'

Module pgpm.plan

%syntax-version=1.0.0
%project=pgpm-faker
%uri=pgpm-faker

schemas/faker 2025-01-01T00:00:00Z Author <author@example.com>
schemas/faker/functions/random_name [schemas/faker] 2025-01-01T00:00:00Z Author <author@example.com>

Build Workflow

Bundle a Module

cd packages/faker
pgpm package

Or bundle all modules:

pnpm -r bundle

Run Tests

# All modules
pnpm -r test

# Specific module
pnpm --filter @pgpm/faker test

Publishing Workflow

1. Prepare

pnpm install
pnpm -r bundle
pnpm -r test

2. Version

# Fixed versioning (all packages get same version)
pnpm lerna version

# Or with conventional commits
pnpm lerna version --conventional-commits

3. Publish

# Use from-package to publish versioned packages
pnpm lerna publish from-package

One-Liner

pnpm install && pnpm -r bundle && pnpm -r test && pnpm lerna version && pnpm lerna publish from-package

Dry Run Commands

# Test versioning (no git operations)
pnpm lerna version --no-git-tag-version --no-push

# Test publishing
pnpm lerna publish from-package --dry-run

Module Dependencies

Internal Dependencies

Use workspace:* for dependencies on other pgpm modules:

{
  "dependencies": {
    "@pgpm/types": "workspace:*",
    "@pgpm/verify": "workspace:*"
  }
}

SQL Dependencies

Declare SQL-level dependencies in the .control file:

requires = 'plpgsql,uuid-ossp,@pgpm/types'

And in deploy scripts:

-- Deploy: schemas/faker/functions/random_name
-- requires: schemas/faker
-- requires: @pgpm/types:schemas/types

CREATE FUNCTION faker.random_name()
RETURNS TEXT AS $$
  -- implementation
$$ LANGUAGE plpgsql;

Three-File Pattern

Every SQL change has three files:

Example:

deploy/schemas/faker/functions/random_name.sql:

-- Deploy: schemas/faker/functions/random_name
-- requires: schemas/faker

BEGIN;
CREATE FUNCTION faker.random_name()
RETURNS TEXT AS $$
BEGIN
  RETURN 'John Doe';
END;
$$ LANGUAGE plpgsql;
COMMIT;

revert/schemas/faker/functions/random_name.sql:

-- Revert: schemas/faker/functions/random_name

BEGIN;
DROP FUNCTION IF EXISTS faker.random_name();
COMMIT;

verify/schemas/faker/functions/random_name.sql:

-- Verify: schemas/faker/functions/random_name

SELECT verify_function('faker.random_name');

Naming Conventions

• Package name: @pgpm/<module-name>
• Control file: pgpm-<module-name>.control
• SQL uses snake_case for identifiers
• Never use CREATE OR REPLACE — pgpm is deterministic

Best Practices

1. Fixed versioning: Use for tightly coupled SQL modules
2. Test before publish: Run pnpm -r test to verify all modules
3. Bundle before publish: Run pnpm -r bundle to create packages
4. Use verify helpers: Leverage @pgpm/verify for consistent verification
5. Document dependencies: Keep .control file and SQL requires in sync

References

• Related skill: pgpm-workspace for workspace setup
• Related skill: pgpm-changes for authoring SQL changes
• Related skill: pgpm-dependencies for managing dependencies
• Related skill: pnpm-publishing for TypeScript package publishing