Back
Prisma

Prisma

Prisma
Prisma
Prisma

Overview

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.

Documentation

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.

Getting the connection string

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:


_10
curl 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:


_10
cd postgresql-supabase
_10
npm 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:


_10
touch .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

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

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


_24
datasource db {
_24
provider = "postgresql"
_24
url = env("DATABASE_URL")
_24
}
_24
_24
generator client {
_24
provider = "prisma-client-js"
_24
}
_24
_24
model Post {
_24
id Int @id @default(autoincrement())
_24
title String
_24
content String?
_24
published Boolean @default(false)
_24
author User? @relation(fields: [authorId], references: [id])
_24
authorId Int?
_24
}
_24
_24
model User {
_24
id Int @id @default(autoincrement())
_24
email String @unique
_24
name String?
_24
posts Post[]
_24
}

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


_10
npx 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:


_10
Your database is now in sync with your schema.
_10
: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 track of the migration history and ensure that the database schema stays in sync with your Prisma schema.

tables created in the UI

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 Supavisor. That’s because every function invocation may result in a new connection to the database.

Supabase supports connection management using Supavisor 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:

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:

  1. Rename the DATABASE_URL environment variable to DIRECT_URL
  2. 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. You still need ?pgbouncer=true when using Supavisor.

Also make sure to append connection_limit=1. This limits the amount of connections made from a serverless function.

Your .env file should resemble the following:

.env

_10
# PostgreSQL connection string used for migrations
_10
DIRECT_URL="postgres://postgres:[YOUR-PASSWORD]@db.[YOUR-PROJECT-REF].supabase.co:5432/postgres"
_10
_10
# PostgreSQL connection string with Supavisor config — used by Prisma Client
_10
DATABASE_URL="postgres://postgres.[YOUR-PROJECT-ID]:[YOUR-PASSWORD]@aws-0-[REGION].pooler.supabase.com:6543/postgres?pgbouncer=true&connection_limit=1"

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


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

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:


_10
grant usage on schema public to postgres, anon, authenticated, service_role;
_10
_10
grant all privileges on all tables in schema public to postgres, anon, authenticated, service_role;
_10
grant all privileges on all functions in schema public to postgres, anon, authenticated, service_role;
_10
grant all privileges on all sequences in schema public to postgres, anon, authenticated, service_role;
_10
_10
alter default privileges in schema public grant all on tables to postgres, anon, authenticated, service_role;
_10
alter default privileges in schema public grant all on functions to postgres, anon, authenticated, service_role;
_10
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:


_10
datasource db {
_10
provider = "postgresql"
_10
url = env("DATABASE_URL")
_10
directUrl = env("DIRECT_URL")
_10
}
_10
_10
generator client {
_10
provider = "prisma-client-js"
_10
previewFeatures = ["multiSchema"]
_10
}

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


_11
datasource db {
_11
provider = "postgresql"
_11
url = env("DATABASE_URL")
_11
directUrl = env("DIRECT_URL")
_11
schemas = ["public", "auth"]
_11
}
_11
_11
generator client {
_11
provider = "prisma-client-js"
_11
previewFeatures = ["multiSchema"]
_11
}

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


_10
model User {
_10
id Int @id
_10
// ...
_10
_10
@@schema("auth") // or @@schema("public")
_10
}

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:


_10
datasource db {
_10
provider = "postgresql"
_10
url = env("DATABASE_URL")
_10
directUrl = env("DIRECT_URL")
_10
}
_10
_10
generator client {
_10
provider = "prisma-client-js"
_10
previewFeatures = ["postgresqlExtensions"]
_10
}

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


_11
datasource db {
_11
provider = "postgresql"
_11
url = env("DATABASE_URL")
_11
directUrl = env("DIRECT_URL")
_11
extensions = [hstore(schema: "myHstoreSchema"), pg_trgm, postgis(version: "2.1")]
_11
}
_11
_11
generator client {
_11
provider = "prisma-client-js"
_11
previewFeatures = ["postgresqlExtensions"]
_11
}

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

Using ?pgbouncer=true with Supavisor

You still need to use ?pgbouncer=true in your pooler connection string with Supavisor. This connection string parameter tells Prisma not to use named prepared statements when querying the database.

Resources

Details

DeveloperPrisma
CategoryDevTools