Building a CRUD API with Node.js and MySQL

Published: March 23, 2025•10 min read

In this guide, we'll walk through creating a simple CRUD (Create, Read, Update, Delete) API using Node.js with Express and MySQL. This is perfect for beginners who want to understand how to build a backend service that interacts with a database.

Prerequisites

Node.js installed on your machine
MySQL installed and running
Basic knowledge of JavaScript

Setting Up the Project

First, let's create a new directory for our project and initialize it with npm. Open your terminal and run the following commands:

Terminal

$npm init -y
$npm install express mysql2
$node server.js

Creating the Server

Now, let's create our server file. Create a new file called server.js and add the following code:

server.js

javascript

const express = require('express');
const mysql = require('mysql2/promise');
const app = express();
const port = 3000;

// Database connection
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'crud_example'
});

app.use(express.json());

// GET all items
app.get('/api/items', async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT * FROM items');
    res.json(rows);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

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

Setting Up the Database

Before running our server, we need to set up our MySQL database. Let's create a database and a table for our items:

setup.sql

sql

CREATE DATABASE IF NOT EXISTS crud_example;
USE crud_example;

CREATE TABLE IF NOT EXISTS items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO items (name, description) VALUES 
  ('Item 1', 'Description for item 1'),
  ('Item 2', 'Description for item 2');

Testing the API

Now that we have our server and database set up, let's test our API endpoint. Start the server and use a tool like curl or Postman to make a GET request:

Terminal

$curl http://localhost:3000/api/items

This is just the beginning of our CRUD API. In the next part of this guide, we'll implement the Create, Update, and Delete operations to complete our API.