Database CRUD Operations in Node.js
Learn how to Create, Read, Update, and Delete database records using Node.js, Express.js, and MySQL.
CRUD represents the four basic operations performed on database records. Almost every real-world application uses CRUD operations to manage information such as students, employees, products, and users.
In this tutorial, we will create a simple Product API using
Node.js, Express.js, and the mysql2 package.
We will use parameterized SQL queries to interact safely with MySQL.
1. What Are CRUD Operations?
CRUD stands for Create, Read, Update, and Delete. Each operation performs a specific action on database records.
| Operation | Purpose | SQL Command | HTTP Method |
|---|---|---|---|
| Create | Add a new record | INSERT | POST |
| Read | Retrieve existing records | SELECT | GET |
| Update | Modify an existing record | UPDATE | PUT / PATCH |
| Delete | Remove a record | DELETE | DELETE |
2. Install Required Packages
First, create a Node.js project and install Express.js and mysql2. Express handles HTTP requests, while mysql2 connects Node.js to MySQL.
npm init -y
npm install express mysql2
Create a file named server.js.
3. Create the Database and Table
Before writing the API, create a database named
crud_demo and a table named products.
Each product will have an ID, name, and price.
CREATE DATABASE crud_demo;
USE crud_demo;
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL
);
The id column automatically generates a unique
number for every new product.
4. Configure the MySQL Connection
Import Express and mysql2, create an Express application, and configure a connection pool to manage database connections.
const express = require("express");
const mysql = require("mysql2/promise");
const app = express();
app.use(express.json());
const pool = mysql.createPool({
host: "localhost",
user: "root",
password: "YOUR_MYSQL_PASSWORD",
database: "crud_demo",
waitForConnections: true,
connectionLimit: 10
});
YOUR_MYSQL_PASSWORD with your local MySQL
password. In production, store database credentials in environment
variables instead of hardcoding them.
The express.json() middleware allows Express to read
JSON data sent in the request body.
5. CREATE Operation — Insert a Record
The CREATE operation adds a new product to the database. In a REST API, we commonly use the HTTP POST method.
app.post("/api/products", async (req, res) => {
try {
const { name, price } = req.body;
if (
typeof name !== "string" ||
name.trim() === "" ||
price === undefined ||
price === null ||
price === "" ||
!Number.isFinite(Number(price)) ||
Number(price) < 0
) {
return res.status(400).json({
message: "Valid product name and price are required."
});
}
const [result] = await pool.execute(
"INSERT INTO products (name, price) VALUES (?, ?)",
[name.trim(), Number(price)]
);
res.status(201).json({
message: "Product created successfully",
productId: result.insertId
});
} catch (error) {
console.error(error);
res.status(500).json({
message: "Unable to create product."
});
}
});
Here, INSERT INTO adds the record.
The question marks are placeholders, and the values are supplied
separately to prevent user input from being treated as SQL code.
Example request:
{
"name": "Keyboard",
"price": 1200
}
Example response:
{
"message": "Product created successfully",
"productId": 1
}
6. READ Operation — Retrieve Records
The READ operation retrieves information from the database. We can create one endpoint to get all products and another endpoint to get a product by its ID.
A. Get All Products
app.get("/api/products", async (req, res) => {
try {
const [products] = await pool.execute(
"SELECT id, name, price FROM products ORDER BY id"
);
res.status(200).json(products);
} catch (error) {
console.error(error);
res.status(500).json({
message: "Unable to retrieve products."
});
}
});
The SELECT statement retrieves product records.
The API returns the records as a JSON array.
B. Get a Product by ID
app.get("/api/products/:id", async (req, res) => {
try {
const id = Number(req.params.id);
if (!Number.isSafeInteger(id) || id <= 0) {
return res.status(400).json({
message: "A valid product ID is required."
});
}
const [products] = await pool.execute(
"SELECT id, name, price FROM products WHERE id = ?",
[id]
);
if (products.length === 0) {
return res.status(404).json({
message: "Product not found."
});
}
res.status(200).json(products[0]);
} catch (error) {
console.error(error);
res.status(500).json({
message: "Unable to retrieve product."
});
}
});
The :id route parameter identifies the requested
product. If the record does not exist, the API returns
404 Not Found.
7. UPDATE Operation — Modify a Record
The UPDATE operation changes an existing product. PUT is commonly used when replacing the editable fields of a resource, while PATCH is used for partial updates.
A. PUT — Replace Editable Fields
app.put("/api/products/:id", async (req, res) => {
try {
const id = Number(req.params.id);
const { name, price } = req.body;
if (!Number.isSafeInteger(id) || id <= 0) {
return res.status(400).json({
message: "A valid product ID is required."
});
}
if (
typeof name !== "string" ||
name.trim() === "" ||
price === undefined ||
price === null ||
price === "" ||
!Number.isFinite(Number(price)) ||
Number(price) < 0
) {
return res.status(400).json({
message: "Valid product name and price are required."
});
}
const [result] = await pool.execute(
"UPDATE products SET name = ?, price = ? WHERE id = ?",
[name.trim(), Number(price), id]
);
if (result.affectedRows === 0) {
return res.status(404).json({
message: "Product not found."
});
}
res.status(200).json({
message: "Product updated successfully."
});
} catch (error) {
console.error(error);
res.status(500).json({
message: "Unable to update product."
});
}
});
This endpoint expects both name and
price. If a record exists but the submitted values
are identical to its current values, MySQL may report zero changed
rows; for a strict not-found check, verify the record's existence
separately.
B. PATCH — Update Selected Fields
PATCH allows the client to send only the fields it wants to change. The following example supports updating the name, the price, or both.
app.patch("/api/products/:id", async (req, res) => {
try {
const id = Number(req.params.id);
if (!Number.isSafeInteger(id) || id <= 0) {
return res.status(400).json({
message: "A valid product ID is required."
});
}
const fields = [];
const values = [];
if (Object.prototype.hasOwnProperty.call(req.body, "name")) {
if (
typeof req.body.name !== "string" ||
req.body.name.trim() === ""
) {
return res.status(400).json({
message: "Name must be a non-empty string."
});
}
fields.push("name = ?");
values.push(req.body.name.trim());
}
if (Object.prototype.hasOwnProperty.call(req.body, "price")) {
const price = req.body.price;
if (
price === null ||
price === "" ||
!Number.isFinite(Number(price)) ||
Number(price) < 0
) {
return res.status(400).json({
message: "Price must be a valid non-negative number."
});
}
fields.push("price = ?");
values.push(Number(price));
}
if (fields.length === 0) {
return res.status(400).json({
message: "Provide a valid name or price to update."
});
}
const [existing] = await pool.execute(
"SELECT id FROM products WHERE id = ?",
[id]
);
if (existing.length === 0) {
return res.status(404).json({
message: "Product not found."
});
}
values.push(id);
await pool.execute(
`UPDATE products SET ${fields.join(", ")} WHERE id = ?`,
values
);
res.status(200).json({
message: "Product updated successfully."
});
} catch (error) {
console.error(error);
res.status(500).json({
message: "Unable to update product."
});
}
});
The column names used in the SQL statement are selected from predefined strings in the application. User-provided values are still passed through placeholders. Never insert arbitrary client-provided column names directly into SQL.
8. DELETE Operation — Remove a Record
The DELETE operation removes a product from the database. We commonly use the HTTP DELETE method and identify the record by ID.
app.delete("/api/products/:id", async (req, res) => {
try {
const id = Number(req.params.id);
if (!Number.isSafeInteger(id) || id <= 0) {
return res.status(400).json({
message: "A valid product ID is required."
});
}
const [result] = await pool.execute(
"DELETE FROM products WHERE id = ?",
[id]
);
if (result.affectedRows === 0) {
return res.status(404).json({
message: "Product not found."
});
}
res.status(200).json({
message: "Product deleted successfully."
});
} catch (error) {
console.error(error);
res.status(500).json({
message: "Unable to delete product."
});
}
});
The DELETE statement removes the matching record.
If the ID does not exist, the API returns a 404 response.
9. Start the Server
Add this code at the bottom of server.js to start
the Express server.
const PORT = 3000;
app.listen(PORT, () => {
console.log(`Server running at http://localhost:${PORT}`);
});
Run the application from your terminal:
node server.js
Use Postman, another API client, or your frontend to test the endpoints. The server must be running and the MySQL connection settings must be correct.
10. CRUD API Endpoints
| Method | Endpoint | Operation |
|---|---|---|
| POST | /api/products |
Create a product |
| GET | /api/products |
Get all products |
| GET | /api/products/1 |
Get product with ID 1 |
| PUT | /api/products/1 |
Replace editable product fields |
| PATCH | /api/products/1 |
Update selected product fields |
| DELETE | /api/products/1 |
Delete product with ID 1 |
11. CRUD Operation Flow
The client sends an HTTP request to the Express API. The route validates the input, executes a database query, and returns a response to the client.
Postman or Frontend Application
Receive request and validate input
Execute parameterized SQL query
Create, Read, Update, or Delete records
Return the result or an error message
Important Points to Remember
- CRUD stands for Create, Read, Update, and Delete.
- POST is commonly used to create a record.
- GET retrieves records from the database.
- PUT replaces the editable fields of a resource.
- PATCH updates selected fields of a resource.
- DELETE removes a record.
- Use parameterized queries for user-supplied SQL values.
- Validate request data before executing database queries.
- Return suitable HTTP status codes, such as 201, 400, 404, and 500.
- Keep database credentials outside source code in production.
Summary
CRUD operations are the foundation of database-driven applications. Using Node.js, Express.js, and MySQL, we created API endpoints to insert, retrieve, update, and delete product records.
In the next tutorial, we will learn how to retrieve and filter database records using queries, conditions, sorting, and limits.