DEV Community

awalias
awalias

Posted on • Updated on

Prisma + Postgres

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.

  1. 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".
  2. 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.

  1. Initialize a Node.js Project

    mkdir prisma-postgres
    cd prisma-postgres
    npm init -y
    
    
  2. Install Prisma and Postgres Client

    npm install prisma --save-dev
    npm install @prisma/client
    
    
  3. Initialize Prisma

    npx prisma init
    
    

    This command creates a prisma directory with a schema.prisma file and a .env file.

Configuring Prisma + Postgres

  1. Set Up the Database Connection

    • Open the .env file (located in the new prisma folder).
    • Add DATABASE_URL and DIRECT_URL you 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"
      
      

The direct connection (on port 5432 is required for running database migrations against the database).

  1. 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
    }
    
    ```
    

Generating the Postgres Schema

Use Prisma Migrate to apply your schema to the Postgres database on Supabase.

npx prisma migrate dev --name init

Enter fullscreen mode Exit fullscreen mode

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();
  });

Enter fullscreen mode Exit fullscreen mode

Run the script:

node script.js

Enter fullscreen mode Exit fullscreen mode

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 (30)

Collapse
 
awalias profile image
awalias • Edited

If anyone needs help regarding Prisma / Postgres you can get support in our discord

Collapse
 
joshenlim profile image
Joshen Lim

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

Collapse
 
supabase_io profile image
Supabase

Yes! Thanks for sharing @joshenlim

Collapse
 
supa_community profile image
Supabase Community

Prisma Postgres is an incredible pairing for a backend. Both tools are used and loved by the Supabase Community.

Collapse
 
timnpalmer profile image
timnpalmer

AMAZING!!

Collapse
 
steve_val profile image
Steve_Val

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

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}
Enter fullscreen mode Exit fullscreen mode

Very nice.

Collapse
 
swamp_dog profile image
swamp_dog

I was trying initially without the direct_url but apparently it's required if you need to do migrations.

Collapse
 
thorwebdev profile image
Thor ι›·η₯ž

You can also find some details on using Row Level Security (RLS) with Prisma here: supabase.com/partners/integrations...

Collapse
 
jonmeyers_io profile image
Jon

This is an absolute game changer!

Collapse
 
tylerhillery profile image
Tyler Hillery

Prisma + Supabse is an awesome combo.

Collapse
 
_tylerhillery profile image
Tyler Hillery

The discord is very welcoming to new comers!

Collapse
 
chasers profile image
Chase Granberry

πŸ’š Prisma + Supabase

Collapse
 
jgoux profile image
Julien Goux

Prisma and Supabase are really a great fit. Great combo!

Collapse
 
deji_ibrahim_6039f39d0278 profile image
Deji Ibrahim

Easy to setup πŸ‘Œ, thanks!

Collapse
 
sausagebutty profile image
David Wilson

yes yes yes

Collapse
 
sivenruot profile image
Lee AVULAT

Nice guide. I would add that if you want to use prisma to generate migrations. For those to be usable with supabase-js and postgrest I recommend to leverage the native database functions in your "id" definitions like mentioned here: github.com/supabase/cli/issues/277...

Collapse
 
joel_lee_f718aee79f12cef2 profile image
Joel Lee

Thanks, will check it out

Collapse
 
jonmeyers_io profile image
Jon

This is so sick! πŸ’―

Collapse
 
gregnr profile image
Greg Richardson

Super seamless, nice πŸ˜ƒπŸš€

Collapse
 
jessshears profile image
Jessica Shears

This is awesome!

Collapse
 
karloison_27 profile image
Karlo Ison

Awesome! 🀩

Collapse
 
supadavid profile image
SupaDavid

awesome

Collapse
 
encima_49 profile image
Chris • Edited

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?

Collapse
 
tylerfontaine profile image
Tyler Fontaine

Bingo! You got it

Collapse
 
4l3k51 profile image
Aleksi Immonen

Excellent guide !

Collapse
 
4l3k51 profile image
Aleksi Immonen

Excellent guide!

Collapse
 
davy_c1f0f5e0e5e0 profile image
Dave

This is great!

Collapse
 
timnpalmer profile image
timnpalmer

Super valuable guide, thanks

Collapse
 
w3b6x9 profile image
Wen Bo Xie

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.

Collapse
 
jonmeyers_io profile image
Jon

What an incredibly powerful stack! Prisma and Postgres powered by Supabase! πŸš€