Creating Your First Postgres Database Module

Previously: In Initializing a Workspace for Modular Postgres Development, we set up a pgpm workspace. Now let's create your first database module.

Creating your first database module is where modular database development shines. You'll scaffold a complete module structure with deploy, revert, and verify scripts—all generated automatically. By the end of this lesson, you'll have a working module deployed to Postgres.

Prerequisites

See Prerequisites. Requires: Complete Initializing a Workspace for Modular Postgres Development.

Step 1: Navigate to Your Workspace

Navigate to your pgpm workspace directory:

cd my-database-project

pgpm needs to be inside a workspace to create modules. The CLI detects workspaces by finding the pgpm.json file.

Step 2: Create Your First Module

Initialize a new module:

pgpm init

pgpm prompts for module details:

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

Select extensions your module needs. Use arrow keys to navigate and space to select. Common choices:

• uuid-ossp - UUID generation functions
• plpgsql - PL/pgSQL procedural language
• pgcrypto - Cryptographic functions

You can also add the --extensions flag to pre-select:

pgpm init --extensions plpgsql,uuid-ossp

pgpm scaffolds the module structure:

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

This structure follows Postgres extension conventions with Constructive's migration tracking.

Step 3: Understanding Module Structure

pets.control - Module metadata:

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

The .control file declares:

• Module name (from filename)
• Description
• Version
• Dependencies (Postgres extensions or other database modules)

pgpm.plan - Migration plan:

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

The plan file tracks all changes in deployment order. As you add changes, they appear here with timestamps and dependencies.

deploy/, revert/, verify/ - Migration directories:

• deploy/ - SQL to apply changes
• revert/ - SQL to undo changes
• verify/ - SQL to validate changes

This three-script pattern ensures every change is reversible and verifiable.

Step 4: Add Your First Change

Navigate to your module and add a change:

cd packages/pets
pgpm add schemas/pets

pgpm creates three files:

• deploy/schemas/pets.sql
• revert/schemas/pets.sql
• verify/schemas/pets.sql

The plan file updates:

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

schemas/pets 2025-11-14T00:00:00Z Author <author@example.com> # Adds schemas/pets

Step 5: Define Your Schema

Edit deploy/schemas/pets.sql:

-- Deploy schemas/pets

CREATE SCHEMA pets;

Edit revert/schemas/pets.sql:

-- Revert schemas/pets

DROP SCHEMA pets CASCADE;

Edit verify/schemas/pets.sql:

-- Verify schemas/pets

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name = 'pets';

The verify script confirms the schema exists by querying the information schema.

Step 6: Add a Table Change

Add another change for the pets table that depends on the schema:

pgpm add schemas/pets/tables/pets --requires schemas/pets

This creates a nested directory structure:

deploy/
└── schemas/
    └── pets/
        └── tables/
            └── pets.sql

The plan file shows the dependency:

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

Nested paths organize changes hierarchically. This is useful for complex modules with many tables, functions, and triggers.

Note: The --requires flag is optional. If you use pgpm add chronologically, changes deploy in order like git commits. However, explicitly declaring dependencies helps with advanced features like pgpm plan, which rebuilds deployment plans based on dependencies.

Edit deploy/schemas/pets/tables/pets.sql:

-- Deploy schemas/pets/tables/pets
-- requires: schemas/pets

CREATE TABLE pets.pets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  species TEXT NOT NULL,
  age INTEGER,
  adopted BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now()
);

Edit revert/schemas/pets/tables/pets.sql:

-- Revert schemas/pets/tables/pets

DROP TABLE pets.pets;

Edit verify/schemas/pets/tables/pets.sql:

-- Verify schemas/pets/tables/pets

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'pets'
  AND table_name = 'pets';

Step 7: Deploy Your Module

Create a database for your module:

createdb pets_dev

Deploy your module:

pgpm deploy --database pets_dev --package pets --yes

pgpm:

1. Resolves dependencies (uuid-ossp, plpgsql)
2. Deploys changes in order (schemas/pets, then schemas/pets/tables/pets)
3. Tracks deployment in pgpm_migrate schema
4. Confirms success

The --yes flag skips confirmation prompts. Remove it to see what will deploy before confirming.

Step 8: Verify Your Deployment

Verify the deployment succeeded:

pgpm verify --database pets_dev --package pets

Or query the table directly:

psql -d pets_dev -c "SELECT * FROM pets.pets;"

You should see an empty table with the columns you defined. Your module is now deployed and working correctly!

What's Next

You've created and deployed your first database module. In the next lesson, we'll add tests to your module with instant feedback using watch mode—enabling rapid test-driven development.

Key Takeaways

• pgpm init scaffolds a complete module structure
• The .control file declares module metadata and dependencies
• The pgpm.plan file tracks changes in deployment order
• pgpm add creates deploy, revert, and verify scripts
• Nested paths organize changes hierarchically
• pgpm deploy deploys modules with dependency resolution
• pgpm verify validates deployment success

Your first module is deployed and working. Let's explore multi-module dependencies next.