> ## Documentation Index
> Fetch the complete documentation index at: https://docs.loremstock.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Establishing a MySQL Database Connection in Node.js

> Connect Node.js to MySQL using mysql2's createConnection, run queries with the callback-style db.query(), define tables with SQL, and use parameterized queries safely.

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

```bash theme={null}
npm install mysql2
```

## Create the Database and Table (MySQL)

Run this in MySQL Workbench or the terminal before starting:

```sql theme={null}
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

```javascript theme={null}
// 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 theme={null}
# .env
DB_HOST=localhost
DB_PORT=3306
DB_USER=root
DB_PASS=yourpassword
DB_NAME=swdbd_app
```

```env theme={null}
# .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

```javascript theme={null}
// 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)`:

```javascript theme={null}
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 |

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

## Basic Query Examples

### SELECT all rows

```javascript theme={null}
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

```javascript theme={null}
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

```javascript theme={null}
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

```javascript theme={null}
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

```javascript theme={null}
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:

```javascript theme={null}
// 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

<Accordion title="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.
</Accordion>

<Accordion title="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.
</Accordion>

<Accordion title="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.
</Accordion>

<Accordion title="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`.
</Accordion>