{"id":16487,"library":"pgpm","title":"PostgreSQL Package Manager CLI","description":"pgpm is a powerful and opinionated command-line interface (CLI) for managing PostgreSQL database schemas, migrations, and modules, akin to a package manager for your database. It is designed for modular PostgreSQL development, enabling the creation and management of reusable database packages with dependency resolution and semantic versioning. The tool features a deterministic migration engine, offering version-controlled, plan-driven deployments with rollback capabilities and idempotent execution. It supports recursive module resolution across workspaces and provides turnkey module-first workspaces ready for CI/CD, Docker, and end-to-end testing with TypeScript tooling. The current stable version is 4.16.3, and it appears to be actively maintained with a focus on robust and reproducible database development workflows.","status":"active","version":"4.16.3","language":"javascript","source_language":"en","source_url":"https://github.com/constructive-io/constructive","tags":["javascript","cli","command-line","tool","postgres","postgresql","migration","package-manager","database","typescript"],"install":[{"cmd":"npm install pgpm","lang":"bash","label":"npm"},{"cmd":"yarn add pgpm","lang":"bash","label":"yarn"},{"cmd":"pnpm add pgpm","lang":"bash","label":"pnpm"}],"dependencies":[],"imports":[{"note":"While `pgpm` is primarily a CLI tool and not typically imported for runtime execution, it ships TypeScript types. This import is for type-checking configuration objects (e.g., in a `pgpm.config.ts` file) if such programmatic configuration is supported or exposed. Direct runtime imports of `pgpm`'s core functionality are uncommon.","symbol":"PgpmConfiguration","correct":"import type { PgpmConfiguration } from 'pgpm'"},{"note":"This is a hypothetical import for programmatic access to specific `pgpm` commands. The primary usage pattern for `pgpm` is via the command line. Direct programmatic invocation might exist for advanced scenarios or internal tooling, but is not the typical user interaction.","symbol":"deploy","correct":"import { deploy } from 'pgpm/commands'"},{"note":"Similar to `deploy`, this is a hypothetical programmatic import for workspace initialization. Most users will interact with `pgpm` via `pgpm init workspace` directly from the terminal, reflecting its CLI-first design.","symbol":"initWorkspace","correct":"import { initWorkspace } from 'pgpm/workspace'"}],"quickstart":{"code":"# Install pgpm globally\nnpm install -g pgpm\n\n# Start a local PostgreSQL instance using Docker and export environment variables\npgpm docker start\neval \"$(pgpm env)\"\n\n# Create a new pgpm workspace\npgpm init workspace my-app\ncd my-app\n\n# Create your first database module within the workspace\npgpm init packages/my-module\ncd packages/my-module\n\n# Add a sample migration (e.g., create a users table)\n# Create src/migrations/001_create_users_table.sql with content:\n/*\ncreate table users (\n  id serial primary key,\n  name text not null\n);\n*/\n\n# Return to the workspace root\ncd ../..\n\n# Deploy all changes and modules to the database, creating the database if it doesn't exist\npgpm deploy --createdb --database mydatabase\n\nconsole.log(\"pgpm workspace and initial module deployed successfully!\")","lang":"typescript","description":"Demonstrates global installation, starting a local PostgreSQL instance via Docker, initializing a workspace and a module, adding a basic migration, and deploying changes to the database."},"warnings":[{"fix":"Consult the official `pgpm` release notes for your target version and update migration scripts or project configurations as specified. Back up your database and codebase before any major upgrade.","message":"Major versions of `pgpm` may introduce changes to migration file formats, project structure, or command-line arguments. Always review release notes carefully before upgrading to avoid unexpected behavior or requiring manual migration adjustments.","severity":"breaking","affected_versions":">=4.0"},{"fix":"Use `eval \"$(pgpm env)\"` after `pgpm docker start` or manually set `PG*` environment variables in your shell. For CI/CD, configure these variables in your pipeline environment.","message":"When running `pgpm` commands that interact with a database (e.g., `deploy`, `verify`), ensure your `PG*` environment variables (like `PGHOST`, `PGPORT`, `PGUSER`, `PGPASSWORD`, `PGDATABASE`) are correctly set. `pgpm` relies on these for connection, and incorrect settings will lead to connection errors.","severity":"gotcha","affected_versions":">=1.0"},{"fix":"Ensure your `pgpm` workspace and module definitions conform to the latest recursive module resolution standards. Use `pgpm init` and `pgpm install` to correctly scaffold and manage module dependencies.","message":"The package resolution logic for `pgpm`'s database modules changed in prior versions, moving towards a more `npm`-like recursive dependency model. This might affect how modules are located and deployed if not updated.","severity":"breaking","affected_versions":">=3.0"},{"fix":"Always use `pgpm`'s migration system to introduce schema changes. If a mismatch occurs, investigate the differences in your migration files and the applied schema. You may need to revert, fix, and re-deploy, or in rare cases, manually synchronize the migration state, understanding the associated risks.","message":"Direct modification of `pgpm`-managed database objects outside of `pgpm` migrations can lead to 'migration checksum mismatch' errors, as `pgpm`'s deterministic engine expects controlled schema changes. This often happens if you manually alter the database schema or run raw SQL outside the `pgpm` workflow.","severity":"gotcha","affected_versions":">=1.0"}],"env_vars":null,"last_verified":"2026-04-22T00:00:00.000Z","next_check":"2026-07-21T00:00:00.000Z","problems":[{"fix":"Verify that your PostgreSQL database is running and accessible. Check your `PGHOST`, `PGPORT`, `PGUSER`, `PGPASSWORD`, and `PGDATABASE` environment variables. If using Docker, ensure `pgpm docker start` was successful and `eval \"$(pgpm env)\"` was run.","cause":"The `pgpm` CLI tool cannot establish a connection to the PostgreSQL database, likely due to incorrect connection parameters, the database not running, or network issues.","error":"Error: Database connection refused."},{"fix":"Do NOT modify migration files after they have been deployed to a shared environment. If this occurred in development, revert the changes, or if necessary, reset your local database state and re-deploy. For production, a new 'reverting' migration or a data patch might be needed, with careful planning.","cause":"The content of a previously applied migration file has changed, or the corresponding schema object in the database no longer matches the expected state recorded by `pgpm`.","error":"Error: Migration checksum mismatch detected for migration '001_initial_schema.sql'."},{"fix":"Ensure the module name is correct. If it's a public module, run `pgpm install @pgpm/my-module`. If it's a local module within your workspace, verify its `package.json` and directory structure. Check your `pgpm.json` configuration for correct module paths.","cause":"The `pgpm` CLI could not locate a referenced database module or resolve its dependencies, often due to an incorrect module name, missing installation, or an issue with the workspace configuration.","error":"Error: Module '@pgpm/my-module' not found or dependency not resolved."},{"fix":"Run `pgpm deploy` to apply the pending migrations to your database. If you expect the database to be up-to-date, run `pgpm verify` to identify which migrations are pending or if there's a discrepancy.","cause":"The database schema is not up-to-date with the latest migration files in your project. This means new migration files exist in your codebase that haven't been run against the connected database.","error":"Error: There are pending migrations that have not been applied."}],"ecosystem":"npm"}