Testing Your Database Module

Previously: In Creating Your First Postgres Database Module, you created and deployed the pets module. Now let's add tests.

Testing database logic requires real Postgres—not mocks. Your pgpm workspace comes with testing configured, so you can start writing tests immediately.

Prerequisites

Requires: Complete Creating Your First Postgres Database Module.

Step 1: Install Dependencies

Navigate to your workspace root and install dependencies:

cd my-database-project
pnpm install

This installs the testing dependencies configured by pgpm init workspace.

Note: If you haven't already, make sure to bootstrap database users (covered in Prerequisites). This creates anonymous, authenticated, and administrator roles for realistic testing scenarios. Run this once:

pgpm admin-users bootstrap --yes

Step 2: Write Your First Test

Navigate to your pets module and create/update __tests__/basic.test.ts:

import { getConnections, PgTestClient } from 'pgsql-test';

let pg: PgTestClient;
let teardown: () => Promise<void>;

beforeAll(async () => {
  // Automatically discovers and deploys your module
  ({ pg, teardown } = await getConnections());
});

afterAll(() => teardown());
beforeEach(() => pg.beforeEach());
afterEach(() => pg.afterEach());

it('can insert and query pets', async () => {
  await pg.query(`
    INSERT INTO pets.pets (name, species, age)
    VALUES ('Buddy', 'dog', 3)
  `);

  const result = await pg.query(
    'SELECT * FROM pets.pets WHERE name = $1',
    ['Buddy']
  );

  console.log('Query result:', result.rows[0]);

  expect(result.rows[0].species).toBe('dog');
  expect(result.rows[0].age).toBe(3);
  expect(result.rows[0].adopted).toBe(false);
});

Step 3: Run Tests

Start Jest:

pnpm test

You'll see:

Query result: {
  id: '8eb947ad-0e73-4cd4-8720-11ce3c7a83ba',
  name: 'Buddy',
  species: 'dog',
  age: 3,
  adopted: false,
  created_at: 2025-11-18T03:54:44.289Z
}


 PASS  __tests__/pets.test.js
  ✓ can insert and query pets (8ms)

Test Suites: 1 passed, 1 total
Tests:       1 passed, 1 total

What's Next

You've added tests to your module. In the next lesson, we'll explore module dependencies—creating modules that depend on other modules and using cross-module references.

Key Takeaways

• pgpm workspaces come with testing configured out of the box
• Run pnpm test to verify your database module
• Tests automatically deploy your module with zero configuration
• Each test starts with a clean state—no manual cleanup needed