🚀 Complete Guide: Node.js + Express + Prisma + PostgreSQL (Docker) Boilerplate

🎯 Project Overview

This comprehensive guide walks you through setting up a production-ready Node.js backend application with:

• Runtime: Node.js with TypeScript
• Framework: Express.js
• Database: PostgreSQL (Dockerized)
• ORM: Prisma (v7.x+)
• Development Tools: tsx for hot-reloading
• Database UI: Adminer for database management

🏗️ Architecture Stack

┌─────────────────────────────────────┐
│   Express.js (REST API Server)                            |
├─────────────────────────────────────┤
│   Prisma Client (ORM Layer)                              |
├─────────────────────────────────────┤
│   @prisma/adapter-pg (Adapter)                      |
├─────────────────────────────────────┤
│   PostgreSQL (Docker Container)                      |   
└─────────────────────────────────────┘

✅ Prerequisites

Before starting, ensure you have:

• Node.js (v18+ recommended)
• Docker and Docker Compose installed
• npm or yarn package manager
• Basic understanding of TypeScript and SQL
• A code editor (VS Code recommended)

📦 Project Initialization

Step 1: Initialize Node.js Project

npm init -y

What this does:

• Creates a package.json file with default configuration
• The -y flag accepts all default prompts automatically

Step 2: Install TypeScript Dependencies

npm i -D typescript @types/node tsx

Package breakdown:

• typescript - TypeScript compiler
• @types/node - Type definitions for Node.js core modules
• tsx - TypeScript executor for development (supports hot-reload)

Step 3: Initialize TypeScript Configuration

npx tsc --init

What this does:

• Creates tsconfig.json with default TypeScript compiler options
• We'll customize this in the next steps

Step 4: Install Express.js

npm i express
npm i -D @types/express
npm i dotenv

Package breakdown:

• express - Web framework for Node.js
• @types/express - TypeScript type definitions for Express
• dotenv - Loads environment variables from .env file

Step 5: Configure ES Modules (ESM)

In package.json, add:

{
  "type": "module"
}

Why this matters:

• Enables modern import/export syntax instead of require()
• Required for Prisma 7.x compatibility
• Aligns with modern JavaScript standards

Step 6: Configure TypeScript Compiler

In tsconfig.json, update the following:

{
  "compilerOptions": {
    "rootDir": "./src",
    "outDir": "./dist",
    "module": "nodenext",
    "target": "esnext",
    "lib": ["esnext"],
    "types": ["node"],
    "sourceMap": true,
    "declaration": true,
    "declarationMap": true,
    "noUncheckedIndexedAccess": true,
    "exactOptionalPropertyTypes": true,
    "strict": true,
    "verbatimModuleSyntax": true,
    "isolatedModules": true,
    "noUncheckedSideEffectImports": true,
    "moduleDetection": "force",
    "skipLibCheck": true
  },
  "include": ["src"],
  "exclude": ["prisma.config.ts"]
}

Key configuration points:

• rootDir: All TypeScript source files must be in ./src
• outDir: Compiled JavaScript will output to ./dist
• module: "nodenext": Critical for ESM + TypeScript compatibility
• exclude: Prevents Prisma config file from causing TypeScript errors

🖥️ Express Server Setup

Step 1: Create Project Structure

mkdir src
cd src
touch index.ts

Directory structure:

project-root/
├── src/
│   └── index.ts
├── package.json
└── tsconfig.json

Step 2: Create Express Server

File: ./src/index.ts

import express from "express";
 
const app = express();
 
// Test endpoint
app.get("/check", (req, res) => {
  res.json({
    message: "test running!!!",
  });
});
 
// Start server
app.listen("3003", () => console.log("endpoint started at Port 3003"));

Code explanation:

• express() - Creates Express application instance
• app.get() - Defines GET route handler
• app.listen() - Starts HTTP server on port 3003

Step 3: Configure Development Script

In package.json, add to scripts section:

{
  "scripts": {
    "dev": "tsx --watch ./src/index.ts"
  }
}

Script breakdown:

• tsx - Runs TypeScript files directly without compilation
• --watch - Auto-restarts server on file changes
• ./src/index.ts - Entry point file

Step 4: Run Development Server

npm run dev

Test in browser:

http://localhost:3003/check

Expected response:

{
  "message": "test running!!!"
}

✅ Success indicator: Console shows "endpoint started at Port 3003"

🐳 PostgreSQL Setup with Docker

Step 1: Create Docker Compose Configuration

File: docker-compose.yml (create in project root)

services:
  db:
    image: postgres:15
    container_name: postgres
    restart: always
    environment:
      POSTGRES_DB: ${DB_NAME}
      POSTGRES_USER: ${DB_USER}
      POSTGRES_PASSWORD: ${DB_PASSWORD}
    ports:
      - ${DB_PORT}:5432
    volumes:
      - pgdata:/var/lib/postgresql/data
    attach: false
 
  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080
 
volumes:
  pgdata:

Configuration breakdown:

Adminer service:

• Web-based database management UI
• Accessible at http://localhost:8080
• Alternative to pgAdmin or DBeaver

Step 2: Create Environment Variables

File: .env (create in project root)

DATABASE_URL="postgresql://phi_hrms:phiAT1234@localhost:5432/hrms-db"
DB_NAME=hrms-db
DB_USER=phi_hrms
DB_PASSWORD=phiAT1234
DB_PORT=5432

DATABASE_URL format breakdown:

postgresql://[USER]:[PASSWORD]@[HOST]:[PORT]/[DATABASE]

Security best practices:

• ⚠️ Never commit .env to version control
• Add .env to .gitignore
• Use strong passwords in production
• Consider using Docker secrets for production

Step 3: Start Docker Containers

docker compose up -d

Command breakdown:

• docker compose - Docker Compose CLI command
• up - Create and start containers
• -d - Detached mode (runs in background)

What happens:

1. Downloads PostgreSQL 15 image (if not cached)
2. Downloads Adminer image (if not cached)
3. Creates pgdata volume for persistence
4. Starts both containers
5. Database accessible at localhost:5432
6. Adminer accessible at localhost:8080

Step 4: Verify Database Connection

Option 1: Using Docker CLI

docker exec -it postgres psql -U phi_hrms -d hrms-db

Command breakdown:

• docker exec - Execute command in running container
• -it - Interactive terminal mode
• postgres - Container name
• psql - PostgreSQL interactive terminal
• -U phi_hrms - Username
• -d hrms-db - Database name

You should see:

psql (15.x)
Type "help" for help.

hrms-db=#

Useful psql commands:

\dt          -- List all tables
\l           -- List all databases
\q           -- Quit psql

Option 2: Using Adminer

1. Open http://localhost:8080
2. Login with credentials from .env
3. Explore database visually

⚙️ Prisma ORM Setup

Step 1: Install Prisma Dependencies

npm i -D prisma @types/node @types/pg
npm i @prisma/client @prisma/adapter-pg pg dotenv

Development dependencies (-D):

• prisma - Prisma CLI for migrations and codegen
• @types/node - Node.js type definitions
• @types/pg - TypeScript types for node-postgres

Production dependencies:

• @prisma/client - Prisma query builder
• @prisma/adapter-pg - PostgreSQL adapter for Prisma 7.x
• pg - Node.js PostgreSQL driver
• dotenv - Environment variable loader

Package roles:

┌──────────────────────────────┐
│   Your Application Code      │
├──────────────────────────────┤
│   @prisma/client             │  ← Type-safe database queries
├──────────────────────────────┤
│   @prisma/adapter-pg         │  ← Connects Prisma to PostgreSQL
├──────────────────────────────┤
│   pg (node-postgres)         │  ← Low-level PostgreSQL driver
├──────────────────────────────┤
│   PostgreSQL Database        │
└──────────────────────────────┘

Step 2: Initialize Prisma

npx prisma init

npx prisma init creates:

1. prisma/ directory with schema.prisma file
2. .env file (if doesn't exist)
3. prisma.config.ts configuration file

Generated file structure:

project-root/
├── prisma/
│   └── schema.prisma
├── prisma.config.ts
├── .env
└── ...

Step 3: Configure Prisma (prisma.config.ts)

File: prisma.config.ts (auto-generated)

import "dotenv/config";
import { defineConfig } from "prisma/config";
 
if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is not defined");
}
 
export default defineConfig({
  schema: "prisma/schema.prisma",
  migrations: {
    path: "prisma/migrations",
  },
  datasource: {
    url: process.env["DATABASE_URL"],
  },
});

Configuration explanation:

• dotenv/config - Loads .env variables automatically
• schema - Path to Prisma schema file
• migrations.path - Where migration files are stored
• datasource.url - Database connection string from environment

Step 4: Understanding the Generated Files

prisma/schema.prisma (initial state):

generator client {
  provider = "prisma-client"
}
 
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

Update .env with your credentials:

DATABASE_URL="postgresql://phi_hrms:phiAT1234@localhost:5432/hrms-db"

🗄️ Database Schema Design

Step 1: Update Prisma Schema

File: prisma/schema.prisma

generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}
 
datasource db {
  provider = "postgresql"
}
 
model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]
}
 
model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  author    User    @relation(fields: [authorId], references: [id])
  authorId  Int
}

Step 2: Schema Syntax Breakdown

Generator Block

generator client {
  provider = "prisma-client"
  output   = "../src/generated/prisma"
}

Why custom output path?

• Better for version control
• Easier to inspect generated types
• More control over TypeScript imports

Datasource Block

datasource db {
  provider = "postgresql"
}

Supported providers: postgresql, mysql, sqlite, sqlserver, mongodb, cockroachdb

Model: User

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

Model: Post

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

Relation field breakdown:

• author - Navigation property (not a database column)
• @relation(fields: [authorId]) - Foreign key column
• references: [id] - Points to User.id
• authorId - Actual database column storing foreign key

Step 3: Relationship Visualization

User (1) ──────< (Many) Post
  │                       │
  id ←─────────────── authorId

Relationship type: One-to-Many — one user can have many posts, each post belongs to one user.

Step 4: Common Prisma Attributes Reference

🔄 Database Migrations

Step 1: Run Migration Command

npx prisma migrate dev --name init

Command breakdown:

• prisma migrate dev - Create and apply migration in development
• --name init - Migration name (descriptive identifier)

Step 2: What Happens During Migration?

1. Prisma compares schema to database
2. Generates SQL migration file
3. Creates migration in prisma/migrations/
4. Applies migration to database
5. Generates Prisma Client
6. Creates tables in PostgreSQL

Step 3: Generated Migration Structure

prisma/
├── migrations/
│   └── 20240115120000_init/
│       └── migration.sql
└── schema.prisma

File: prisma/migrations/20240115120000_init/migration.sql

-- CreateTable
CREATE TABLE "User" (
    "id" SERIAL NOT NULL,
    "email" TEXT NOT NULL,
    "name" TEXT,
 
    CONSTRAINT "User_pkey" PRIMARY KEY ("id")
);
 
-- CreateTable
CREATE TABLE "Post" (
    "id" SERIAL NOT NULL,
    "title" TEXT NOT NULL,
    "content" TEXT,
    "published" BOOLEAN NOT NULL DEFAULT false,
    "authorId" INTEGER NOT NULL,
 
    CONSTRAINT "Post_pkey" PRIMARY KEY ("id")
);
 
-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");
 
-- AddForeignKey
ALTER TABLE "Post" ADD CONSTRAINT "Post_authorId_fkey"
  FOREIGN KEY ("authorId") REFERENCES "User"("id")
  ON DELETE RESTRICT ON UPDATE CASCADE;

Step 4: Generate Prisma Client

npx prisma generate

What this does:

• Reads schema.prisma file
• Generates TypeScript types
• Creates type-safe query builder
• Outputs to src/generated/prisma/ (as specified in schema)

Generated files:

src/
└── generated/
    └── prisma/
        ├── client.ts
        ├── index.ts
        └── ... (type definition files)

Step 5: Prisma 7.x — What Changed

Why the change?

• Better separation of concerns
• Support for edge runtimes (Cloudflare Workers, Vercel Edge)
• More flexible connection pooling
• Improved performance

🔌 Prisma Client Setup

Step 1: Create Prisma Client Wrapper

File: ./src/lib/prisma.ts

import "dotenv/config";
import { PrismaPg } from "@prisma/adapter-pg";
import { PrismaClient } from "../generated/prisma/client";
 
const connectionString = `${process.env.DATABASE_URL}`;
 
const adapter = new PrismaPg({ connectionString });
const prisma = new PrismaClient({ adapter });
 
export { prisma };

Step 2: Code Walkthrough

Import dotenv config

import "dotenv/config";

Loads environment variables from .env before using process.env.

Import Prisma Adapter

import { PrismaPg } from "@prisma/adapter-pg";

PostgreSQL-specific adapter for Prisma 7.x — handles low-level database connections.

Import Generated Prisma Client

import { PrismaClient } from "../generated/prisma/client";

Custom import path as defined in schema.prisma. Contains type-safe database methods.

Initialize Adapter and Client

const adapter = new PrismaPg({ connectionString });
const prisma = new PrismaClient({ adapter });

Creates the adapter for connection pooling, then wires it into the Prisma Client.

Step 3: Architecture Overview

┌─────────────────────────────────────┐
│   Your Application Code             │
│   import { prisma } from './lib'    │
├─────────────────────────────────────┤
│   PrismaClient Instance             │  ← Type-safe queries
├─────────────────────────────────────┤
│   PrismaPg Adapter                  │  ← Connection management
├─────────────────────────────────────┤
│   node-postgres (pg)                │  ← PostgreSQL driver
├─────────────────────────────────────┤
│   PostgreSQL Database               │
└─────────────────────────────────────┘

Step 4: Singleton Pattern — Best Practice

❌ Don't do this:

// file1.ts
const prisma = new PrismaClient();
 
// file2.ts
const prisma = new PrismaClient(); // Creates another connection!

✅ Do this:

// lib/prisma.ts
export const prisma = new PrismaClient();
 
// file1.ts
import { prisma } from "./lib/prisma";
 
// file2.ts
import { prisma } from "./lib/prisma"; // Reuses same connection

✍️ Writing Database Queries

Step 1: Create Test Script

File: ./src/script.ts

import { prisma } from "./lib/prisma";
 
async function main() {
  // Create a new user with a post
  const user = await prisma.user.create({
    data: {
      name: "Alice",
      email: "alice@prisma.io",
      posts: {
        create: {
          title: "Hello World",
          content: "This is my first post!",
          published: true,
        },
      },
    },
    include: {
      posts: true,
    },
  });
  console.log("Created user:", user);
 
  // Fetch all users with their posts
  const allUsers = await prisma.user.findMany({
    include: {
      posts: true,
    },
  });
  console.log("All users:", JSON.stringify(allUsers, null, 2));
}
 
main()
  .then(async () => {
    await prisma.$disconnect();
  })
  .catch(async (e) => {
    console.error(e);
    await prisma.$disconnect();
    process.exit(1);
  });

Step 2: Query Breakdown

Create User with Nested Post

const user = await prisma.user.create({
  data: {
    name: "Alice",
    email: "alice@prisma.io",
    posts: {
      create: { title: "Hello World", content: "This is my first post!", published: true },
    },
  },
  include: { posts: true },
});

• prisma.user.create() — Insert new user record
• posts: { create: { ... } } — Nested write (creates related post atomically)
• include: { posts: true } — Return created post with user

Generated SQL (approximate):

BEGIN;
INSERT INTO "User" (name, email) VALUES ('Alice', 'alice@prisma.io') RETURNING *;
INSERT INTO "Post" (title, content, published, authorId) VALUES ('Hello World', 'This is my first post!', true, 1) RETURNING *;
COMMIT;

Fetch All Users with Posts

const allUsers = await prisma.user.findMany({
  include: { posts: true },
});

Result structure:

[
  {
    "id": 1,
    "email": "alice@prisma.io",
    "name": "Alice",
    "posts": [
      {
        "id": 1,
        "title": "Hello World",
        "content": "This is my first post!",
        "published": true,
        "authorId": 1
      }
    ]
  }
]

Step 3: Connection Management

main()
  .then(async () => {
    await prisma.$disconnect(); // ← Always disconnect
  })
  .catch(async (e) => {
    console.error(e);
    await prisma.$disconnect(); // ← Even on error
    process.exit(1);
  });

Always disconnect to close the connection pool and prevent hanging processes.

Step 4: Run Test Script

npx tsx ./src/script.ts

Step 5: Common Prisma Query Operations

🗃️ Database Management

Adminer — Web UI

Open in browser: http://localhost:8080

Login credentials:

Useful Adminer operations: browse table data, edit records inline, run custom SQL, export/import data.

Prisma Studio — Alternative GUI

npx prisma studio

Opens at http://localhost:5555. Built specifically for Prisma — respects schema relationships and provides a visual relationship editor.

Reset Migrations

npx prisma migrate reset

⚠️ Development only — destroys all data.

Safe Data Clear — Keep Schema

Use TRUNCATE when you need to clear data without dropping the schema:

TRUNCATE TABLE
  "User",
  "Post"
RESTART IDENTITY CASCADE;

HRMS example:

TRUNCATE TABLE
  "AttendanceDay",
  "AttendanceEvent",
  "AttendanceViolation",
  "Company",
  "Department",
  "Designation",
  "DesignationAttendancePolicy",
  "EmployeeAttendanceOverride",
  "EmployeeLeaveOverride",
  "EmployeeProfile",
  "Holiday",
  "LeaveBalance",
  "LeaveEncashment",
  "LeavePolicy",
  "LeaveRequest",
  "LeaveType",
  "OfficeLocation",
  "RefreshToken",
  "Team",
  "User"
RESTART IDENTITY CASCADE;

SQL command breakdown:

• TRUNCATE TABLE — Removes all rows, faster than DELETE
• RESTART IDENTITY — Resets auto-increment sequences back to 1
• CASCADE — Automatically truncates dependent tables, respects foreign keys

Via Prisma script:

import { prisma } from "./lib/prisma";
 
async function truncateTables() {
  await prisma.$executeRaw`
    TRUNCATE TABLE "User", "Post"
    RESTART IDENTITY CASCADE
  `;
}
 
truncateTables();

TRUNCATE vs DELETE vs DROP

Migration Commands Reference

🐛 Troubleshooting

Issue 1: TypeScript can't find Prisma Client

Error:

Cannot find module '../generated/prisma/client'

Solution:

npx prisma generate

Root cause: Prisma Client not generated or outdated.

Issue 2: Database connection refused

Error:

Can't reach database server at localhost:5432

Solution:

docker ps
docker compose down
docker compose up -d
docker exec -it postgres psql -U phi_hrms -d hrms-db

Issue 3: Migration fails with "relation already exists"

Error:

relation "User" already exists

Solution:

npx prisma migrate reset
# OR manually:
docker exec -it postgres psql -U phi_hrms -d hrms-db
# then: DROP TABLE "User" CASCADE;

Issue 4: Port 5432 already in use

Error:

Bind for 0.0.0.0:5432 failed: port is already allocated

Solutions:

# Option 1: Stop local PostgreSQL
sudo systemctl stop postgresql
 
# Option 2: Change port in docker-compose.yml
ports:
  - "5433:5432"

Update .env accordingly:

DATABASE_URL="postgresql://phi_hrms:phiAT1234@localhost:5433/hrms-db"

Issue 5: ESM import errors

Error:

require() of ES Module not supported

Solution: Ensure package.json has "type": "module".

Issue 6: prisma.config.ts causes TypeScript errors

Error:

File 'prisma.config.ts' is not under 'rootDir'

Solution: Add to tsconfig.json:

{
  "exclude": ["prisma.config.ts"]
}

✅ Best Practices

Development Workflow

# 1. Modify schema.prisma
# 2. Create migration
npx prisma migrate dev --name your_change
 
# 3. Generate Prisma Client
npx prisma generate
 
# 4. Test changes
npm run dev

Security

# ✅ Good: Use .env file
DATABASE_URL="postgresql://user:pass@localhost:5432/db"
 
# ❌ Bad: Hardcode in source
const url = "postgresql://user:pass@localhost:5432/db"

.gitignore essentials:

.env
.env.local
node_modules/
dist/

Recommended Project Structure

project-root/
├── src/
│   ├── generated/
│   │   └── prisma/
│   ├── lib/
│   │   └── prisma.ts
│   ├── routes/
│   ├── controllers/
│   ├── middleware/
│   └── index.ts
├── prisma/
│   ├── migrations/
│   └── schema.prisma
├── docker-compose.yml
├── package.json
├── tsconfig.json
└── .env

Database Design Tips

1. Use meaningful names:

// ✅ Good
model EmployeeProfile { ... }
// ❌ Bad
model EP { ... }

2. Add indexes for frequently queried fields:

model User {
  email String @unique
  @@index([email])
}

3. Use enums for fixed values:

enum UserRole {
  ADMIN
  USER
  GUEST
}
model User {
  role UserRole @default(USER)
}

4. Always add timestamps:

model User {
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Migration Strategy

Never use migrate dev in production.

📋 Quick Reference Commands

Docker Commands

docker compose up -d           # Start containers
docker compose down            # Stop containers
docker compose logs -f         # View logs
docker compose restart db      # Restart DB service
docker exec -it postgres psql -U phi_hrms -d hrms-db  # Access psql

Prisma Commands

npx prisma init                          # Initialize Prisma
npx prisma migrate dev --name <name>     # Create migration (dev)
npx prisma migrate deploy                # Apply migrations (prod)
npx prisma migrate reset                 # Reset database (dev only!)
npx prisma migrate status                # Check migration status
npx prisma generate                      # Generate Prisma Client
npx prisma studio                        # Open Prisma Studio
npx prisma format                        # Format schema file

npm Scripts

npm run dev    # Run development server
npm run build  # Build TypeScript
npm start      # Run production

psql Commands

\dt            -- List all tables
\d "User"      -- Describe table structure
\l             -- List databases
\dn            -- List schemas
\q             -- Quit psql

PostgreSQL Query Examples

SELECT COUNT(*) FROM "User";
SELECT * FROM "User";
SELECT * FROM "User" WHERE email = 'alice@prisma.io';
 
SELECT u.name, p.title
FROM "User" u
LEFT JOIN "Post" p ON u.id = p."authorId";
 
DELETE FROM "Post";
TRUNCATE TABLE "User" RESTART IDENTITY CASCADE;

🎓 Summary

What You've Learned

✅ Project Setup — Node.js + TypeScript, ES Modules, Express.js

✅ Database Infrastructure — PostgreSQL via Docker, Adminer, environment variables

✅ Prisma ORM — Schema design, migrations, Prisma Client generation, type-safe queries

✅ Best Practices — Singleton pattern, migration workflow, error handling, dev vs prod strategies

📚 Additional Resources

• Prisma Docs
• Express.js Guide
• TypeScript Handbook
• PostgreSQL Manual

📝 Document Metadata

Version: 1.1.0
Last Updated: 13-Feb-2026
Compatible With: Node.js 18+, Prisma 7.x, PostgreSQL 15, TypeScript 5.x
Tested On: macOS, Linux, Windows (WSL2)

Happy Coding! 🚀

This document was created with ❤️ for developers, by Anubhaw. Feel free to suggest improvements or connect @ ContactMe