In this tutorial, we'll explore how to set up Prisma + Postgres. We'll use real-world examples involving books and authors.
Prerequisites for Prisma + Postgres
Before we begin, ensure you have the following installed:
- Node.js (v14 or later)
- npm or yarn
- An account on Supabase
Setting Up Prisma + Postgres
Supabase provides a hosted Postgres database for use with Prisma, making it easy to get started without setting up your own database server. Supabase is quick and easy to get started, and is a cost effective way to scale up to millions of users if your project gets traction.
-
Create a New Supabase Project
- Sign in to your Supabase account.
- Click on "New Project".
- Fill in the project details and click "Create new project".
-
Retrieve Database Connection Details
- In the navigation bar, go to "Project Settings" > "Database".
-
Copy the Connection string (URI) for Postgres. You’ll need it in the next step:
postgres://postgres.[ref]:[password]@[region].pooler.supabase.com:6543/postgres
Setting Up Prisma in Your Project
Prisma is an ORM that simplifies database interactions in Node.js applications.
-
Initialize a Node.js Project
mkdir prisma-postgres cd prisma-postgres npm init -y -
Install Prisma and Postgres Client
npm install prisma --save-dev npm install @prisma/client -
Initialize Prisma
npx prisma initThis command creates a
prismadirectory with aschema.prismafile and a.envfile.
Configuring Prisma + Postgres
-
Set Up the Database Connection
- Open the
.envfile (located in the newprismafolder). -
Add
DATABASE_URLandDIRECT_URLyou can get the connection details here:
DATABASE_URL="postgres://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres?pgbouncer=true" DIRECT_URL="postgres://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:5432/postgres"
- Open the
The direct connection (on port 5432 is required for running database migrations against the database).
-
Define the Data Model
- Open
prisma/schema.prisma. -
Update the
datasourceblock to include DIRECT_URL like so:
datasource db { provider = "postgresql" url = env("DATABASE_URL") directUrl = env("DIRECT_URL") }
Add your data models. For a library system:
``` model Author { id Int @id @default(autoincrement()) name String books Book[] } model Book { id Int @id @default(autoincrement()) title String author Author @relation(fields: [authorId], references: [id]) authorId Int } ``` - Open
Generating the Postgres Schema
Use Prisma Migrate to apply your schema to the Postgres database on Supabase.
npx prisma migrate dev --name init
This command will:
- Generate migration files.
- Apply the migration to the Postgres database.
- Generate the Prisma Client.
Using Prisma Client to Interact with Prisma + Postgres
Create a script.js file to test database operations.
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();
async function main() {
// Create a new author
const author = await prisma.author.create({
data: {
name: 'Yevgeny Zamyatin',
},
});
// Create a new book
const book = await prisma.book.create({
data: {
title: 'We',
authorId: author.id,
},
});
// Retrieve all books with their authors
const books = await prisma.book.findMany({
include: { author: true },
});
console.log(books);
}
main()
.catch((e) => console.error(e))
.finally(async () => {
await prisma.$disconnect();
});
Run the script:
node script.js
You should see an array of books with their associated authors logged to the console.
Conclusion
Congratulations! You've set up Prisma with Postgres. This setup allows you to interact with your Postgres database seamlessly using Prisma.
By following this guide, you've taken the first steps toward building robust applications with Prisma and Postgres.
Top comments (47)
If anyone needs help regarding Prisma / Postgres you can get support in our discord
Hopefully this might be helpful - there's a set of handy instructions on your projects' home page if you click the "Connect" button and select the "ORMs" tab
Yes! Thanks for sharing @joshenlim
The memes in there are crazy lol
Also note for anyone else who's wondering the official Prisma documentation related to this is here: prisma.io/docs/orm/overview/databa...
The important step adding
Very nice.
I was trying initially without the direct_url but apparently it's required if you need to do migrations.
Prisma Postgres is an incredible pairing for a backend. Both tools are used and loved by the Supabase Community.
AMAZING!!
You can also find some details on using Row Level Security (RLS) with Prisma here: supabase.com/partners/integrations...
This is an absolute game changer!
Prisma + Supabse is an awesome combo.
The discord is very welcoming to new comers!
💚 Prisma + Supabase
Prisma and Supabase are really a great fit. Great combo!
Easy to setup 👌, thanks!
yes yes yes
Nice guide. I would add that if you want to use prisma to generate migrations. For those to be usable with
supabase-jsandpostgrestI recommend to leverage thenative database functionsin your "id" definitions like mentioned here: github.com/supabase/cli/issues/277...Thanks, will check it out
This is so sick! 💯
I know a lot of apps built with this combination!
Super seamless, nice 😃🚀
Prisma + Supabase is the perfect combo.
Awesome! 🤩
awesome
This is great!!
This is awesome!
Supabase and Prisma is the perfect match for us, it makes life so easy to build things quickly.
Supabase and Prisma makes development so much easier. Love this combo
Nice share!
Brilliant, thank you!
Thank you so much, this is what I was looking for. A great guide to setup Prisma with Postgres.
I was just trying out setting up Prisma with Postgres and found this guide. Thanks for writing this.
That is awesome!
Thank you!
Thankyou!
This is amazing! 💪💪
Always happy to help
Thank you so much. I wish I had known how easy this was in the past. Grateful for this resource!
thanks for this! If I am using a pooler like Supavisor does that mean I do not use Accelerate or anything, just the Prisma library and Supabase handles the rest?
Bingo! You got it
Excellent guide !
Thanks for the starter tutorial -- it's clear and simple. Things can get tricker if you modify the schema and want to actually push to production:
prisma migrate devis only for dev environment. Usenpx prisma migrate deployfor production and testing (CI/CD pipelines). Docsnpx prisma generateto generate new client code whenever you modify the schema as well.Also, I noticed that Prisma doesn't really support rolling back schema changes. So to revert changes you need to revert your schema file and generate a "new" migration/client to undo the previous change instead.
Excellent guide!
This is great!
Excited to use Prisma and Supabase together! This makes it so easy to get started and distinguish between which database connection strings to use and why.
What an incredibly powerful stack! Prisma and Postgres powered by Supabase! 🚀
Super valuable guide, thanks
This is great! Easy to setup.