Node.js & MySQL — Beginner Tutorial

📌 Introduction

Now that you understand how to work with Express routes and middleware,
It’s time to connect Node.js to a real database — MySQL.

In this beginner tutorial, you will learn how to:

• Connect Node.js to a MySQL database

• Run basic SQL queries

• Build simple CRUD (Create, Read, Update, Delete) API routes

• Test APIs using Postman

This guide is written step by step and is perfect for beginners.

✅ Prerequisites

Before starting, make sure you have the following installed:

• Node.js and npm

• MySQL running locally

  • Default user: root

• A MySQL database named testdb

Create Database & Table

Run the following commands in your MySQL terminal:

CREATE DATABASE testdb;
USE testdb;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

📁 Step 1 — Set Up the Project

Create a new project folder and move into it:

mkdir node-mysql
cd node-mysql

Initialize the project and install dependencies:

npm init -y
npm install express mysql2

Create the main application file:

touch app.js

🔌 Step 2 — Connect Node.js to MySQL

Open app.js and add the following code:

const express = require('express');
const mysql = require('mysql2');
const app = express();
const PORT = 3000;

app.use(express.json()); // Parse JSON requests

// Create MySQL connection
const db = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '', // leave empty if none
  database: 'testdb'
});

// Connect to MySQL
db.connect(err => {
  if (err) throw err;
  console.log('✅ MySQL Connected!');
});

Run the application:

nodemon app.js

Output:

✅ MySQL Connected!

➕ Step 3 — Insert Data (POST)

Add a route to insert a new user:

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

  db.query(sql, [name, email], (err, result) => {
    if (err) throw err;
    res.json({
      message: 'User added successfully',
      id: result.insertId
    });
  });
});

Test with Postman

POST → http://localhost:3000/users
Body (JSON):

{
  "name": "Souy",
  "email": "souy@example.com"
}

Response:

{
  "message": "User added successfully",
  "id": 1
}

📥 Step 4 — Get All Users (GET)

Add the GET route:

app.get('/users', (req, res) => {
  db.query('SELECT * FROM users', (err, results) => {
    if (err) throw err;
    res.json(results);
  });
});

Test:

GET → http://localhost:3000/users

Response:

[
  {
    "id": 1,
    "name": "Souy",
    "email": "souy@example.com"
  }
]

✏ Step 5 — Update User (PUT)

Add the update route:

app.put('/users/:id', (req, res) => {
  const { name, email } = req.body;
  const { id } = req.params;

  const sql = 'UPDATE users SET name = ?, email = ? WHERE id = ?';

  db.query(sql, [name, email, id], err => {
    if (err) throw err;
    res.json({ message: 'User updated successfully' });
  });
});

Test:

PUT → http://localhost:3000/users/1
Body (JSON):

{
  "name": "Souy Updated",
  "email": "souy@newmail.com"
}

❌ Step 6 — Delete User (DELETE)

Add the delete route:

app.delete('/users/:id', (req, res) => {
  const { id } = req.params;

  db.query('DELETE FROM users WHERE id = ?', [id], err => {
    if (err) throw err;
    res.json({ message: 'User deleted successfully' });
  });
});

Test:

DELETE → http://localhost:3000/users/1

Response:

{
  "message": "User deleted successfully"
}

📄 Step 7 — Full app.js File

const express = require('express');
const mysql = require('mysql2');
const app = express();
const PORT = 3000;

app.use(express.json());

// Home route ✅
app.get('/', (req, res) => {
  res.send('🚀 Node.js & MySQL API is running');
});

// MySQL connection
const db = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'testdb'
});

db.connect(err => {
  if (err) throw err;
  console.log('✅ MySQL Connected!');
});

// CRUD routes
app.get('/users', (req, res) => {
  db.query('SELECT * FROM users', (err, results) => {
    if (err) throw err;
    res.json(results);
  });
});

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

  db.query(sql, [name, email], (err, result) => {
    if (err) throw err;
    res.json({ message: 'User added successfully', id: result.insertId });
  });
});

app.put('/users/:id', (req, res) => {
  const { name, email } = req.body;
  const { id } = req.params;

  db.query(
    'UPDATE users SET name=?, email=? WHERE id=?',
    [name, email, id],
    err => {
      if (err) throw err;
      res.json({ message: 'User updated successfully' });
    }
  );
});

app.delete('/users/:id', (req, res) => {
  const { id } = req.params;

  db.query('DELETE FROM users WHERE id=?', [id], err => {
    if (err) throw err;
    res.json({ message: 'User deleted successfully' });
  });
});

// Start server
app.listen(PORT, () => {
  console.log(`🚀 Server running at http://localhost:${PORT}`);
});

🧠 Summary

In this tutorial, you learned how to:

• Connect Node.js to a MySQL database

• Perform CRUD operations (Create, Read, Update, Delete)

• Use prepared SQL queries

• Build RESTful APIs with Express

• Test APIs step by step using Postman