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:

bash

npm install -D prisma
npm install @prisma/client

2. Initialize Prisma

Initialize Prisma in your project:

bash

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

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:

prisma

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

prisma

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

Add to

.env

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:

prisma

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:

bash

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

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:

bash

npx prisma migrate deploy

This applies pending migrations without prompts or seeds.

CI/CD Integration: Add to your deployment pipeline:

yaml

# 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:

bash

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

json

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

Install ts-node for TypeScript execution:

bash

npm install -D ts-node

3. Run Seed Manually

Execute seed script:

bash

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:

typescript

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:

bash

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

typescript

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

typescript

'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:

yaml

- 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:

env

# 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

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

Migration Management

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

Prisma Client

Use Singleton Pattern: Prevent connection exhaustion
Close in Serverless: Disconnect after operations
Type Everything: Leverage Prisma's TypeScript types
Use Select: Only fetch needed fields
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

supabase-prisma-database-management

NPX Install

Tags

SKILL.md Content

Supabase + Prisma Database Management

Overview

Installation and Setup

1. Install Prisma

2. Initialize Prisma

3. Configure Supabase Connection

4. Configure Shadow Database (Required for Migrations)

Schema Definition

1. Define Your Schema

2. Link to Supabase Auth

Migrations

1. Create Migration

2. Review Migration SQL

3. Apply Migrations in Production

4. Reset Database (Development Only)

Seeding Data

1. Create Seed Script

2. Configure Seed in package.json

3. Run Seed Manually

4. Idempotent Seeding

Prisma Client Usage

1. Generate Client

2. Use in Next.js Server Components

3. Use in Server Actions

CI/CD Integration

1. Add Schema Validation to CI

2. Migration Deployment

3. Environment-Specific Databases

Best Practices

Schema Design

Migration Management

Prisma Client

Troubleshooting

Resources

scripts/

references/

assets/