Supabase + Prisma Database Management

Overview

Manage database schema, migrations, and seed data using Prisma ORM with Supabase PostgreSQL, including shadow database configuration, seed files, and automated schema checks in CI.

Installation and Setup

1. Install Prisma

Install Prisma CLI and client:

npm install -D prisma
npm install @prisma/client

2. Initialize Prisma

Initialize Prisma in your project:

npx prisma init

This creates:

• prisma/schema.prisma - Database schema definition
• .env - Environment variables (add DATABASE_URL)

3. Configure Supabase Connection

Get your Supabase database URL from:

• Supabase Dashboard > Project Settings > Database > Connection String > URI

Add to .env:

# Transaction pooler for Prisma migrations
DATABASE_URL="postgresql://postgres:[YOUR-PASSWORD]@db.[PROJECT-REF].supabase.co:5432/postgres"

# Session pooler for queries (with pgBouncer)
DIRECT_URL="postgresql://postgres:[YOUR-PASSWORD]@db.[PROJECT-REF].supabase.co:6543/postgres?pgbouncer=true"

Update prisma/schema.prisma to use both URLs:

datasource db {
  provider  = "postgresql"
  url       = env("DIRECT_URL")
  directUrl = env("DATABASE_URL")
}

Why two URLs?

• DATABASE_URL: Direct connection for migrations (required)
• DIRECT_URL: Pooled connection for application queries (optional, better performance)

4. Configure Shadow Database (Required for Migrations)

For migration preview and validation, configure a shadow database in prisma/schema.prisma:

datasource db {
  provider  = "postgresql"
  url       = env("DIRECT_URL")
  directUrl = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}

Add to .env:

SHADOW_DATABASE_URL="postgresql://postgres:[PASSWORD]@db.[PROJECT-REF].supabase.co:5432/postgres"

Note: Supabase free tier allows using the same database for shadow. For production, use a separate database.

Schema Definition

1. Define Your Schema

Edit prisma/schema.prisma using the example from assets/example-schema.prisma. This example includes:

• User profiles with auth integration
• Timestamps with @default(now()) and @updatedAt
• Relations between entities
• Indexes for performance
• Unique constraints

Key Prisma features:

• @id @default(uuid()) - Auto-generated UUIDs
• @default(now()) - Automatic timestamps
• @updatedAt - Auto-update on modification
• @@index([field]) - Database indexes
• @relation - Define relationships

2. Link to Supabase Auth

To integrate with Supabase Auth, reference the auth.users table:

model Profile {
  id        String   @id @db.Uuid
  email     String   @unique
  // Other fields...

  // This doesn't create a foreign key, just documents the relationship
  // The actual user exists in auth.users (managed by Supabase)
}

Important: Don't create a foreign key to auth.users as it's in a different schema. Handle the relationship in application logic.

Migrations

1. Create Migration

After defining/modifying schema, create a migration:

npx prisma migrate dev --name add_profiles_table

This:

• Generates SQL migration in prisma/migrations/
• Applies migration to database
• Regenerates Prisma Client
• Runs seed script (if configured)

2. Review Migration SQL

Always review generated SQL in prisma/migrations/[timestamp]_[name]/migration.sql:

-- CreateTable
CREATE TABLE "Profile" (
    "id" UUID NOT NULL,
    "email" TEXT NOT NULL,
    -- ...

    CONSTRAINT "Profile_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "Profile_email_key" ON "Profile"("email");

Make manual adjustments if needed before applying to production.

3. Apply Migrations in Production

For production deployments:

npx prisma migrate deploy

This applies pending migrations without prompts or seeds.

CI/CD Integration: Add to your deployment pipeline:

# Example GitHub Actions step
- name: Run migrations
  run: npx prisma migrate deploy
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}

4. Reset Database (Development Only)

To reset database to clean state:

npx prisma migrate reset

This:

• Drops database
• Creates database
• Applies all migrations
• Runs seed script

Warning: This deletes all data. Only use in development.

Seeding Data

1. Create Seed Script

Create prisma/seed.ts using the template from assets/seed.ts. This script:

• Uses Prisma Client to insert data
• Creates initial users, settings, or reference data
• Can be run manually or after migrations
• Supports idempotent operations (safe to run multiple times)

2. Configure Seed in package.json

Add seed configuration to package.json:

{
  "prisma": {
    "seed": "ts-node --compiler-options {\"module\":\"CommonJS\"} prisma/seed.ts"
  }
}

Install ts-node for TypeScript execution:

npm install -D ts-node

3. Run Seed Manually

Execute seed script:

npx prisma db seed

Seed runs automatically after prisma migrate dev and prisma migrate reset.

4. Idempotent Seeding

Make seeds safe to run multiple times using upsert:

await prisma.user.upsert({
  where: { email: 'admin@example.com' },
  update: {}, // No updates if exists
  create: {
    email: 'admin@example.com',
    name: 'Admin User',
  },
});

Prisma Client Usage

1. Generate Client

After schema changes, regenerate Prisma Client:

npx prisma generate

This updates node_modules/@prisma/client with types matching your schema.

2. Use in Next.js Server Components

Create a Prisma client singleton using assets/prisma-client.ts:

import { prisma } from '@/lib/prisma';

export default async function UsersPage() {
  const users = await prisma.profile.findMany();

  return (
    <ul>
      {users.map((user) => (
        <li key={user.id}>{user.name}</li>
      ))}
    </ul>
  );
}

3. Use in Server Actions

'use server';

import { prisma } from '@/lib/prisma';
import { revalidatePath } from 'next/cache';

export async function createProfile(formData: FormData) {
  const name = formData.get('name') as string;

  await prisma.profile.create({
    data: {
      name,
      email: formData.get('email') as string,
    },
  });

  revalidatePath('/profiles');
}

CI/CD Integration

1. Add Schema Validation to CI

Create .github/workflows/schema-check.yml using the template from assets/github-workflows-schema-check.yml. This workflow:

• Runs on pull requests
• Validates schema syntax
• Checks for migration drift
• Ensures migrations are generated
• Verifies Prisma Client generation

2. Migration Deployment

Add migration step to deployment workflow:

- name: Apply database migrations
  run: npx prisma migrate deploy
  env:
    DATABASE_URL: ${{ secrets.PROD_DATABASE_URL }}

3. Environment-Specific Databases

Use different database URLs for each environment:

# Development
DATABASE_URL="postgresql://localhost:5432/dev"

# Staging
DATABASE_URL="postgresql://staging-db.supabase.co:5432/postgres"

# Production
DATABASE_URL="postgresql://prod-db.supabase.co:5432/postgres"

Best Practices

Schema Design

1. Use UUIDs for IDs: Better for distributed systems
2. Add Timestamps: Track createdAt and updatedAt
3. Define Indexes: Improve query performance on filtered fields
4. Use Enums: Type-safe status/role fields
5. Validate at DB Level: Use unique constraints and checks

Migration Management

1. Review Before Applying: Always check generated SQL
2. Name Descriptively: Use clear migration names
3. Keep Atomic: One logical change per migration
4. Test Locally First: Verify migrations work before production
5. Never Modify Applied Migrations: Create new ones instead

Prisma Client

1. Use Singleton Pattern: Prevent connection exhaustion
2. Close in Serverless: Disconnect after operations
3. Type Everything: Leverage Prisma's TypeScript types
4. Use Select: Only fetch needed fields
5. Batch Operations: Use createMany, updateMany for bulk ops

Troubleshooting

Migration fails with "relation already exists": Reset development database with npx prisma migrate reset. For production, manually fix conflicts.

Prisma Client out of sync: Run npx prisma generate after schema changes.

Connection pool exhausted: Use connection pooling via DIRECT_URL with pgBouncer.

Shadow database errors: Ensure shadow database URL is correct and accessible. For Supabase free tier, same DB can be used.

Type errors after schema changes: Restart TypeScript server in IDE after prisma generate.

Resources

scripts/

No executable scripts needed for this skill.

references/

• prisma-best-practices.md - Comprehensive guide to Prisma patterns, performance optimization, and common pitfalls
• supabase-integration.md - Specific considerations for using Prisma with Supabase, including RLS integration

assets/

• example-schema.prisma - Complete schema example with common patterns (auth, timestamps, relations, indexes)
• seed.ts - Idempotent seed script template for initial data
• prisma-client.ts - Singleton Prisma Client for Next.js to prevent connection exhaustion
• github-workflows-schema-check.yml - CI workflow for schema validation and migration checks