How to Build a Database Seeding and Fixtures System in Next.js

Database seeding gives you consistent data for development and testing. Here is how to build a proper system.

Schema Setup

// db/schema.ts
import { pgTable, text, timestamp, integer, boolean, uuid } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  role: text("role", { enum: ["admin", "member", "viewer"] }).notNull().default("member"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

export const projects = pgTable("projects", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: text("name").notNull(),
  description: text("description"),
  ownerId: uuid("owner_id").references(() => users.id).notNull(),
  public: boolean("public").default(false).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

export const tasks = pgTable("tasks", {
  id: uuid("id").primaryKey().defaultRandom(),
  title: text("title").notNull(),
  status: text("status", { enum: ["todo", "in_progress", "done"] }).notNull().default("todo"),
  priority: integer("priority").notNull().default(0),
  projectId: uuid("project_id").references(() => projects.id).notNull(),
  assigneeId: uuid("assignee_id").references(() => users.id),
  createdAt: timestamp("created_at").defaultNow().notNull(),
});

Factory System

// db/factories.ts
import { faker } from "@faker-js/faker";
import type { InferInsertModel } from "drizzle-orm";
import { users, projects, tasks } from "./schema";

type UserInsert = InferInsertModel<typeof users>;
type ProjectInsert = InferInsertModel<typeof projects>;
type TaskInsert = InferInsertModel<typeof tasks>;

export function createUserFactory(overrides: Partial<UserInsert> = {}): UserInsert {
  return {
    name: faker.person.fullName(),
    email: faker.internet.email().toLowerCase(),
    role: faker.helpers.arrayElement(["admin", "member", "viewer"]),
    ...overrides,
  };
}

export function createProjectFactory(
  ownerId: string,
  overrides: Partial<ProjectInsert> = {}
): ProjectInsert {
  return {
    name: faker.commerce.productName(),
    description: faker.lorem.sentence(),
    ownerId,
    public: faker.datatype.boolean(),
    ...overrides,
  };
}

export function createTaskFactory(
  projectId: string,
  overrides: Partial<TaskInsert> = {}
): TaskInsert {
  return {
    title: faker.hacker.phrase(),
    status: faker.helpers.arrayElement(["todo", "in_progress", "done"]),
    priority: faker.number.int({ min: 0, max: 3 }),
    projectId,
    ...overrides,
  };
}

Seed Runner

// db/seed.ts
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import { users, projects, tasks } from "./schema";
import { createUserFactory, createProjectFactory, createTaskFactory } from "./factories";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const db = drizzle(pool);

interface SeedConfig {
  users: number;
  projectsPerUser: number;
  tasksPerProject: number;
  clean?: boolean;
}

const DEFAULT_CONFIG: SeedConfig = {
  users: 10,
  projectsPerUser: 3,
  tasksPerProject: 8,
  clean: true,
};

async function seed(config: SeedConfig = DEFAULT_CONFIG) {
  console.log("Starting database seed...");
  const start = Date.now();

  if (config.clean) {
    console.log("Cleaning existing data...");
    await db.delete(tasks);
    await db.delete(projects);
    await db.delete(users);
  }

  // Create admin user (always the same for development)
  console.log("Creating admin user...");
  const [admin] = await db
    .insert(users)
    .values({
      name: "Admin User",
      email: "admin@example.com",
      role: "admin",
    })
    .returning();

  // Create regular users
  console.log(`Creating ${config.users} users...`);
  const userRecords = await db
    .insert(users)
    .values(
      Array.from({ length: config.users }, () => createUserFactory())
    )
    .returning();

  const allUsers = [admin, ...userRecords];

  // Create projects
  console.log("Creating projects...");
  const projectRecords = [];
  for (const user of allUsers) {
    const userProjects = await db
      .insert(projects)
      .values(
        Array.from({ length: config.projectsPerUser }, () =>
          createProjectFactory(user.id)
        )
      )
      .returning();
    projectRecords.push(...userProjects);
  }

  // Create tasks
  console.log("Creating tasks...");
  let taskCount = 0;
  for (const project of projectRecords) {
    const assignees = allUsers.slice(0, 5);
    await db.insert(tasks).values(
      Array.from({ length: config.tasksPerProject }, () =>
        createTaskFactory(project.id, {
          assigneeId: faker.helpers.arrayElement(assignees).id,
        })
      )
    );
    taskCount += config.tasksPerProject;
  }

  const duration = Date.now() - start;
  console.log(`\nSeed complete in ${duration}ms:`);
  console.log(`  Users: ${allUsers.length}`);
  console.log(`  Projects: ${projectRecords.length}`);
  console.log(`  Tasks: ${taskCount}`);
}

seed()
  .then(() => process.exit(0))
  .catch((err) => {
    console.error("Seed failed:", err);
    process.exit(1);
  });

Test Fixtures

// db/fixtures.ts
import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import { users, projects, tasks } from "./schema";

export async function setupTestFixtures(db: ReturnType<typeof drizzle>) {
  // Clean state
  await db.delete(tasks);
  await db.delete(projects);
  await db.delete(users);

  // Insert known fixtures
  const [testUser] = await db
    .insert(users)
    .values({ name: "Test User", email: "test@example.com", role: "member" })
    .returning();

  const [testAdmin] = await db
    .insert(users)
    .values({ name: "Test Admin", email: "admin@test.com", role: "admin" })
    .returning();

  const [testProject] = await db
    .insert(projects)
    .values({ name: "Test Project", ownerId: testUser.id, public: true })
    .returning();

  const [testTask] = await db
    .insert(tasks)
    .values({
      title: "Test Task",
      status: "todo",
      priority: 1,
      projectId: testProject.id,
      assigneeId: testUser.id,
    })
    .returning();

  return { testUser, testAdmin, testProject, testTask };
}

// Usage in tests
// import { setupTestFixtures } from "@/db/fixtures";
//
// beforeEach(async () => {
//   const fixtures = await setupTestFixtures(db);
//   // Use fixtures.testUser, fixtures.testProject, etc.
// });

Package.json Scripts

{
  "scripts": {
    "db:seed": "tsx db/seed.ts",
    "db:seed:minimal": "SEED_USERS=3 SEED_PROJECTS=1 SEED_TASKS=3 tsx db/seed.ts",
    "db:reset": "pnpm db:push && pnpm db:seed"
  }
}

Idempotent Seed (Upsert Pattern)

// For seeds that need to run multiple times safely
import { sql } from "drizzle-orm";

async function idempotentSeed(db: ReturnType<typeof drizzle>) {
  await db
    .insert(users)
    .values({ name: "Admin", email: "admin@example.com", role: "admin" })
    .onConflictDoUpdate({
      target: users.email,
      set: { name: "Admin", role: "admin" },
    });
}

Need Database Architecture?

We design and implement database schemas, migrations, and seeding systems for production applications. Contact us for help.