supabase-prisma-database-management

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