How to Use Supabase with Prisma

1# Connection pooler (for Prisma Client queries)
2# Settings > Database > Connection string > Mode: Transaction
3postgresql://postgres.[project-ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres?pgbouncer=true
4
5# Direct connection (for Prisma Migrate)
6# Settings > Database > Connection string > Mode: Session
7postgresql://postgres.[project-ref]:[password]@aws-0-[region].pooler.supabase.com:5432/postgres

1# Initialize Prisma
2npx prisma init
3
4# .env
5DATABASE_URL="postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres?pgbouncer=true"
6DIRECT_URL="postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:5432/postgres"
7
8# prisma/schema.prisma
9generator client {
10  provider = "prisma-client-js"
11}
12
13datasource db {
14  provider  = "postgresql"
15  url       = env("DATABASE_URL")
16  directUrl = env("DIRECT_URL")
17}
18
19model Post {
20  id        Int      @id @default(autoincrement())
21  title     String
22  content   String?
23  published Boolean  @default(false)
24  authorId  String   @map("author_id")
25  createdAt DateTime @default(now()) @map("created_at")
26  updatedAt DateTime @updatedAt @map("updated_at")
27
28  @@map("posts")
29}

1# Pull existing schema from Supabase
2npx prisma db pull
3
4# Generate the Prisma Client
5npx prisma generate
6
7# The schema.prisma file is now populated with models
8# matching your existing Supabase tables

1# Create and apply a migration
2npx prisma migrate dev --name add_comments_table
3
4# After the migration, add RLS policies via Supabase SQL Editor:
5# alter table public.comments enable row level security;
6# create policy "Users can read comments" on public.comments
7#   for select to authenticated using (true);
8# create policy "Users can insert own comments" on public.comments
9#   for insert to authenticated
10#   with check ((select auth.uid())::text = author_id);
11
12# Deploy migrations to production
13npx prisma migrate deploy

1import { PrismaClient } from '@prisma/client'
2import { createClient } from '@supabase/supabase-js'
3
4const prisma = new PrismaClient()
5const supabase = createClient(
6  process.env.NEXT_PUBLIC_SUPABASE_URL!,
7  process.env.SUPABASE_SERVICE_ROLE_KEY!  // Server-side only
8)
9
10// Use Prisma for complex queries
11async function getPostsWithCommentCount() {
12  return prisma.post.findMany({
13    where: { published: true },
14    include: { _count: { select: { comments: true } } },
15    orderBy: { createdAt: 'desc' },
16  })
17}
18
19// Use Supabase for auth operations
20async function getUserFromToken(token: string) {
21  const { data: { user }, error } = await supabase.auth.getUser(token)
22  return user
23}