Skip to content

Database Connections

Your app is dead in the water without a working DB connection Every Node database driver has its quirks - connection strings that break silently , pool settings that look fine until production hits 500 concurrent users , and timeouts that fire at exactly the wrong moment

Connection Strings

// PostgreSQL
DATABASE_URL=postgresql://user:password@localhost:5432/mydb

// MySQL
DATABASE_URL=mysql://user:password@localhost:3306/mydb

// MongoDB
DATABASE_URL=mongodb://user:password@localhost:27017/mydb

// Redis
REDIS_URL=redis://user:password@localhost:6379

Never hardcode connection strings Put them in environment variables and never commit them to git If you leak a connection string , someone owns your database

Connecting to PostgreSQL

The pg package is the standard PostgreSQL driver - pg-pool manages connection pools

const { Pool } = require('pg')

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000
})

// Single query
const { rows } = await pool.query('SELECT * FROM users WHERE id = $1', [userId])
console.log('User found:', rows[0])

// Named pool for different workloads
const reportPool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 5,
  statement_timeout: 30000  // kill slow queries after 30s
})

const apiPool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  statement_timeout: 5000   // API queries should be fast
})

Separate pools for different workloads prevent a slow reporting query from starving your API connections

Connecting to MySQL

The mysql2 package supports promises natively - no callback hell here

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,
  port: 3306,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0
})

const [rows] = await pool.query('SELECT * FROM users WHERE id = ?', [userId])
console.log('User:', rows[0])

mysql2 uses ? placeholders - different from pg's $1 syntax Get them mixed up and your queries silently fail

Connecting to MongoDB

Mongoose wraps the MongoDB driver with schema validation and model abstractions

const mongoose = require('mongoose')

await mongoose.connect(process.env.MONGODB_URI, {
  maxPoolSize: 10,
  serverSelectionTimeoutMS: 5000,
  socketTimeoutMS: 45000,
  family: 4  // Force IPv4 , avoid IPv6 issues
})

const db = mongoose.connection

db.on('error', (err) => {
  console.error('MongoDB connection error:', err)
})

db.on('disconnected', () => {
  console.warn('MongoDB disconnected - attempting reconnect')
})

Mongoose caches queries during disconnection by default That sounds helpful until your app silently queues writes that never reach the database and you lose data

Connecting to Redis

const Redis = require('ioredis')

const redis = new Redis({
  host: process.env.REDIS_HOST || 'localhost',
  port: 6379,
  password: process.env.REDIS_PASSWORD,
  retryStrategy(times) {
    const delay = Math.min(times * 50, 2000)
    return delay
  },
  maxRetriesPerRequest: 3,
  enableReadyCheck: true,
  lazyConnect: true
})

await redis.connect()

redis.on('error', (err) => {
  console.error('Redis error:', err)
})

redis.on('connect', () => {
  console.info('Redis connected')
})

ioredis auto-reconnects by default - that's convenient until it silently reconnects to a wrong Redis instance in a failover scenario

Connection Pooling Best Practices

Get pool settings wrong and your app falls over under load

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,                    // Max concurrent connections
  min: 4,                     // Keep this many idle
  idleTimeoutMillis: 30000,   // Close idle connections after 30s
  acquireTimeoutMillis: 10000,// Fail if no connection in 10s
  createTimeoutMillis: 3000,  // Timeout for creating a new connection
  destroyTimeoutMillis: 5000, // Timeout for destroying a stale connection
  reapIntervalMillis: 1000    // Check for stale connections every 1s
})
  • max should be based on your database server's max_connections setting
  • Set max lower than DB limit - leave room for admin queries
  • Too many idle connections waste memory on the database server
  • Connection acquisition timeouts prevent cascading failures
// Pool drain for graceful shutdown
async function shutdown() {
  console.info('Closing database pool...')
  await pool.end()
  console.info('Pool closed')
  process.exit(0)
}

process.on('SIGTERM', shutdown)
process.on('SIGINT', shutdown)

Always drain connection pools on shutdown Dangling connections lock up database resources and ops will hate you

Connection Retry with Exponential Backoff

No database is up 100% of the time - handle transient failures

async function connectWithRetry(maxRetries = 5) {
  let attempt = 0
  while (attempt < maxRetries) {
    try {
      await mongoose.connect(process.env.MONGODB_URI)
      console.info('Database connected')
      return
    } catch (err) {
      attempt++
      const delay = Math.pow(2, attempt) * 1000
      console.error(`Connection attempt ${attempt} failed , retrying in ${delay}ms`)
      await new Promise(resolve => setTimeout(resolve, delay))
    }
  }
  throw new Error('Failed to connect after max retries')
}

Exponential backoff gives the database time to recover Immediate retries crash your app harder when the DB is already struggling

SSL/TLS for Database Connections

In production , always encrypt database traffic

// PostgreSQL with SSL
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync('/etc/ssl/certs/db-ca.crt').toString()
  }
})

// MongoDB with SSL
await mongoose.connect(process.env.MONGODB_URI, {
  ssl: true,
  sslCA: fs.readFileSync('/path/to/ca.pem'),
  sslCert: fs.readFileSync('/path/to/client-cert.pem'),
  sslKey: fs.readFileSync('/path/to/client-key.pem')
})

// Redis with TLS
const redis = new Redis({
  host: process.env.REDIS_HOST,
  port: 6380,
  tls: {
    rejectUnauthorized: true,
    ca: [fs.readFileSync('/etc/ssl/certs/redis-ca.crt')]
  }
})

Self-signed certs in dev are fine - in prod , use proper CA-signed certs or TLS termination

Prerequisites

next -> db_02_sql.md