Prisma

This guide explains how to quickly connect the Postgres database provided by Supabase to a Prisma project.

Prisma is an open source next-generation ORM. It consists of the following parts:

Prisma Client: Auto-generated and type-safe query builder for Node.js & TypeScript.
Prisma Migrate: Migration system.
Prisma Studio: GUI to view and edit data in your database.

Step 1: Get the connection string from Supabase project settings#

Go to the settings page from the sidebar and navigate to the Database tab. You’ll find the database’s connection string with a placeholder for the password you provided when you created the project.

Step 2: Testing the connection#

To make sure that everything works correctly, let’s try the connection string in a Prisma project.

If you already have one, all you need to do is set the DATABASE_URL to the connection string (including the password) in your .env file, and you’re good to go.

In case you don’t have a Prisma project or this is your first time working with Prisma, you’re going to use the repo from the quickstart guide.

Cloning the starter project#

Navigate into a directory of your choice and run the following command in your terminal:

1curl https://codeload.github.com/prisma/prisma-examples/tar.gz/latest | tar -xz --strip=2 prisma-examples-latest/databases/postgresql-supabase

You can now navigate into the directory and install the project’s dependencies:

1cd postgresql-supabase
2npm install

A look at the project’s structure#

This project comes with TypeScript configured and has the following structure.

A prisma directory which contains:
- A seed.ts file: This is the data used to seed your database.
- A schema.prisma file: Where you define the different database models and relations between them.
A script.ts file: where you will run some queries using Prisma Client.

This starter also comes with the following packages installed:

@prisma/client: An auto-generated and type-safe query builder that’s tailored to your data.
prisma: Prisma’s command-line interface (CLI). It allows you to initialize new project assets, generate Prisma Client, and analyze existing database structures through introspection to automatically create your application models.

Note: Prisma works with both JavaScript and TypeScript. However, to get the best possible development experience, using TypeScript is highly recommended.

Configuring the project#

Create a .env file at the root of your project:

1touch .env

In the .env file, add a DATABASE_URL variable and add the connection string from step 1. The .env file should look like:

# .env
DATABASE_URL="postgres://postgres:[YOUR-PASSWORD]@db.[YOUR-PROJECT-REF].supabase.co:5432/postgres"

This is what your schema.prisma file should look like:

datasource db {
  provider          = "postgresql"
  url               = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

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[]
}

To test that everything works correctly, run the following command to create a migration:

1npx prisma migrate dev --name init

You can optionally give your migration a name, depending on the changes you made. Since this is the project’s first migration, you’re setting the --name flag to “init”. If everything works correctly, you should get the following message in your terminal:

Your database is now in sync with your schema.
:heavy_check_mark: Generated Prisma Client (4.x.x) to ./node_modules/@prisma/client in 111ms

This will create a prisma/migrations folder inside your prisma directory and synchronize your Prisma schema with your database schema.

Note: If you want to skip the process of creating a migration history, you can use the prisma db push command instead of prisma migrate dev. However, we recommend using prisma migrate dev to evolve your database schema in development. If you would like to get a conceptual overview of how Prisma Migrate works and which commands to use in what environment, refer to this page in the Prisma documentation.

If you go to your Supabase project, in the table editor, you should see that two tables have been created, a Post, User, and _prisma_migrations tables. The _prisma_migrations table is used to keep

That’s it! You have now successfully connected a Prisma project to a PostgreSQL database hosted on Supabase and ran your first migration.

Connection pooling with Supabase#

If you’re working in a serverless environment (for example Node.js functions hosted on AWS Lambda, Vercel or Netlify Functions), you need to set up connection pooling using a tool like PgBouncer. That’s because every function invocation may result in a new connection to the database.

Supabase supports connection management using PgBouncer which prevents a traffic spike from overwhelming your database.

Go to the Database page from the sidebar in the Supabase dashboard and navigate to Connection pool settings:

When updating your database schema, you need to use the non-pooled connection URL (like the one used in step 1). You can configure the non-pooled connection string by using the directUrl property in the datasource block.

Update your .env file with the following changes:

Rename the DATABASE_URL environment variable to DIRECT_URL
Create a DATABASE_URL environment variable and paste in the new connection string from the dashboard as its value

Append the ?pgbouncer=true flag to the DATABASE_URL variable.

Your .env file should resemble the following:

# .env
# PostgreSQL connection string used for migrations
DIRECT_URL="postgres://postgres:[YOUR-PASSWORD]@db.[YOUR-PROJECT-REF].supabase.co:5432/postgres"
# PostgreSQL connection string with pgBouncer config — used by Prisma Client
DATABASE_URL="postgres://postgres:[YOUR-PASSWORD]@db.[YOUR-PROJECT-REF].supabase.co:6543/postgres?pgbouncer=true"

Update your Prisma schema by setting the directUrl in the datasource block:

datasource db {
  provider          = "postgresql"
  url               = env("DATABASE_URL")
  directUrl         = env("DIRECT_URL")
}

Note: This feature is available from Prisma version 4.10.0 and higher.

If you want to learn more about Prisma, check out the docs. Also in case you have any questions or run into any issue, feel free to start a discussion in the repo’s discussions section.

Troubleshooting#

Missing grants#

If your database schema is out of sync from your migration history, prisma migrate dev will detect a migration history conflict or a schema drift. When prisma migrate dev detects the drift, it might ask to to reset your database schema. If you choose yes, it will delete the public schema along with the default grants defined in your database.

If you run into this problem, create a draft migration using prisma migrate dev --create-only, and add the following helper SQL:

grant usage on schema public to postgres, anon, authenticated, service_role;

grant all privileges on all tables in schema public to postgres, anon, authenticated, service_role;
grant all privileges on all functions in schema public to postgres, anon, authenticated, service_role;
grant all privileges on all sequences in schema public to postgres, anon, authenticated, service_role;

alter default privileges in schema public grant all on tables to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on functions to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on sequences to postgres, anon, authenticated, service_role;

Run prisma migrate dev to apply the draft migration to the database.

Using Supabase Auth with Prisma#

If you would like to use Supabase Auth and Prisma in your application, you will have to enable the multiSchema Preview feature flag in the generator block of your Prisma schema:

datasource db {
  provider          = "postgresql"
  url               = env("DATABASE_URL")
  directURL         = env("DIRECT_URL")
}

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

Next, specify the database schemas you would like to include in your Prisma schema:

datasource db {
  provider          = "postgresql"
  url               = env("DATABASE_URL")
  directURL         = env("DIRECT_URL")
  schemas           = ["public", "auth"]
}

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

You can then specify what schema a model or enum belongs to using the @@schema attribute:

model User {
  id     Int     @id
  // ...

  @@schema("auth") // or @@schema("public")
}

To learn more about using Prisma with multiple database schemas, refer to this page in the Prisma docs.

Using PostgreSQL Row Level Security with Prisma#

If you would like to use Row Level Security (RLS) with Prisma, check out the Prisma Client Extension - Row Level Security example that provides the primitives you could use to build and extend Prisma Client in PostgreSQL.

Also check out useSupabaseRowLevelSecurity Prisma Client extension that supports Supabase RLS and policies written to use Supabase auth.

The example and extension use Prisma Client extensions Preview feature.

Enabling PosgreSQL extensions#

If you would like to use a PostgreSQL extension with Prisma, enable the postgresqlExtensions Preview feature flag in the generator block of your Prisma schema:

datasource db {
  provider          = "postgresql"
  url               = env("DATABASE_URL")
  directURL         = env("DIRECT_URL")
}

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["postgresqlExtensions"]
}

Next, specify the extensions you need in the datasource block:

datasource db {
  provider          = "postgresql"
  url               = env("DATABASE_URL")
  directURL         = env("DIRECT_URL")
  extensions        = [hstore(schema: "myHstoreSchema"), pg_trgm, postgis(version: "2.1")]
}

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["postgresqlExtensions"]
}

To learn more about using Prisma with PostgreSQL extensions, refer to this page in the Prisma docs.

Resources#

Prisma official website.
Prisma GitHub.
Prisma documentation.

Edit this page on GitHub