menu_bookGyanByte
nodejs5 Min Read

Database Integration — PostgreSQL with Node.js

Gorav Singal

April 02, 2026

TL;DR

Use pg for raw SQL control, Knex.js for a query builder with migrations, or Prisma for type-safe ORM. Always use connection pooling, parameterized queries to prevent SQL injection, and transactions for multi-step operations.

Database Integration — PostgreSQL with Node.js

Choosing Your PostgreSQL Client

Node.js has three main approaches to working with PostgreSQL — each with different tradeoffs:

Approach Library Best For
Raw SQL pg (node-postgres) Full control, complex queries
Query Builder Knex.js SQL-like syntax with migrations
ORM Prisma Type safety, schema-first workflow

node-postgres (pg) — Raw SQL

Connection Pooling

Never create a new connection per query. Use a connection pool.

Connection Pooling

const { Pool } = require('pg');

const pool = new Pool({
  host: process.env.DB_HOST,
  port: 5432,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 20,                 // Maximum pool size
  idleTimeoutMillis: 30000, // Close idle connections after 30s
  connectionTimeoutMillis: 5000, // Fail if can't connect in 5s
});

// Monitor pool events
pool.on('error', (err) => {
  console.error('Unexpected pool error:', err);
});

// Graceful shutdown
process.on('SIGTERM', async () => {
  await pool.end();
  process.exit(0);
});

Parameterized Queries (SQL Injection Prevention)

// NEVER do this — SQL injection vulnerable
const bad = await pool.query(`SELECT * FROM users WHERE id = ${userId}`);

// ALWAYS use parameterized queries
const { rows } = await pool.query(
  'SELECT id, name, email FROM users WHERE id = $1',
  [userId]
);

// Multiple parameters
const result = await pool.query(
  `SELECT * FROM products
   WHERE category = $1 AND price BETWEEN $2 AND $3
   ORDER BY created_at DESC
   LIMIT $4 OFFSET $5`,
  [category, minPrice, maxPrice, limit, offset]
);

CRUD Operations

// Create
async function createUser(name, email, passwordHash) {
  const { rows } = await pool.query(
    `INSERT INTO users (name, email, password_hash)
     VALUES ($1, $2, $3)
     RETURNING id, name, email, created_at`,
    [name, email, passwordHash]
  );
  return rows[0];
}

// Read with pagination
async function getUsers(page = 1, limit = 20) {
  const offset = (page - 1) * limit;

  const [dataResult, countResult] = await Promise.all([
    pool.query(
      'SELECT id, name, email, created_at FROM users ORDER BY created_at DESC LIMIT $1 OFFSET $2',
      [limit, offset]
    ),
    pool.query('SELECT COUNT(*) FROM users'),
  ]);

  return {
    items: dataResult.rows,
    total: parseInt(countResult.rows[0].count),
    page,
    totalPages: Math.ceil(countResult.rows[0].count / limit),
  };
}

// Update
async function updateUser(id, updates) {
  const fields = Object.keys(updates);
  const values = Object.values(updates);

  const setClause = fields
    .map((field, i) => `${field} = $${i + 2}`)
    .join(', ');

  const { rows } = await pool.query(
    `UPDATE users SET ${setClause}, updated_at = NOW()
     WHERE id = $1 RETURNING *`,
    [id, ...values]
  );
  return rows[0];
}

// Delete
async function deleteUser(id) {
  const { rowCount } = await pool.query(
    'DELETE FROM users WHERE id = $1',
    [id]
  );
  return rowCount > 0;
}

Transactions

async function transferFunds(fromId, toId, amount) {
  const client = await pool.connect();

  try {
    await client.query('BEGIN');

    // Debit sender
    const { rows: [sender] } = await client.query(
      `UPDATE accounts SET balance = balance - $1
       WHERE id = $2 AND balance >= $1
       RETURNING balance`,
      [amount, fromId]
    );

    if (!sender) {
      throw new Error('Insufficient funds');
    }

    // Credit receiver
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
      [amount, toId]
    );

    // Log the transfer
    await client.query(
      `INSERT INTO transfers (from_id, to_id, amount)
       VALUES ($1, $2, $3)`,
      [fromId, toId, amount]
    );

    await client.query('COMMIT');
    return { success: true, newBalance: sender.balance };
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Knex.js — Query Builder

Knex provides a SQL-like API with built-in migration support.

const knex = require('knex')({
  client: 'pg',
  connection: {
    host: process.env.DB_HOST,
    database: process.env.DB_NAME,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
  },
  pool: { min: 2, max: 20 },
});

// Select with joins
const users = await knex('users')
  .select('users.id', 'users.name', 'orders.total')
  .leftJoin('orders', 'users.id', 'orders.user_id')
  .where('users.active', true)
  .orderBy('users.created_at', 'desc')
  .limit(20);

// Insert
const [user] = await knex('users')
  .insert({ name: 'John', email: '[email protected]' })
  .returning('*');

// Transaction
await knex.transaction(async (trx) => {
  const [order] = await trx('orders')
    .insert({ user_id: userId, total: 99.99 })
    .returning('*');

  await trx('order_items').insert(
    items.map(item => ({ order_id: order.id, ...item }))
  );
});

Knex Migrations

npx knex migrate:make create_users_table
// migrations/20260402_create_users.js
exports.up = function(knex) {
  return knex.schema.createTable('users', (table) => {
    table.increments('id').primary();
    table.string('name', 100).notNullable();
    table.string('email', 255).notNullable().unique();
    table.string('password_hash', 255).notNullable();
    table.enum('role', ['user', 'admin']).defaultTo('user');
    table.timestamps(true, true); // created_at, updated_at
    table.index(['email']);
  });
};

exports.down = function(knex) {
  return knex.schema.dropTable('users');
};

Prisma — Type-Safe ORM

Prisma takes a schema-first approach with auto-generated TypeScript types.

Schema Definition

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

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

model User {
  id        Int      @id @default(autoincrement())
  name      String   @db.VarChar(100)
  email     String   @unique @db.VarChar(255)
  password  String   @db.VarChar(255)
  role      Role     @default(USER)
  posts     Post[]
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  @@map("users")
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String   @db.VarChar(255)
  content   String
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int      @map("author_id")
  createdAt DateTime @default(now()) @map("created_at")

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

enum Role {
  USER
  ADMIN
}

Migration Workflow

Prisma CRUD

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

// Create with relation
const user = await prisma.user.create({
  data: {
    name: 'Alice',
    email: '[email protected]',
    password: hashedPassword,
    posts: {
      create: [
        { title: 'First Post', content: 'Hello world' },
      ],
    },
  },
  include: { posts: true },
});

// Find with pagination and filtering
const users = await prisma.user.findMany({
  where: {
    role: 'USER',
    name: { contains: 'ali', mode: 'insensitive' },
  },
  select: {
    id: true,
    name: true,
    email: true,
    _count: { select: { posts: true } },
  },
  orderBy: { createdAt: 'desc' },
  skip: 0,
  take: 20,
});

// Update
const updated = await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Alice Smith' },
});

// Transaction
const [order, payment] = await prisma.$transaction([
  prisma.order.create({ data: { userId: 1, total: 99.99 } }),
  prisma.payment.create({ data: { userId: 1, amount: 99.99 } }),
]);

// Interactive transaction
await prisma.$transaction(async (tx) => {
  const account = await tx.account.findUnique({
    where: { id: fromId },
  });

  if (account.balance < amount) {
    throw new Error('Insufficient funds');
  }

  await tx.account.update({
    where: { id: fromId },
    data: { balance: { decrement: amount } },
  });

  await tx.account.update({
    where: { id: toId },
    data: { balance: { increment: amount } },
  });
});

Query Optimization Tips

-- Add indexes for frequently filtered/sorted columns
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

-- Use EXPLAIN ANALYZE to understand query plans
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC;
// Avoid N+1 queries
// BAD: One query per user
const users = await prisma.user.findMany();
for (const user of users) {
  user.posts = await prisma.post.findMany({ where: { authorId: user.id } });
}

// GOOD: Single query with include
const users = await prisma.user.findMany({
  include: { posts: true },
});

Choosing the Right Tool

Need Choose
Complex SQL, stored procedures pg
SQL comfort with migration tooling Knex.js
Type safety, rapid development Prisma
Maximum performance control pg
Team prefers schema-first Prisma

For most new projects, Prisma offers the best developer experience. Fall back to raw pg queries for performance-critical paths where you need fine-grained control.

nodejspostgresqldatabaseprismaknexbackend
Share

Related Posts

MongoDB with Mongoose — Patterns and Pitfalls

MongoDB with Mongoose — Patterns and Pitfalls

Schema Design Philosophy MongoDB schema design is fundamentally different from…

WebSockets with Socket.io in Node.js

WebSockets with Socket.io in Node.js

WebSocket vs HTTP Traditional HTTP follows a request/response model — the client…

Testing Node.js — Unit, Integration, and E2E

Testing Node.js — Unit, Integration, and E2E

Testing Strategy A solid testing strategy follows the testing pyramid — many…

Redis — Caching, Sessions, Pub/Sub in Node.js

Redis — Caching, Sessions, Pub/Sub in Node.js

Why Redis for Node.js Redis is an in-memory data store that serves as a cache…

Performance Optimization and Profiling in Node.js

Performance Optimization and Profiling in Node.js

Profiling First, Optimize Second Never optimize blindly. Always profile to find…

Node.js Architecture — Event Loop Deep Dive

Node.js Architecture — Event Loop Deep Dive

Why the Event Loop Matters Node.js runs JavaScript on a single thread, yet…

Latest Posts

AI Video Generation in 2025 — Models, Costs, and How to Build a Cost-Effective Pipeline

AI Video Generation in 2025 — Models, Costs, and How to Build a Cost-Effective Pipeline

AI video generation went from “cool demo” to “usable in production” in 2024-202…

AI Models in 2025 — Cost, Capabilities, and Which One to Use

AI Models in 2025 — Cost, Capabilities, and Which One to Use

Choosing the right AI model is one of the most impactful decisions you’ll make…

AI Image Generation in 2025 — Models, Costs, and How to Optimize Spend

AI Image Generation in 2025 — Models, Costs, and How to Optimize Spend

Generating one image with AI costs between $0.002 and $0.12. That might sound…

AI Coding Assistants in 2025 — Every Tool Compared, and Which One to Actually Use

AI Coding Assistants in 2025 — Every Tool Compared, and Which One to Actually Use

Two years ago, AI coding meant one thing: GitHub Copilot autocompleting your…

AI Agents Demystified — It's Just Automation With a Better Brain

AI Agents Demystified — It's Just Automation With a Better Brain

Let’s cut through the noise. If you read Twitter or LinkedIn, you’d think “AI…

Security Ticketing and Incident Response

Security Ticketing and Incident Response

The worst time to figure out your incident response process is during an…