How to Connect PostgreSQL to Node.js Using pg and Prisma

When building a backend application in Node.js, PostgreSQL is one of the most reliable relational databases available. It offers ACID compliance, robust data integrity constraints, JSON support, and rich indexing options.

To interact with PostgreSQL from Node.js, developers generally choose between two primary approaches:

1. A raw SQL driver (node-postgres / pg): You write explicit SQL queries, manage connection pools directly, and keep application overhead minimal.
2. A schema-driven ORM (Prisma): You define models in a declarative schema, let the ORM manage migrations, and interact with your database using auto-generated, type-safe TypeScript methods.

These approaches are not mutually exclusive—many production systems use Prisma for standard application CRUD while relying on raw SQL for complex reporting queries. This guide covers how to set up, configure, and optimize both tools in TypeScript.

---

Why Connection Pooling Matters in Node.js

PostgreSQL uses a process-based connection architecture. For every client connection opened, the PostgreSQL server assigns a dedicated backend worker process with its own memory allocation.

Establishing a new TCP connection, performing the authentication handshake, and initializing a backend process takes noticeable time and server resources. If your Node.js application attempts to open a fresh database connection on every incoming HTTP request, under moderate traffic the database will quickly exhaust memory or hit its max_connections limit.

Incoming HTTP Requests (100 concurrent)
            │
            ▼
┌─────────────────────────────────────────┐
│        Node.js Application              │
│  ┌───────────────────────────────────┐  │
│  │     Connection Pool (e.g., max: 20)│  │
│  │   [Client] [Client] ... [Client]  │  │
│  └─────────────────┬─────────────────┘  │
└────────────────────┼────────────────────┘
                     │ (Reuses 20 persistent TCP connections)
                     ▼
┌─────────────────────────────────────────┐
│        PostgreSQL Database Server       │
│   (20 Dedicated Backend Worker PIDs)    │
└─────────────────────────────────────────┘

A connection pool solves this by opening a fixed set of persistent connections and reusing them across incoming requests. When a query needs to run, it borrows a connection from the pool, executes the query, and immediately returns the connection to the pool for the next request.

---

Part 1: Connecting with node-postgres (pg)

node-postgres (the pg package) is the standard raw SQL driver for Node.js. It provides direct, non-blocking access to PostgreSQL without an intermediate abstraction layer.

Step 1: Install Dependencies

npm install pg dotenv
npm install -D typescript @types/node @types/pg tsx

Step 2: Configure Environment Variables

Store your database connection parameters in a .env file:

# .env
DB_HOST=localhost
DB_PORT=5432
DB_NAME=production_db
DB_USER=app_user
DB_PASSWORD=your_secure_password
DATABASE_URL=postgresql://app_user:your_secure_password@localhost:5432/production_db?schema=public

---

Step 3: Configure the Connection Pool

Always instantiate a Pool rather than individual Client instances for web servers:

// src/db/pool.ts
import { Pool, PoolConfig } from "pg";
import dotenv from "dotenv";

dotenv.config();

const isProduction = process.env.NODE_ENV === "production";

const poolConfig: PoolConfig = {
  host: process.env.DB_HOST || "localhost",
  port: parseInt(process.env.DB_PORT || "5432", 10),
  database: process.env.DB_NAME || "production_db",
  user: process.env.DB_USER || "postgres",
  password: process.env.DB_PASSWORD,

  // Pool Configuration
  max: 20, // Maximum active connections in the pool
  idleTimeoutMillis: 30000, // Close idle connections after 30 seconds
  connectionTimeoutMillis: 2000, // Fail fast if all connections are busy for > 2s

  // SSL for managed database providers (AWS RDS, DigitalOcean, Neon)
  ssl: isProduction ? { rejectUnauthorized: true } : false,
};

export const pool = new Pool(poolConfig);

// Catch unexpected errors on idle clients to prevent process termination
pool.on("error", (err: Error) => {
  console.error("Unexpected error on idle PostgreSQL client:", err.message);
});

---

Step 4: Safe Querying with Parameterized Statements

Never use string concatenation or template literals to insert user data into SQL queries.

Use parameterized queries with positional placeholders ($1, $2, $3). The database driver sends the query template and the parameter values separately. PostgreSQL compiles the query plan first, treating parameters strictly as literal data values—preventing user input from altering query structure.

// src/db/users.ts
import { pool } from "./pool";

export interface User {
  id: string;
  email: string;
  name: string | null;
  created_at: Date;
}

// Reusable query helper
export async function query<T = any>(text: string, params?: any[]) {
  const start = Date.now();
  const res = await pool.query(text, params);
  const duration = Date.now() - start;

  if (process.env.NODE_ENV !== "production") {
    console.log(`Executed query: ${text} | ${duration}ms | Rows: ${res.rowCount}`);
  }

  return res;
}

// Fetch user by email
export async function getUserByEmail(email: string): Promise<User | null> {
  const sql = `
    SELECT id, email, name, created_at
    FROM users
    WHERE email = $1
    LIMIT 1;
  `;

  const { rows } = await query<User>(sql, [email]);
  return rows[0] || null;
}

// Create new user
export async function createUser(email: string, name: string): Promise<User> {
  const sql = `
    INSERT INTO users (email, name)
    VALUES ($1, $2)
    RETURNING id, email, name, created_at;
  `;

  const { rows } = await query<User>(sql, [email, name]);
  return rows[0];
}

Important Note on Identifiers: Parameterized placeholders ($1) only work for data values. If you need dynamic table names or column names, you cannot use $1. You must validate identifiers against a strict whitelist before constructing the query string.

---

Step 5: Managing Atomic Transactions

When executing multiple operations that must succeed or fail together, wrap them in a transaction (BEGIN, COMMIT, ROLLBACK).

When using a connection pool, you must lease a specific client from the pool for the entire transaction and release it in a finally block so it is not leaked:

// src/db/transactions.ts
import { PoolClient } from "pg";
import { pool } from "./pool";

export async function executeTransaction<T>(
  callback: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect(); // Acquire dedicated connection

  try {
    await client.query("BEGIN");
    const result = await callback(client);
    await client.query("COMMIT");
    return result;
  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    client.release(); // Crucial: return connection back to the pool
  }
}

// Example: User registration with an initial wallet creation
export async function registerUserWithWallet(email: string, name: string, initialBalance: number) {
  return executeTransaction(async (client) => {
    const userRes = await client.query(
      `INSERT INTO users (email, name) VALUES ($1, $2) RETURNING id, email;`,
      [email, name]
    );
    const userId = userRes.rows[0].id;

    await client.query(
      `INSERT INTO wallets (user_id, balance) VALUES ($1, $2);`,
      [userId, initialBalance]
    );

    return { userId, email, initialBalance };
  });
}

---

Step 6: Mapping PostgreSQL Error Codes

PostgreSQL returns standard 5-character SQLSTATE error codes. Handling these in your data layer lets you return appropriate HTTP status codes:

// src/db/errorHandler.ts
import { DatabaseError } from "pg";

export function mapDatabaseError(error: unknown) {
  if (error instanceof DatabaseError) {
    switch (error.code) {
      case "23505": // unique_violation
        return { status: 409, message: "A record with this identifier already exists." };
      case "23503": // foreign_key_violation
        return { status: 400, message: "Referenced foreign record does not exist." };
      case "23502": // not_null_violation
        return { status: 400, message: `Missing required field: ${error.column}` };
      case "08006": // connection_failure
      case "57P01": // admin_shutdown
        return { status: 503, message: "Database connection temporarily unavailable." };
      default:
        console.error(`PostgreSQL Error [${error.code}]:`, error.message);
    }
  }

  return { status: 500, message: "Internal server error." };
}

---

Part 2: Connecting with Prisma ORM

Prisma is a database toolkit and ORM for Node.js and TypeScript. It defines models in a single schema.prisma file, manages schema migrations, and provides an auto-generated TypeScript query client tailored to your schema.

Step 1: Initialize Prisma

npm install @prisma/client
npm install -D prisma
npx prisma init --datasource-provider postgresql

This creates a prisma/schema.prisma file and sets the DATABASE_URL format in your .env.

---

Step 2: Define Models in schema.prisma

Define your models, relations, indexes, and database table mappings:

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String?
  role      Role     @default(USER)
  posts     Post[]
  wallets   Wallet?
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

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

model Post {
  id        String   @id @default(uuid())
  title     String
  slug      String   @unique
  content   String?
  published Boolean  @default(false)
  authorId  String   @map("author_id")
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@index([authorId])
  @@index([slug])
  @@map("posts")
}

model Wallet {
  id        String   @id @default(uuid())
  userId    String   @unique @map("user_id")
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  balance   Decimal  @default(0.00) @db.Decimal(12, 2)
  updatedAt DateTime @updatedAt @map("updated_at")

  @@map("wallets")
}

enum Role {
  USER
  ADMIN
}

---

Step 3: Run Database Migrations

Apply your schema changes to the database:

# In Local Development (creates SQL migration file and applies it):
npx prisma migrate dev --name init_schema

# In Production CI/CD Pipelines (applies existing migrations):
npx prisma migrate deploy

prisma migrate dev generates a timestamped SQL migration in prisma/migrations/ and updates the generated @prisma/client types automatically.

---

Step 4: Global Prisma Client Singleton

In development environments with hot-reloading (such as Next.js or nodemon), re-instantiating PrismaClient on every reload can quickly exhaust connection limits. Use the global singleton pattern:

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

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;
}

---

Step 5: Type-Safe Queries & Transactions

All queries through Prisma are validated by TypeScript against your schema:

// src/services/postService.ts
import { prisma } from "../db/prisma";

// Create User with nested relations in a single call
export async function createUserWithProfile(email: string, name: string, title: string, slug: string) {
  return await prisma.user.create({
    data: {
      email,
      name,
      wallets: {
        create: { balance: 50.0 },
      },
      posts: {
        create: {
          title,
          slug,
          published: true,
        },
      },
    },
    include: {
      wallets: true,
      posts: true,
    },
  });
}

// Query with nested relation filtering
export async function getPublishedPosts() {
  return await prisma.post.findMany({
    where: { published: true },
    select: {
      id: true,
      title: true,
      slug: true,
      createdAt: true,
      author: {
        select: {
          id: true,
          name: true,
          email: true,
        },
      },
    },
    orderBy: { createdAt: "desc" },
  });
}

// Interactive Transaction
export async function transferFunds(fromUserId: string, toUserId: string, amount: number) {
  return await prisma.$transaction(async (tx) => {
    const senderWallet = await tx.wallet.update({
      where: { userId: fromUserId },
      data: { balance: { decrement: amount } },
    });

    if (Number(senderWallet.balance) < 0) {
      throw new Error("Insufficient funds for transfer.");
    }

    const receiverWallet = await tx.wallet.update({
      where: { userId: toUserId },
      data: { balance: { increment: amount } },
    });

    return { senderWallet, receiverWallet };
  });
}

---

Comparing pg and Prisma

Can You Use Both Together?

Yes. In many production systems, teams use Prisma for standard CRUD operations, relationship traversal, and schema migrations, while using pg (or Prisma's $queryRaw) for specific complex reporting queries, bulk inserts, or performance-sensitive batch jobs.

---

Production & Security Considerations

When deploying a Node.js and PostgreSQL backend (such as on a VPS with Nginx and PM2 or Apache):

1. Connection Sizing

PostgreSQL defaults to max_connections = 100. If you run 4 Node.js worker processes via PM2 cluster mode, each configured with a pool max = 20, your application can use up to 80 connections.

• For traditional servers/VPS: Keep pool sizes moderate (max: 10–20 per process).
• For serverless / edge environments (e.g., Next.js on Vercel or AWS Lambda): Many short-lived functions can easily exceed PostgreSQL connection limits. Use a connection pooler like PgBouncer or managed pooling (e.g., Supabase / Neon connection poolers).

2. Least-Privilege Database Users

Avoid connecting application services as the postgres superuser. Create an application-specific user with permissions limited to the required database:

-- Run as superuser
CREATE USER app_user WITH PASSWORD 'secure_random_password';
GRANT CONNECT ON DATABASE production_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

3. Enforce SSL in Production

For cloud-hosted databases, configure SSL connections to encrypt traffic in transit:

ssl: process.env.NODE_ENV === "production" ? { rejectUnauthorized: true } : false

4. Restrict Network Access

If PostgreSQL runs on a dedicated server, configure your firewall (such as Linux UFW) to accept connections only from your application server's IP address:

sudo ufw allow proto tcp from 192.168.1.50 to any port 5432

---

Common Errors and Troubleshooting

1. error: remaining connection slots are reserved for non-replication superuser connections

• Cause: The number of active connections exceeded PostgreSQL's max_connections setting.
• Fix: Check current connections using SELECT count(*), state FROM pg_stat_activity GROUP BY state;. Reduce max in your application connection pool config, or set up PgBouncer to multiplex client connections.

2. PrismaClientInitializationError: Can't reach database server at ...

• Cause: Network unreachable, PostgreSQL service down, or SSL required by the database host.
• Fix: Verify the database host and port are accessible from the application server, and append ?sslmode=require to DATABASE_URL if connecting to a cloud provider.

3. error: duplicate key value violates unique constraint (Code 23505 / Prisma P2002)

• Cause: Attempting to insert a duplicate value on a unique column (e.g., email).
• Fix: Handle error code 23505 (in pg) or PrismaClientKnownRequestError with code P2002 in your request handlers and return a 409 Conflict status.

---

Decision Guidance: Choosing Your Tooling

• Choose node-postgres (pg) when you want total control over every SQL query, are building microservices with specific raw SQL requirements, or prefer writing database migrations manually.
• Choose Prisma when you want fast feature delivery, integrated database migrations, and end-to-end TypeScript types across your application (as detailed in our Next.js App Router Best Practices Guide).
• Combine them when you want Prisma's developer experience for standard application logic, supplemented by raw SQL for complex queries.

---

Keep Learning

• Deploying your Node.js application to a server? Read our guides on How to Host an Express App on a VPS with Nginx or How to Host with Apache.
• Securing your database ports? Check our Linux UFW Firewall Setup Guide.
• Building full-stack React applications? Learn Next.js App Router Best Practices.