Connecting Express to PostgreSQL with Prisma ORM

Setting Up Prisma with PostgreSQL

Prisma is a type-safe ORM for Node.js with an excellent schema-first workflow. You define your data model once; Prisma generates a fully-typed client and handles migrations.

Installation and Setup

npm install @prisma/client
npm install --save-dev prisma
npx prisma init

This creates prisma/schema.prisma and adds DATABASE_URL to .env. Set your connection string:

DATABASE_URL="postgresql://user:password@localhost:5432/mydb"

Define a Model and Generate Client

// prisma/schema.prisma
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())
}

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  author   User   @relation(fields: [authorId], references: [id])
  authorId Int
}

npx prisma migrate dev --name init
npx prisma generate

PrismaClient Singleton

// lib/prisma.js — prevent multiple instances in development
const { PrismaClient } = require('@prisma/client');
const prisma = global.prisma || new PrismaClient();
if (process.env.NODE_ENV !== 'production') global.prisma = prisma;
module.exports = prisma;