CRUD application to manage a collection of books in a library. We'll create a Node.js application that allows users to add, view, update, and delete books in a MySQL database.
### Implementing CRUD Operations for a Book Collection Using Node.js and MySQL
In this tutorial, we'll walk through how to create a simple CRUD (Create, Read, Update, Delete) application to manage a collection of books using Node.js and MySQL.
### Prerequisites
Before we get started, make sure you have the following installed:
- [Node.js](https://nodejs.org/)
- [MySQL](https://www.mysql.com/)
- A MySQL client like [MySQL Workbench](https://www.mysql.com/products/workbench/)
### Step 1: Setting Up the Project
1. **Initialize a new Node.js project:**
```bash
mkdir nodejs-mysql-book-crud
cd nodejs-mysql-book-crud
npm init -y
```
2. **Install necessary packages:**
```bash
npm install express mysql2 body-parser
```
3. **Create the project structure:**
```bash
mkdir src
touch src/index.js
```
### Step 2: Create the MySQL Database and Table
1. **Log into your MySQL database:**
```bash
mysql -u root -p
```
2. **Create a new database and table:**
```sql
CREATE DATABASE library_db;
USE library_db;
CREATE TABLE books (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
author VARCHAR(255) NOT NULL,
published_year INT NOT NULL,
genre VARCHAR(100) NOT NULL
);
```
### Step 3: Set Up the Express Server
1. **Edit `src/index.js` to set up the Express server:**
```javascript
const express = require('express');
const bodyParser = require('body-parser');
const mysql = require('mysql2');
const app = express();
const port = 3000;
// Middleware
app.use(bodyParser.json());
// MySQL connection
const db = mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'your_password',
database: 'library_db'
});
db.connect(err => {
if (err) {
throw err;
}
console.log('MySQL Connected...');
});
app.listen(port, () => {
console.log(`Server running on port ${port}`);
});
```
### Step 4: Implement CRUD Operations
#### 1. **Create a Book (C)**
Add the following route to `src/index.js`:
```javascript
// Create a new book
app.post('/books', (req, res) => {
const { title, author, published_year, genre } = req.body;
const sql = 'INSERT INTO books (title, author, published_year, genre) VALUES (?, ?, ?, ?)';
db.query(sql, [title, author, published_year, genre], (err, result) => {
if (err) {
return res.status(500).send(err);
}
res.status(201).send({ id: result.insertId, title, author, published_year, genre });
});
});
```
#### 2. **Read Books (R)**
Add the following routes to `src/index.js`:
```javascript
// Get all books
app.get('/books', (req, res) => {
const sql = 'SELECT * FROM books';
db.query(sql, (err, results) => {
if (err) {
return res.status(500).send(err);
}
res.send(results);
});
});
// Get a single book by ID
app.get('/books/:id', (req, res) => {
const { id } = req.params;
const sql = 'SELECT * FROM books WHERE id = ?';
db.query(sql, [id], (err, result) => {
if (err) {
return res.status(500).send(err);
}
if (result.length === 0) {
return res.status(404).send({ message: 'Book not found' });
}
res.send(result[0]);
});
});
```
#### 3. **Update a Book (U)**
Add the following route to `src/index.js`:
```javascript
// Update a book
app.put('/books/:id', (req, res) => {
const { id } = req.params;
const { title, author, published_year, genre } = req.body;
const sql = 'UPDATE books SET title = ?, author = ?, published_year = ?, genre = ? WHERE id = ?';
db.query(sql, [title, author, published_year, genre, id], (err, result) => {
if (err) {
return res.status(500).send(err);
}
if (result.affectedRows === 0) {
return res.status(404).send({ message: 'Book not found' });
}
res.send({ id, title, author, published_year, genre });
});
});
```
#### 4. **Delete a Book (D)**
Add the following route to `src/index.js`:
```javascript
// Delete a book
app.delete('/books/:id', (req, res) => {
const { id } = req.params;
const sql = 'DELETE FROM books WHERE id = ?';
db.query(sql, [id], (err, result) => {
if (err) {
return res.status(500).send(err);
}
if (result.affectedRows === 0) {
return res.status(404).send({ message: 'Book not found' });
}
res.send({ message: 'Book deleted' });
});
});
```
### Step 5: Run the Application
1. **Start the server:**
```bash
node src/index.js
```
2. **Test the CRUD operations using a tool like [Postman](https://www.postman.com/):**
- **Create a Book:** Send a POST request to `http://localhost:3000/books` with JSON body `{ "title": "The Great Gatsby", "author": "F. Scott Fitzgerald", "published_year": 1925, "genre": "Fiction" }`
- **Read Books:** Send a GET request to `http://localhost:3000/books`
- **Read a Single Book:** Send a GET request to `http://localhost:3000/books/:id`
- **Update a Book:** Send a PUT request to `http://localhost:3000/books/:id` with JSON body `{ "title": "The Great Gatsby", "author": "F. Scott Fitzgerald", "published_year": 1925, "genre": "Classic" }`
- **Delete a Book:** Send a DELETE request to `http://localhost:3000/books/:id`
### Conclusion
You have now created a simple CRUD application for managing a book collection using Node.js and MySQL. This tutorial covered the basics of setting up an Express server, connecting to a MySQL database, and implementing the CRUD operations. You can expand this foundation to build more complex applications and add additional features as needed.
Happy coding!
Contact us for software training, education or development
.png)
0 Comments