Back
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 and 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 database settings page. In this example, we are going to connect via the connection pooler. If your network supports IPv6, you can connect to the database directly without using the connection pooler.

We will use the pooler both in Transaction and Session mode. Transaction mode is used for application queries and Session mode is used for running migrations with Prisma.

To do this, set the connection mode to Transaction in the database settings page and copy the connection string and append ?pgbouncer=true&connection_limit=1. pgbouncer=true disables Prisma from generating prepared statements. This is required since our connection pooler does not support prepared statements in transaction mode yet. The connection_limit=1 parameter is only required if you are using Prisma from a serverless environment. For more information on these parameters, refer to the Troubleshooting section below. If your database is Postgres 14 and above, the Transaction connection pooler string will look like this


_10
postgres://[db-user].[project-ref]:[db-password]@aws-0-[aws-region].pooler.supabase.com:6543/[db-name]?pgbouncer=true&connection_limit=1

To get the Session connection pooler string, change the port to 5432. If your database is Postgres 14 and above, it will look like this


_10
postgres://[db-user].[project-ref]:[db-password]@aws-0-[aws-region].pooler.supabase.com:5432/[db-name]

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="" # Set this to the Transaction connection pooler string you copied in Step 1
_10
DIRECT_URL="" # Set this to the Session connection pooler string you copied in Step 1

Update your Prisma schema by setting the directUrl in the datasource block. This is what your schema.prisma file should look like:


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

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.

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.

Prepared statements error

PostgreSQL supports prepared statements. They are pre-parsed queries that can be quickly modified for re-use. So, if you were executing the same query over-and-over, but only changing the arguments or some other small aspect, prepared statements offer performance benefits. Prisma will try to create prepared statements in the background, but Supavisor does not support them and leads to the following error


_10
> “prepared statement \“s#\” does not exist”

To prevent Supavisor from erroring, it is necessary to add the following query parameter to your connection strings: pgbouncer=true to the Transaction connection pooler string as mentioned in Step 1.

Prisma uses the parameters for adjusting its configuration settings. The pgbouncer parameter tells Prisma to not attempt to create prepared statements. The parameter is called pgbouncer because the most popular pooler for PostgreSQL is PGbouncer and it does not support prepared statements. The Prisma Team specifically made the configuration to reflect that, but the parameter works for Supavisor, too.

Connection limits

Prisma offers a guide for optimizing the amount of permissible connections in a serverless environment. The general recommendation for the transaction connection string is to start with connection_limit=1, and if you do not experience timeout waiting | no slots available errors, try increasing the number by 1 or 2. Under-the-hood, some serverless deployments may create helper functions that also try to connect to the database. So if the connection limit is set to 3, and then 2 helper functions are invoked, a single serverless function call could exhaust 9 connections.

Resources

Details

DeveloperPrisma
CategoryDevTools