Home

Seeding your database

Populate your database with initial data for reproducible environments across local and testing.

When you run supabase init we create an empty seed.sql file in the root of the supabase folder. You can use this to populate your database with seed data.

What is seed data?#

Seeding is the process of populating a database with initial data, typically used to provide sample or default records for testing and development purposes. You can use this to create "reproducible environments" for local development, staging, and production.

Using the seed file#

The seed.sql file is run every time you run supabase start or supabase db reset. Seeding is done after all the database migrations have been run. As a general rule, you should not add schema statements to your seed file, only data.

You can add any SQL statements to this file. For example:

supabase/seed.sql

_10
insert into countries
_10
(name, code)
_10
values
_10
('United States', 'US'),
_10
('Canada', 'CA'),
_10
('Mexico', 'MX');

Generating seed data#

You can generate seed data for local development using Snaplet.

Let's generate some seed data for a typical blog application, with the following schema:

An example schema

We can use the Snaplet configuration file to define the values you want to generate. For example

  • A Post with the title "There is a lot of snow around here!"
  • The Post.createdBy user with an email address ending in "@acme.org"
  • Three Post.comments from three different users.
snaplet.config.ts

_16
import { defineConfig } from 'snaplet'
_16
import { copycat } from '@snaplet/copycat'
_16
_16
export default defineConfig({
_16
generate: {
_16
async run(snaplet) {
_16
await snaplet.posts({
_16
title: "There is a lot of snow around here!"
_16
userByCreatedBy: {
_16
email: copycat.email(seed, { domain: 'acme.org' })
_16
},
_16
comments: (x) => x(3)
_16
})
_16
}
_16
}
_16
})

Running npx snaplet generate --sql > supabase/seed.sql would generate these relevant SQL statements inside our supabase/seed.sql file:


_18
-- The `Post.createdBy` user with an email address ending in `"@acme.org"`
_18
INSERT INTO "User" (name, email) VALUES ("John Snow", "snow@acme.org")
_18
_18
--- A `Post` with the title `"There is a lot of snow around here!"`
_18
INSERT INTO "Post" (title, content, createdBy) VALUES (
_18
"There is a lot of snow around here!",
_18
"Lorem ipsum dolar",
_18
1)
_18
_18
--- Three `Post.comments` from three different users.
_18
INSERT INTO "User" (name, email) VALUES ("Stephanie Shadow", "shadow@domain.com")
_18
INSERT INTO "Comment" (text, userId, postId) VALUES ("I love cheese", 2, 1)
_18
_18
INSERT INTO "User" (name, email) VALUES ("John Rambo", "rambo@trymore.dev")
_18
INSERT INTO "Comment" (text, userId, postId) VALUES ("Lorem ipsum dolar sit", 3, 1)
_18
_18
INSERT INTO "User" (name, email) VALUES ("Steven Plank", "s@plank.org")
_18
INSERT INTO "Comment" (text, userId, postId) VALUES ("Actually, that's not correct...", 4, 1)

For more information, check out Snaplet's "generate" documentation

© Supabase Inc—ContributingAuthor StyleguideOpen SourceSupaSquad