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 init
This command creates a
prisma
directory with aschema.prisma
file and a.env
file.
Configuring Prisma + Postgres
-
Set Up the Database Connection
- Open the
.env
file (located in the newprisma
folder). -
Add
DATABASE_URL
andDIRECT_URL
you can get the connection details here:
DATABASE_URL="postgres://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres" 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
datasource
block 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 (10)
If anyone needs help regarding Prisma / Postgres you can get support in our discord
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.
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
Prisma Postgres is an incredible pairing for a backend. Both tools are used and loved by the Supabase Community.
Nice guide. I would add that if you want to use prisma to generate migrations. For those to be usable with
supabase-js
andpostgrest
I recommend to leverage thenative database functions
in your "id" definitions like mentioned here: github.com/supabase/cli/issues/277...Excellent guide !
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?
Excellent guide!