Migrations & Seeding

Prisma provides two distinct workflows for applying schema changes to your database — one optimized for development speed, and one for production safety. This page covers both, plus how to set up the pgvector extension and seed your database with test data.

Development Workflow

During development, use prisma db push to apply schema changes instantly. It reads your schema.prisma file, compares it to the current database state, and applies the diff — no migration files, no SQL review.

Edit the schema

Open apps/boilerplate/prisma/schema.prisma and make your changes. For example, adding a new field:

prisma

model User {
  // ... existing fields
  bio String? // NEW: User biography
}

Push the schema

Apply the change to your local database:

bash

cd apps/boilerplate && npx prisma db push

This command:

Computes the diff between your schema and the database
Generates and runs the necessary SQL (ALTER TABLE)
Regenerates the Prisma client automatically

Verify in Prisma Studio

Optionally, open Prisma Studio to inspect the database visually:

bash

pnpm db:studio

This launches a browser-based GUI at http://localhost:5555 where you can browse tables, view records, and edit data.

prisma db push is fast and perfect for prototyping, but it does not create migration files. If you need a reviewable migration history (recommended for production), use the production workflow below.

When to Use db push

Scenario	Use `db push`	Use `migrate`
Local prototyping	Yes	No
Exploring schema designs	Yes	No
CI/CD pipeline	No	Yes
Production deployment	No	Yes
Team collaboration (shared DB)	No	Yes
Schema history tracking	No	Yes

Production Migrations

For production databases, use Prisma Migrate. It creates versioned SQL migration files that are reviewed, committed to Git, and deployed through your CI/CD pipeline.

Create a new migration from your schema changes:

cd apps/boilerplate && npx prisma migrate dev --name add-user-bio

This command:

Computes the schema diff
Generates a SQL migration file in apps/boilerplate/prisma/migrations/
Applies the migration to your local database
Regenerates the Prisma client

The generated migration file looks like:

apps/boilerplate/prisma/migrations/
  20260214120000_add_user_bio/
    migration.sql

-- prisma/migrations/20260214120000_add_user_bio/migration.sql
ALTER TABLE "User" ADD COLUMN "bio" TEXT;

Migration Best Practices

Some schema changes cause data loss. Prisma will warn you, but always review the generated SQL before deploying to production:

Dropping a column — All data in that column is lost
Dropping a table — All data in the table is lost
Changing a column type — May fail if existing data cannot be cast
Adding a required field without a default — Fails if the table has existing rows

Safe patterns for common changes:

prisma

// Adding an optional field — always safe
bio String?

// Adding a required field with a default — safe for existing rows
role String @default("member")

// Renaming a field — use @map to avoid data loss
displayName String @map("name")

Complete Schema Change Workflow

Changing schema.prisma requires updates in five places — not just the database. Missing any of these steps causes type errors, test failures, or stale mock data in development:

Prisma Client — cd apps/boilerplate && npx prisma generate (automatic with db push / migrate dev)
Database — cd apps/boilerplate && npx prisma db push (development) or cd apps/boilerplate && npx prisma migrate dev (production)
Query Modules — Update apps/boilerplate/src/lib/db/queries/ if fields changed
MSW Mock Handlers — Update inline type definitions in apps/boilerplate/src/mocks/*-handlers.ts to match new fields. Remember: MSW handlers cannot import from @prisma/client (browser bundle), so types must be defined inline.
Test Factories — Update apps/boilerplate/src/test/factories/ to include new required fields

Run pnpm typecheck && pnpm test:unit after every schema change to catch missed updates.

Common Schema Changes

Adding a New Model

Define the model in schema.prisma (see Adding Your Own Models)
Create a migration: cd apps/boilerplate && npx prisma migrate dev --name add-blog-post
Create a query module in apps/boilerplate/src/lib/db/queries/
Export from apps/boilerplate/src/lib/db/queries/index.ts
Add MSW mock handlers in apps/boilerplate/src/mocks/ with inline mock types
Add test factories in apps/boilerplate/src/test/factories/ for the new model

Adding a Field to an Existing Model

prisma

model User {
  // ... existing fields
  avatarUrl String? // New field — nullable, so no migration issues
}

bash

cd apps/boilerplate && npx prisma migrate dev --name add-user-avatar

Adding a Relation

To connect two models, add a foreign key field and a @relation directive:

prisma

model Comment {
  id        String   @id @default(uuid())
  content   String   @db.Text
  userId    String
  postId    String
  createdAt DateTime @default(now())
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  post      BlogPost @relation(fields: [postId], references: [id], onDelete: Cascade)

  @@index([userId])
  @@index([postId])
}

Remember to add the reverse relation arrays on both User and BlogPost:

prisma

model User {
  // ...
  comments Comment[]
}

model BlogPost {
  // ...
  comments Comment[]
}

Removing a Field Safely

Mark the field as optional first

If the field is currently required, make it optional in the first migration:

prisma

// Before
bio String
// After (first migration)
bio String?

Deploy and verify

Deploy the first migration. Ensure no application code reads this field as required.

Remove the field

In a second migration, remove the field entirely:

prisma

// Remove the line from schema.prisma

bash

cd apps/boilerplate && npx prisma migrate dev --name remove-user-bio

This two-step approach prevents runtime errors if your application code still references the field during deployment.

pgvector Setup

Kit uses the pgvector extension for semantic search in the RAG system. After creating your Supabase database, you need to enable the extension and create the vector search index.

Open the Supabase SQL Editor

Go to your Supabase dashboard → SQL Editor → New query.

Run the setup script

Copy and run the pgvector setup SQL:

prisma/setup-pgvector.sql

-- ============================================
-- Supabase pgvector Setup for FAQ RAG System
-- ============================================
-- Run this script in Supabase SQL Editor
-- (https://supabase.com/dashboard/project/YOUR_PROJECT_ID/sql/new)

-- 1. Enable pgvector extension
-- This adds vector data type and similarity search operators
CREATE EXTENSION IF NOT EXISTS vector;

-- 2. Verify extension is installed
SELECT
  extname AS "Extension Name",
  extversion AS "Version",
  'Installed successfully' AS "Status"
FROM pg_extension
WHERE extname = 'vector';

-- 3. Create IVFFlat index for fast similarity search
-- IVFFlat: Inverted File with Flat compression
-- Good balance between speed and accuracy for < 1M vectors
-- vector_cosine_ops: Uses cosine similarity (1 - cosine distance)
CREATE INDEX IF NOT EXISTS faq_chunks_embedding_idx
ON faq_chunks
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- Lists = 100 is good for ~10K-50K vectors
-- Adjust based on dataset size:
--   < 10K vectors: lists = 50
--   10K-50K: lists = 100
--   50K-100K: lists = 200
--   > 100K: lists = sqrt(row_count)

The IVFFlat index uses lists = 100, which is optimized for 10K–50K vectors. Adjust the lists parameter based on your dataset size:

Vector Count	Recommended Lists
< 10,000	50
10,000 – 50,000	100
50,000 – 100,000	200
> 100,000	`sqrt(row_count)`

Verify the installation

The script includes verification queries. You should see:

The vector extension listed with version 0.5.1 or higher
The faq_chunks_embedding_idx index created on the faq_chunks table
The embedding column with type USER-DEFINED in the column listing

The pgvector extension must be enabled before running prisma db push or prisma migrate deploy for the first time. Without it, Prisma cannot create the faq_chunks table because the vector(1536) column type does not exist yet.

Seeding

Kit does not ship with a seed file — the database is populated through normal application usage (user signup, Clerk webhooks, payment events). However, you can create a seed script for development and testing.

Creating a Seed Script

Create apps/boilerplate/prisma/seed.ts:

typescript

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  console.log('Seeding database...')

  // Create a test user
  const user = await prisma.user.upsert({
    where: { clerkId: 'user_test_123' },
    update: {},
    create: {
      clerkId: 'user_test_123',
      email: 'test@example.com',
      name: 'Test User',
      tier: 'pro',
      creditBalance: 100.00,
      creditsPerMonth: 100.00,
    },
  })

  console.log(`Created user: ${user.id}`)

  // Create a subscription
  await prisma.subscription.upsert({
    where: { userId: user.id },
    update: {},
    create: {
      userId: user.id,
      customerId: 'cus_test_123',
      subscriptionId: 'sub_test_123',
      productId: 'prod_test_123',
      variantId: 'var_test_123',
      status: 'active',
      currentPeriodEnd: new Date(Date.now() + 30 * 24 * 60 * 60 * 1000), // 30 days
    },
  })

  console.log('Created subscription')

  // Create sample FAQ chunks (for RAG testing)
  await prisma.fAQChunk.createMany({
    data: [
      {
        content: 'How do I reset my password? Go to the login page and click "Forgot password?".',
        question: 'How do I reset my password?',
        answer: 'Go to the login page and click "Forgot password?" to receive a reset email.',
        category: 'Getting Started',
      },
      {
        content: 'How do I upgrade my plan? Go to Dashboard > Billing and select a new plan.',
        question: 'How do I upgrade my plan?',
        answer: 'Navigate to Dashboard > Billing and click "Upgrade" on the plan you want.',
        category: 'Billing',
      },
    ],
    skipDuplicates: true,
  })

  console.log('Created FAQ chunks')
  console.log('Seeding complete!')
}

main()
  .catch((e) => {
    console.error('Seeding failed:', e)
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

Configuring the Seed Command

Add the seed configuration to apps/boilerplate/package.json:

json

{
  "prisma": {
    "seed": "npx tsx prisma/seed.ts"
  }
}

Then run it:

bash

cd apps/boilerplate && npx prisma db seed

Using upsert instead of create makes seed scripts idempotent — you can run them multiple times without creating duplicate records. This is especially useful when resetting local databases.

Resetting the Database

To wipe the database and re-seed from scratch:

bash

# Reset database (drops all tables, re-applies migrations, runs seed)
cd apps/boilerplate && npx prisma migrate reset

# Or push schema and seed separately
cd apps/boilerplate && npx prisma db push --force-reset
cd apps/boilerplate && npx prisma db seed

migrate reset and db push --force-reset delete all data in the database. Only use these in development.

Troubleshooting

Key Commands Reference

Command	Purpose	When to Use
`cd apps/boilerplate && npx prisma db push`	Apply schema changes directly	Development, prototyping
`cd apps/boilerplate && npx prisma migrate dev --name <name>`	Create and apply a migration	Before committing schema changes
`cd apps/boilerplate && npx prisma migrate dev --create-only`	Create migration without applying	Code review before apply
`cd apps/boilerplate && npx prisma migrate deploy`	Apply pending migrations	CI/CD, production deployment
`cd apps/boilerplate && npx prisma migrate reset`	Drop all tables, re-migrate, re-seed	Reset local database
`cd apps/boilerplate && npx prisma generate`	Regenerate TypeScript client	After any schema change
`pnpm db:studio`	Open database GUI	Inspect and edit data
`cd apps/boilerplate && npx prisma db pull`	Introspect database into schema	Reverse-engineer existing DB
`cd apps/boilerplate && npx prisma db seed`	Run seed script	Populate development data

Development Workflow

Edit the schema

Push the schema

Verify in Prisma Studio

When to Use db push

Production Migrations

Migration Best Practices

Complete Schema Change Workflow

Common Schema Changes

Adding a New Model

Adding a Field to an Existing Model

Adding a Relation

Removing a Field Safely

Mark the field as optional first

Deploy and verify

Remove the field

pgvector Setup

Open the Supabase SQL Editor

Run the setup script

Verify the installation

Seeding

Creating a Seed Script

Configuring the Seed Command

Resetting the Database

Troubleshooting

Key Commands Reference

Related