To seed data in Supabase, create a supabase/seed.sql file with INSERT statements and run supabase db reset to apply it. The seed file runs automatically after all migrations during a reset. Write idempotent seed scripts using INSERT ... ON CONFLICT DO NOTHING to avoid errors on re-runs. For larger datasets, use the COPY command or the Supabase JS client with bulk inserts. Seeding is essential for local development, testing, and demo environments.
Seeding Data in Supabase for Development and Testing
Data seeding populates your database with initial or sample data for development, testing, and demo purposes. Supabase uses a seed.sql file that runs automatically after migrations during supabase db reset. This tutorial covers creating seed files, handling foreign key relationships, writing idempotent scripts, and seeding larger datasets.
Prerequisites
- A Supabase project initialized with supabase init
- The Supabase CLI installed (brew install supabase/tap/supabase or npm install supabase)
- At least one migration file creating your tables
- Basic understanding of SQL INSERT statements
Step-by-step guide
Create the seed.sql file
Create the seed.sql file
The Supabase CLI looks for a file at supabase/seed.sql in your project. Create this file and add your INSERT statements. The seed file runs after all migration files have been applied during supabase db reset. This means all your tables, indexes, and RLS policies are in place before the seed data is inserted. The seed runs with superuser privileges, so RLS policies do not block the inserts.
1-- supabase/seed.sql2-- Seed data for local development34insert into public.categories (name, slug) values5 ('Technology', 'technology'),6 ('Design', 'design'),7 ('Business', 'business');Expected result: A supabase/seed.sql file exists in your project with INSERT statements for sample data.
Run the seed with supabase db reset
Run the seed with supabase db reset
The supabase db reset command drops the local database, re-applies all migrations in order, and then runs seed.sql. This gives you a clean database with known data every time. Use this during development when you want to start fresh. The reset command only affects the local Supabase instance started with supabase start — it does not touch your production database.
1# Reset local database and apply all migrations + seed2supabase db reset34# Output:5# Resetting local database...6# Applying migration 20240101000000_create_tables.sql...7# Applying migration 20240102000000_add_indexes.sql...8# Running seed.sql...9# Finished supabase db reset.Expected result: The local database is reset with all migrations applied and seed data inserted.
Write idempotent seed scripts
Write idempotent seed scripts
A seed script may be run multiple times (for example, when running supabase db seed without reset). To avoid duplicate key errors, use INSERT ... ON CONFLICT DO NOTHING or check for existing data before inserting. This makes your seed script safe to re-run without errors. For tables with auto-generated primary keys, you can also truncate the table before inserting.
1-- Idempotent seed: use ON CONFLICT DO NOTHING2insert into public.categories (id, name, slug) values3 (1, 'Technology', 'technology'),4 (2, 'Design', 'design'),5 (3, 'Business', 'business')6on conflict (id) do nothing;78-- Alternative: truncate and re-insert9truncate public.posts cascade;10insert into public.posts (title, category_id, published) values11 ('Getting Started with Supabase', 1, true),12 ('UI Design Patterns', 2, true),13 ('Startup Funding Guide', 3, false);Expected result: The seed script can be run multiple times without causing duplicate key errors.
Seed related tables with foreign key dependencies
Seed related tables with foreign key dependencies
When seeding tables with foreign key relationships, insert parent records before child records. If you use auto-generated IDs, you need to either hardcode IDs in the seed or use CTEs (Common Table Expressions) to capture the generated IDs and reference them in subsequent inserts. Hardcoding IDs is simpler for seeds but requires using the OVERRIDING SYSTEM VALUE clause for identity columns.
1-- Seed with hardcoded IDs for related tables2insert into public.categories (id, name, slug)3overriding system value4values5 (1, 'Technology', 'technology'),6 (2, 'Design', 'design')7on conflict (id) do nothing;89insert into public.posts (title, category_id, published) values10 ('Getting Started with Supabase', 1, true),11 ('Advanced PostgreSQL Tips', 1, true),12 ('Design System Best Practices', 2, true);1314-- Reset the sequence after hardcoded inserts15select setval(16 pg_get_serial_sequence('public.categories', 'id'),17 (select max(id) from public.categories)18);Expected result: Parent and child records are seeded correctly with valid foreign key references.
Seed data programmatically with the JS client
Seed data programmatically with the JS client
For more complex seeding scenarios, you can write a TypeScript seed script that uses the Supabase JS client. This is useful when you need to generate random data, seed auth users (which requires the admin API), or apply business logic during seeding. Use the service role key to bypass RLS when seeding from a script.
1// scripts/seed.ts2import { createClient } from '@supabase/supabase-js'34const supabase = createClient(5 'http://127.0.0.1:54321', // Local Supabase URL6 'your-local-service-role-key' // From supabase status7)89async function seed() {10 // Seed categories11 const { data: categories } = await supabase12 .from('categories')13 .upsert([14 { name: 'Technology', slug: 'technology' },15 { name: 'Design', slug: 'design' },16 ], { onConflict: 'slug' })17 .select()1819 console.log('Seeded categories:', categories?.length)2021 // Seed posts referencing categories22 const techId = categories?.find(c => c.slug === 'technology')?.id23 if (techId) {24 const { data: posts } = await supabase25 .from('posts')26 .insert([27 { title: 'Supabase Tutorial', category_id: techId, published: true },28 { title: 'PostgreSQL Tips', category_id: techId, published: true },29 ])30 .select()3132 console.log('Seeded posts:', posts?.length)33 }34}3536seed().catch(console.error)Expected result: Data is seeded programmatically using the Supabase JS client with service role access.
Complete working example
1-- supabase/seed.sql2-- Idempotent seed script for local development3-- Run with: supabase db reset (or supabase db seed)45-- ============================================6-- 1. Seed categories (parent table)7-- ============================================8insert into public.categories (id, name, slug, description)9overriding system value10values11 (1, 'Technology', 'technology', 'Software and engineering topics'),12 (2, 'Design', 'design', 'UI/UX and visual design'),13 (3, 'Business', 'business', 'Startups and entrepreneurship'),14 (4, 'Marketing', 'marketing', 'Growth and content marketing')15on conflict (id) do nothing;1617-- Reset the sequence18select setval(19 pg_get_serial_sequence('public.categories', 'id'),20 (select coalesce(max(id), 0) from public.categories)21);2223-- ============================================24-- 2. Seed posts (child table with FK to categories)25-- ============================================26insert into public.posts (title, slug, category_id, content, published)27values28 ('Getting Started with Supabase', 'getting-started-supabase', 1,29 'Learn how to set up your first Supabase project.', true),30 ('Advanced PostgreSQL Tips', 'advanced-postgresql-tips', 1,31 'Tips for optimizing your PostgreSQL queries.', true),32 ('Design System Best Practices', 'design-system-practices', 2,33 'How to build a scalable design system.', true),34 ('Startup Funding 101', 'startup-funding-101', 3,35 'A beginner guide to raising your first round.', false),36 ('SEO for SaaS', 'seo-for-saas', 4,37 'How to drive organic traffic to your SaaS product.', true)38on conflict (slug) do nothing;3940-- ============================================41-- 3. Seed tags (many-to-many)42-- ============================================43insert into public.tags (id, name)44overriding system value45values46 (1, 'supabase'), (2, 'postgresql'), (3, 'react'),47 (4, 'typescript'), (5, 'tailwind')48on conflict (id) do nothing;4950select setval(51 pg_get_serial_sequence('public.tags', 'id'),52 (select coalesce(max(id), 0) from public.tags)53);Common mistakes when seeding Data in Supabase
Why it's a problem: Running supabase db reset against the production database
How to avoid: supabase db reset only affects the local database. However, always double-check which database you are connected to. Never run destructive commands against your production project.
Why it's a problem: Inserting hardcoded IDs without resetting the sequence afterward
How to avoid: After inserting rows with hardcoded IDs into identity columns, reset the sequence with setval(). Otherwise, the next auto-generated ID may collide with an existing seed ID.
Why it's a problem: Seeding child rows before parent rows, causing foreign key violations
How to avoid: Always insert parent table records first, then child table records. In seed.sql, order your INSERT statements so parent tables come before tables that reference them.
Why it's a problem: Including real user data or production secrets in the seed file
How to avoid: The seed file is committed to version control. Use only fake sample data for development. Never include real email addresses, API keys, or production user data.
Best practices
- Use ON CONFLICT DO NOTHING to make seed scripts idempotent and safe to re-run
- Order INSERT statements so parent table records are created before child table records
- Reset auto-increment sequences after inserting hardcoded IDs with OVERRIDING SYSTEM VALUE
- Keep seed data realistic but fake — use descriptive sample content that helps with development
- Commit supabase/seed.sql to version control so all team members have the same development data
- Use TRUNCATE ... CASCADE before bulk re-inserts when you want to start fresh each time
- Separate seed data by environment — use seed.sql for local dev and a different script for staging
Still stuck?
Copy one of these prompts to get a personalized, step-by-step explanation.
I have a Supabase project with categories, posts, and tags tables (posts has a FK to categories, and a join table connects posts to tags). Write me an idempotent seed.sql file that populates all three tables with realistic sample data and handles sequences correctly.
Create a supabase/seed.sql file for a blog application with categories, posts, and tags tables. Use OVERRIDING SYSTEM VALUE for hardcoded IDs, ON CONFLICT DO NOTHING for idempotency, and reset sequences after seeding. Include 4 categories, 8 posts, and 5 tags with realistic content.
Frequently asked questions
Where should the seed.sql file be located?
The seed file must be at supabase/seed.sql in your project root. The Supabase CLI automatically looks for this file when running supabase db reset or supabase db seed.
Does supabase db seed run with RLS enabled?
No, the seed.sql file runs with superuser privileges, bypassing all RLS policies. This means your INSERT statements will succeed regardless of any RLS policies on the tables.
Can I have multiple seed files?
The CLI only looks for supabase/seed.sql. If you need multiple seed files, use SQL includes or organize your seeds in the single file with comments. Alternatively, write programmatic seed scripts that you run manually.
How do I seed auth.users for testing?
You cannot directly INSERT into auth.users from seed.sql. Instead, write a programmatic seed script using the admin API: supabase.auth.admin.createUser({ email, password, email_confirm: true }). Use the local service role key.
Does seeding work on the production database?
supabase db reset and supabase db seed only run against the local database started with supabase start. To seed production data, use supabase db push for migrations and a separate script for data. Be extremely careful with production data operations.
What is the difference between supabase db reset and supabase db seed?
supabase db reset drops the entire local database, re-applies all migrations, and then runs seed.sql. supabase db seed only runs the seed.sql file on the existing database without dropping or re-migrating. Use reset for a clean start and seed to add data to an existing schema.
Can RapidDev help set up a development workflow with Supabase seeding and migrations?
Yes, RapidDev can configure your local development environment with Supabase CLI, create migration files, write comprehensive seed data, and establish a workflow for your team to develop against consistent sample data.
Talk to an Expert
Our team has built 600+ apps. Get personalized help with your project.
Book a free consultation