Seeding data

Seed data is the sample content your app needs to be usable in development: a few users, some rows in each table, the lookup values your UI expects. Keeping it in a file means every developer, CI run and preview environment starts from the same known state.

The seed file

The Supabase CLI runs supabase/seed.sql after applying your migrations, every time you run supabase db reset locally. Seeds are for data, not schema: tables belong in migrations.

-- supabase/seed.sql
insert into public.rooms (code, name, nightly_rate) values
  ('A1', 'Garden double', 120),
  ('A2', 'Garden twin', 110),
  ('B1', 'Loft suite', 210);

-- A price for every room for the next 90 nights, with a weekend surcharge
insert into public.room_prices (room_code, night, price)
select
  r.code,
  d::date,
  r.nightly_rate + case when extract(isodow from d) in (5, 6) then 30 else 0 end
from public.rooms r
cross join generate_series(current_date, current_date + 89, interval '1 day') as d;

generate_series is the quickest way to produce volume for testing queries and pagination.

To split seeds across files, list them in config.toml. Files run in the order given, and globs run in alphabetical order:

# supabase/config.toml
[db.seed]
enabled = true
sql_paths = ['./seeds/01_rooms.sql', './seeds/*.sql']

Then rebuild:

npx supabase db reset

Seeding users

Rows that reference auth.users need users to exist first. You can insert into auth.users directly in a local seed, but the Auth schema is owned by the Auth service and its columns change between versions, so a script that goes through the Auth admin API holds up better:

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

const admin = createClient(process.env.SUPABASE_URL!, process.env.SUPABASE_SERVICE_ROLE_KEY!, {
  auth: { persistSession: false, autoRefreshToken: false },
})

const people = [
  { email: '[email protected]', password: 'alice-password-123', name: 'Alice' },
  { email: '[email protected]', password: 'bob-password-123', name: 'Bob' },
]

for (const p of people) {
  const { data, error } = await admin.auth.admin.createUser({
    email: p.email,
    password: p.password,
    email_confirm: true,
    user_metadata: { name: p.name },
  })
  if (error) {
    console.error(p.email, error.message)
    continue
  }

  // Rows that belong to a user can be added once the user exists.
  await admin.from('bookings').insert([
    { guest_id: data.user.id, room: 'A1', starts_at: '2026-12-01T15:00:00Z', nights: 2 },
    { guest_id: data.user.id, room: 'B1', starts_at: '2027-01-10T15:00:00Z', nights: 4 },
  ])
  console.log('seeded', p.email)
}

Run it against the local stack with the values from npx supabase status:

SUPABASE_URL=http://127.0.0.1:54321 \
SUPABASE_SERVICE_ROLE_KEY=<local service_role key> \
npx tsx scripts/seed-users.ts

If you want the same user ids on every run, pass id to createUser. Use an address domain such as example.test so seed users can never receive real email.

Seeding Storage

Locally, buckets can be declared in config.toml and created with the CLI:

[storage.buckets.room-photos]
public = true
file_size_limit = "5MiB"
allowed_mime_types = ["image/png", "image/jpeg"]
objects_path = "./seeds/room-photos"

npx supabase seed buckets

Files in objects_path are uploaded into the bucket. On SupaHost, create buckets in Studio or with SQL in a migration, and upload files from a script with supabase.storage.from('room-photos').upload(). Keep files under 50 MB each.

Putting seed data on SupaHost

Production should never get development seed data. For staging or demo projects, and for branches, you have these options:

A branch created with Copy data, users and files turned off keeps your schema and bucket definitions but empties user tables, Auth users and Storage objects, which makes it a clean target for a seed.

Make seeds safe to run twice, so a repeat doesn't fail or duplicate rows:

insert into public.rooms (code, name, nightly_rate)
values ('A1', 'Garden double', 120)
on conflict (code) do update set name = excluded.name, nightly_rate = excluded.nightly_rate;

If you load rows with explicit ids, move the sequence past them afterwards, or the next normal insert collides:

select setval(pg_get_serial_sequence('public.rooms', 'id'), (select max(id) from public.rooms));

See duplicate key value violates unique constraint after importing data.

Related

• Importing data (CSV, SQL dumps)
• Branching
• Testing your database