Database Integration — PostgreSQL with Node.js

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

// 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]
);

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

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

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

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

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

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