Prisma

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

If you plan to solely use Prisma instead of the Supabase Data API (PostgREST), turn it off in the API Settings.

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 Log Explorer.

password manager

For security, consider using a password generator for the Prisma role.


_15-- Create custom user
_15create user "prisma" with password 'custom_password' bypassrls createdb;
_15
_15-- extend prisma's privileges to postgres (necessary to view changes in Dashboard)
_15grant "prisma" to "postgres";
_15
_15-- Grant it necessary permissions over the relevant schemas (public)
_15grant usage on schema public to prisma;
_15grant create on schema public to prisma;
_15grant all on all tables in schema public to prisma;
_15grant all on all routines in schema public to prisma;
_15grant all on all sequences in schema public to prisma;
_15alter default privileges for role postgres in schema public grant all on tables to prisma;
_15alter default privileges for role postgres in schema public grant all on routines to prisma;
_15alter default privileges for role postgres in schema public grant all on sequences to prisma;

Create a Prisma Project

Create a new prisma Project on your computer

Create a new directory

Terminal


_10mkdir hello-prisma
_10cd hello-prisma

Initiate a new Prisma project


_10npm init -y
_10npm install prisma typescript ts-node @types/node --save-dev
_10
_10npx tsc --init
_10
_10npx prisma init

Add your connection information to your .env file

Visit the Database Settings
Find your Supavisor Session Mode string. It should end with 5432. It will be used in your .env file.

If you're in an IPv6 environment or have the IPv4 Add-On, you can use the direct connection string instead of Supavisor in Session mode.

If you plan on deploying Prisma to a serverless or auto-scaling environment, you'll also need your Supavisor transaction mode 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

.env


_10# Used for Prisma Migrations and within your application
_10DATABASE_URL="postgres://[DB-USER].[PROJECT-REF]:[YOUR-PASSWORD]@aws-0-us-east-1.pooler.supabase.com:5432/postgres"

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


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

Create your migrations

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

Create new tables in your prisma.schema file

prisma/schema.prisma


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

commit your migration


_10npx prisma migrate dev --name first_prisma_migration

Install the prisma client

Install the prisma client and generate its model


_10npm install @prisma/client
_10npx prisma generate

Test your API

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

index.ts


_21const { PrismaClient } = require('@prisma/client');
_21
_21const prisma = new PrismaClient();
_21
_21async function main() {
_21  //change to reference a table in your schema
_21  const val = await prisma.<SOME_TABLE_NAME>.findMany({
_21    take: 10,
_21  });
_21  console.log(val);
_21}
_21
_21main()
_21  .then(async () => {
_21    await prisma.$disconnect();
_21  })
_21  .catch(async (e) => {
_21    console.error(e);
_21    await prisma.$disconnect();
_21  process.exit(1);
_21});