Prisma + Supabase: connect and sync schema

Introduction

Many teams use Prisma as the ORM for Node/TypeScript. Supabase provides Postgres; Prisma connects to Postgres via a connection string. This post covers configuring the Prisma datasource for Supabase, using introspect (DB → schema) vs migrate (schema → DB), and avoiding conflicts with Supabase migrations.

Goal: Connect Prisma to Supabase safely and be clear about “who is the source of truth” for schema (Supabase migrations vs Prisma migrate).

Features

Prisma and Supabase

• Prisma: ORM with schema in schema.prisma; generates a TypeScript/JavaScript client for queries. It can create migrations (Prisma Migrate) or only introspect from an existing DB.
• Supabase: Postgres + migrations in supabase/migrations/. PostgREST and RLS depend on the actual DB schema.
• Connection: Prisma datasource uses the Postgres connection string (from Supabase Dashboard → Settings → Database). Use the connection pooler (port 6543) for serverless; direct (5432) for long-running processes if needed.

Introspect vs migrate

• Introspect: DB already exists (schema from Supabase migrations) → prisma db pull generates schema.prisma from the DB. Prisma does not create migrations; source of truth remains Supabase migrations.
• Migrate: Prisma creates migrations from changes in schema.prisma and applies them to the DB. With Migrate, Prisma is the source of truth; conflicts are likely if Supabase migrations also change the same tables. Recommendation: Pick one: either Supabase migrations (Prisma introspect only), or Prisma Migrate (no Supabase migrations for that DB).

Workflow / process

1. Get connection string: Supabase Dashboard → Settings → Database → Connection string (URI). Use pooler (Transaction mode) for serverless; replace password with an env variable.
2. Configure Prisma: datasource db { provider = "postgresql", url = env("DATABASE_URL") }. Do not commit DATABASE_URL (it contains the password).
3. If schema already exists (Supabase migrations): Run prisma db pull → adjust schema.prisma if needed (e.g. remove system tables) → prisma generate. Do not use prisma migrate deploy on the same DB.
4. If starting fresh and choosing Prisma Migrate: Define models in schema.prisma → prisma migrate dev → for deploy use prisma migrate deploy. Leave Supabase migrations empty or don’t use them for this DB.
5. Generate client: After any schema change (pull or migrate), run prisma generate to use the client in code.

Convention: One source of truth for schema; avoid changing the same database with both Supabase migrations and Prisma Migrate.

Sample code

schema.prisma (after introspect from Supabase)

generator client {
  provider = "prisma-client-js"
}
 
datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL") // optional: direct 5432 for migrate
}
 
model conversations {
  id        String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  user_id   String   @map("user_id") @db.Uuid
  title     String?  @db.Text
  created_at DateTime @default(now())
  updated_at DateTime @default(now())
  messages  messages[]
 
  @@map("conversations")
}
 
model messages {
  id              String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  conversation_id String   @map("conversation_id") @db.Uuid
  role            String   @db.Text
  content         String   @db.Text
  created_at      DateTime @default(now())
  conversations   conversations @relation(fields: [conversation_id], references: [id], onDelete: Cascade)
 
  @@map("messages")
}

.env (do not commit)

# Pooler (port 6543) for serverless / many connections
DATABASE_URL="postgresql://postgres.[ref]:[PASSWORD]@aws-0-[region].pooler.supabase.com:6543/postgres?pgbouncer=true"
# Direct for migrate (if using Prisma Migrate)
DIRECT_URL="postgresql://postgres.[ref]:[PASSWORD]@db.[ref].supabase.co:5432/postgres"

Query with Prisma (backend)

const conversations = await prisma.conversations.findMany({
  where: { user_id: userId },
  orderBy: { updated_at: 'desc' },
  take: 10,
});

Apply in your architecture

• One source of truth for schema: Either Supabase migrations (Prisma only introspects), or Prisma Migrate; avoid two sources changing the same DB and causing schema drift and rollback pain.
• Clear migration ownership: Decide explicitly “who owns schema”; Supabase + RLS + PostgREST usually go with “Supabase migrations are source of truth”, Prisma only reads/writes via the client.
• Secrets in connection string: DATABASE_URL contains the password; server env only, never commit. Use pooler URL for serverless to stay within connection limits.

Summary

Prisma connects to Supabase via the Postgres URL; prefer introspect from an existing DB (Supabase migrations) and don’t run Prisma Migrate in parallel. Next: Row Level Security (RLS) and policies.

Next: 07 — Row Level Security (RLS)

Further reading: Prisma — Connect to Supabase