Working with Database Integration
1. Connecting to MongoDB
Example: Mongoose
import mongoose from "mongoose";
await mongoose.connect(process.env.MONGO_URL, { maxPoolSize: 20, serverSelectionTimeoutMS: 5_000 });
mongoose.connection.on("error", (err) => logger.error({ err }, "Mongo error"));2. Connecting to PostgreSQL
Example: pg Pool
import pg from "pg";
export const pool = new pg.Pool({
connectionString: process.env.DATABASE_URL,
max: 20, idleTimeoutMillis: 30_000, connectionTimeoutMillis: 5_000
});3. Connecting to MySQL
Example: mysql2 pool
import mysql from "mysql2/promise";
export const pool = mysql.createPool({
uri: process.env.MYSQL_URL,
connectionLimit: 20, waitForConnections: true, queueLimit: 0
});4. Creating Database Connection Pool
| Setting | Guidance |
|---|---|
| max connections | ≈ (CPU cores * 2) + effective_spindle_count per process |
| idleTimeout | 30s |
| connectionTimeout | 3–5s; fail fast |
| statementTimeout | Set on Postgres to bound long queries |
5. Handling Connection Errors
Example: Pool error handler
pool.on("error", (err) => {
logger.error({ err }, "Idle pg client error");
// process.exit(1); // optionally crash so orchestrator restarts
});6. Using Environment Variables
Example: Validated env
const env = z.object({
DATABASE_URL: z.string().url(),
DB_POOL_MAX: z.coerce.number().int().min(1).default(20)
}).parse(process.env);7. Implementing Connection Retry Logic
Example: Exponential backoff
async function connectWithRetry(fn, { tries = 5, baseMs = 500 } = {}) {
for (let i = 0; i < tries; i++) {
try { return await fn(); }
catch (err) {
if (i === tries - 1) throw err;
const wait = baseMs * 2 ** i + Math.random() * 100;
logger.warn({ err, attempt: i + 1, waitMs: wait }, "DB connect failed, retrying");
await new Promise(r => setTimeout(r, wait));
}
}
}8. Closing Database Connections
Example: Graceful shutdown
async function shutdown() {
await Promise.allSettled([pool.end(), redis.quit(), mongoose.connection.close()]);
process.exit(0);
}
["SIGTERM","SIGINT"].forEach(sig => process.on(sig, shutdown));9. Using Database Middleware
Example: Attach client per request
app.use(async (req, res, next) => {
req.db = await pool.connect();
res.on("finish", () => req.db.release());
next();
});Warning: Always release connections — leaked connections quickly exhaust the pool. Prefer wrappers like Prisma that manage release automatically.
10. Implementing Database Transaction Handling
Example: Postgres transaction wrapper
export async function withTx(fn) {
const client = await pool.connect();
try {
await client.query("BEGIN");
const result = await fn(client);
await client.query("COMMIT");
return result;
} catch (err) {
await client.query("ROLLBACK").catch(() => {});
throw err;
} finally {
client.release();
}
}
await withTx(async (db) => {
await db.query("UPDATE accounts SET balance = balance - $1 WHERE id = $2", [amt, from]);
await db.query("UPDATE accounts SET balance = balance + $1 WHERE id = $2", [amt, to]);
});