How to Use Prisma ORM with PostgreSQL (Complete 2026 Tutorial)

I've used every major Node.js ORM at some point — Sequelize, TypeORM, Knex, Drizzle, and Prisma. After years of fighting with Sequelize's confusing association model and TypeORM's decorator-heavy approach, Prisma felt like a genuine improvement when I first tried it. The schema-first approach, the generated types, and the readable query API made a real difference in day-to-day development.

This tutorial covers everything you need to go from zero to a working Prisma + PostgreSQL setup: schema definition, migrations, CRUD, relations, and the scenarios where you'll want to reach for raw SQL.

Setting Up Prisma

mkdir prisma-demo && cd prisma-demo
npm init -y
npm install prisma @prisma/client
npm install -D typescript ts-node @types/node
npx tsc --init
npx prisma init --datasource-provider postgresql

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

Update your .env:

DATABASE_URL="postgresql://username:password@localhost:5432/prisma_demo?schema=public"

Defining Your Schema

The Prisma schema file is the heart of the whole system. You define your models here, and Prisma generates both the migration SQL and the TypeScript types from it.

// 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
  role      Role     @default(USER)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  posts    Post[]
  profile  Profile?
  comments Comment[]

  @@index([email])
  @@map("users")
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  avatar String?
  userId Int     @unique
  user   User    @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("profiles")
}

model Post {
  id          Int       @id @default(autoincrement())
  title       String
  content     String
  published   Boolean   @default(false)
  authorId    Int
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  author   User      @relation(fields: [authorId], references: [id])
  comments Comment[]
  tags     Tag[]     @relation("PostTags")

  @@index([authorId])
  @@index([published, createdAt(sort: Desc)])
  @@map("posts")
}

model Comment {
  id        Int      @id @default(autoincrement())
  body      String
  postId    Int
  authorId  Int
  createdAt DateTime @default(now())

  post   Post @relation(fields: [postId], references: [id], onDelete: Cascade)
  author User @relation(fields: [authorId], references: [id])

  @@index([postId])
  @@map("comments")
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[] @relation("PostTags")

  @@map("tags")
}

enum Role {
  USER
  EDITOR
  ADMIN
}

A few things worth noting in this schema:

@updatedAt automatically updates the field on every save — no trigger needed
@@map("users") lets you use Pascal-case model names in Prisma while keeping snake_case table names in the database
The compound index on [published, createdAt(sort: Desc)] is important for the common query pattern of fetching published posts sorted by date
onDelete: Cascade on the Profile relation means deleting a user automatically deletes their profile

Running Migrations

With your schema defined, create and run a migration:

# Create a migration file and apply it to your dev database
npx prisma migrate dev --name init

# Preview the SQL without applying (useful for review)
npx prisma migrate dev --create-only --name add-user-profile

# Apply pending migrations in production
npx prisma migrate deploy

The generated migration file lives in prisma/migrations/ and should be committed to git. This gives you a full history of schema changes, and prisma migrate deploy is idempotent — safe to run on every deployment.

# Generate (or regenerate) the Prisma Client after schema changes
npx prisma generate

# View your data in Prisma Studio (GUI)
npx prisma studio

Initializing the Prisma Client

// src/db.ts
import { PrismaClient } from '@prisma/client';

// Global singleton pattern — important in development with hot reloading
// (prevents creating multiple Prisma Client instances)
const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: process.env.NODE_ENV === 'development'
      ? ['query', 'error', 'warn']
      : ['error'],
  });

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}

CRUD Operations

Prisma's query API is where the generated types really shine — your IDE knows exactly what fields are available on every model.

// src/examples/crud.ts
import { prisma } from './db';

// CREATE — create a user with a profile in one transaction
async function createUser(email: string, name: string, bio?: string) {
  const user = await prisma.user.create({
    data: {
      email,
      name,
      profile: bio ? {
        create: { bio },
      } : undefined,
    },
    include: {
      profile: true,
    },
  });
  return user;
}

// READ — find a single user by email
async function getUserByEmail(email: string) {
  return prisma.user.findUnique({
    where: { email },
    include: {
      profile: true,
      posts: {
        where: { published: true },
        orderBy: { createdAt: 'desc' },
        take: 5,
        select: { id: true, title: true, createdAt: true },
      },
    },
  });
}

// READ — paginated list with filters
async function getPublishedPosts(page: number, limit: number, tag?: string) {
  const where = {
    published: true,
    ...(tag && {
      tags: { some: { name: tag } },
    }),
  };

  const [posts, total] = await prisma.$transaction([
    prisma.post.findMany({
      where,
      orderBy: { createdAt: 'desc' },
      skip: (page - 1) * limit,
      take: limit,
      include: {
        author: { select: { id: true, name: true } },
        tags: { select: { name: true } },
        _count: { select: { comments: true } },
      },
    }),
    prisma.post.count({ where }),
  ]);

  return {
    posts,
    pagination: { page, limit, total, totalPages: Math.ceil(total / limit) },
  };
}

// UPDATE — update specific fields
async function publishPost(postId: number, authorId: number) {
  return prisma.post.update({
    where: {
      id: postId,
      authorId, // Ownership check built into the query
    },
    data: {
      published: true,
      updatedAt: new Date(),
    },
  });
}

// DELETE — with cascade (profile is auto-deleted via DB constraint)
async function deleteUser(userId: number) {
  return prisma.user.delete({
    where: { id: userId },
  });
}

Notice the ownership check in publishPost — the authorId in the where clause means Prisma will return null (and throw a RecordNotFound error) if the post exists but belongs to a different user. This is a clean way to enforce authorization at the query level. Our API security guide covers why this ownership check is critical.

Working with Relations

// Many-to-many: add/remove tags from a post
async function addTagsToPost(postId: number, tagNames: string[]) {
  return prisma.post.update({
    where: { id: postId },
    data: {
      tags: {
        connectOrCreate: tagNames.map(name => ({
          where: { name },
          create: { name },
        })),
      },
    },
    include: { tags: true },
  });
}

// Nested writes: create a post with tags and an initial comment
async function createPostWithContent(
  authorId: number,
  title: string,
  content: string,
  tags: string[],
) {
  return prisma.post.create({
    data: {
      title,
      content,
      authorId,
      tags: {
        connectOrCreate: tags.map(name => ({
          where: { name },
          create: { name },
        })),
      },
    },
    include: {
      author: { select: { name: true } },
      tags: true,
    },
  });
}

Raw SQL When You Need It

Prisma's query builder covers most cases, but complex analytics queries sometimes need raw SQL:

// Raw query with parameterized inputs (safe from SQL injection)
async function getTopAuthors(limit: number) {
  const result = await prisma.$queryRaw<Array<{
    id: number;
    name: string;
    post_count: bigint;
    avg_comments: number;
  }>>`
    SELECT 
      u.id,
      u.name,
      COUNT(p.id) AS post_count,
      AVG(comment_counts.cnt) AS avg_comments
    FROM users u
    LEFT JOIN posts p ON p.author_id = u.id AND p.published = true
    LEFT JOIN (
      SELECT post_id, COUNT(*) AS cnt 
      FROM comments 
      GROUP BY post_id
    ) comment_counts ON comment_counts.post_id = p.id
    GROUP BY u.id, u.name
    HAVING COUNT(p.id) > 0
    ORDER BY post_count DESC
    LIMIT ${limit}
  `;

  // Convert BigInt to number for JSON serialization
  return result.map(r => ({ ...r, post_count: Number(r.post_count) }));
}

The template literal syntax for $queryRaw automatically parameterizes interpolated values — ${limit} becomes $1 in the actual SQL, preventing injection.

ORM Comparison Table

Feature	Prisma	TypeORM	Drizzle
Type safety	Excellent (auto-generated)	Good (decorators)	Excellent (schema-derived)
Schema approach	Schema file (schema.prisma)	Decorator classes	TypeScript schema
Migrations	Automatic (migrate dev)	Manual or sync	Manual (drizzle-kit)
Query builder	Fluent object API	QueryBuilder + Repository	SQL-like builder
Raw SQL support	$queryRaw, $executeRaw	query()	sql template tag
Performance (N+1)	Requires care	Requires care	Excellent (SQL-explicit)
Bundle size	Larger (generated client)	Medium	Very small
Learning curve	Low–Medium	Medium–High	Low (if you know SQL)
Serverless support	Via Prisma Accelerate	Limited	Excellent
Maturity	High	High	Growing fast

Drizzle deserves a mention here: it's the fastest-growing ORM in the Node.js ecosystem as of 2026, particularly popular for serverless and edge deployments because of its tiny bundle size and SQL-explicit API. If you're comfortable with SQL, Drizzle's approach is genuinely appealing. Prisma remains the better choice when you want the highest-possible type safety with minimal SQL knowledge.

Prisma's official documentation is among the best in the ORM space — the "Concepts" section in particular is worth reading.

Error Handling

import { PrismaClientKnownRequestError } from '@prisma/client/runtime/library';

async function safeCreateUser(email: string, name: string) {
  try {
    return await prisma.user.create({ data: { email, name } });
  } catch (err) {
    if (err instanceof PrismaClientKnownRequestError) {
      // P2002 — unique constraint violation
      if (err.code === 'P2002') {
        throw new Error(`Email ${email} is already in use`);
      }
      // P2025 — record not found (update/delete on nonexistent record)
      if (err.code === 'P2025') {
        throw new Error('Record not found');
      }
    }
    throw err; // Re-throw unknown errors
  }
}

Prisma's error codes are documented and stable — building your error handling around PrismaClientKnownRequestError and its code field is reliable. Our Python error handling guide covers similar patterns for database errors in Python — the principles translate.

Wrapping Up

Prisma has earned its place as the most popular ORM in the Node.js ecosystem through genuine quality. The schema-first workflow, auto-generated types, readable query API, and solid migration tooling solve real problems. The trade-offs — slightly larger bundle size, some limitations on very complex queries, connection pooling nuances in serverless — are manageable and well-documented.

For most TypeScript + Node.js projects using PostgreSQL, Prisma is the right default choice. Check Prisma's documentation for PostgreSQL-specific configuration options, particularly around connection pooling for production deployments.

Pair Prisma with a solid database foundation — our PostgreSQL vs MySQL vs SQLite comparison covers why PostgreSQL is the right database to start with, and the REST vs GraphQL guide helps you think through the API layer that sits on top of your Prisma data access layer.

Frequently Asked Questions

Is Prisma good for production applications?

Yes — Prisma is widely used in production by companies of various sizes. Its type-safety, excellent TypeScript integration, and migration tooling make it particularly strong for TypeScript-first teams. The main production consideration is connection pooling: Prisma Accelerate or a separate PgBouncer instance is recommended for high-concurrency serverless deployments.

What is the difference between Prisma Migrate and db push?

Prisma Migrate generates versioned migration files that you commit to git, giving you a full history of schema changes. It's designed for production workflows. db push applies schema changes directly without creating migration files — useful for rapid prototyping, but not recommended for production because changes aren't tracked or reviewable.

Can Prisma handle complex raw SQL queries?

Yes. When Prisma's query API can't express what you need, you can use prisma.$queryRaw and prisma.$executeRaw for raw SQL with parameterized queries. This gives you full SQL expressiveness as an escape hatch without abandoning type safety for simpler queries.

Setting Up Prisma

mkdir prisma-demo && cd prisma-demo
npm init -y
npm install prisma @prisma/client
npm install -D typescript ts-node @types/node
npx tsc --init
npx prisma init --datasource-provider postgresql

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

Update your .env:

DATABASE_URL="postgresql://username:password@localhost:5432/prisma_demo?schema=public"

Defining Your Schema

The Prisma schema file is the heart of the whole system. You define your models here, and Prisma generates both the migration SQL and the TypeScript types from it.

// 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
  role      Role     @default(USER)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  posts    Post[]
  profile  Profile?
  comments Comment[]

  @@index([email])
  @@map("users")
}

model Profile {
  id     Int     @id @default(autoincrement())
  bio    String?
  avatar String?
  userId Int     @unique
  user   User    @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("profiles")
}

model Post {
  id          Int       @id @default(autoincrement())
  title       String
  content     String
  published   Boolean   @default(false)
  authorId    Int
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  author   User      @relation(fields: [authorId], references: [id])
  comments Comment[]
  tags     Tag[]     @relation("PostTags")

  @@index([authorId])
  @@index([published, createdAt(sort: Desc)])
  @@map("posts")
}

model Comment {
  id        Int      @id @default(autoincrement())
  body      String
  postId    Int
  authorId  Int
  createdAt DateTime @default(now())

  post   Post @relation(fields: [postId], references: [id], onDelete: Cascade)
  author User @relation(fields: [authorId], references: [id])

  @@index([postId])
  @@map("comments")
}

model Tag {
  id    Int    @id @default(autoincrement())
  name  String @unique
  posts Post[] @relation("PostTags")

  @@map("tags")
}

enum Role {
  USER
  EDITOR
  ADMIN
}

A few things worth noting in this schema:

@updatedAt automatically updates the field on every save — no trigger needed
@@map("users") lets you use Pascal-case model names in Prisma while keeping snake_case table names in the database
The compound index on [published, createdAt(sort: Desc)] is important for the common query pattern of fetching published posts sorted by date
onDelete: Cascade on the Profile relation means deleting a user automatically deletes their profile

Running Migrations

With your schema defined, create and run a migration:

# Create a migration file and apply it to your dev database
npx prisma migrate dev --name init

# Preview the SQL without applying (useful for review)
npx prisma migrate dev --create-only --name add-user-profile

# Apply pending migrations in production
npx prisma migrate deploy

# Generate (or regenerate) the Prisma Client after schema changes
npx prisma generate

# View your data in Prisma Studio (GUI)
npx prisma studio

Initializing the Prisma Client

// src/db.ts
import { PrismaClient } from '@prisma/client';

// Global singleton pattern — important in development with hot reloading
// (prevents creating multiple Prisma Client instances)
const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: process.env.NODE_ENV === 'development'
      ? ['query', 'error', 'warn']
      : ['error'],
  });

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}

CRUD Operations

Prisma's query API is where the generated types really shine — your IDE knows exactly what fields are available on every model.

// src/examples/crud.ts
import { prisma } from './db';

// CREATE — create a user with a profile in one transaction
async function createUser(email: string, name: string, bio?: string) {
  const user = await prisma.user.create({
    data: {
      email,
      name,
      profile: bio ? {
        create: { bio },
      } : undefined,
    },
    include: {
      profile: true,
    },
  });
  return user;
}

// READ — find a single user by email
async function getUserByEmail(email: string) {
  return prisma.user.findUnique({
    where: { email },
    include: {
      profile: true,
      posts: {
        where: { published: true },
        orderBy: { createdAt: 'desc' },
        take: 5,
        select: { id: true, title: true, createdAt: true },
      },
    },
  });
}

// READ — paginated list with filters
async function getPublishedPosts(page: number, limit: number, tag?: string) {
  const where = {
    published: true,
    ...(tag && {
      tags: { some: { name: tag } },
    }),
  };

  const [posts, total] = await prisma.$transaction([
    prisma.post.findMany({
      where,
      orderBy: { createdAt: 'desc' },
      skip: (page - 1) * limit,
      take: limit,
      include: {
        author: { select: { id: true, name: true } },
        tags: { select: { name: true } },
        _count: { select: { comments: true } },
      },
    }),
    prisma.post.count({ where }),
  ]);

  return {
    posts,
    pagination: { page, limit, total, totalPages: Math.ceil(total / limit) },
  };
}

// UPDATE — update specific fields
async function publishPost(postId: number, authorId: number) {
  return prisma.post.update({
    where: {
      id: postId,
      authorId, // Ownership check built into the query
    },
    data: {
      published: true,
      updatedAt: new Date(),
    },
  });
}

// DELETE — with cascade (profile is auto-deleted via DB constraint)
async function deleteUser(userId: number) {
  return prisma.user.delete({
    where: { id: userId },
  });
}

Working with Relations

// Many-to-many: add/remove tags from a post
async function addTagsToPost(postId: number, tagNames: string[]) {
  return prisma.post.update({
    where: { id: postId },
    data: {
      tags: {
        connectOrCreate: tagNames.map(name => ({
          where: { name },
          create: { name },
        })),
      },
    },
    include: { tags: true },
  });
}

// Nested writes: create a post with tags and an initial comment
async function createPostWithContent(
  authorId: number,
  title: string,
  content: string,
  tags: string[],
) {
  return prisma.post.create({
    data: {
      title,
      content,
      authorId,
      tags: {
        connectOrCreate: tags.map(name => ({
          where: { name },
          create: { name },
        })),
      },
    },
    include: {
      author: { select: { name: true } },
      tags: true,
    },
  });
}

Raw SQL When You Need It

Prisma's query builder covers most cases, but complex analytics queries sometimes need raw SQL:

// Raw query with parameterized inputs (safe from SQL injection)
async function getTopAuthors(limit: number) {
  const result = await prisma.$queryRaw<Array<{
    id: number;
    name: string;
    post_count: bigint;
    avg_comments: number;
  }>>`
    SELECT 
      u.id,
      u.name,
      COUNT(p.id) AS post_count,
      AVG(comment_counts.cnt) AS avg_comments
    FROM users u
    LEFT JOIN posts p ON p.author_id = u.id AND p.published = true
    LEFT JOIN (
      SELECT post_id, COUNT(*) AS cnt 
      FROM comments 
      GROUP BY post_id
    ) comment_counts ON comment_counts.post_id = p.id
    GROUP BY u.id, u.name
    HAVING COUNT(p.id) > 0
    ORDER BY post_count DESC
    LIMIT ${limit}
  `;

  // Convert BigInt to number for JSON serialization
  return result.map(r => ({ ...r, post_count: Number(r.post_count) }));
}

The template literal syntax for $queryRaw automatically parameterizes interpolated values — ${limit} becomes $1 in the actual SQL, preventing injection.

ORM Comparison Table

Feature	Prisma	TypeORM	Drizzle
Type safety	Excellent (auto-generated)	Good (decorators)	Excellent (schema-derived)
Schema approach	Schema file (schema.prisma)	Decorator classes	TypeScript schema
Migrations	Automatic (migrate dev)	Manual or sync	Manual (drizzle-kit)
Query builder	Fluent object API	QueryBuilder + Repository	SQL-like builder
Raw SQL support	$queryRaw, $executeRaw	query()	sql template tag
Performance (N+1)	Requires care	Requires care	Excellent (SQL-explicit)
Bundle size	Larger (generated client)	Medium	Very small
Learning curve	Low–Medium	Medium–High	Low (if you know SQL)
Serverless support	Via Prisma Accelerate	Limited	Excellent
Maturity	High	High	Growing fast

Prisma's official documentation is among the best in the ORM space — the "Concepts" section in particular is worth reading.

Error Handling

import { PrismaClientKnownRequestError } from '@prisma/client/runtime/library';

async function safeCreateUser(email: string, name: string) {
  try {
    return await prisma.user.create({ data: { email, name } });
  } catch (err) {
    if (err instanceof PrismaClientKnownRequestError) {
      // P2002 — unique constraint violation
      if (err.code === 'P2002') {
        throw new Error(`Email ${email} is already in use`);
      }
      // P2025 — record not found (update/delete on nonexistent record)
      if (err.code === 'P2025') {
        throw new Error('Record not found');
      }
    }
    throw err; // Re-throw unknown errors
  }
}

Wrapping Up

Frequently Asked Questions

Is Prisma good for production applications?

What is the difference between Prisma Migrate and db push?

Can Prisma handle complex raw SQL queries?

How to Use Prisma ORM with PostgreSQL (Complete 2026 Tutorial)

Setting Up Prisma

Defining Your Schema

Running Migrations

Initializing the Prisma Client

CRUD Operations

Working with Relations

Raw SQL When You Need It

ORM Comparison Table

Error Handling

Wrapping Up

Frequently Asked Questions

Frequently Asked Questions

AiTechWorlds Team

Related Articles

How to Use Docker Compose for Local Dev (Node.js + PostgreSQL)

5 GraphQL Resolver Best Practices (DataLoader, Error Handling)

10 SQL Query Optimization Techniques (Indexes, EXPLAIN, Joins)

7 Common API Security Vulnerabilities (and How to Fix Them)

Get Free AI Notes Daily

How to Use Prisma ORM with PostgreSQL (Complete 2026 Tutorial)

Setting Up Prisma

Defining Your Schema

Running Migrations

Initializing the Prisma Client

CRUD Operations

Working with Relations

Raw SQL When You Need It

ORM Comparison Table

Error Handling

Wrapping Up

Frequently Asked Questions

Frequently Asked Questions

AiTechWorlds Team

Related Articles

How to Use Docker Compose for Local Dev (Node.js + PostgreSQL)

5 GraphQL Resolver Best Practices (DataLoader, Error Handling)

10 SQL Query Optimization Techniques (Indexes, EXPLAIN, Joins)

7 Common API Security Vulnerabilities (and How to Fix Them)

Get Free AI Notes Daily