Database Integration¶

npm install pg

const { Pool } = require('pg')

// connection pool - never create individual clients
const pool = new Pool({
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 5432,
  database: process.env.DB_NAME || 'myapp',
  user: process.env.DB_USER || 'postgres',
  password: process.env.DB_PASSWORD,
  max: 20,                           // max connections in pool
  idleTimeoutMillis: 30000,          // close idle connections after 30s
  connectionTimeoutMillis: 5000      // fail fast if DB is down
})

// query helper
async function query(text , params) {
  const start = Date.now()
  const result = await pool.query(text , params)
  const duration = Date.now() - start
  console.log('Query:', { text: text.slice(0, 80), duration, rows: result.rowCount })
  return result
}

// route
app.get('/users' , async (req , res) => {
  const result = await query('SELECT id , email , name FROM users ORDER BY id')
  res.json(result.rows)
})

app.get('/users/:id' , async (req , res) => {
  const { id } = req.params
  const result = await query('SELECT * FROM users WHERE id = $1' , [id])
  if (result.rows.length === 0) {
    return res.status(404).json({ error: 'User not found' })
  }
  res.json(result.rows[0])
})

npm install mysql2

const mysql = require('mysql2/promise')

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0
})

// route
app.get('/users' , async (req , res) => {
  const [rows] = await pool.execute(
    'SELECT id , email , name FROM users ORDER BY id'
  )
  res.json(rows)
})

npm install mongoose

const mongoose = require('mongoose')

mongoose.connect(process.env.MONGO_URI , {
  maxPoolSize: 10,
  serverSelectionTimeoutMS: 5000,
  socketTimeoutMS: 45000
})

// schema
const userSchema = new mongoose.Schema({
  email: { type: String , required: true , unique: true , lowercase: true },
  name: { type: String , required: true , trim: true },
  passwordHash: { type: String , required: true },
  role: { type: String , enum: ['user' , 'admin'] , default: 'user' },
  createdAt: { type: Date , default: Date.now }
})

// don't return password hash in JSON
userSchema.methods.toJSON = function() {
  const obj = this.toObject()
  delete obj.passwordHash
  delete obj.__v
  return obj
}

const User = mongoose.model('User' , userSchema)

// route
app.get('/users' , async (req , res) => {
  const users = await User.find().select('-passwordHash')
  res.json(users)
})

// src/config/database.js
const { Pool } = require('pg')

let pool

function getPool() {
  if (!pool) {
    pool = new Pool({
      connectionString: process.env.DATABASE_URL,
      max: 20,
      idleTimeoutMillis: 30000
    })

    pool.on('error' , (err) => {
      console.error('Unexpected pool error:', err)
      // pool will auto-recover but log for alerting
    })
  }
  return pool
}

module.exports = { getPool }

// routes/users.js
const { getPool } = require('../config/database')

router.get('/' , async (req , res) => {
  const pool = getPool()
  const result = await pool.query('SELECT * FROM users')
  res.json(result.rows)
})

async function withTransaction(callback) {
  const pool = getPool()
  const client = await pool.connect()
  try {
    await client.query('BEGIN')
    const result = await callback(client)
    await client.query('COMMIT')
    return result
  } catch (err) {
    await client.query('ROLLBACK')
    throw err
  } finally {
    client.release()
  }
}

// usage
app.post('/transfer' , async (req , res) => {
  const { fromId , toId , amount } = req.body

  const result = await withTransaction(async (client) => {
    // deduct from sender
    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1',
      [amount , fromId]
    )

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

    // log transaction
    await client.query(
      'INSERT INTO transfers (from_id , to_id , amount) VALUES ($1 , $2 , $3)',
      [fromId , toId , amount]
    )
  })

  res.json({ success: true })
})

// 1. NEVER log query parameters with sensitive data
// BAD: console.log('Query:', text, params)
// GOOD: console.log('Query:', text) - params might have passwords

// 2. Always use parameterized queries - NEVER string interpolation
// BAD: `SELECT * FROM users WHERE id = ${req.params.id}`
// GOOD: 'SELECT * FROM users WHERE id = $1', [req.params.id]

// 3. Limit returned columns
// BAD: SELECT * FROM users  (returns passwordHash to Node.js)
// GOOD: SELECT id , email , name , role FROM users

// 4. Set statement timeout
await pool.query('SET statement_timeout = 5000')  // kill queries over 5s

// 5. Connection string from env , never hardcoded
// process.env.DATABASE_URL - not a literal string in code