Testing Row-Level Security with Drizzle ORM

Previously: In 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. Requires: Complete 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:

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:

pgpm add pets_rls --requires pets_table

Edit deploy/pets_rls.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:

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:

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

Then run tests in watch mode:

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:

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:

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:

pnpm test

Context Switching Between Tests

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

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