Using Prisma ORM with PostgreSQL in Node.js: A Complete Beginner's Guide

14/07/2025

Using Prisma ORM with PostgreSQL in Node.js: A Complete Beginner's Guide

Learn how to integrate Prisma ORM with PostgreSQL in a Node.js application. This step-by-step guide covers database setup, schema modeling, migrations, and querying with practical examples.

Using Prisma ORM with PostgreSQL in Node

Modern database access for your Node.js applications.

Interacting with databases is a core task for almost every backend application. While traditional ORMs (Object-Relational Mappers) have been around for a while, **Prisma** has emerged as a powerful, next-generation ORM that offers type-safety, an intuitive schema definition language, and powerful migrations. When combined with Node.js and PostgreSQL, Prisma provides an excellent developer experience for building robust and scalable APIs. This guide will walk you through setting up Prisma with a PostgreSQL database in a Node.js project, covering schema definition, migrations, and basic CRUD operations.

Why Prisma?

  • Type-Safety: Generates a type-safe Prisma Client based on your database schema, providing auto-completion and compile-time error checking.
  • Intuitive Schema: Define your database schema using Prisma's declarative Schema Definition Language (SDL).
  • Powerful Migrations: Prisma Migrate makes database schema evolution easy and reliable.
  • Developer Experience: Excellent tooling, including Prisma Studio for data browsing.
  • Performance: Optimized query engine for efficient database interactions.

Prerequisites

  • Node.js and npm (or yarn) installed.
  • PostgreSQL database running (locally or hosted).
  • Basic understanding of JavaScript and Node.js.

1. Project Setup

Create a new Node.js project and install `express` (for our API) and `dotenv` (for environment variables). 

# Create a new project directory
mkdir node-prisma-pg
cd node-prisma-pg

# Initialize npm project
npm init -y

# Install core dependencies
npm install express dotenv

2. Initialize Prisma

Now, install Prisma CLI as a development dependency and initialize Prisma in your project. 

# Install Prisma CLI
npm install prisma --save-dev

# Initialize Prisma (creates prisma/schema.prisma and .env)
npx prisma init

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

3. Configure Database Connection

Open your `.env` file and set your PostgreSQL database connection string. 

# .env
DATABASE_URL="postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=public"

# Example for local PostgreSQL:
# DATABASE_URL="postgresql://postgres:mysecretpassword@localhost:5432/mydatabase?schema=public"

Next, open `prisma/schema.prisma` and ensure your `datasource` block is configured for PostgreSQL. 

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// Define your models here...

4. Define Your Prisma Schema (Models)

Define your data models in `prisma/schema.prisma`. Let's create a `User` model. 

// prisma/schema.prisma (updated)
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  posts     Post[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

5. Run Migrations

Apply your schema changes to the database using Prisma Migrate. This will create the tables. 

npx prisma migrate dev --name init

This command will:

  • Create a new migration folder with SQL files.
  • Apply the migration to your database.
  • Generate Prisma Client (the type-safe query builder).

6. Use Prisma Client for CRUD Operations

Now you can use the generated Prisma Client to interact with your database. Create `index.js` (or `app.js`) for your Express server. 

// index.js
require('dotenv').config();
const express = require('express');
const { PrismaClient } = require('@prisma/client');

const prisma = new PrismaClient();
const app = express();
const PORT = process.env.PORT || 3000;

app.use(express.json()); // Middleware to parse JSON bodies

// --- CRUD Operations for Users ---

// Create a new user (POST /users)
app.post('/users', async (req, res) => {
  try {
    const { email, name } = req.body;
    const user = await prisma.user.create({
      data: { email, name },
    });
    res.status(201).json(user);
  } catch (error) {
    res.status(400).json({ error: 'Could not create user', details: error.message });
  }
});

// Get all users (GET /users)
app.get('/users', async (req, res) => {
  try {
    const users = await prisma.user.findMany({
      include: { posts: true }, // Include user's posts
    });
    res.status(200).json(users);
  } catch (error) {
    res.status(500).json({ error: 'Could not fetch users', details: error.message });
  }
});

// Get a single user by ID (GET /users/:id)
app.get('/users/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const user = await prisma.user.findUnique({
      where: { id: parseInt(id) },
      include: { posts: true },
    });
    if (!user) return res.status(404).json({ error: 'User not found' });
    res.status(200).json(user);
  } catch (error) {
    res.status(500).json({ error: 'Could not fetch user', details: error.message });
  }
});

// Update a user (PUT /users/:id)
app.put('/users/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { email, name } = req.body;
    const user = await prisma.user.update({
      where: { id: parseInt(id) },
      data: { email, name },
    });
    res.status(200).json(user);
  } catch (error) {
    res.status(400).json({ error: 'Could not update user', details: error.message });
  }
});

// Delete a user (DELETE /users/:id)
app.delete('/users/:id', async (req, res) => {
  try {
    const { id } = req.params;
    await prisma.user.delete({
      where: { id: parseInt(id) },
    });
    res.status(204).send(); // No content
  } catch (error) {
    res.status(400).json({ error: 'Could not delete user', details: error.message });
  }
});

// --- CRUD Operations for Posts (Example) ---

// Create a new post for a user (POST /users/:userId/posts)
app.post('/users/:userId/posts', async (req, res) => {
  try {
    const { userId } = req.params;
    const { title, content, published } = req.body;
    const post = await prisma.post.create({
      data: {
        title,
        content,
        published,
        author: { connect: { id: parseInt(userId) } },
      },
    });
    res.status(201).json(post);
  } catch (error) {
    res.status(400).json({ error: 'Could not create post', details: error.message });
  }
});

// Disconnect Prisma Client when the app closes
process.on('beforeExit', async () => {
  await prisma.$disconnect();
});

// Start the server
app.listen(PORT, () => {
  console.log(`Server running on http://localhost:${PORT}`);
});

7. Running and Testing Your API

Start your Node.js server: 

node index.js

You can now use tools like cURL, Postman, or Insomnia to test your API endpoints.

Example cURL Commands:

# Create User
curl -X POST -H "Content-Type: application/json" -d '{"email": "john.doe@example.com", "name": "John Doe"}' http://localhost:3000/users

# Get All Users
curl http://localhost:3000/users

# Create Post for User (replace <USER_ID>)
curl -X POST -H "Content-Type: application/json" -d '{"title": "My First Post", "content": "Hello world!", "published": true}' http://localhost:3000/users/<USER_ID>/posts

# Get Single User with Posts (replace <USER_ID>)
curl http://localhost:3000/users/<USER_ID>

# Update User (replace <USER_ID>)
curl -X PUT -H "Content-Type: application/json" -d '{"name": "Johnathan Doe"}' http://localhost:3000/users/<USER_ID>

# Delete User (replace <USER_ID>)
curl -X DELETE http://localhost:3000/users/<USER_ID>

Prisma ORM offers a modern, type-safe, and developer-friendly approach to database interactions in Node.js applications. By combining it with PostgreSQL, you get a robust and scalable stack for your backend. You've learned how to set up Prisma, define your schema, run migrations, and perform essential CRUD operations. This foundation empowers you to build complex data models and interact with your database efficiently, making your Node.js development experience more enjoyable and productive.

Managing databases in Node.js applications is easier and more efficient with modern ORMs like Prisma. Prisma offers a type-safe, auto-completing, and developer-friendly way to interact with relational databases—especially PostgreSQL.

In this tutorial, you'll learn how to set up Prisma ORM with PostgreSQL in a Node.js project. We’ll walk through the entire setup process: initializing Prisma, defining your schema, running migrations, and performing common database operations like create, read, update, and delete.

This guide is perfect for developers looking to streamline their database workflows with cleaner code, improved type safety, and strong tooling. Whether you're new to PostgreSQL or switching from another ORM, Prisma makes working with databases more intuitive and maintainable.

Next Blog

How to Rate Limit API Requests in Express: Protect Your Node.js Server

How to Rate Limit API Requests in Express: Protect Your Node.js Server

Learn how to implement rate limiting in your Express.js API to prevent abuse, secure your backend, and improve performance. Includes middleware setup and real-world examples.