Seeding Supabase Test Databases

Dan Lynch

Dan Lynch

Nov 17, 2025

lesson header image

Previously: In Testing RLS Policies in Supabase, we tested RLS policies with authenticated users. Now let's explore efficient strategies for seeding Supabase test databases with realistic data.

Testing with realistic data reveals edge cases that empty tables hide. Manually inserting data in every test is tedious and error-prone. You need flexible, maintainable ways to populate your Supabase test database that work with RLS policies, auth.users, and Supabase system tables.

In this lesson, you'll learn how to seed Supabase test databases using supabase-test's direct load methods: loadJson(), loadSql(), loadCsv(), and the insertUser() helper.

Prerequisites

See Prerequisites. Requires: Complete Testing RLS Policies in Supabase.

Table of Contents

Why Seeding Matters in Supabase

Testing without proper seeding leads to:

  • Verbose tests cluttered with INSERT statements
  • Repetitive auth.users creation in every test
  • Unrealistic data that misses edge cases

supabase-test provides insertUser(), loadJson(), loadSql(), and loadCsv() for clean, maintainable test data.

Seeding auth.users with insertUser()

Supabase requires users in auth.users for authenticated operations. The insertUser() helper makes this simple:

Create __tests__/user-seeding.test.ts:

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

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

let user1: any;
let user2: any;

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

  // Insert users into auth.users using pg client (superuser)
  user1 = await insertUser(pg, 'alice@example.com', '550e8400-e29b-41d4-a716-446655440001');
  user2 = await insertUser(pg, 'bob@example.com', '550e8400-e29b-41d4-a716-446655440002');
});

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

it('users exist in auth.users', async () => {
  const result = await pg.query('SELECT id, email FROM auth.users ORDER BY email');

  expect(result.rows).toHaveLength(2);
  expect(result.rows[0].email).toBe('alice@example.com');
  expect(result.rows[1].email).toBe('bob@example.com');
});

it('authenticated user can insert their own pet', async () => {
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user1.id
  });

  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.owner_id).toBe(user1.id);
});

Key Features:

  • insertUser(pg, email, id?) - Creates users in auth.users
  • Use pg client - Superuser client bypasses RLS for user creation
  • Returns user object - Contains id, email, and other user properties

Run the test:

pnpm test:watch

Seeding with loadJson()

The loadJson() method is perfect for inline test data. It's simple, readable, and respects RLS policies.

Create __tests__/json-seeding.test.ts:

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

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

let user1: any;
let user2: any;

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

  // Create users first
  user1 = await insertUser(pg, 'alice@example.com', '550e8400-e29b-41d4-a716-446655440001');
  user2 = await insertUser(pg, 'bob@example.com', '550e8400-e29b-41d4-a716-446655440002');

  // Seed test data using loadJson
  await pg.loadJson({
    'pets.pets': [
      {
        id: '11111111-1111-1111-1111-111111111111',
        name: 'Buddy',
        breed: 'Labrador',
        owner_id: user1.id
      },
      {
        id: '22222222-2222-2222-2222-222222222222',
        name: 'Whiskers',
        breed: 'Tabby',
        owner_id: user1.id
      },
      {
        id: '33333333-3333-3333-3333-333333333333',
        name: 'Tweety',
        breed: 'Canary',
        owner_id: user2.id
      }
    ]
  });
});

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

it('has loaded all pets', async () => {
  // Use authenticated context to query
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user1.id
  });

  const result = await db.query('SELECT * FROM pets.pets WHERE owner_id = $1 ORDER BY name', [user1.id]);
  expect(result.rows).toHaveLength(2);
  expect(result.rows[0].name).toBe('Buddy');
});

it('users only see their own pets', async () => {
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user1.id
  });

  const result = await db.query(`
    SELECT name FROM pets.pets
    WHERE owner_id = $1
    ORDER BY name
  `, [user1.id]);

  expect(result.rows).toHaveLength(2);
  expect(result.rows.map(r => r.name)).toEqual(['Buddy', 'Whiskers']);
});

Key Features:

  • Schema-qualified tables - Use 'pets.pets' to specify the schema
  • UUID support - Can specify explicit IDs for referential integrity
  • RLS-aware - Respects RLS policies (use pg for setup, db for testing)
  • Clean syntax - JSON objects map directly to table rows

Seeding with loadSql()

For larger datasets or when you want to version-control fixtures separately, SQL files work well.

Create __tests__/fixtures/pets-fixture.sql:

-- Assumes users already exist in auth.users
INSERT INTO pets.pets (id, name, breed, owner_id) VALUES
  ('44444444-4444-4444-4444-444444444444', 'Max', 'Golden Retriever', '550e8400-e29b-41d4-a716-446655440001'),
  ('55555555-5555-5555-5555-555555555555', 'Luna', 'Persian', '550e8400-e29b-41d4-a716-446655440001'),
  ('66666666-6666-6666-6666-666666666666', 'Charlie', 'Beagle', '550e8400-e29b-41d4-a716-446655440002'),
  ('77777777-7777-7777-7777-777777777777', 'Nemo', 'Goldfish', '550e8400-e29b-41d4-a716-446655440002'),
  ('88888888-8888-8888-8888-888888888888', 'Bella', 'Siamese', '550e8400-e29b-41d4-a716-446655440003');

Create __tests__/sql-seeding.test.ts:

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

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

let user1: any;
let user2: any;

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

  // Create users
  user1 = await insertUser(pg, 'alice@example.com', '550e8400-e29b-41d4-a716-446655440001');
  user2 = await insertUser(pg, 'bob@example.com', '550e8400-e29b-41d4-a716-446655440002');
  await insertUser(pg, 'charlie@example.com', '550e8400-e29b-41d4-a716-446655440003');

  // Load SQL fixture
  await pg.loadSql([
    path.join(__dirname, 'fixtures/pets-fixture.sql')
  ]);
});

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

it('loads fixtures from SQL file', async () => {
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user1.id
  });

  const result = await db.query('SELECT * FROM pets.pets WHERE owner_id = $1 ORDER BY name', [user1.id]);
  expect(result.rows).toHaveLength(2);
});

it('supports complex queries on seeded data', async () => {
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user1.id
  });

  const result = await db.query(`
    SELECT breed, COUNT(*) as count
    FROM pets.pets
    WHERE owner_id = $1
    GROUP BY breed
    ORDER BY breed
  `, [user1.id]);

  expect(result.rows).toHaveLength(2);
  expect(result.rows[0].breed).toBe('Golden Retriever');
  expect(result.rows[1].breed).toBe('Persian');
});

Seeding with loadCsv()

For very large datasets or data exported from spreadsheets, CSV is ideal.

Create __tests__/fixtures/pets.csv:

id,name,breed,owner_id
99999999-9999-9999-9999-999999999999,Rocky,Bulldog,550e8400-e29b-41d4-a716-446655440001
aaaaaaaa-aaaa-aaaa-aaaa-000000000001,Shadow,Labrador,550e8400-e29b-41d4-a716-446655440001
bbbbbbbb-bbbb-bbbb-bbbb-000000000001,Sunny,Parakeet,550e8400-e29b-41d4-a716-446655440002
cccccccc-cccc-cccc-cccc-000000000001,Bubbles,Betta,550e8400-e29b-41d4-a716-446655440003

Create __tests__/csv-seeding.test.ts:

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

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

let user1: any;

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

  // Create users
  user1 = await insertUser(pg, 'alice@example.com', '550e8400-e29b-41d4-a716-446655440001');
  await insertUser(pg, 'bob@example.com', '550e8400-e29b-41d4-a716-446655440002');
  await insertUser(pg, 'charlie@example.com', '550e8400-e29b-41d4-a716-446655440003');

  // Load CSV data
  await pg.loadCsv({
    'pets.pets': path.join(__dirname, 'fixtures/pets.csv')
  });
});

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

it('loads CSV data correctly', async () => {
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user1.id
  });

  const result = await db.query('SELECT * FROM pets.pets WHERE owner_id = $1 ORDER BY name', [user1.id]);
  expect(result.rows).toHaveLength(2);
  expect(result.rows[0].name).toBe('Rocky');
});

it('CSV data works with RLS policies', async () => {
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user1.id
  });

  const result = await db.query(`
    SELECT name FROM pets.pets
    WHERE owner_id = $1
    ORDER BY name
  `, [user1.id]);

  expect(result.rows).toHaveLength(2);
  expect(result.rows.map(r => r.name)).toEqual(['Rocky', 'Shadow']);
});

Important CSV Note: loadCsv() uses Postgres's COPY command, which bypasses RLS policies during load. Always use pg (superuser) to load CSV data, not db.

Combining Seeding Strategies

You can mix seeding methods in the same test to leverage each approach's strengths.

Create __tests__/fixtures/users.csv:

id,email
550e8400-e29b-41d4-a716-446655440001,alice@example.com
550e8400-e29b-41d4-a716-446655440002,bob@example.com

Create the test:

import path from 'path';
import {
  getConnections,
  PgTestClient
} from 'supabase-test';

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

const user1Id = '550e8400-e29b-41d4-a716-446655440001';
const user2Id = '550e8400-e29b-41d4-a716-446655440002';

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

  // 1. Load users from CSV
  await pg.loadCsv({
    'auth.users': path.join(__dirname, 'fixtures/users.csv')
  });

  // 2. Load base data from SQL fixture
  await pg.loadSql([
    path.join(__dirname, 'fixtures/pets-fixture.sql')
  ]);

  // 3. Add test-specific data with JSON
  await pg.loadJson({
    'pets.pets': [
      {
        name: 'Test Pet',
        breed: 'Hamster',
        owner_id: user1Id
      }
    ]
  });
});

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

it('combines multiple seeding strategies', async () => {
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user1Id
  });

  const result = await db.query(`
    SELECT * FROM pets.pets
    WHERE name = 'Test Pet' AND owner_id = $1
  `, [user1Id]);

  expect(result.rows).toHaveLength(1);
  expect(result.rows[0].breed).toBe('Hamster');
});

Per-Test Seeding

Sometimes different tests need different data. Seed in beforeEach() instead of beforeAll():

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

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

let user1: any;

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

  // Create user once
  user1 = await insertUser(pg, 'alice@example.com', '550e8400-e29b-41d4-a716-446655440001');
});

afterAll(async () => await teardown());

beforeEach(async () => {
  await db.beforeEach();

  // Seed fresh data for each test
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user1.id
  });

  await db.loadJson({
    'pets.pets': [
      { name: 'Test Dog', breed: 'Poodle', owner_id: user1.id },
      { name: 'Test Cat', breed: 'Maine Coon', owner_id: user1.id }
    ]
  });
});

afterEach(async () => await db.afterEach());

it('scenario 1 - can modify seeded data', async () => {
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user1.id
  });

  await db.query(`
    UPDATE pets.pets SET breed = 'Poodle Mix' WHERE name = 'Test Dog' AND owner_id = $1
  `, [user1.id]);

  const result = await db.query(`
    SELECT breed FROM pets.pets WHERE name = 'Test Dog' AND owner_id = $1
  `, [user1.id]);

  expect(result.rows[0].breed).toBe('Poodle Mix');
});

it('scenario 2 - starts with clean data', async () => {
  // Previous test's UPDATE was rolled back
  db.setContext({
    role: 'authenticated',
    'request.jwt.claim.sub': user1.id
  });

  const result = await db.query(`
    SELECT breed FROM pets.pets WHERE name = 'Test Dog' AND owner_id = $1
  `, [user1.id]);

  expect(result.rows[0].breed).toBe('Poodle'); // Back to original
});

Each test gets fresh data thanks to transaction rollback combined with per-test seeding.

Key Takeaways

  • insertUser() - Essential helper for creating users in auth.users
  • loadJson() - Perfect for inline test data, respects RLS policies
  • loadSql() - Ideal for large fixtures and complex SQL operations
  • loadCsv() - Fast for huge datasets, but bypasses RLS (use pg client)
  • Transaction isolation - Changes are rolled back between tests automatically

What's Next

You've mastered seeding strategies for Supabase test databases. Next, you'll learn how to automate these tests with GitHub Actions, running your full test suite on every commit and pull request.

Previous LessonNext Lesson
Supabase Testing for the Modern Developer

1:00

Supabase Testing for the Modern Developer

Supabase

Testing

Setting Up Local Supabase Database Testing

8:00

Setting Up Local Supabase Database Testing

Supabase

Testing

Writing Your First Supabase Test in TypeScript

8:00

Writing Your First Supabase Test in TypeScript

Supabase

Testing

Testing Row-Level Security Policies in Supabase

10:00

Testing Row-Level Security Policies in Supabase

Supabase

Testing

Seeding Supabase Test Databases

12:00

Seeding Supabase Test Databases

Supabase

Testing

Running Supabase Tests in GitHub Actions

8:00

Running Supabase Tests in GitHub Actions

Supabase

Testing

CI/CD

Troubleshooting Supabase Test

6:00

Troubleshooting Supabase Test

Supabase

Testing