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

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.