Skip to content

Updated

Prisma

This guide shows how to connect your Prisma application to Supabase Postgres. If you encounter any problems, reference the Prisma troubleshooting docs.

1
Create a custom user for Prisma
  • In the SQL Editor, create a Prisma DB user with full privileges on the public schema.
  • This gives you better control over Prisma's access and makes it easier to monitor using Supabase tools like the Query Performance Dashboard and Logs.
-- Create custom user
create user "prisma" with password 'custom_password' bypassrls createdb;
-- extend prisma's privileges to postgres (necessary to view changes in Dashboard)
grant "prisma" to "postgres";
-- Grant it necessary permissions over the relevant schemas (public)
grant usage on schema public to prisma;
grant create on schema public to prisma;
grant all on all tables in schema public to prisma;
grant all on all routines in schema public to prisma;
grant all on all sequences in schema public to prisma;
alter default privileges for role postgres in schema public grant all on tables to prisma;
alter default privileges for role postgres in schema public grant all on routines to prisma;
alter default privileges for role postgres in schema public grant all on sequences to prisma;
-- alter prisma password if needed
alter user "prisma" with password 'new_password';
2
Create a Prisma Project

Create a new Prisma Project on your computer

Create a new directory

mkdir hello-prisma
cd hello-prisma

Initiate a new Prisma project

npm init -y
npm install prisma tsx @types/pg --save-dev
npm install @prisma/client @prisma/adapter-pg dotenv pg
npx tsc --init
npx prisma init
pnpm init
pnpm install prisma tsx @types/pg --save-dev
pnpm install @prisma/client @prisma/adapter-pg dotenv pg
pnpx tsc --init
pnpx prisma init
yarn init -y
yarn add prisma tsx @types/pg --save-dev
yarn add @prisma/client @prisma/adapter-pg dotenv pg
yarn tsc --init
yarn prisma init
bun init -y
bun install prisma tsx @types/pg --save-dev
bun install @prisma/client @prisma/adapter-pg dotenv pg
bunx tsc --init
bunx prisma init
3
Add your connection information to your .env file
  • On your project dashboard, click Connect
  • Find your Session pooler string. It should end with 5432. It will be used in your .env file.
  • If you plan on deploying Prisma to a serverless or auto-scaling environment, you'll also need your Transaction pooler string.
  • The string is identical to the session mode string but uses port 6543 at the end.

In your .env file, set the DATABASE_URL variable to your connection string

# Used for Prisma Migrations and within your application
DATABASE_URL="postgres://[DB-USER].[PROJECT-REF]:[PRISMA-PASSWORD]@[DB-REGION].pooler.supabase.com:5432/postgres"

Change your string's [DB-USER] to prisma and add the password you created in step 1

postgres://prisma.[PROJECT-REF]...

Assign the Transaction pooler connection string (using port 6543) to the DATABASE_URL variable in your .env file. Make sure to append "pgbouncer=true" to the end of the string, which turns off prepared statements.

Next, create a DIRECT_URL variable in your .env file and assign the connection string that ends with port 5432 to it.

DATABASE_URL="postgres://[DB-USER].[PROJECT-REF]:[PRISMA-PASSWORD]@aws-0-us-east-1.pooler.supabase.com:6543/postgres?pgbouncer=true"
# Used for Prisma Migrations (use session mode or direct connection)
DIRECT_URL="postgres://[DB-USER].[PROJECT-REF]:[PRISMA-PASSWORD]@aws-0-us-east-1.pooler.supabase.com:5432/postgres"

Change both your strings' [DB-USER] to prisma and then add the password created in step 1

postgres://prisma.[PROJECT-REF]...
4
Configure prisma.config.ts

Add import "dotenv/config" to the generated prisma.config.ts. If you are using a serverless environment, change the data source URL to DIRECT_URL.

import "dotenv/config";
import { defineConfig, env } from "prisma/config";
export default defineConfig({
  schema: "prisma/schema",
  migrations: {
    path: "prisma/migrations",
  },
  datasource: {
    url: env("DATABASE_URL"),
  },
});
import "dotenv/config";
import { defineConfig, env } from "prisma/config";
export default defineConfig({
  schema: "prisma/schema",
  migrations: {
    path: "prisma/migrations",
  },
  datasource: {
    url: env("DIRECT_URL"),
  },
});
5
Migrate and generate your Prisma client

If you have already modified your Supabase database, synchronize it with your migration file. Otherwise create new tables for your database, then generate the Prisma client.

Create new tables in your prisma.schema file

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  author    User?   @relation(fields: [authorId], references: [id])
  authorId  Int?
}
model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]
}

commit your migration

npx prisma migrate dev --name first_prisma_migration
npx prisma generate
pnpx prisma migrate dev --name first_prisma_migration
pnpx prisma generate
yarn prisma migrate dev --name first_prisma_migration
yarn prisma generate
bunx prisma migrate dev --name first_prisma_migration
bunx prisma generate

Synchronize changes from your project:

npx prisma db pull

Create a migration file

mkdir -p prisma/migrations/0_init_supabase

Synchronize the migrations

npx prisma migrate diff \
  --from-empty \
  --to-schema prisma/schema.prisma \
  --script > prisma/migrations/0_init_supabase/migration.sql
npx prisma migrate resolve --applied 0_init_supabase
npx prisma generate
pnpx prisma db pull

Create a migration file

mkdir -p prisma/migrations/0_init_supabase

Synchronize the migrations

pnpx prisma migrate diff \
  --from-empty \
  --to-schema prisma/schema.prisma \
  --script > prisma/migrations/0_init_supabase/migration.sql
pnpx prisma migrate resolve --applied 0_init_supabase
pnpx prisma generate
yarn prisma db pull

Create a migration file

mkdir -p prisma/migrations/0_init_supabase

Synchronize the migrations

yarn prisma migrate diff \
  --from-empty \
  --to-schema prisma/schema.prisma \
  --script > prisma/migrations/0_init_supabase/migration.sql
yarn prisma migrate resolve --applied 0_init_supabase
yarn prisma generate
bunx prisma db pull

Create a migration file

mkdir -p prisma/migrations/0_init_supabase

Synchronize the migrations

bunx prisma migrate diff \
  --from-empty \
  --to-schema prisma/schema.prisma \
  --script > prisma/migrations/0_init_supabase/migration.sql
bunx prisma migrate resolve --applied 0_init_supabase
bunx prisma generate
6
Test your API

Create a index.ts file and run it to test your connection

import "dotenv/config";
import { PrismaClient } from "./generated/prisma/client";
import { PrismaPg } from "@prisma/adapter-pg";
const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });
export const prisma = new PrismaClient({ adapter });
async function main() {
  const val = await prisma.user.findMany({
    take: 10,
  });
  console.log(val);
}
main()
  .then(async () => {
    await prisma.$disconnect();
  })
  .catch(async (e) => {
    console.error(e);
    await prisma.$disconnect();
    process.exit(1);
});