Seed your Supabase database with this simple pattern

Introduction

When working with Supabase, you may need to seed your database with initial data. This is a common task when setting up a new develper environment, or when you need to populate your database with predictable sample data for testing purposes. In this post, I'll show you a simple pattern for seeding your Supabase database using TypeScript.

Prerequisites

This post assumes you have a Supabase project set up and a basic understanding of TypeScript. If you're new to Supabase, you can follow the official documentation to get started.

Although my site is built with tRPC, Drizzle ORM and [Next.js]https://nextjs.org/, this pattern can be adapted for other frameworks, libraries, and databases.

Seeding the database with sample data using Faker

This example uses a library called Faker to generate fake data. Faker is a popular library for generating random data such as names, addresses, and phone numbers. You can install it using your package manager of choice:

1# note that faker is installed as a dev dependency
2pnpm install faker -D

Setting up the seed data

For this example, we will use a simplified Drizzle ORM Database schema with two tables - chefs and recipeTypes. Here's what the schema might look like:

1// schema.ts
2
3// ...other tables and imports above
4export const chefs = createTable("chefs", {
5  id: serial("id").primaryKey(),
6  name: varchar("name", { length: 256 }).notNull(),
7  email: varchar("email", { length: 256 }).notNull(),
8  phone: varchar("phone", { length: 256 }),
9  address: text("address"),
10});
11
12export const recipeTypes = createTable("project_type", {
13  id: serial("id").primaryKey(),
14  name: varchar("name", { length: 256 }).notNull(),
15  description: text("description"),
16});
17
18export const recipes = createTable("recipes", {
19  id: serial("id").primaryKey(),
20  name: varchar("name", { length: 256 }).notNull(),
21  description: text("description"),
22  recipeTypeId: integer("recipeTypeId").references(recipeTypes.id),
23  recipeChefId: integer("recipeChefId").references(chefs.id),
24});

I try to keep my seed data organized in a src/db/seed directory. Each file in this directory contains seed data for a specific table in the database. For example, if you have a chefs table and a recipeTypes table, you would create two files: chefs.ts and recipeTypes.ts.

Here's an example of what the directory structure might look like:

1src
2└── db
3    ├── seed
4    │   ├── chefs.ts
5    │   ├── recipes.ts
6    │   ├── recipeTypes.ts
7    │   └── seed.ts   // this is where we collate all seed data and insert into db
8    ├── db.ts          // exports the db instance from Drizzle
9    └── schema.ts      // database schema de  ition

Creating seed data

This script will stick data into the chefs, recipes, and recipeTypes tables. To keep things neatly organized, each table is represented by its own file. Each of these files exports a function that seeds the data for that table, which is of the type:

1type SeedFunction = () => Promise<string>;

The string returned by each SeedFunction's promise is a message that will be logged to the console when the seeding is complete. I use this to keep track of how many rows were inserted into the database.

For tables with predefined data

The simplest option is tables with predefined or hand-written data. For these, I more-or-less hand-write objects to stick into the database. Here's an example of how to the recipeTypes table:

1import type { SeedFunction } from "../seed";
2import { recipeTypes } from "../schema";
3import db from "../db";
4
5type RecipeTypeRow = {
6  name: string;
7  description: string;
8};
9
10// note the export: we will use this later
11export const RecipeTypes: SeedFunction = {
12  "Breakfast": { id: 1,  name: "Breakfast", description: "Breakfast recipes" },
13  "Lunch": { id: 2, name: "Lunch", description: "Lunch recipes" },
14  "Dinner": { id: 3, name: "Dinner", description: "Dinner recipes" },
15} as const;
16
17const seedRecipeTypes = async () => {
18  // convert the object to an array of values
19  const data = Object.values(RecipeTypes);
20
21  await db.from(recipeTypes).insert(data);
22
23  return `${data.length} Recipe types seeded successfully`;
24};

Some things to note in the above code:

We're creating an array of objects with predefined data for the recipeTypes table.
We're inserting the predefined data into the recipeTypes table using the db.from(recipeTypes).insert(data) method.
The return statement at the end of the function is a string, which will be logged to the console when seeding this table is complete.
the RecipeTypes object is exported so we can use it in other seeding functions.

Generating data with Faker

For tables that can use randomized data, a slightly different approach is used with the Faker.js library, which can generate many different types of randomized data.

Here's how to populate the Chefs table with a list of fake chefs:

1import faker from "faker";
2import { chefs } from "../schema";
3import db from "../db";
4import type { SeedFunction } from "../seed";
5
6type ChefRow = {
7  name: string;
8  email: string;
9  phone: string;
10  address: string;
11};
12
13const seedChefs: SeedFunction = async () => {
14  // generate random data for 10 people to stick into the chefs table
15  const data: ChefRow[] = Array.from({ length: 10 }, () => ({
16    name: faker.name.findName(),
17    email: faker.internet.email(),
18    phone: faker.phone.phoneNumber(),
19    address: faker.address.streetAddress(),
20  }));
21
22  await db.from(chefs).insert(data);
23
24  return `${data.length} Chefs seeded successfully`;
25};

If you have tables with relationships, you can seed related data by using the id of the parent table. For example, if you have a recipes table that has a foreign key to the recipeTypes table, you can seed the recipes table with the id of the recipeTypes table.

Here's an example of how you might seed the recipes table with related data:

1import { recipes } from "../schema";
2import db from "../db";
3import { RecipeTypes } from "./recipeTypes";
4import type { SeedFunction } from "../seed";
5
6type RecipeRow = {
7  name: string;
8  description: string;
9  recipeTypeId: number;
10};
11
12const seedRecipes: SeedFunction = async () => {
13  const data: RecipeRow[] = [
14    {
15      name: "Pancakes",
16      description: "Delicious pancakes",
17      // use the id from the RecipeTypes object we exported earlier
18      recipeTypeId: RecipeTypes.Breakfast.id,
19      // since it can be any chef, we'll just use the first one
20      recipeChefId: 1,
21    },
22    { name: "Spaghetti", description: "Classic spaghetti", recipeTypeId: RecipeTypes.Dinner.id, recipeChefId: 2 },
23    { name: "Roast chicken", description: "Juicy roast chicken", recipeTypeId: RecipeTypes.Dinner.id, recipeChefId: 3 },
24    { name: "Sandwich", description: "Good ol' ham & swiss", recipeTypeId: RecipeTypes.Lunch.id, recipeChefId: 4 },
25  ];
26
27  await db.from(recipes).insert(data);
28
29  return `${data.length} Recipes seeded successfully`;
30};

Seed the database

Now that we have a script written for each table in the database, we we need to run them each in a specific order, as some tables may depend on others. I keep a file called seed.ts which to run each of the seeding functions in the correct order.

1import seedChefs from "./chefs";
2import seedRecipes from "./recipes";
3import seedRecipeTypes from "./recipeTypes";
4
5const seedDb = async () => {
6  // note: this function assumes we're starting with an empty database
7
8  console.log("Seeding database...");
9
10  console.log("Adding independent data...");
11  const res = await Promise.allSettled([
12    seedRecipeTypes(),
13    seedChefs(),
14    // add more independent seeding functions here
15    // these will run in parallel
16  ]);
17
18  res.forEach((result) => {
19    if (result.status === "rejected") {
20      console.log("Error seeding database:", result.reason);
21    } else {
22      console.log(result.value);
23    }
24  });
25
26  console.log("Adding related data...");
27  const dependentTasks = [
28    seedRecipes,
29    // add more dependent tasks here, they will run in this order
30  ];
31  for (const task of dependentTasks) {
32    try {
33    const result = await task();
34    console.log(result);
35    } catch e {
36      console.log("Error seeding database:", e);
37    }
38  }
39
40  console.log("Seeding complete!");
41};
42
43seedDb()
44  .then(() => {
45    console.log("Seeding complete!");
46    process.exit(0);
47  })
48  .catch((err) => {
49    console.error("Error seeding database:", err);
50    process.exit(1);
51  });

You can run this script using your package manager of choice, by adding an entry to package.json:

1{
2  "scripts": {
3    // ...etc
4    "seed": "tsx --env-file=.env src/db/seed/seed.ts"
5    // ...etc
6  }
7}

Then, run the script using:

1pnpm run seed

This will seed your database with the sample data you've defined. Note that we're using Promise.allSettled to ensure that all the seeding functions run in parallel. This can be useful if you have a large amount of data to seed. It will also catch any errors that occur during seeding and log them to the console.

Promise.allSettled returns an array of promises that are settled (either fulfilled or rejected). This allows you to handle each promise individually and log any errors that occur. It is different from Promise.all, which will reject the entire promise chain if any of the promises are rejected - you may want to use Promise.all if you want to stop seeding if any of the promises fail.

When run, your script should output something like this:

1Seeding Database...
2Adding independent data...
33 Recipe types seeded successfully
410 Chefs seeded successfully
5
6Adding related data...
710 Recipes seeded successfully
8Seeding complete!

Adapt this pattern to your needs

This is a simplification of the pattern I use in my projects to seed my Supabase database. You can adapt this pattern to suit your needs by adding more tables, more data, or more complex relationships between tables. The key is to keep your seed data organized and to use a consistent pattern for seeding your database.

I use this to reset my database to a known state when I'm working on new features, and to set up my test environment with fresh, predictable data. It's a simple pattern, but it's been incredibly useful for me in my projects.