supabase-prisma-database-management

What this skill does

# 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: '[email protected]' },
  update: {}, // No updates if exists
  create: {
    email: '[email protected]',
    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

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