Establishing a MySQL Database Connection in Node.js

Almost every real API persists data to a database. This page teaches you how to connect a Node.js Express application to a MySQL database using the mysql2 library, create a connection, and run safe parameterized queries using the callback pattern.

Key Terms

Term	Definition
Database	Organized collection of structured data
Table	Structure that stores data in rows and columns
Row	A single record in a table
Column	A named field in a table (e.g., `email`, `created_at`)
Primary Key	Unique identifier for each row (usually `id`)
Foreign Key	Column referencing the primary key of another table
Parameterized query	SQL with `?` placeholders to prevent SQL injection
Callback	A function passed to `db.query()` that runs when the query completes

Install mysql2

npm install mysql2

Create the Database and Table (MySQL)

Run this in MySQL Workbench or the terminal before starting:

CREATE DATABASE IF NOT EXISTS swdbd_app;
USE swdbd_app;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(191) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('user', 'admin') DEFAULT 'user',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

config/db.js — Creating the Connection

// config/db.js
const mysql = require('mysql2');

const db = mysql.createConnection({
  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 || 'swdbd_app'
});

db.connect(function (err) {
  if (err) {
    console.error('MySQL connection failed:', err.message);
    process.exit(1);
  }
  console.log('MySQL connected successfully');
});

module.exports = db;

Environment Variables

# .env
DB_HOST=localhost
DB_PORT=3306
DB_USER=root
DB_PASS=yourpassword
DB_NAME=swdbd_app

# .env.example
DB_HOST=localhost
DB_PORT=3306
DB_USER=root
DB_PASS=your_mysql_password
DB_NAME=your_database_name

Using db in app.js

// app.js
require('dotenv').config();
const express = require('express');
const db = require('./config/db'); // triggers the connection

const app = express();
app.use(express.json());

app.use('/api/users', require('./routes/userRoutes'));

app.listen(process.env.PORT || 3000, function () {
  console.log('Server running on port ' + (process.env.PORT || 3000));
});

How db.query() Works

All database operations use db.query(sql, params, callback):

db.query(sql, [param1, param2], function (err, results) {
  if (err) {
    // handle the error
    return response.status(500).json({ message: 'Database error', err });
  }
  // use results
});

Argument	Description
`sql`	The SQL string with `?` placeholders
`[params]`	Array of values substituted for `?` in order
`callback`	Function called with `(err, results)` when done

Always check if (err) first inside the callback. If you skip this, a database error will crash the server with an unhandled exception.

Basic Query Examples

SELECT all rows

const db = require('../config/db');

app.get('/api/users', function (req, res) {
  const sql = 'SELECT id, name, email, role FROM users';

  db.query(sql, function (err, results) {
    if (err) {
      return res.status(500).json({ message: 'Error fetching users', err });
    }
    return res.status(200).json({ data: results });
  });
});

SELECT one row by ID

app.get('/api/users/:id', function (req, res) {
  const sql = 'SELECT id, name, email, role FROM users WHERE id = ?';

  db.query(sql, [req.params.id], function (err, results) {
    if (err) {
      return res.status(500).json({ message: 'Error fetching user', err });
    }
    if (results.length === 0) {
      return res.status(404).json({ message: 'User not found' });
    }
    return res.status(200).json({ data: results[0] });
  });
});

INSERT a row

app.post('/api/users', function (req, res) {
  const { name, email, password } = req.body;
  const sql = 'INSERT INTO users (name, email, password) VALUES (?, ?, ?)';

  db.query(sql, [name, email, password], function (err, results) {
    if (err) {
      return res.status(500).json({ message: 'Error creating user', err });
    }
    return res.status(201).json({ message: 'User created', id: results.insertId });
  });
});

UPDATE a row

app.put('/api/users/:id', function (req, res) {
  const { name, email } = req.body;
  const sql = 'UPDATE users SET name = ?, email = ? WHERE id = ?';

  db.query(sql, [name, email, req.params.id], function (err, results) {
    if (err) {
      return res.status(500).json({ message: 'Error updating user', err });
    }
    if (results.affectedRows === 0) {
      return res.status(404).json({ message: 'User not found' });
    }
    return res.status(200).json({ message: 'User updated' });
  });
});

DELETE a row

app.delete('/api/users/:id', function (req, res) {
  const sql = 'DELETE FROM users WHERE id = ?';

  db.query(sql, [req.params.id], function (err, results) {
    if (err) {
      return res.status(500).json({ message: 'Error deleting user', err });
    }
    if (results.affectedRows === 0) {
      return res.status(404).json({ message: 'User not found' });
    }
    return res.status(204).send();
  });
});

db.query() Result Object Reference

Query Type	Key Property	Description
SELECT	`results` (array)	Array of row objects
SELECT one	`results[0]`	First row or `undefined`
INSERT	`results.insertId`	Auto-generated ID of the new row
UPDATE	`results.affectedRows`	Number of rows changed
DELETE	`results.affectedRows`	Number of rows deleted

Why Use `?` Placeholders

Never build SQL by concatenating user input:

// DANGEROUS — SQL injection vulnerability
const sql = "SELECT * FROM users WHERE email = '" + email + "'";

// SAFE — mysql2 escapes the value
const sql = 'SELECT * FROM users WHERE email = ?';
db.query(sql, [email], function (err, results) { ... });

A malicious user sending ' OR '1'='1 as the email would return all users with string concatenation. With ? placeholders, mysql2 escapes it and it becomes a literal string search.

Common Mistakes

Not requiring dotenv before db.js

require('dotenv').config() must come before require('./config/db'). Otherwise process.env.DB_PASS is undefined when the connection is created.

Forgetting to check err in callbacks

Every db.query() callback must start with if (err) { return res.status(500)... }. Skipping this means a DB error will crash the server instead of returning a clean response.

Using results directly for a SELECT expecting one row

results is always an array. For single-row lookups always check results.length === 0 for not found, then use results[0] to access the row.

Not checking affectedRows on UPDATE/DELETE

If the row does not exist, affectedRows is 0. Without this check you return 200 even though nothing happened. Always return 404 when affectedRows === 0.

Node.js Setup

Database & APIs

Key Terms

Install mysql2

Create the Database and Table (MySQL)

config/db.js — Creating the Connection

Environment Variables

Using db in app.js

How db.query() Works

Basic Query Examples

SELECT all rows

SELECT one row by ID

INSERT a row

UPDATE a row

DELETE a row

db.query() Result Object Reference

Why Use `?` Placeholders

Common Mistakes

​Key Terms

​Install mysql2

​Create the Database and Table (MySQL)

​config/db.js — Creating the Connection

​Environment Variables

​Using db in app.js

​How db.query() Works

​Basic Query Examples

​SELECT all rows

​SELECT one row by ID

​INSERT a row

​UPDATE a row

​DELETE a row

​db.query() Result Object Reference

​Why Use ? Placeholders

​Common Mistakes

Key Terms

Install mysql2

Create the Database and Table (MySQL)

config/db.js — Creating the Connection

Environment Variables

Using db in app.js

How db.query() Works

Basic Query Examples

SELECT all rows

SELECT one row by ID

INSERT a row

UPDATE a row

DELETE a row

db.query() Result Object Reference

Why Use `?` Placeholders

Common Mistakes