Database Operations and Connection Pooling

Challenge

Connect to databases efficiently, manage connection pooling, and handle transactions.

Explanation

Efficient database access is critical for application performance. Connection pooling prevents resource exhaustion.

Basic Database Connection

// MySQL with mysql2/promise
const mysql = require('mysql2/promise');

const connection = await mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb'
});

// Execute queries
const [rows] = await connection.execute(
  'SELECT * FROM users WHERE id = ?',
  [1]
);

await connection.end();

Connection Pooling

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb',
  waitForConnections: true,
  connectionLimit: 10,        // Max connections
  queueLimit: 0               // Unlimited queue
});

// Connections automatically released
const connection = await pool.getConnection();
const [rows] = await connection.execute('SELECT * FROM users');
connection.release();

// Pool handles reuse automatically

Prepared Statements

// Prevent SQL injection with placeholders
const [rows] = await pool.execute(
  'SELECT * FROM users WHERE email = ? AND status = ?',
  ['user@example.com', 'active']
);

// ? replaced with safe values by driver

Transactions

const connection = await pool.getConnection();

try {
  await connection.beginTransaction();

  // Multiple operations
  await connection.execute(
    'UPDATE accounts SET balance = balance - ? WHERE id = ?',
    [100, 1]
  );

  await connection.execute(
    'UPDATE accounts SET balance = balance + ? WHERE id = ?',
    [100, 2]
  );

  await connection.commit();
  console.log('Transaction completed');
} catch (error) {
  await connection.rollback();
  console.error('Transaction failed:', error);
} finally {
  connection.release();
}

ORMs: Sequelize

const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize(
  'database',
  'username',
  'password',
  { host: 'localhost', dialect: 'mysql' }
);

const User = sequelize.define('User', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true
  },
  name: DataTypes.STRING
});

// Create table if not exists
await sequelize.sync();

// Insert
const user = await User.create({
  email: 'user@example.com',
  name: 'John'
});

// Query
const users = await User.findAll({ where: { name: 'John' } });

// Update
await user.update({ name: 'Jane' });

// Delete
await user.destroy();

MongoDB with Mongoose

const mongoose = require('mongoose');

await mongoose.connect('mongodb://localhost/mydb');

// Define schema
const userSchema = new mongoose.Schema({
  email: { type: String, required: true, unique: true },
  name: String,
  created: { type: Date, default: Date.now }
});

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

// Create
const user = await User.create({
  email: 'user@example.com',
  name: 'John'
});

// Read
const foundUser = await User.findById(user._id);
const allUsers = await User.find({ name: 'John' });

// Update
await User.updateOne(
  { _id: user._id },
  { name: 'Jane' }
);

// Delete
await User.deleteOne({ _id: user._id });

Connection Pooling Best Practices

// Create pool once, reuse globally
let pool;

async function getPool() {
  if (!pool) {
    pool = mysql.createPool({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASS,
      database: process.env.DB_NAME,
      waitForConnections: true,
      connectionLimit: 10
    });
  }
  return pool;
}

// Use in handlers
app.get('/users/:id', async (req, res) => {
  const pool = await getPool();
  const [rows] = await pool.execute(
    'SELECT * FROM users WHERE id = ?',
    [req.params.id]
  );
  res.json(rows[0]);
});

// Cleanup on shutdown
process.on('SIGTERM', async () => {
  const pool = await getPool();
  await pool.end();
});

Error Handling

const pool = mysql.createPool(config);

pool.on('error', (err) => {
  console.error('Pool error:', err);
  // Unexpected error from pool
  // Connections are auto-removed
});

async function query(sql, params) {
  try {
    const connection = await pool.getConnection();
    const [rows] = await connection.execute(sql, params);
    connection.release();
    return rows;
  } catch (error) {
    if (error.code === 'ER_DUP_ENTRY') {
      console.error('Duplicate entry error');
    } else if (error.code === 'PROTOCOL_CONNECTION_LOST') {
      console.error('Connection lost');
    } else {
      console.error('Database error:', error);
    }
    throw error;
  }
}