Node.js + Express + MySQL RESTful CRUD API (Beginner Tutorial)

📌 Introduction

In previous tutorials, you learned how to work with Express routes and middlewares, and how to connect Node.js to a MySQL database.

In this tutorial, we’ll combine everything and build a complete RESTful CRUD API — Create, Read, Update, and Delete — using Express.js, MySQL, and Postman for testing.

By the end of this guide, you’ll have a fully working API that can be used with any frontend or mobile application.

✅ Prerequisites

Before starting, make sure you have:

Node.js v18 or higher
MySQL is installed and running
Postman for API testing
Basic knowledge of JavaScript and Express.js

🗄️ Step 1 — Create the Database

Open your MySQL terminal or phpMyAdmin and run:


CREATE DATABASE crud_api;
USE crud_api;

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

This database will store user information for our API.

📂 Step 2 — Set Up the Project

Create a new project folder and initialize Node.js:


mkdir node-crud-api
cd node-crud-api
npm init -y

Install required dependencies:


npm install express mysql2 cors

Create the main application file:


touch app.js

🔌 Step 3 — Connect to MySQL

Open app.js and add the following code:


const express = require('express');
const mysql = require('mysql2');
const cors = require('cors');

const app = express();
const PORT = 3000;

// Middlewares
app.use(cors());
app.use(express.json());

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

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

This establishes a connection between your Node.js app and the MySQL database.

🔄 Step 4 — Create CRUD API Routes

➕ Create User (POST)


app.post('/api/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) return res.status(500).json({ error: err.message });
    res.status(201).json({
      id: result.insertId,
      name,
      email
    });
  });
});

📄 Read All Users (GET)


app.get('/api/users', (req, res) => {
  db.query('SELECT * FROM users ORDER BY id DESC', (err, results) => {
    if (err) return res.status(500).json({ error: err.message });
    res.json(results);
  });
});

🔍 Read User by ID (GET)


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

  db.query('SELECT * FROM users WHERE id = ?', [id], (err, results) => {
    if (err) return res.status(500).json({ error: err.message });
    if (results.length === 0) {
      return res.status(404).json({ message: 'User not found' });
    }
    res.json(results[0]);
  });
});

✏️ Update User (PUT)


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

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

  db.query(sql, [name, email, id], (err, result) => {
    if (err) return res.status(500).json({ error: err.message });
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'User not found' });
    }
    res.json({ message: 'User updated successfully' });
  });
});

🗑️ Delete User (DELETE)


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

  db.query('DELETE FROM users WHERE id = ?', [id], (err, result) => {
    if (err) return res.status(500).json({ error: err.message });
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'User not found' });
    }
    res.json({ message: 'User deleted successfully' });
  });
});

▶️ Step 5 — Start the Server

At the bottom of app.js, add:


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

Run the application:


node app.js

Expected output:


✅ MySQL Connected!
🚀 Server running at http://localhost:3000

🧪 Step 6 — Test with Postman

Method	Endpoint	Description
POST	/api/users	Create a new user
GET	/api/users	Get all users
GET	/api/users/:id	Get user by ID
PUT	/api/users/:id	Update user
DELETE	/api/users/:id	Delete user

Example JSON Body (POST / PUT)


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

📁 Step 7 — Recommended Folder Structure

Current structure:


node-crud-api/
├── app.js
├── package.json
└── node_modules/

For larger projects, refactor to:


src/
├── db.js
├── routes/
│   └── userRoutes.js
└── app.js

This keeps your code clean and scalable.

🧠 Summary

In this tutorial, you built a fully functional RESTful CRUD API using:

Express.js for routing
MySQL as the database
JSON-based API responses
Postman for testing

This API structure is production-ready and can easily connect to React, Vue, mobile apps, or any frontend framework.

Souy Soeng