BLOG POSTS
How to Build a REST API with Prisma and PostgreSQL

How to Build a REST API with Prisma and PostgreSQL

REST APIs remain the backbone of modern web development, and building them efficiently with the right tools can make or break your project. Prisma combined with PostgreSQL offers a powerful, type-safe solution for creating robust APIs that scale. This comprehensive guide walks you through building a complete REST API from scratch, covering everything from initial setup to production deployment considerations, error handling, and performance optimization strategies that experienced developers actually use in real-world projects.

How Prisma and PostgreSQL Work Together

Prisma acts as a query builder and ORM that sits between your application and PostgreSQL database. Unlike traditional ORMs, Prisma generates a type-safe client based on your database schema, eliminating runtime errors and providing excellent developer experience with auto-completion.

The architecture follows this flow: your REST API endpoints → Prisma Client → PostgreSQL database. Prisma handles connection pooling, query optimization, and provides a clean abstraction layer while maintaining direct access to advanced PostgreSQL features when needed.

Feature Prisma + PostgreSQL Sequelize + PostgreSQL TypeORM + PostgreSQL
Type Safety Full compile-time Partial with TypeScript Good with decorators
Query Performance Optimized, lazy loading Manual optimization needed Good with proper config
Migration System Declarative, Git-friendly Imperative migrations Code-first migrations
Learning Curve Moderate Easy Steep

Step-by-Step Implementation Guide

Let’s build a complete REST API for a blog application with users, posts, and comments. This example covers all CRUD operations and relationships.

Initial Setup and Dependencies

First, create a new Node.js project and install the required dependencies:

mkdir blog-api && cd blog-api
npm init -y
npm install prisma @prisma/client express cors helmet dotenv bcryptjs jsonwebtoken
npm install -D @types/node @types/express @types/bcryptjs @types/jsonwebtoken typescript ts-node nodemon

Initialize Prisma in your project:

npx prisma init

This creates a prisma directory with a schema.prisma file and a .env file for database configuration.

Database Configuration

Configure your PostgreSQL connection in the .env file:

DATABASE_URL="postgresql://username:password@localhost:5432/blog_db?schema=public"
JWT_SECRET="your-super-secret-jwt-key-here"
PORT=3000

Define your database schema in 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 @db.VarChar(255)
  username  String   @unique @db.VarChar(50)
  password  String   @db.VarChar(255)
  firstName String?  @db.VarChar(100)
  lastName  String?  @db.VarChar(100)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  posts     Post[]
  comments  Comment[]
  
  @@map("users")
}

model Post {
  id          Int      @id @default(autoincrement())
  title       String   @db.VarChar(255)
  content     String?  @db.Text
  published   Boolean  @default(false)
  authorId    Int
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  
  author      User      @relation(fields: [authorId], references: [id], onDelete: Cascade)
  comments    Comment[]
  
  @@map("posts")
}

model Comment {
  id        Int      @id @default(autoincrement())
  content   String   @db.Text
  postId    Int
  authorId  Int
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  
  post      Post @relation(fields: [postId], references: [id], onDelete: Cascade)
  author    User @relation(fields: [authorId], references: [id], onDelete: Cascade)
  
  @@map("comments")
}

Generate and run the migration:

npx prisma migrate dev --name init
npx prisma generate

Express Server Setup

Create the main server file src/server.ts:

import express from 'express';
import cors from 'cors';
import helmet from 'helmet';
import dotenv from 'dotenv';
import { PrismaClient } from '@prisma/client';

import userRoutes from './routes/users';
import postRoutes from './routes/posts';
import authRoutes from './routes/auth';
import { errorHandler } from './middleware/errorHandler';
import { requestLogger } from './middleware/logger';

dotenv.config();

const app = express();
const prisma = new PrismaClient({
  log: ['query', 'info', 'warn', 'error'],
});

// Middleware
app.use(helmet());
app.use(cors());
app.use(express.json({ limit: '10mb' }));
app.use(express.urlencoded({ extended: true }));
app.use(requestLogger);

// Make Prisma available to routes
app.use((req, res, next) => {
  req.prisma = prisma;
  next();
});

// Routes
app.use('/api/auth', authRoutes);
app.use('/api/users', userRoutes);
app.use('/api/posts', postRoutes);

// Health check
app.get('/health', (req, res) => {
  res.json({ status: 'OK', timestamp: new Date().toISOString() });
});

// Error handling
app.use(errorHandler);

const PORT = process.env.PORT || 3000;

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

process.on('beforeExit', async () => {
  await prisma.$disconnect();
});

Authentication Middleware

Create src/middleware/auth.ts for JWT authentication:

import { Request, Response, NextFunction } from 'express';
import jwt from 'jsonwebtoken';
import { PrismaClient } from '@prisma/client';

interface AuthRequest extends Request {
  user?: any;
  prisma: PrismaClient;
}

export const authenticateToken = async (req: AuthRequest, res: Response, next: NextFunction) => {
  const authHeader = req.headers['authorization'];
  const token = authHeader && authHeader.split(' ')[1];

  if (!token) {
    return res.status(401).json({ error: 'Access token required' });
  }

  try {
    const decoded = jwt.verify(token, process.env.JWT_SECRET!) as { userId: number };
    const user = await req.prisma.user.findUnique({
      where: { id: decoded.userId },
      select: { id: true, email: true, username: true, firstName: true, lastName: true }
    });

    if (!user) {
      return res.status(401).json({ error: 'Invalid token' });
    }

    req.user = user;
    next();
  } catch (error) {
    return res.status(403).json({ error: 'Invalid or expired token' });
  }
};

User Routes and CRUD Operations

Create src/routes/users.ts with complete user management:

import express from 'express';
import bcrypt from 'bcryptjs';
import { authenticateToken } from '../middleware/auth';
import { validateUser, validateUserUpdate } from '../validators/userValidator';

const router = express.Router();

// Get current user profile
router.get('/me', authenticateToken, async (req, res) => {
  try {
    const user = await req.prisma.user.findUnique({
      where: { id: req.user.id },
      select: {
        id: true,
        email: true,
        username: true,
        firstName: true,
        lastName: true,
        createdAt: true,
        _count: {
          select: { posts: true, comments: true }
        }
      }
    });

    res.json(user);
  } catch (error) {
    res.status(500).json({ error: 'Failed to fetch user profile' });
  }
});

// Update user profile
router.put('/me', authenticateToken, validateUserUpdate, async (req, res) => {
  try {
    const { firstName, lastName, email } = req.body;
    
    const updatedUser = await req.prisma.user.update({
      where: { id: req.user.id },
      data: { firstName, lastName, email },
      select: {
        id: true,
        email: true,
        username: true,
        firstName: true,
        lastName: true,
        updatedAt: true
      }
    });

    res.json(updatedUser);
  } catch (error) {
    if (error.code === 'P2002') {
      res.status(400).json({ error: 'Email already exists' });
    } else {
      res.status(500).json({ error: 'Failed to update profile' });
    }
  }
});

// Get user's posts
router.get('/me/posts', authenticateToken, async (req, res) => {
  try {
    const page = parseInt(req.query.page as string) || 1;
    const limit = parseInt(req.query.limit as string) || 10;
    const skip = (page - 1) * limit;

    const [posts, total] = await Promise.all([
      req.prisma.post.findMany({
        where: { authorId: req.user.id },
        skip,
        take: limit,
        orderBy: { createdAt: 'desc' },
        include: {
          _count: { select: { comments: true } }
        }
      }),
      req.prisma.post.count({ where: { authorId: req.user.id } })
    ]);

    res.json({
      posts,
      pagination: {
        page,
        limit,
        total,
        pages: Math.ceil(total / limit)
      }
    });
  } catch (error) {
    res.status(500).json({ error: 'Failed to fetch posts' });
  }
});

export default router;

Posts API with Advanced Querying

Create src/routes/posts.ts with full CRUD and advanced features:

import express from 'express';
import { authenticateToken } from '../middleware/auth';
import { validatePost } from '../validators/postValidator';

const router = express.Router();

// Get all posts with filtering and pagination
router.get('/', async (req, res) => {
  try {
    const page = parseInt(req.query.page as string) || 1;
    const limit = Math.min(parseInt(req.query.limit as string) || 10, 50);
    const skip = (page - 1) * limit;
    const search = req.query.search as string;
    const published = req.query.published === 'true' ? true : undefined;

    const whereClause: any = {};
    
    if (published !== undefined) {
      whereClause.published = published;
    }
    
    if (search) {
      whereClause.OR = [
        { title: { contains: search, mode: 'insensitive' } },
        { content: { contains: search, mode: 'insensitive' } }
      ];
    }

    const [posts, total] = await Promise.all([
      req.prisma.post.findMany({
        where: whereClause,
        skip,
        take: limit,
        orderBy: { createdAt: 'desc' },
        include: {
          author: {
            select: { id: true, username: true, firstName: true, lastName: true }
          },
          _count: { select: { comments: true } }
        }
      }),
      req.prisma.post.count({ where: whereClause })
    ]);

    res.json({
      posts,
      pagination: {
        page,
        limit,
        total,
        pages: Math.ceil(total / limit),
        hasNext: page * limit < total,
        hasPrev: page > 1
      }
    });
  } catch (error) {
    res.status(500).json({ error: 'Failed to fetch posts' });
  }
});

// Get single post with comments
router.get('/:id', async (req, res) => {
  try {
    const postId = parseInt(req.params.id);
    
    const post = await req.prisma.post.findUnique({
      where: { id: postId },
      include: {
        author: {
          select: { id: true, username: true, firstName: true, lastName: true }
        },
        comments: {
          include: {
            author: {
              select: { id: true, username: true, firstName: true, lastName: true }
            }
          },
          orderBy: { createdAt: 'desc' }
        }
      }
    });

    if (!post) {
      return res.status(404).json({ error: 'Post not found' });
    }

    res.json(post);
  } catch (error) {
    res.status(500).json({ error: 'Failed to fetch post' });
  }
});

// Create new post
router.post('/', authenticateToken, validatePost, async (req, res) => {
  try {
    const { title, content, published = false } = req.body;
    
    const post = await req.prisma.post.create({
      data: {
        title,
        content,
        published,
        authorId: req.user.id
      },
      include: {
        author: {
          select: { id: true, username: true, firstName: true, lastName: true }
        }
      }
    });

    res.status(201).json(post);
  } catch (error) {
    res.status(500).json({ error: 'Failed to create post' });
  }
});

// Update post
router.put('/:id', authenticateToken, validatePost, async (req, res) => {
  try {
    const postId = parseInt(req.params.id);
    const { title, content, published } = req.body;

    // Check if post exists and user owns it
    const existingPost = await req.prisma.post.findUnique({
      where: { id: postId }
    });

    if (!existingPost) {
      return res.status(404).json({ error: 'Post not found' });
    }

    if (existingPost.authorId !== req.user.id) {
      return res.status(403).json({ error: 'Not authorized to update this post' });
    }

    const updatedPost = await req.prisma.post.update({
      where: { id: postId },
      data: { title, content, published },
      include: {
        author: {
          select: { id: true, username: true, firstName: true, lastName: true }
        }
      }
    });

    res.json(updatedPost);
  } catch (error) {
    res.status(500).json({ error: 'Failed to update post' });
  }
});

// Delete post
router.delete('/:id', authenticateToken, async (req, res) => {
  try {
    const postId = parseInt(req.params.id);

    const existingPost = await req.prisma.post.findUnique({
      where: { id: postId }
    });

    if (!existingPost) {
      return res.status(404).json({ error: 'Post not found' });
    }

    if (existingPost.authorId !== req.user.id) {
      return res.status(403).json({ error: 'Not authorized to delete this post' });
    }

    await req.prisma.post.delete({
      where: { id: postId }
    });

    res.status(204).send();
  } catch (error) {
    res.status(500).json({ error: 'Failed to delete post' });
  }
});

export default router;

Real-World Examples and Use Cases

This Prisma + PostgreSQL setup excels in several production scenarios:

  • E-commerce platforms: Handle complex product catalogs, inventory management, and order processing with strong ACID guarantees
  • Content management systems: Manage hierarchical content structures with efficient querying and full-text search capabilities
  • SaaS applications: Multi-tenant architectures with row-level security and efficient connection pooling
  • Analytics dashboards: Aggregate large datasets with PostgreSQL’s advanced analytical functions while maintaining type safety

Here’s a real-world example of handling complex queries with Prisma aggregations:

// Advanced analytics endpoint
router.get('/analytics/dashboard', authenticateToken, async (req, res) => {
  try {
    const [
      totalPosts,
      publishedPosts,
      totalComments,
      topAuthors,
      recentActivity
    ] = await Promise.all([
      req.prisma.post.count(),
      req.prisma.post.count({ where: { published: true } }),
      req.prisma.comment.count(),
      req.prisma.user.findMany({
        take: 5,
        orderBy: {
          posts: { _count: 'desc' }
        },
        select: {
          id: true,
          username: true,
          _count: {
            select: { posts: true, comments: true }
          }
        }
      }),
      req.prisma.post.findMany({
        take: 10,
        orderBy: { createdAt: 'desc' },
        select: {
          id: true,
          title: true,
          createdAt: true,
          author: { select: { username: true } }
        }
      })
    ]);

    res.json({
      stats: {
        totalPosts,
        publishedPosts,
        totalComments,
        publishRate: publishedPosts / totalPosts * 100
      },
      topAuthors,
      recentActivity
    });
  } catch (error) {
    res.status(500).json({ error: 'Failed to fetch analytics' });
  }
});

Performance Optimization and Best Practices

Performance optimization becomes critical as your API scales. Here are proven strategies from production environments:

Connection Pooling Configuration

Configure Prisma’s connection pool in your schema.prisma:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  
  // Connection pool settings
  connectionLimit = 10
  poolTimeout     = 20
}

For high-traffic applications, use a connection string with pool parameters:

DATABASE_URL="postgresql://user:pass@localhost:5432/db?schema=public&connection_limit=20&pool_timeout=20"

Query Optimization Techniques

Use Prisma’s query optimization features:

// Bad: N+1 query problem
const posts = await prisma.post.findMany();
for (const post of posts) {
  const author = await prisma.user.findUnique({ where: { id: post.authorId } });
}

// Good: Single query with include
const posts = await prisma.post.findMany({
  include: {
    author: { select: { id: true, username: true } }
  }
});

// Best: Select only needed fields
const posts = await prisma.post.findMany({
  select: {
    id: true,
    title: true,
    createdAt: true,
    author: {
      select: { username: true }
    }
  }
});

Database Index Strategy

Add strategic indexes in your Prisma schema:

model Post {
  id        Int      @id @default(autoincrement())
  title     String   @db.VarChar(255)
  content   String?  @db.Text
  published Boolean  @default(false)
  authorId  Int
  createdAt DateTime @default(now())
  
  // Strategic indexes for common queries
  @@index([published, createdAt])
  @@index([authorId, createdAt])
  @@index([title]) // For search functionality
}

Common Issues and Troubleshooting

Here are the most frequent problems developers encounter and their solutions:

Connection Pool Exhaustion

Symptoms: “Too many connections” errors or hanging requests. This typically occurs when you forget to close Prisma connections or have too many concurrent requests.

// Problem: Not properly handling Prisma lifecycle
const badHandler = async (req, res) => {
  const prisma = new PrismaClient(); // Don't do this per request!
  const users = await prisma.user.findMany();
  res.json(users);
  // Connection never closed
};

// Solution: Use singleton pattern and proper cleanup
class Database {
  private static instance: PrismaClient;
  
  public static getInstance(): PrismaClient {
    if (!Database.instance) {
      Database.instance = new PrismaClient({
        log: ['error', 'warn'],
      });
    }
    return Database.instance;
  }
}

export const prisma = Database.getInstance();

Migration Conflicts in Team Environments

When multiple developers work on schema changes, migration conflicts are common. Here’s how to handle them:

# Reset to a clean state (development only)
npx prisma migrate reset

# Create a new migration after resolving conflicts
npx prisma migrate dev --name resolve-conflicts

# For production, use deploy
npx prisma migrate deploy

Type Generation Issues

Sometimes the Prisma client becomes out of sync with your schema:

# Regenerate Prisma client
npx prisma generate

# Clear node_modules and reinstall if problems persist
rm -rf node_modules package-lock.json
npm install
npx prisma generate

Security Considerations and Production Deployment

Security should be built into your API from the ground up. Here are essential security measures:

Input Validation and Sanitization

Create comprehensive validators using a library like Joi or Zod:

import { z } from 'zod';

const createPostSchema = z.object({
  title: z.string().min(1).max(255),
  content: z.string().optional(),
  published: z.boolean().default(false)
});

export const validatePost = (req: Request, res: Response, next: NextFunction) => {
  try {
    req.body = createPostSchema.parse(req.body);
    next();
  } catch (error) {
    res.status(400).json({ error: 'Invalid input data', details: error.errors });
  }
};

Rate Limiting and DDoS Protection

Implement rate limiting to prevent abuse:

import rateLimit from 'express-rate-limit';

const apiLimiter = rateLimit({
  windowMs: 15 * 60 * 1000, // 15 minutes
  max: 100, // Limit each IP to 100 requests per windowMs
  message: 'Too many requests from this IP, please try again later.',
  standardHeaders: true,
  legacyHeaders: false,
});

const authLimiter = rateLimit({
  windowMs: 15 * 60 * 1000,
  max: 5, // Stricter limit for auth endpoints
  skipSuccessfulRequests: true,
});

app.use('/api/', apiLimiter);
app.use('/api/auth/', authLimiter);

Production Environment Configuration

Your production DATABASE_URL should include SSL and proper connection pooling:

# Production environment variables
DATABASE_URL="postgresql://user:password@db-host:5432/production_db?schema=public&sslmode=require&connection_limit=10"
NODE_ENV=production
JWT_SECRET="your-very-long-random-jwt-secret-key"
LOG_LEVEL=warn
Configuration Development Production Notes
Connection Pool 5-10 10-20 Based on server resources
SSL Mode disable require Always use SSL in production
Log Level query, info, warn, error warn, error Reduce logging overhead
Query Timeout 10s 5s Fail fast in production

For deployment on VPS services or dedicated servers, consider using PM2 for process management:

# ecosystem.config.js
module.exports = {
  apps: [{
    name: 'blog-api',
    script: './dist/server.js',
    instances: 'max',
    exec_mode: 'cluster',
    env: {
      NODE_ENV: 'production',
      PORT: 3000
    },
    error_file: './logs/err.log',
    out_file: './logs/out.log',
    log_file: './logs/combined.log'
  }]
};

This comprehensive setup provides a production-ready REST API with Prisma and PostgreSQL. The combination offers excellent performance, type safety, and developer experience while maintaining the flexibility to handle complex business requirements. Remember to monitor your application’s performance metrics and adjust connection pool settings based on your specific traffic patterns and server resources.

For more advanced features, explore Prisma’s official documentation and PostgreSQL’s comprehensive guides to leverage database-specific features like full-text search, JSON operations, and advanced indexing strategies.

This article incorporates information and material from various online sources. We acknowledge and appreciate the work of all original authors, publishers, and websites. While every effort has been made to appropriately credit the source material, any unintentional oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. If you believe that any content used in this article infringes upon your copyright, please contact us immediately for review and prompt action.

This article is intended for informational and educational purposes only and does not infringe on the rights of the copyright owners. If any copyrighted material has been used without proper credit or in violation of copyright laws, it is unintentional and we will rectify it promptly upon notification. Please note that the republishing, redistribution, or reproduction of part or all of the contents in any form is prohibited without express written permission from the author and website owner. For permissions or further inquiries, please contact us.

More stories

How to Use Apache HTTP Server as Reverse Proxy Using mod_proxy
admin, August 4, 2025
How to Use Apache HTTP Server as Reverse Proxy Using mo...
Install and Uninstall Node.js on Ubuntu – Step-by-Step
admin, August 4, 2025
Install and Uninstall Node.js on Ubuntu – Step-by-Step
Key Components and Internals of the Spring Boot Framework
admin, August 4, 2025
Key Components and Internals of the Spring Boot Framewo...

Leave a reply

Your email address will not be published. Required fields are marked

Login
Register
Forgot something?