Prisma

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 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

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

Cloning the starter project

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

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

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:

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

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

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

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:

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.

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:

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:

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

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

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:

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

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

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

• Prisma official website.
• Prisma GitHub.
• Prisma documentation.