#MySQL2 — Connecting Node.js to a MySQL Database

A guide to connecting a Node.js backend application to a MySQL database using the mysql2 package.

---

#Installation

bashcopynpm install mysql2 dotenv

---

#Environment Variables (.env)

Never store credentials in your code. Use a .env file instead.

textcopyDB_HOST=localhost
DB_USER=root
DB_PASSWORD=yourpassword
DB_NAME=mydatabase
CLIENT_URL=http://localhost:5173

Make sure to add .env to your .gitignore file!

---

#Single Connection

The simplest approach — a single persistent connection to the database.

jscopyconst mysql = require("mysql2");

const connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "yourpassword",
  database: "mydatabase",
});

connection.connect((err) => {
  if (err) {
    console.error("Connection failed:", err.message);
    return;
  }
  console.log("Connected to MySQL!");
});

---

#Connection Pool (Recommended)

A pool manages multiple connections automatically — the preferred approach for backend applications handling many requests.

Create a db.js file:

jscopyrequire("dotenv").config();
const mysql = require("mysql2");

const 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,
});

module.exports = pool.promise();

Import this file wherever you need to query the database.

---

#Executing Queries (Promises)

jscopyconst db = require("./db");

// SELECT all
async function getAllUsers() {
  const [rows] = await db.query("SELECT * FROM users");
  return rows;
}

// SELECT by ID (prevents SQL Injection with ?)
async function getUserById(id) {
  const [rows] = await db.query(
    "SELECT * FROM users WHERE id = ?",
    [id]
  );
  return rows[0];
}

// INSERT
async function createUser(name, email) {
  const [result] = await db.query(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    [name, email]
  );
  return result.insertId;
}

// UPDATE
async function updateUser(id, name) {
  const [result] = await db.query(
    "UPDATE users SET name = ? WHERE id = ?",
    [name, id]
  );
  return result.affectedRows;
}

// DELETE
async function deleteUser(id) {
  const [result] = await db.query(
    "DELETE FROM users WHERE id = ?",
    [id]
  );
  return result.affectedRows;
}

Always use ? placeholders instead of string interpolation to prevent
SQL Injection attacks.

---

#Integration with Express.js

jscopyconst express = require("express");
const router = express.Router();
const db = require("../db");

// GET /users
router.get("/", async (req, res) => {
  try {
    const [rows] = await db.query("SELECT * FROM users");
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET /users/:id
router.get("/:id", async (req, res) => {
  try {
    const [rows] = await db.query(
      "SELECT * FROM users WHERE id = ?",
      [req.params.id]
    );
    if (rows.length === 0)
      return res.status(404).json({ error: "User not found" });
    res.json(rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// POST /users
router.post("/", async (req, res) => {
  try {
    const { name, email } = req.body;
    const [result] = await db.query(
      "INSERT INTO users (name, email) VALUES (?, ?)",
      [name, email]
    );
    res.status(201).json({ id: result.insertId, name, email });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// PUT /users/:id
router.put("/:id", async (req, res) => {
  try {
    const { name } = req.body;
    const [result] = await db.query(
      "UPDATE users SET name = ? WHERE id = ?",
      [name, req.params.id]
    );
    if (result.affectedRows === 0)
      return res.status(404).json({ error: "User not found" });
    res.json({ message: "User updated" });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// DELETE /users/:id
router.delete("/:id", async (req, res) => {
  try {
    const [result] = await db.query(
      "DELETE FROM users WHERE id = ?",
      [req.params.id]
    );
    if (result.affectedRows === 0)
      return res.status(404).json({ error: "User not found" });
    res.json({ message: "User deleted" });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;

---

#Project Structure (Recommended)

textcopymy-app/
├── routes/
│   └── users.js
├── middleware/
│   └── cors.js
├── db.js
├── index.js
├── .env
└── package.json