Prisma + PostgreSQL Full Setup

Schema design, migrations, seeding, CRUD operations, and relations

What You’ll Build

After following this guide, you will have a working implementation of prisma + postgresql full setup in your project. Set up Prisma ORM with PostgreSQL from scratch. Covers schema modeling with relations, running migrations, seeding data, and performing type-safe CRUD operations. Prisma gives you auto-generated TypeScript types and an intuitive query API.

Use Cases & Problems Solved

• Set up a production-ready database layer with type-safe queries
• Manage schema changes through migrations without data loss
• Avoid raw SQL injection risks and inconsistent data access patterns

Prerequisites

• Node.js 18+
• PostgreSQL running locally or a connection string
• TypeScript project (recommended)

Step-by-Step Implementation

Install and init Prisma

The following snippet shows how to install and init prisma. Copy this into your project and adjust the values for your environment.

npm install prisma @prisma/client
npx prisma init

Define your schema

The following snippet shows how to define your schema. Copy this into your project and adjust the values for your environment.

// 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
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

Run migration and generate client

The following snippet shows how to run migration and generate client. Copy this into your project and adjust the values for your environment.

npx prisma migrate dev --name init
npx prisma generate

CRUD operations

The following snippet shows how to crud operations. Copy this into your project and adjust the values for your environment.

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

// Create
const user = await prisma.user.create({
  data: { email: 'alice@example.com', name: 'Alice' },
});

// Read with relation
const userWithPosts = await prisma.user.findUnique({
  where: { email: 'alice@example.com' },
  include: { posts: true },
});

// Update
await prisma.post.update({
  where: { id: 1 },
  data: { published: true },
});

// Delete
await prisma.post.delete({ where: { id: 1 } });

⚠️ Don’t Do This

// Creates a new connection pool per request — will exhaust DB connections!
app.get('/users', async (req, res) => {
  const prisma = new PrismaClient(); // BAD!
  const users = await prisma.user.findMany();
  res.json(users);
});

// lib/prisma.ts — single instance reused across requests
const globalForPrisma = globalThis as unknown as { prisma: PrismaClient };
export const prisma = globalForPrisma.prisma || new PrismaClient();
if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

Testing

Verify your implementation with these tests:

// __tests__/prisma-postgresql-full-setup.test.ts
import { describe, it, expect } from 'vitest';

describe('Prisma + PostgreSQL Full Setup', () => {
  it('should initialize without errors', () => {
    // Test that the setup completes successfully
    expect(() => setup()).not.toThrow();
  });

  it('should handle the primary use case', async () => {
    const result = await execute();
    expect(result).toBeDefined();
    expect(result.success).toBe(true);
  });

  it('should handle edge cases', async () => {
    // Test with empty/null input
    const result = await execute(null);
    expect(result.error).toBeDefined();
  });
});

Verification

# Run Prisma Studio to visually inspect your database
npx prisma studio
# Opens http://localhost:5555
# Verify tables exist and CRUD operations work