Get started with modular databases in minutes. Install prerequisites and deploy your first module.

Quickstart: Getting Up and Running

Set up Node.js, PostgreSQL, and pgpm. Everything you need to get started with modular database development.

Prerequisites

Experience the power of modular databases by installing a pre-built module and deploying it to PostgreSQL—no SQL writing required.

Spinning Up a Modular Database in 2 Minutes

Learn to organize PostgreSQL projects with pgpm workspaces and reusable database modules.

Modular PostgreSQL Development with Database Packages

Understand how pgpm workspaces bring npm-style modularity to PostgreSQL development.

pgpm Workspaces: A New Way to Organize PostgreSQL Projects

Set up your first pgpm workspace with proper configuration and structure.

Initializing a Workspace for Modular PostgreSQL Development

Build your first database module with deploy, revert, and verify scripts.

Creating Your First PostgreSQL Database Module

Write and run tests for your database module with watch mode for rapid feedback.

Testing Your Database Module

Master module dependencies, cross-module references, and automatic dependency resolution.

Managing Database Package Dependencies in a Workspace

Master the workflow for adding, organizing, and managing database changes with pgpm.

Authoring Database Changes

Learn the fundamental workflow for adding database changes using the pgpm add command and the three-file pattern for safe, reversible migrations.

Adding Your First Database Change

Structure migrations using nested paths for maintainable schemas.

Change Naming Conventions

Understand the plan file format and how pgpm generates deployment plans from SQL dependencies.

Plan Files and Deployment Plans

Master end-to-end PostgreSQL testing with ephemeral databases, RLS testing, and CI/CD automation.

End-to-End PostgreSQL Testing with TypeScript

Create ephemeral PostgreSQL databases for testing with pgsql-test. Fast, isolated, and automatically cleaned up.

Spinning Up Temporary Testing Databases for Postgres

Test RLS policies by simulating different users and roles. Verify that your security policies actually work as intended.

How to Test Row-Level Security (RLS) in PostgreSQL

Test complex multi-user scenarios by switching between users within tests. Master the savepoint pattern to handle exceptions and aborted transactions when testing operations that should fail.

Advanced RLS Testing Scenarios - Simulating Many Users and Roles

Learn how to seed test databases with realistic data using loadJson, loadSql, and loadCsv methods for comprehensive testing scenarios.

Seeding PostgreSQL Test Databases

TypeScript-native testing for Supabase with modern workflows.

Supabase Testing

Introducing Supabase Test Suite: TypeScript-native testing for Supabase with isolated databases, RLS testing, and instant feedback loops.

Supabase Testing for the Modern Developer

Get started with supabase-test: install dependencies, configure local Supabase, and write your first isolated database test with automatic rollbacks.

Setting Up Local Supabase Database Testing

Create your first Supabase test with supabase-test's getConnections(), understand test isolation, and verify your setup with simple database queries.

Writing Your First Supabase Test in TypeScript

Test Supabase RLS policies by simulating authenticated users and anonymous access. Verify your security policies work as intended with supabase-test.

Testing Row-Level Security Policies in Supabase

Learn how to seed Supabase test databases with auth.users, realistic data using loadJson, loadSql, and loadCsv methods for comprehensive testing.

Seeding Supabase Test Databases

Set up GitHub Actions CI/CD pipeline to automatically test your Supabase database with every commit. Learn how to configure workflows, start Supabase, and run tests.

Running Supabase Tests in GitHub Actions

Automate Supabase testing in GitHub Actions: provision local Supabase stack, run tests in CI, and optimize for fast feedback loops.

Supabase Troubleshooting

Common issues and solutions for pgpm, PostgreSQL, and testing.

Troubleshooting

Quick fixes for common pgpm, PostgreSQL, and testing issues.

Common Issues and Solutions


> **Previously:** In [Spinning Up Temporary Testing Databases for Postgres](./spinning-up-temporary-testing-databases), we created a pets module with a schema and table. Now let's add RLS policies and test them.

Row-Level Security (RLS) is PostgreSQL's most powerful security feature, but it's also the hardest to test. You need to simulate different users, different roles, and verify that policies actually enforce the access control you expect.

In this lesson, you'll add RLS policies to your pets module and test them with pgsql-test by switching between user contexts.

## Prerequisites

See [Prerequisites](/learn/quickstart/prerequisites). Requires: Complete [Spinning Up Temporary Testing Databases for Postgres](./spinning-up-temporary-testing-databases).

## Why Test RLS?

RLS secures multi-tenant applications by ensuring users only access their own data. Without testing, you risk data leaks between users or overly restrictive policies that block legitimate access.

pgsql-test makes RLS testing simple with the `setContext()` method—switch between users and roles to verify your security policies work correctly.

## Adding RLS to Pets

Add owner_id column and RLS policies:

```bash
pgpm add schemas/pets/tables/pets/add_rls --requires schemas/pets/tables/pets
```

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

```sql
-- Deploy: schemas/pets/tables/pets/add_rls
-- requires: schemas/pets/tables/pets

-- Add owner_id column
ALTER TABLE pets.pets
  ADD COLUMN owner_id UUID;

-- Enable RLS
ALTER TABLE pets.pets ENABLE ROW LEVEL SECURITY;

-- Grant schema usage
GRANT USAGE ON SCHEMA pets TO anonymous;
GRANT USAGE ON SCHEMA pets TO authenticated;

-- Grant permissions
GRANT SELECT ON TABLE pets.pets TO anonymous;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE pets.pets TO authenticated;

-- Policy: Anyone can view all pets
CREATE POLICY pets_select_all ON pets.pets
  FOR SELECT
  USING (true);

-- Policy: Users can only insert pets they own
CREATE POLICY pets_insert_own ON pets.pets
  FOR INSERT
  WITH CHECK (owner_id = current_setting('jwt.claims.user_id', true)::UUID);

-- Policy: Users can only update their own pets
CREATE POLICY pets_update_own ON pets.pets
  FOR UPDATE
  USING (owner_id = current_setting('jwt.claims.user_id', true)::UUID);

-- Policy: Users can only delete their own pets
CREATE POLICY pets_delete_own ON pets.pets
  FOR DELETE
  USING (owner_id = current_setting('jwt.claims.user_id', true)::UUID);
```

## Understanding Test Database Clients

pgsql-test provides two database clients:

- **`pg`** - Postgres superuser for setup/teardown operations
- **`db`** - User client for testing with authentication roles

Use `pg` to insert test data as superuser. Use `db` with `setContext()` to test RLS policies.

## Testing RLS Policies

Create `__tests__/rls.test.ts`:

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

const user1 = '11111111-1111-1111-1111-111111111111';
const user2 = '22222222-2222-2222-2222-222222222222';
const user3 = '33333333-3333-3333-3333-333333333333';
const user4 = '44444444-4444-4444-4444-444444444444';
const user5 = '55555555-5555-5555-5555-555555555555';

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

beforeAll(async () => {
  ({ pg, db, teardown } = await getConnections());

  // Insert test data as superuser
  await pg.query(`
    INSERT INTO pets.pets (name, species, age, owner_id) VALUES
    ('Public Pet', 'dog', 3, $1)
  `, [user1]);
});

afterAll(async () => await teardown());
beforeEach(async () => await db.beforeEach());
afterEach(async () => await db.afterEach());

it('anonymous users can view all pets', async () => {
  // Switch to anonymous role
  db.setContext({ role: 'anonymous' });

  const result = await db.query('SELECT * FROM pets.pets');
  expect(result.rows).toHaveLength(1);
  expect(result.rows[0].name).toBe('Public Pet');
});

it('anonymous users cannot insert pets', async () => {
  db.setContext({ role: 'anonymous' });

  await expect(
    db.query(`
      INSERT INTO pets.pets (name, species, owner_id)
      VALUES ('Hacker Pet', 'cat', $1)
    `, [user2])
  ).rejects.toThrow(/permission denied/);
});
```

The `setContext()` method on `db` sets the role and user context for the current transaction. All subsequent queries run with that authentication context.

## Using Watch Mode for Rapid Testing

For faster feedback, use watch mode. First, initialize git if you haven't already:

```bash
cd /path/to/my-database-project
git init .
```

Then run tests in watch mode:

```bash
cd packages/pets
pnpm test:watch
```

Watch mode re-runs tests automatically when you save changes, giving you instant feedback as you develop.

> **Note:** `pgpm init` defaults to Jest, but pgsql-test is test-framework agnostic—you can use it with Mocha, Vitest, or any other test runner.

**Jest watch mode commands:**
- Press `p` to filter by filename pattern
- Press `t` to filter by test name pattern
- Press `a` to run all tests
- Press `q` to quit watch mode

## Testing Authenticated User Access

Add tests for authenticated users managing their own pets:

```typescript
it('authenticated users can insert pets they own', async () => {
  // Switch to authenticated user
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': user3
  });

  await db.query(`
      INSERT INTO pets.pets (name, species, age, owner_id)
      VALUES ('My Pet', 'cat', 2, $1)
    `, [user3]);

  const result = await db.query('SELECT * FROM pets.pets WHERE name = $1', ['My Pet']);
  expect(result.rows).toHaveLength(1);
  expect(result.rows[0].owner_id).toBe(user3);
});

it('users cannot insert pets for other users', async () => {
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': user4
  });

  await expect(
    db.query(`
        INSERT INTO pets.pets (name, species, owner_id)
        VALUES ('Not My Pet', 'bird', $1)
      `, [user5])
  ).rejects.toThrow(/new row violates row-level security policy/);
});
```

Run the tests:

```bash
pnpm test
```

## Key Takeaways

- **`pg` client** - Use for superuser operations (setup/teardown)
- **`db` client** - Use for testing with authentication roles
- **setContext()** sets authentication context for the current transaction
- **Role switching** is automatic and scoped to the transaction
- **Use `pgpm add`** to add RLS policies as proper database changes
- **owner_id with jwt.claims.user_id** enables user-based access control
- **Test isolation** ensures RLS context doesn't bleed between tests

## What's Next

You've added RLS policies to your pets module and tested basic user access scenarios. In the next lesson, we'll explore advanced multi-user testing scenarios, simulating malicious attempts and testing complex user interactions.
