This guide explains how to quickly connect the Postgres database provided by Supabase to a Prisma project.
- 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.
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.
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 with the repo from the quickstart guide.
Navigate into a directory of your choice and run the following command in your terminal if you’re on a Windows machine:
And if you’re using Mac OS or Linux, run the following command:
You can now navigate into the directory and install the project’s dependencies:
This project comes with TypeScript configured and has the following structure.
prismadirectory which contains:
dev.dbfile: This is a SQLite database.
schema.prismafile: Where we define the different database models and relations between them.
.envfile: Contains the
DATABASE_URLvariable, which Prisma will use.
script.tsfile: where we 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.
Go ahead and delete the
prisma/dev.db file because we will be switching to PostgreSQL.
Next, inside the
prisma/.env file, update the value of the
DATABASE_URL variable to the connection string you got in step 3. The URL might look as follows:
Finally, inside your
schema.prisma file, change the
provider from “sqlite” to
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
db pushcommand instead of
migrate dev. If you go to your Supabase project, in the table editor, you should see that two tables have been created, a
Usertable. That’s it! You have now successfully connected a Prisma project to a PostgreSQL database hosted on Supabase and ran your first migration.
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 support connection management using PgBouncer and are enabled by default.
Go to the Database page from the sidebar in the Supabase dashboard and navigate to connection pool settings
When running migrations you need to use the non pooled connection URL (like the one we used in step 4). However, when deploying your app, you’ll use the pooled connection URL. and add the
?pgbouncer=true flag to the PostgreSQL connection URL. The URL might look as follows:
Prisma Migrate uses database transactions to check out the current state of the database and the migrations table. However, the Migration Engine is designed to use a single connection to the database, and does not support connection pooling with PgBouncer. If you attempt to run Prisma Migrate commands in any environment that uses PgBouncer for connection pooling, you might see the following error:
This is a known issue and it is being worked on, you can follow the progress on this GitHub issue. 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.