Database Integration MySQL

Last Updated : Oct 2026

In previous tutorials, we have learned how to Connect MongoDB with Express.js. Now let's see how to connect MySQL with Express.js.

Connecting Express to MySQL with mysql2 (Connection Pool)

To get started with real code, install the required packages in your Express project:

Step 1: Install Dependencies

npm install mysql2 dotenv

Step 2: Environment Configuration (.env)

Create a .env file in your project root to store your database credentials securely:

PORT=3000
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=your_password
DB_NAME=my_express_db

Step 3: Database Connection

For Better Structure, Create a folder called lib in the root of your project and create a db.js file in it:

import mysql from "mysql2/promise";

let pool;

export const connectDb = async () => {
  try {
    if (pool) return pool;
    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,
    });

    // Test connection
    const connection = await pool.getConnection();
    console.log("Connected to MySQL");
    connection.release(); //  return the connection to the pool
  } catch (error) {
    console.error(`Error connecting to MySQL: ${error.message}`);
    process.exit(1); // Exit process with failure
  }
};

export const getPool = () => pool;

Step 4: Server Setup (server.js)

import express from "express";
import dotenv from "dotenv";
import { connectDb } from "./lib/db.js";

dotenv.config();

const app = express();

app.use(express.json());

await connectDb();

app.get("/", (req, res) => {
  res.send("API is running");
});

app.listen(process.env.PORT, () => {
  console.log(`Server running on http://localhost:${process.env.PORT}`);
});

Step 5: Start the Server

node --watch server.js

You should see the following message in the console:

Connected to MySQL
Server running on http://localhost:3000

Basic CRUD Operations

mysql2 provides a simple and easy-to-use API for interacting with MySQL databases. Here's how you can perform basic CRUD (Create, Read, Update, Delete) operations using mysql2

Note: Always use ? placeholders for parameter substitution. mysql2 automatically escapes values to prevent SQL injection vulnerabilities.

Create a Table

Run a custom script to create a table in MySQL. Create utils/initDb.js:

import { getPool } from "../lib/db.js";

const init = async () => {
  try {
    console.log("Running database setup...");

    const pool = getPool();

    await pool.execute(`
      CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) NOT NULL UNIQUE,
        role ENUM('user', 'admin') DEFAULT 'user',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );
    `);

    console.log("Tables created successfully.");
    process.exit(0);
  } catch (error) {
    console.error(" Migration failed:", error.message);
    process.exit(1);
  }
};

init();

Add an NPM script to package.json:

"scripts": {
  "start": "node server.js",
  "dev": "node --watch server.js",
  "db:init": "node utils/initDb.js"
}

Now, you only run npm run db:init manually when you set up the environment or change your database schema.

Create a User

import { getPool } from "./lib/db.js";

export const createUser = async (name, email) => {
  const pool = getPool();

  const query = `INSERT INTO users (name, email) VALUES (?, ?)`;

  // pool.execute returns [result, fields]
  const [result] = await pool.execute(query, [name, email]);

  // result.insertId gives the auto-incremented primary key
  return { id: result.insertId, name, email };
};

Read a User

import { getPool } from "./lib/db.js";

// Fetch all users
export const getAllUsers = async () => {
  const pool = getPool();

  const query = `SELECT id, name, email, created_at FROM users`;
  const [rows] = await pool.execute(query);
  return rows;
};

// Fetch single user by ID
export const getUserById = async (id) => {
  const query = `SELECT id, name, email, created_at FROM users WHERE id = ?`;
  const [rows] = await pool.execute(query, [id]);

  // Returns undefined if user is not found
  return rows[0];
};

Update a User

import { getPool } from "./lib/db.js";

export const updateUser = async (id, name, email) => {
  const pool = getPool();

  const query = `UPDATE users SET name = ?, email = ? WHERE id = ?`;

  const [result] = await pool.execute(query, [name, email, id]);

  // result.affectedRows indicates if any row was updated
  if (result.affectedRows === 0) {
    return null; // User not found
  }

  return { id, name, email };
};

Delete a User

import { getPool } from "./lib/db.js";

export const deleteUser = async (id) => {
  const pool = getPool();

  const query = `DELETE FROM users WHERE id = ?`;

  const [result] = await pool.execute(query, [id]);

  return result.affectedRows > 0; // Returns true if deleted, false if not found
};

Note: Above functions are just examples, you can use them as per your needs.

Report An Issue

← Previous Next →