In our previous blog post, we explored creating CRUD operations for student data using Node.js
, Express.js
, and JSON
. However, we encountered a major drawback – data was lost when the server closed. Today, we're introducing MySQL, a relational database management system, to store data securely.
Let's dive into the world of databases and create a resilient RESTful API using Node.js
, unlocking the full potential of web development.
Table of Contents
- Prerequisites
- Step 1: Initialize the Project
- Step 2: Set Up the Database
- Step 3: Create API Endpoints
- Step 4: Start the Server
- Step 5: Test Your API
- Conclusion
Prerequisites
Before you begin, make sure you have the following installed on your system:
- Node.js/Express.js
- MySQL Server (e.g., MariaDB)
- A code editor (e.g., Visual Studio Code)
Step 1: Initialize the Project
- Create a new directory for your project.
- Open a terminal and navigate to the project directory.
- Run
npm init
to initialize your Node.js project and follow the prompts to create apackage.json
file. -
Install the necessary packages:
mkdir crud-MySQL cd crud-MySQL npm init -y npm i express mysql
Create a database and table where data which we store
All done in inilization
Step 2: Set Up the Database
- Create a MySQL database named
test
. - Inside your project directory, create a JavaScript file (e.g.,
app.js
) and require the necessary modules:
import express from 'express';
import mysql from 'mysql';
- Create an instance of the Express application and configure it to parse JSON and URL-encoded data:
const app = express();
app.use(express.urlencoded({ extended: true }));
app.use(express.json());
- Define the MySQL connection parameters and establish a connection:
const con = mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database: "test"
});
con.connect(err => {
if (err) {
console.error("Database Connection Error:", err);
} else {
console.log('Successfully connected to the database');
}
});
Step 3: Create API Endpoints
Now, let's define the API endpoints for your RESTful API.
Here we use query promise which use promise which help to avoide "pyramid of doom"
function queryPromise(query, value) {
return new Promise((resolve, reject) => {
con.query(query, value, (err, result) => {
if (err) reject(err);
else resolve(result);
})
})
}
Get All Students
- Endpoint:
/students
- Method: GET
- Description: Retrieves a list of all student records from database.
app.get("/students/", async (req, res) => {
try {
const query = "SELECT * FROM students";
const result = await queryPromise(query);
res.status(200).send(result);
} catch (error) {
console.error(error);
res.status(500).json({ message: "Some issue occurred" });
}
});
Get a Student by ID
- Endpoint:
/students/:id
- Method: GET
- Description: Retrieves a single student record by providing the
id
parameter in db table.
app.get('/students/:id', async (req, res) => {
try {
const id = req.params.id;
const query = "SELECT * FROM students WHERE id = ?";
const result = await queryPromise(query, id);
res.status(200).send(result);
} catch (error) {
console.error(error);
res.status(500).json({ message: "Some issue occurred" });
}
});
Add a Student
- Endpoint:
/students
- Method: POST
- Description: Adds a new student record to the db table.
app.post('/students', async (req, res) => {
try {
const value = req.body;
const query = "INSERT INTO students SET ?";
const result = await queryPromise(query, value);
res.status(201).json({ message: "Data successfully inserted" });
console.log(result);
} catch (error) {
console.error(error);
res.status(500).json({ message: "Some issue occurred" });
}
});
Update a Student
Full Update
- Endpoint:
/students/:id
- Method: PUT
- Description: Updates an existing student from table by providing the
id
parameter.
app.put('/students/:id', async (req, res) => {
try {
let id = req.params.id;
const value = req.body;
const query = "UPDATE students SET ? WHERE id = ?";
const result = await queryPromise(query, [value, id]);
res.status(202).json({ message: "Data updated successfully" });
console.log(result);
} catch (error) {
console.error(error);
res.status(500).json({ message: "Some issue occurred" });
}
});
Partial Update
- Endpoint:
/students/:id
- Method: PATCH
- Description: Partially updates an existing student from table by providing the
id
parameter. You can update one or more fields individually.
app.patch('/students/:id', async (req, res) => {
try {
const id = req.params.id;
const value = req.body;
const query = "UPDATE students SET ? WHERE id = ?";
const result = await queryPromise(query, [value, id]);
res.status(202).json({ message: "Data updated successfully" });
console.log(result);
} catch (error) {
console.error(error);
res.status(500).json({ message: "Some issue occurred" });
}
});
Delete a Student
- Endpoint:
/students/:id
- Method: DELETE
- Description: Deletes an existing student from table by providing the
id
parameter.
app.delete('/students/:id', async (req, res) => {
try {
const id = req.params.id;
const query = "DELETE FROM students WHERE id = ?";
const result = await queryPromise(query, id);
res.status(200).json({ message: "Data deleted successfully" });
} catch (error) {
console.error(error);
res.status(500).json({ message: "Some issue occurred" });
}
});
Step 4: Start the Server
Finally, start the server and listen on a port (e.g., 3000):
const port = process.env.PORT || 3000;
app.listen(port, () => {
console.log(`Server started on http://localhost:${port}`);
});
Step 5: Test Your API
It's crucial to thoroughly test your RESTful API to ensure that it functions as expected. You can use tools like Postman or cURL to make HTTP requests to the defined endpoints and verify their behavior. Here's how you can test each HTTP request type:
- GET Request
- Description: Perform a GET request to retrieve data from your API.
- Expected Result: Since there's no data in the initial state, the response should be empty. Ensure that the API responds with the correct HTTP status code (usually 200 OK for a successful GET request).
- Description: Execute a POST request to add new elements to your database.
- Expected Result: The API should accept the POST request, insert the specified data into the database, and respond with a success message or a unique identifier for the newly created resource. Check that the data has been added to your database.
- PUT Request
To check the update, use a GET Request with a single user:
- Description: Use a PUT request to update existing data in your database.
- Expected Result: The API should allow updates to the specified resource, and when you subsequently make a GET request for the updated resource, it should return the modified data. Confirm that the resource was successfully updated.
- PATCH Request
For verification, use a GET Request with a single user:
- Description: Send a PATCH request to partially modify an existing resource.
- Expected Result: The API should apply the specified changes to the resource and respond with a success message. When you subsequently make a GET request for the resource, it should return the modified data. Ensure that the partial update was successful.
- DELETE Request
To retrieve all data, send a GET Request:
- Description: Issue a DELETE request to remove a resource from the database.
- Expected Result: The API should delete the specified resource and return a success message. When you make a subsequent GET request for all data, the deleted resource should no longer be present.
By conducting these tests, you can ensure that your RESTful API functions correctly and meets the requirements of your application. It's essential to verify that your API handles various HTTP methods and responds appropriately to different requests.
That's it! You've successfully created and tested a RESTful API using MySQL and Node.js. You can further enhance your API by implementing features like authentication, data validation, error handling, and more to meet the specific needs of your project.
Conclusion
In conclusion, this documentation has outlined the process of creating a RESTful API using MySQL and Node.js. We've covered setting up the environment, establishing a database connection, defining API endpoints, and using query promises to handle asynchronous database queries. This approach enhances code organization and readability, making it easier to manage asynchronous operations in your API. You can further extend this foundation to build more complex and robust applications.
Top comments (0)