In this article, we are going to learn about one of the most used databases with Node Js, MySQL.We will learn how to integrate Node JS and MySQL,make basic APIs for CRUD operations and the pros and cons of using MySQL as a Database.Now we assume that you have learnt about the basics of Node JS and having it in your systems, if not then go through this article.

This article is split into 2 parts:

Creating CRUD APIs

Now that we have our express server up and running with the database connection, we need to manage todos in the database.

1- Create

Now let’s try to create one todo from post API and for this paste the following code into your server.js file:(Do not forget to change request type to POST)

app.post(“/todo”, function(req, res) {
let task = req.body.task;
if (!task) {
return res
.status(400)
.send({ error: true, message: “Please provide task” });
}
mc.query(“INSERT INTO todo SET ? “, { task: task },
function(error,results,fields ) {
if (error) throw error;
return res.send({
error: false,
data: results,
message: “New task has been created successfully.”
});
});
});

It simply created a new todo using the data coming from a POST request and saves it to our database.Last step would testing that we can easily create a new todo.

Let’s open Postman and send a POST request to the following url ‘http://localhost:3000/todo’ and specify the POST data as task: “Create API as a test example”.

Also make sure that you choose raw then json format in the Body tab in Postman as specified in the image below and write the product details in json format as shown in image and then click on the send button and will see the same output as shown here.

To verify this head over to your mysql shell and type this command

mysql> select * from todos;

And you will see your new todo has been added as below:

2- Read

We are going to create a new route so that when a user hits /todos, it will return a list of todos in JSON format. Update your server.js with below code.

// Retrieve all todos
app.get(‘/todos’, function (req, res) {
mc.query(‘SELECT * FROM todos’, function (error, results, fields) {
if (error) throw error;
return res.send({ error: false, data: results, message: ‘Todos list.’
});
});
});

It simply reads all existing product from todos table.Now let’s head to Postman and try-out our new endpoint. Call the following url ‘localhost:3000/todos’.

You should get this from your database and it will be different from mine for sure.Your output will be somewhat like this:(Do not forget to change request type to GET)

3- Update

The third operation in our CRUD is to update a todo with the help of its unique id. Head over to server.js file and start designing the expected path that the browser would hit.(Do not forget to change request type to PUT)

// Update todo with id
app.put(“/todo”, function(req, res) {
let todo_id = req.body.todo_id;
let task = req.body.task;
if (!todo_id || !task) {
return res
.status(400)
.send({ error: task, message: “Please provide task and todo_id” });
}
mc.query(“UPDATE tasks SET task = ? WHERE id = ?”, [task, todo_id],
function(
error,
results,
fields
) {
if (error) throw error;
return res.send({
error: false,
message: “Task has been updated successfully.”
});
});
});

It simply finds an existing todo using its id that was sent in the params.

Now let’s head to Postman and try-out our new endpoint. Call the following URL ‘localhost:3000/todo’.

And after hitting this route the task of the todo will be changed to “Testing API” which you can verify either by hitting the read route or by checking into the database itself.

Todos in database after hitting update route:

4- Delete Todo

The last operation in our CRUD is to delete a product with the help of its unique id. Head over to server.js file and start designing the expected path that the browser would hit.

// Delete todo
app.delete(“/todo/:id”, function(req, res) {
let todo_id = req.params.id;
mc.query(“DELETE FROM tasks WHERE id = ?”, [task_id], function( error, results, fields ) {
if (error) throw error;
return res.send({
error: false,
message: “Todo has been deleted successfully.”
});
});
});

It simply deletes an existing todo with the help of its unique id.

Now let’s head to Postman and try-out our new endpoint. Call the following URL ‘localhost:3000/todo:id’.(Do not forget to change request type to delete)

And todos in database after hitting this route are:

HURRAY! And this completes our CRUD operations implemented through APIs using MySQL as database.

Pros of MySQL:

  • Data security.
  • On-demand scalability.
  • Round the clock uptime.
  • Comprehensive transactional support.
  • Complete workflow control.
  • The flexibility of open source.
  • Wide adopted and easy to use.
  • Fast, portable, and secure.

Cons of MySQL:

  • MySQL is Oracle-owned instead of community driven.
  • Restricted for complex business logic.
  • Few stability  issues.
  • Transactions are not handled very efficiently.
  • The functionality tends to be heavily dependent on the addons.
  • Prior knowledge is a must.
  • MySQL is not as mature as other relational database management systems.
  • Red Hat Enterprise Linux, Fedora, Slackware Linux, openSUSE, and the Wikimedia Foundation have all migrated to MariaDB.MySQL still has some great things going for it.

That’s all I have and thanks a lot for reading. Please let me know if any corrections/suggestions. Please do share and comments if you like the post. Thanks in advance… 😉

Thanks Yash Kesarvani for helping us to grow day by day. He is expert in Node Js and loves to solve competitive programming.

Categories: Miscellaneous

0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Insert math as
Block
Inline
Additional settings
Formula color
Text color
#333333
Type math using LaTeX
Preview
\({}\)
Nothing to preview
Insert