In this tutorial, we’ll walk through the process of building a CRUD RESTful (CRUD) API using Express.js, a popular web framework for Node.js, and MySQL, a powerful relational database management system.
This guide will cover setting up the server, creating a connection to the database, and implementing basic CRUD (Create, Read, Update, Delete) operations.
Setting Up the Server
First, we need to set up our Express server and include the necessary middleware:
const express = require("express");
const mysql = require("mysql");
const bodyParser = require("body-parser");
const app = express();
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
app.listen(3000, () => {
console.log("Server Started");
});
Here, we import the express and mysql modules, as well as body-parser to handle JSON and URL-encoded data. We then initialize the Express application and configure it to use body-parser. The server listens on port 3000.
Connecting to the Database
Next, we create a connection to the MySQL database:
We use mysql.createConnection to establish a connection to the database. Replace the connection details (host, port, user, password, database) with your own. The db.connect method attempts to connect and logs a message based on the result.
const db = mysql.createConnection({
host: '127.0.0.1',
port: '8889',
user: 'arctutorials',
password: 'arctutorials',
database: 'issues'
});
db.connect((err) => {
if (err) {
console.log("Unable to connect to DB");
}
console.log("Database connected successfully");
});
Handling Database Queries
To handle database queries more efficiently, we define a generic method that returns a promise:
function queryPromise(sql, values = []) {
return new Promise((resolve, reject) => {
db.query(sql, values, (error, results) => {
if (error) {
reject(error);
} else {
resolve(results);
}
});
});
}
This function wraps the db.query method in a promise, making it easier to work with asynchronous code using async/await.
Creating a Ticket (POST)
To create a new ticket, we define a POST route:
app.post('/tickets', async (req, res) => {
try {
const { title, description, active } = req.body;
if (!title || !description) {
throw new Error("Title and Description are mandatory");
}
const isActive = active !== undefined ? active : true;
const issue = [title, description, isActive];
const SQL = "INSERT INTO tickets (title, description, active) VALUES (?,?,?)";
const result = await queryPromise(SQL, issue);
res.status(200).json({ id: result.insertId, title, description, active: isActive });
} catch (err) {
console.log(err);
res.status(500).json({ error: 'Failed to create the ticket' });
}
});
This route extracts data from the request body, validates it, and inserts a new ticket into the database. If successful, it responds with the created ticket’s details.
Searching Tickets (GET)
To search for tickets, we define a GET route:
app.get('/tickets/search', async (req, res) => {
try {
const query = req.query.q;
const SQL = 'SELECT * FROM tickets WHERE title LIKE ? OR description LIKE ?';
const result = await queryPromise(SQL, [`%${query}%`, `%${query}%`]);
if (result.length === 0) {
res.status(200).json({ msg: 'No Matching Records Found', length: result.length });
} else {
res.status(200).json(result);
}
} catch (err) {
res.status(500).json({ error: 'Failed to search the tickets' });
}
});
This route handles search queries by checking if the title or description fields contain the search term. The results are returned as JSON.
Reading a Ticket (GET)
To read a specific ticket by ID, we define another GET route:
app.get('/tickets/:id', async (req, res) => {
try {
const { id } = req.params;
const SQL = 'SELECT * FROM tickets WHERE id = ?';
const results = await queryPromise(SQL, [id]);
if (results.length === 0) {
res.status(404).json({ error: 'No Matching Tickets Found' });
} else {
res.status(200).json(results[0]);
}
} catch (err) {
console.log(err);
res.status(500).json({ error: 'Failed to fetch the ticket details' });
}
});
This route retrieves a ticket based on its ID and returns the result as JSON.
Updating a Ticket (PUT)
To update an existing ticket, we define a PUT route:
app.put('/tickets/:id', async (req, res) => {
try {
const id = req.params.id;
const { title, description, active } = req.body;
const SQL = "UPDATE tickets SET title = ?, description = ?, active = ? WHERE id = ?";
const result = await queryPromise(SQL, [title, description, active, id]);
if (result.affectedRows === 0) {
res.status(404).json({ error: "Unable to find matching ticket" });
} else {
res.status(200).json({ id, title, description, active });
}
} catch (err) {
console.log(err);
res.status(500).json({ error: "Failed to update the ticket" });
}
});
This route updates a ticket’s details based on the provided ID and request body data.
Deleting a Ticket (DELETE)
Finally, to delete a ticket, we define a DELETE route:
app.delete('/tickets/:id', async (req, res) => {
try {
const id = req.params.id;
const SQL = "DELETE FROM tickets WHERE id = ?";
const result = await queryPromise(SQL, [id]);
if (result.affectedRows === 0) {
res.status(404).json({ error: 'Unable to find any matching ticket' });
} else {
res.status(200).json({ msg: "Successfully deleted the ticket" });
}
} catch (err) {
console.log(err);
res.status(500).json({ error: 'Failed to delete the ticket' });
}
});
This route deletes a ticket based on its ID and returns a confirmation message.
Conclusion
In this tutorial, we’ve built a RESTful API using Express.js and MySQL, covering basic CRUD operations. This setup provides a robust foundation for developing more complex applications.
By understanding and implementing these principles, you can create scalable and maintainable APIs for your projects.