Node.js – Database Connection

Node.js

Database Connection – MySQL, PostgreSQL & MongoDB

Connect a Node.js app to relational and NoSQL databases using mysql2 connection pools, the pg client for PostgreSQL, and Mongoose for MongoDB.

Install Drivers: Pick the driver for your database. You rarely need all three — install only what you use.

📄terminal
BASH

# MySQL
npm install mysql2

# PostgreSQL
npm install pg

# MongoDB
npm install mongoose

MySQL Connection Pool: Use mysql2/promise to create a pool. Pools reuse connections and handle concurrency automatically.

📄src/config/mysql.js
JS

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

const pool = mysql.createPool({
  host:            process.env.DB_HOST || 'localhost',
  port:            process.env.DB_PORT || 3306,
  user:            process.env.DB_USER || 'root',
  password:        process.env.DB_PASS || '',
  database:        process.env.DB_NAME || 'myapp',
  waitForConnections: true,
  connectionLimit:    10,
  queueLimit:         0,
});

// Test the connection on startup
(async () => {
  try {
    const conn = await pool.getConnection();
    console.log('MySQL connected');
    conn.release();
  } catch (err) {
    console.error('MySQL connection failed:', err.message);
    process.exit(1);
  }
})();

module.exports = pool;

Async Query Execution: Destructure the [rows] result from pool.execute(). Always use parameterised queries to prevent SQL injection.

📄src/models/User.js (MySQL)
JS

const pool = require('../config/mysql');

const findAll = async () => {
  const [rows] = await pool.execute('SELECT id, name, email FROM users');
  return rows;
};

const findById = async (id) => {
  const [rows] = await pool.execute(
    'SELECT * FROM users WHERE id = ? LIMIT 1',
    [id]
  );
  return rows[0] || null;
};

const create = async ({ name, email, password }) => {
  const [result] = await pool.execute(
    'INSERT INTO users (name, email, password) VALUES (?, ?, ?)',
    [name, email, password]
  );
  return { id: result.insertId, name, email };
};

const remove = async (id) => {
  const [result] = await pool.execute('DELETE FROM users WHERE id = ?', [id]);
  return result.affectedRows > 0;
};

module.exports = { findAll, findById, create, remove };

PostgreSQL with pg: Create a Pool from the pg package using a connection string or individual config options.

📄src/config/postgres.js
JS

const { Pool } = require('pg');

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  // Or explicit fields:
  // host:     process.env.PG_HOST,
  // port:     5432,
  // user:     process.env.PG_USER,
  // password: process.env.PG_PASS,
  // database: process.env.PG_NAME,
  ssl: process.env.NODE_ENV === 'production'
    ? { rejectUnauthorized: false }
    : false,
});

pool.on('connect',  () => console.log('PostgreSQL pool connected'));
pool.on('error',    (err) => console.error('PG pool error', err));

// Helper for parameterised queries
const query = (text, params) => pool.query(text, params);

module.exports = { query, pool };

PostgreSQL Query Example: Use $1, $2 placeholders (not ?) in pg queries.

📄src/models/User.js (PG)
JS

const { query } = require('../config/postgres');

const findAll = async () => {
  const { rows } = await query('SELECT id, name, email FROM users ORDER BY id');
  return rows;
};

const findByEmail = async (email) => {
  const { rows } = await query(
    'SELECT * FROM users WHERE email = $1 LIMIT 1',
    [email]
  );
  return rows[0] || null;
};

module.exports = { findAll, findByEmail };

MongoDB with Mongoose: Call mongoose.connect() once at startup. Mongoose manages its own internal connection pool.

📄src/config/mongodb.js
JS

const mongoose = require('mongoose');

const connectMongo = async () => {
  try {
    await mongoose.connect(process.env.MONGO_URI, {
      dbName: 'myapp',
    });
    console.log('MongoDB connected:', mongoose.connection.host);
  } catch (err) {
    console.error('MongoDB connection error:', err.message);
    process.exit(1);
  }
};

mongoose.connection.on('disconnected', () =>
  console.warn('MongoDB disconnected')
);

module.exports = connectMongo;