Database Seeding for Self-Hosted Supabase: A Complete Guide

Every developer running self-hosted Supabase faces the same challenge: how do you populate your database with realistic test data that actually exercises your application's logic? Manually inserting a few rows in Studio works for demos, but it falls apart the moment you need to test pagination, search, or complex relational queries.

Database seeding solves this by creating reproducible, realistic datasets that you can reset and regenerate at will. For self-hosted deployments, getting seeding right is even more important since you don't have access to Supabase Cloud's branching features that handle some of this automatically.

Why Seeding Matters for Self-Hosted Supabase

When you're running your own Supabase instance, you're responsible for the entire development workflow. Seeding provides several critical benefits:

Reproducible development environments: Every team member gets the exact same test data after running supabase db reset. No more "it works on my machine" debugging sessions caused by data inconsistencies.

Meaningful testing: Unit tests that operate on an empty database tell you nothing about real-world performance. With proper seeding, you can test pagination, full-text search, and complex joins against realistic data volumes.

Faster onboarding: New developers can start working immediately with a fully populated database instead of spending hours manually creating test data.

Safe schema iteration: When you need to test database migrations against realistic data, seeding lets you validate changes without touching production.

The Basics: Understanding seed.sql

The Supabase CLI looks for a supabase/seed.sql file by default. This file executes after all migrations complete when you run supabase start (first time) or supabase db reset. Here's a minimal example:

-- supabase/seed.sql
INSERT INTO public.profiles (id, username, email, created_at)
VALUES 
  ('11111111-1111-1111-1111-111111111111', 'alice', '[email protected]', now()),
  ('22222222-2222-2222-2222-222222222222', 'bob', '[email protected]', now());

INSERT INTO public.posts (id, author_id, title, content, published_at)
VALUES 
  ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', '11111111-1111-1111-1111-111111111111', 
   'First Post', 'Hello from Alice!', now()),
  ('bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb', '22222222-2222-2222-2222-222222222222', 
   'Second Post', 'Hello from Bob!', now());

A critical best practice: only include INSERT statements in your seed file. Never add CREATE TABLE, ALTER TABLE, or other schema changes—those belong in your migrations. Your migrations define the structure; your seeds populate it.

Using Hardcoded UUIDs

Notice the explicit UUIDs above. This is intentional. Using hardcoded UUIDs for key test records ensures:

Tests can reliably reference specific records
Foreign key relationships are deterministic
Debugging is easier when IDs are predictable

For bulk data, you can let Postgres generate UUIDs automatically, but your "fixture" records should have stable, known identifiers.

Organizing Multiple Seed Files

As your project grows, a single seed.sql becomes unwieldy. Configure multiple seed files in your supabase/config.toml:

[db.seed]
sql_paths = [
  './seeds/00-reference-data.sql',
  './seeds/10-users.sql',
  './seeds/20-content.sql',
  './seeds/30-relationships.sql'
]

Files execute in the order listed. Glob patterns also work:

[db.seed]
sql_paths = ['./seeds/*.sql']

With globs, files execute alphabetically—hence the numeric prefixes. This ordering matters when you have foreign key constraints: users must exist before you can create their posts.

Transaction Boundaries

The CLI doesn't wrap all seed files in a single transaction. If you need atomic seeding (all-or-nothing), wrap each file's contents explicitly:

-- seeds/20-content.sql
BEGIN;

INSERT INTO public.posts (id, author_id, title, content)
VALUES (...);

INSERT INTO public.comments (id, post_id, author_id, body)
VALUES (...);

COMMIT;

This prevents partial seeding if something fails mid-file.

Generating Realistic Data with Faker.js

Handwritten seed data works for small datasets, but what about testing with thousands of records? That's where data generation libraries come in.

Here's a Node.js script that generates seed SQL using Faker:

// scripts/generate-seeds.ts
import { faker } from '@faker-js/faker';
import * as fs from 'fs';

// Set seed for deterministic output
faker.seed(42);

const USERS_COUNT = 100;
const POSTS_PER_USER = 10;

let sql = '-- Generated seed data\n\n';

// Generate users
const userIds: string[] = [];
sql += '-- Users\n';
for (let i = 0; i < USERS_COUNT; i++) {
  const id = faker.string.uuid();
  userIds.push(id);
  
  const username = faker.internet.username().toLowerCase();
  const email = faker.internet.email().toLowerCase();
  const bio = faker.person.bio().replace(/'/g, "''");
  const createdAt = faker.date.past({ years: 2 }).toISOString();
  
  sql += `INSERT INTO public.profiles (id, username, email, bio, created_at)
VALUES ('${id}', '${username}', '${email}', '${bio}', '${createdAt}');\n`;
}

// Generate posts
sql += '\n-- Posts\n';
for (const userId of userIds) {
  for (let i = 0; i < POSTS_PER_USER; i++) {
    const id = faker.string.uuid();
    const title = faker.lorem.sentence().replace(/'/g, "''");
    const content = faker.lorem.paragraphs(3).replace(/'/g, "''");
    const publishedAt = faker.date.recent({ days: 90 }).toISOString();
    
    sql += `INSERT INTO public.posts (id, author_id, title, content, published_at)
VALUES ('${id}', '${userId}', '${title}', '${content}', '${publishedAt}');\n`;
  }
}

fs.writeFileSync('supabase/seeds/50-generated.sql', sql);
console.log(`Generated ${USERS_COUNT} users with ${USERS_COUNT * POSTS_PER_USER} posts`);

Run it with:

npx tsx scripts/generate-seeds.ts
supabase db reset

The faker.seed(42) call ensures deterministic output—running the script twice produces identical data. This is crucial for reproducible test environments.

Seeding Auth Users

Here's a common gotcha: you can't safely seed auth.users with raw SQL inserts on production Supabase. The auth schema has triggers and constraints that expect users to be created through the proper channels.

For self-hosted instances, you have two options:

Option 1: Use the Admin API

Create a seeding script that uses Supabase's admin API:

// scripts/seed-auth-users.ts
import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
);

const testUsers = [
  { email: '[email protected]', password: 'testpass123' },
  { email: '[email protected]', password: 'testpass123' },
];

async function seedUsers() {
  for (const user of testUsers) {
    const { data, error } = await supabase.auth.admin.createUser({
      email: user.email,
      password: user.password,
      email_confirm: true,
    });
    
    if (error) {
      console.error(`Failed to create ${user.email}:`, error.message);
    } else {
      console.log(`Created user: ${data.user.email} (${data.user.id})`);
    }
  }
}

seedUsers();

Option 2: Direct SQL for Local Development Only

For local development where you control the entire stack, you can insert directly into auth.users. This approach is documented in the official Supabase seeding guide but should never be used against production databases:

-- ONLY for local development!
INSERT INTO auth.users (
  id,
  instance_id,
  email,
  encrypted_password,
  email_confirmed_at,
  created_at,
  updated_at,
  raw_app_meta_data,
  raw_user_meta_data,
  aud,
  role
)
VALUES (
  '11111111-1111-1111-1111-111111111111',
  '00000000-0000-0000-0000-000000000000',
  '[email protected]',
  crypt('password123', gen_salt('bf')),
  now(),
  now(),
  now(),
  '{"provider":"email","providers":["email"]}',
  '{}',
  'authenticated',
  'authenticated'
);

Seeding Self-Hosted Remote Databases

The Supabase CLI doesn't directly support seeding remote databases with the standard workflow. For self-hosted deployments, use the --db-url flag:

# Reset and seed (destructive - drops everything!)
supabase db reset --db-url "postgresql://postgres:password@your-server:5432/postgres"

# Or apply only missing migrations (no seed)
supabase db push --db-url "postgresql://postgres:password@your-server:5432/postgres"

The key difference:

db reset drops the database, re-applies all migrations, and runs seed files
db push only applies missing migrations, no seeding

For staging environments, you typically want db reset to get a clean slate with test data. For production, you never run seeds—use db push instead.

Connection Considerations

When connecting to your self-hosted instance for seeding, use the session pooler (port 5432) or direct connection, not the transaction pooler (port 6543). The transaction pooler returns connections after each transaction, which breaks multi-statement seed files that depend on session state.

Advanced: Using Supabase Community Seed

The supabase-community/seed project (originally Snaplet) analyzes your schema and generates type-safe seed scripts automatically. It's particularly useful for complex schemas with many relationships.

Install and initialize:

npx @snaplet/seed init

This creates a seed.config.ts and seed.ts. The generated client understands your foreign key relationships:

// seed.ts
import { createSeedClient } from '@snaplet/seed';

const seed = await createSeedClient();

// Clear existing data
await seed.$resetDatabase();

// Seed with automatic relationship handling
await seed.users((x) => x(10, {
  posts: (x) => x(5, {
    comments: (x) => x(3),
  }),
}));

// Process the seed script into SQL
await seed.$syncDatabase();

Generate the SQL:

npx tsx seed.ts > supabase/seed.sql

One note of caution: the supabase-community/seed project has had maintenance gaps since the Snaplet team joined Supabase in 2024. It still works well, but check the GitHub issues if you encounter problems with newer Postgres features.

Integrating Seeding with Your CI/CD Pipeline

For teams with CI/CD pipelines, seeding becomes part of your automated workflow. Here's a GitHub Actions example:

# .github/workflows/test.yml
name: Test

on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    
    steps:
      - uses: actions/checkout@v4
      
      - name: Setup Supabase CLI
        uses: supabase/setup-cli@v1
        
      - name: Start Supabase
        run: supabase start
        
      - name: Generate seed data
        run: npx tsx scripts/generate-seeds.ts
        
      - name: Reset database with seeds
        run: supabase db reset
        
      - name: Run tests
        run: npm test
        
      - name: Stop Supabase
        run: supabase stop

Each test run gets a fresh database with identical seed data, ensuring consistent test results across environments.

Best Practices Summary

Keep seeds separate from migrations: Seeds are data, migrations are schema. Never mix them.
Use deterministic generation: Set faker seeds for reproducible data across runs and team members.
Organize with numbered prefixes: Files like 00-reference.sql, 10-users.sql ensure correct execution order.
Wrap in transactions: Use BEGIN/COMMIT blocks for atomic seeding.
Hardcode fixture IDs: Known UUIDs make testing and debugging easier.
Never seed production: Use db push for production, db reset for development and staging.
Test your seeds: A seed file that fails breaks everyone's development environment.