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 [Writing Your First Supabase Test in TypeScript](./writing-your-first-supabase-test-in-typescript), we created a pets table and wrote basic tests. Now let's add Row-Level Security policies and test them.

Row-Level Security (RLS) is the foundation of Supabase's security model. Testing RLS policies ensures your users can only access their own data. With [`supabase-test`](https://www.npmjs.com/package/supabase-test), you can simulate different users and roles to verify your security policies work correctly.

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

## Prerequisites

See [Prerequisites](/learn/quickstart/prerequisites). Requires: Complete [Writing Your First Supabase Test in TypeScript](./writing-your-first-supabase-test-in-typescript).

## Why Test RLS in Supabase?

Supabase uses RLS to secure multi-tenant applications. Users should only access their own data, and anonymous users should have limited permissions. Without testing, you risk:

- Data leaks between users
- Overly restrictive policies blocking legitimate access
- Security vulnerabilities in production

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

## Adding RLS to to your Supabase schema

Add RLS policies to your existing pets table:

```bash
pgpm add rls_pets --requires pets_app
```

Edit `deploy/rls_pets.sql`:

```sql
-- Deploy: rls_pets
-- requires: pets_app

-- Add owner_id column to track pet owners
ALTER TABLE pets.pets
  ADD COLUMN owner_id UUID;

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

-- Grant schema usage to Supabase roles
GRANT USAGE ON SCHEMA pets TO anon;
GRANT USAGE ON SCHEMA pets TO authenticated;

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

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

-- Create RLS policies for users table
-- Users can view their own data
CREATE POLICY "Users can view own data" ON pets.pets
    FOR SELECT USING (auth.uid() = owner_id);

-- Users can update their own data
CREATE POLICY "Users can update own data" ON pets.pets
    FOR UPDATE USING (auth.uid() = owner_id);

-- Users can insert their own data
CREATE POLICY "Users can insert own data" ON pets.pets
    FOR INSERT WITH CHECK (auth.uid() = owner_id);

-- Users can delete their own data
CREATE POLICY "Users can delete own data" ON pets.pets
    FOR DELETE USING (auth.uid() = owner_id);

-- Grant permissions to anon users
GRANT USAGE ON SCHEMA pets TO anon;
GRANT ALL ON pets.pets TO anon;

-- Grant permissions to authenticated users
GRANT USAGE ON SCHEMA pets TO authenticated;
GRANT ALL ON pets.pets TO authenticated;

-- Grant permissions to service role (for admin operations)
GRANT USAGE ON SCHEMA pets TO service_role;
GRANT ALL ON pets.pets TO service_role;

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_users_owner_id ON pets.pets(owner_id);
```

## Understanding Supabase Test Clients

`supabase-test` provides two database clients, just like `pgsql-test`:

- **`pg`** - Superuser client for setup/teardown operations
- **`db`** - User client for testing with Supabase roles (anon, authenticated)

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

## Testing Supabase RLS Policies

Create `__tests__/rls.test.ts`:

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

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

let user1: any;
let user2: any;
let user3: any;

const users = [
  {
    id: '550e8400-e29b-41d4-a716-446655440001',
    email: 'tutorial1@example.com'
  },
  {
    id: '550e8400-e29b-41d4-a716-446655440002',
    email: 'tutorial2@example.com'
  },
  {
    id: '550e8400-e29b-41d4-a716-446655440003',
    email: 'tutorial3@example.com'
  }
];

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

  // insert users using pg PgTestClient to bypass RLS (required to insert into supabase auth.users)
  user1 = await insertUser(pg, users[0].email, users[0].id);
  user2 = await insertUser(pg, users[1].email, users[1].id);
  user3 = await insertUser(pg, users[2].email, users[2].id);
});

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


it('should allow user to create their own user record', async () => {
  // set context to simulate authenticated user
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user1.id
  });

  // user can create their own pet
  const pet = await db.one(
    `INSERT INTO pets.pets (name, breed, owner_id) 
       VALUES ($1, $2, $3) 
       RETURNING id, name, breed, owner_id`,
    ['Fido', 'Labrador', user1.id]
  );

  expect(pet.name).toBe('Fido');
  expect(pet.breed).toBe('Labrador');
  expect(pet.owner_id).toBe(user1.id);
});

it('should prevent user1 from updating user2\'s record', async () => {
  // user2 creates a pet
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user2.id
  });

  const pet = await db.one(
    `INSERT INTO pets.pets (name, breed, owner_id) 
       VALUES ($1, $2, $3) 
       RETURNING id, name, breed, owner_id`,
    ['Buddy', 'Golden Retriever', user2.id]
  );

  expect(pet.owner_id).toBe(user2.id);

  // user1 tries to update user2's pet - should throw
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user1.id
  });

  await expect(
    db.one(
      `UPDATE pets.pets 
         SET name = $1 
         WHERE id = $2 
         RETURNING id, name, breed, owner_id`,
      ['Hacked Name', pet.id]
    )
  ).rejects.toThrow();
});

it('should allow users to see only their own data in list queries', async () => {
  // set context to user1
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user1.id
  });

  // create multiple users as admin
  await db.one(
    `INSERT INTO pets.pets (name, breed, owner_id) 
       VALUES ($1, $2, $3) 
       RETURNING id`,
    ['Fido', 'Labrador', user1.id]
  );

  // set context to user1
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user2.id
  });

  await db.one(
    `INSERT INTO pets.pets (name, breed, owner_id) 
       VALUES ($1, $2, $3) 
       RETURNING id`,
    ['Buddy', 'Golden Retriever', user2.id]
  );

  // set context to user1
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user3.id
  });

  await db.one(
    `INSERT INTO pets.pets (name, breed, owner_id) 
       VALUES ($1, $2, $3) 
       RETURNING id`,
    ['Rex', 'German Shepherd', user3.id]
  );

  // set context to user1
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user1.id
  });

  // user1 should only see their own record in a list query
  const allUsers = await db.many(
    `SELECT id, name, breed, owner_id FROM pets.pets ORDER BY name`
  );

  expect(allUsers.length).toBe(1);
  expect(allUsers[0].owner_id).toBe(user1.id);
  expect(allUsers[0].name).toBe('Fido');
  expect(allUsers[0].breed).toBe('Labrador');
});
```

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

Run the tests:

```bash
pnpm test
```

## Key Takeaways

- **`pg` client** - Use for superuser operations (setup/teardown)
- **`db` client** - Use for testing with Supabase roles (anon, authenticated)
- **setContext()** sets authentication context for the current transaction
- **Use `pgpm add`** to add RLS policies as proper database changes
- **Test isolation** ensures RLS context doesn't bleed between tests

## What's Next

You've added RLS policies to your Supabase table and tested basic user access scenarios. Next, you'll learn how to seed Supabase test databases with auth.users and realistic data using efficient loading strategies.
