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 Postgres 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.

End-to-End Postgres Testing with Supabase

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.

Troubleshooting Supabase Test

Learn to test PostgreSQL databases with Drizzle ORM, including RLS policies and context management.

End-to-End Postgres Testing with Drizzle ORM

Set up Drizzle ORM with drizzle-orm-test for PostgreSQL testing. Create type-safe database queries with automatic test isolation.

How to Setup Drizzle ORM for End-to-End Testing

Test RLS policies with Drizzle ORM using automatic context management. Verify security policies with type-safe queries.

Testing Row-Level Security with Drizzle ORM

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 [Drizzle ORM Setup and Basic Testing](./drizzle-orm-setup-and-basic-testing), we set up Drizzle with drizzle-orm-test and created basic tests. Now let's add RLS policies and test them with type-safe queries.

Row-Level Security (RLS) is critical for multi-tenant applications, but testing it traditionally requires manual context switching and raw SQL. With drizzle-orm-test, you get automatic context management—just set the user context and use standard Drizzle queries.

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

## Prerequisites

See [Prerequisites](/learn/quickstart/prerequisites). Requires: Complete [Drizzle ORM Setup and Basic Testing](./drizzle-orm-setup-and-basic-testing).

## Why Test RLS with Drizzle?

RLS testing verifies that users only access their own data. drizzle-orm-test makes this seamless by automatically applying context (role, user ID, JWT claims) before each query. You write normal Drizzle queries, and the context is handled for you.

## Updating the Schema

First, update `src/schema.ts` to include `userId`:

```typescript
import { pgTable, serial, text, integer, boolean } from 'drizzle-orm/pg-core';

export const pets = pgTable('pets', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  species: text('species').notNull(),
  age: integer('age'),
  userId: text('user_id').notNull(),
  adopted: boolean('adopted').default(false)
});
```

## Adding RLS Policies

Add user_id column and RLS policies that depend on the pets_table from the previous lesson:

```bash
pgpm add pets_rls --requires pets_table
```

Edit `deploy/pets_rls.sql`:

```sql
-- Deploy pets_rls
-- requires: pets_table

-- Add user_id column
ALTER TABLE pets
  ADD COLUMN user_id TEXT NOT NULL;

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

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

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

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

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

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

## Testing RLS with Drizzle

Now we'll test the RLS policies we added via `pgpm add pets_rls`. Since the table and policies are managed by pgpm migrations, our test setup is much simpler—we just need to seed test data.

Create `__tests__/drizzle-rls.test.ts`:

```typescript
import { drizzle } from 'drizzle-orm/node-postgres';
import { getConnections, PgTestClient } from 'drizzle-orm-test';
import { pets } from '../src/schema';
import { eq } from 'drizzle-orm';

const user1 = '11111111-1111-1111-1111-111111111111';
const user2 = '22222222-2222-2222-2222-222222222222';

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

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

  // Seed test data as superuser (bypasses RLS)
  await pg.query(`
    INSERT INTO pets (name, species, age, user_id)
    VALUES ('User 1 Pet', 'dog', 3, '${user1}'),
           ('User 2 Pet', 'cat', 2, '${user2}');
  `);
});

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

it('anonymous users can view all pets', async () => {
  // Set context on db client
  db.setContext({ role: 'anonymous' });

  // Use standard Drizzle pattern
  const drizzleDb = drizzle(db.client);

  const result = await drizzleDb.select().from(pets);
  expect(result).toHaveLength(2);
});

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

  await expect(
    drizzleDb.insert(pets).values({
      name: 'Hacker Pet',
      species: 'cat',
      userId: user1
    })
  ).rejects.toThrow();
});
```

**Key pattern:** We use `pg.beforeEach()/afterEach()` for transaction isolation (keeping tests independent), but `db.setContext()` and `drizzle(db.client)` for RLS testing. This combination ensures:
- Data seeded by `pg` (superuser) bypasses RLS and is visible to all tests
- Transaction rollbacks via `pg.afterEach()` keep each test isolated
- RLS policies are enforced when using `db` with `setContext()`

## Using Watch Mode for Rapid Testing

For faster feedback as you write tests, 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 automatically re-runs tests when you save changes to your TypeScript files. This gives you instant end-to-end feedback as you develop, helping you catch issues immediately and iterate quickly on your RLS policies and test cases.

## Testing Authenticated User Access

Add tests for authenticated users managing their own pets:

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

  const drizzleDb = drizzle(db.client);

  await drizzleDb.insert(pets).values({
    name: 'My New Pet',
    species: 'bird',
    age: 1,
    userId: user1
  });

  const result = await drizzleDb
    .select()
    .from(pets)
    .where(eq(pets.name, 'My New Pet'));

  expect(result).toHaveLength(1);
  expect(result[0].userId).toBe(user1);
});

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

  const drizzleDb = drizzle(db.client);

  await expect(
    drizzleDb.insert(pets).values({
      name: 'Not My Pet',
      species: 'fish',
      userId: user2  // Trying to insert for user2
    })
  ).rejects.toThrow();
});
```

## Testing Update and Delete Operations

Test that users can only modify their own pets:

```typescript
it('users can update their own pets', async () => {
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': user1
  });

  const drizzleDb = drizzle(db.client);

  // Update user1's pet
  await drizzleDb
    .update(pets)
    .set({ adopted: true })
    .where(eq(pets.userId, user1));

  const result = await drizzleDb
    .select()
    .from(pets)
    .where(eq(pets.userId, user1));

  expect(result[0].adopted).toBe(true);
});

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

  const drizzleDb = drizzle(db.client);

  // Try to update user2's pet - should affect 0 rows
  const result = await drizzleDb
    .update(pets)
    .set({ adopted: true })
    .where(eq(pets.userId, user2));

  // Update succeeds but affects 0 rows (RLS prevents access)
  // Query user2's pet to verify it wasn't changed
  const user2Pets = await drizzleDb
    .select()
    .from(pets)
    .where(eq(pets.userId, user2));

  expect(user2Pets[0].adopted).toBe(false);
});

it('users can delete their own pets', async () => {
  db.setContext({
    role: 'authenticated',
    'jwt.claims.user_id': user1
  });

  const drizzleDb = drizzle(db.client);

  await drizzleDb
    .delete(pets)
    .where(eq(pets.userId, user1));

  const result = await drizzleDb
    .select()
    .from(pets)
    .where(eq(pets.userId, user1));

  expect(result).toHaveLength(0);
});
```

Run the tests:

```bash
pnpm test
```

## Context Switching Between Tests

The power of drizzle-orm-test is seamless context switching. Each test can authenticate as a different user:

```typescript
describe('as user 1', () => {
  beforeEach(async () => {
    await pg.beforeEach();
    db.setContext({
      role: 'authenticated',
      'jwt.claims.user_id': user1
    });
  });

  afterEach(() => pg.afterEach());

  it('can only see own pets', async () => {
    const drizzleDb = drizzle(db.client);

    const result = await drizzleDb
      .select()
      .from(pets)
      .where(eq(pets.userId, user1));

    expect(result.every(p => p.userId === user1)).toBe(true);
  });
});

describe('as user 2', () => {
  beforeEach(async () => {
    await pg.beforeEach();
    db.setContext({
      role: 'authenticated',
      'jwt.claims.user_id': user2
    });
  });

  afterEach(() => pg.afterEach());

  it('can only see own pets', async () => {
    const drizzleDb = drizzle(db.client);

    const result = await drizzleDb
      .select()
      .from(pets)
      .where(eq(pets.userId, user2));

    expect(result.every(p => p.userId === user2)).toBe(true);
  });
});
```

## Real-World Example

Want to see Drizzle tests running in production CI? Check out the [drizzle-test-suite](https://github.com/constructive-io/drizzle-test-suite) repository—a comprehensive example with working GitHub Actions workflows.

## Key Takeaways

- **pgpm migrations manage schema** - Use `pgpm add` for tables and policies instead of inline SQL
- **`pg` client** - Use for superuser operations: seeding data and transaction isolation with `beforeEach()/afterEach()`
- **`db` client** - Use for RLS testing with `setContext()` to switch roles and users
- **setContext()** sets authentication context for all subsequent Drizzle queries
- **Automatic context management** - drizzle-orm-test applies context before each query
- **Standard Drizzle API** - no special RLS methods needed
- **Type-safe RLS testing** - use Drizzle's query builder for everything
- **Transaction isolation via `pg.beforeEach()/afterEach()`** ensures tests are independent

## What's Next

You've learned how to test RLS policies with Drizzle ORM using automatic context management. The pattern is simple: set context with `setContext()`, then use standard Drizzle queries. The RLS policies are enforced automatically.

For advanced scenarios like multi-connection testing, cross-user visibility, and complex policies, see the [drizzle-orm-test documentation](https://www.npmjs.com/package/drizzle-orm-test).
