Create a new directory for your project.
Navigate to the directory and initialize a new Node.js project:
npm init -y
Install the necessary packages:
npm install express mysql2
express
: For creating the server and handling routes.mysql2
: A MySQL client for Node.js that supports promises.Create a new database in MySQL:
CREATE DATABASE books_db;
USE books_db;
Create a books
table:
CREATE TABLE books (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
author VARCHAR(255) NOT NULL,
genre VARCHAR(100),
year INT
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
index.js
file, set up the connection to the MySQL database:const express = require("express");
const mysql = require("mysql2");
const app = express();
const port = 3000;
// Middleware to parse JSON requests
app.use(express.json());
// MySQL connection
const db = mysql.createConnection({
host: "localhost",
user: "root", // Your MySQL username
password: "root", // Your MySQL password
database: "nodejs_mysql",
});
db.connect((err) => {
if (err) {
throw err;
}
console.log("Connected to MySQL database");
});
// Basic Route
app.get("/", (req, res) => {
res.send("Welcome to the Node.js MySQL API");
});
app.listen(port, () => {
console.log(`Server is running on http://localhost:${port}`);
});
Run the server and ensure that it connects to the MySQL database without any errors.
app.get("/books", (req, res) => {
const sql = "SELECT * FROM books";
db.query(sql, (err, results) => {
if (err) throw err;
res.json(results);
});
});
app.get("/books/:id", (req, res) => {
const sql = "SELECT * FROM books WHERE id = ?";
db.query(sql, [req.params.id], (err, result) => {
if (err) throw err;
if (result.length === 0) {
return res.status(404).send("Book not found");
}
res.json(result[0]);
});
});
app.post("/books", (req, res) => {
const { title, author, genre, year } = req.body;
const sql = "INSERT INTO books (title, author, genre, year) VALUES (?, ?, ?, ?)";
db.query(sql, [title, author, genre, year], (err, result) => {
if (err) throw err;
res.status(201).json({ id: result.insertId, title, author, genre, year });
});
});
app.put("/books/:id", (req, res) => {
const { title, author, genre, year } = req.body;
const sql = "UPDATE books SET title = ?, author = ?, genre = ?, year = ? WHERE id = ?";
db.query(sql, [title, author, genre, year, req.params.id], (err, result) => {
if (err) throw err;
if (result.affectedRows === 0) {
return res.status(404).send("Book not found");
}
res.json({ id: req.params.id, title, author, genre, year });
});
});
app.delete("/books/:id", (req, res) => {
const sql = "DELETE FROM books WHERE id = ?";
db.query(sql, [req.params.id], (err, result) => {
if (err) throw err;
if (result.affectedRows === 0) {
return res.status(404).send("Book not found");
}
res.status(204).send();
});
});
Use Postman to test the CRUD operations:
Example POST request body to add a book:
{
"title": "The Great Gatsby",
"author": "F. Scott Fitzgerald",
"genre": "Novel",
"year": 1925
}
To ensure that incoming data is valid, you can add basic validation before interacting with the database. For instance, ensure that the title and author fields are not empty:
app.post("/books", (req, res) => {
const { title, author, genre, year } = req.body;
if (!title || !author) {
return res.status(400).send("Title and Author are required");
}
const sql = "INSERT INTO books (title, author, genre, year) VALUES (?, ?, ?, ?)";
db.query(sql, [title, author, genre, year], (err, result) => {
if (err) throw err;
res.status(201).json({ id: result.insertId, title, author, genre, year });
});
});
To handle unexpected errors gracefully, you can wrap your database queries in try-catch
blocks or add a global error handler middleware:
app.use((err, req, res, next) => {
console.error(err.stack);
res.status(500).send("Something went wrong!");
});
Place this at the end of your index.js
file to ensure that it catches any errors from the other routes.
publisher
, ISBN
).jsonwebtoken
or passport.js
.This lesson builds on the previous lessons by integrating a MySQL database with Node.js, allowing for more dynamic and persistent data management. It also introduces important concepts like data validation, error handling, and secure database interactions.