In this article, we are going to learn about one of the most used databases with Node Js,PostgreSQL.We will learn how to integrate Node JS and PostgreSQL,make basic APIs for CRUD operations and the pros and cons of using PostgreSQL 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 users 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 app.js file:(Do not forget to change request type to POST)

app.post(“/users”, (req, res) => {
const name= req.body.name;
const email=req.body.email;
pool.query(
“INSERT INTO users (name, email) VALUES ($1, $2)”,
[name, email],
(error, results) => {
if (error) {
console.log(error);
}
res.status(200).send(`User added with ID: ${results.insertId}`);
}
);
});

What this does is it simply created a new user 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 product. Let’s open Postman and send a POST request to the following url ‘http://localhost:3000/users’ and specify the POST data as name: “Yashu” and email:”yashu@gmail.com”. 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.

And to verify this you can head to postgresql shell and see that newly created user in users table:

2- Read

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

app.get(“/users”, (req, res) =>{
  pool.query(“SELECT * FROM users”, (error, results) => {
    if (error) {
      console.log(error); 
   }  
  res.send(results.rows);  });
});

What the function does is it simply reads all existing product from users table.Now let’s head to Postman and try-out our new endpoint. Call the following url ‘localhost:3000/users’. 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 user with the help of its unique id. Head over to app.js file and start designing the expected path that the browser would hit.(Do not forget to change request type to PUT)

app.put(“/users/:id”, (req, res) => {
const id = parseInt(req.params.id);
const { name, email } = req.body;
pool.query(
“UPDATE users SET name = $1, email = $2 WHERE id = $3”, [name, email, id],
(error, results) => {
if (error) {
console.log(error);
}
res.status(200).send(`User modified with ID: ${id}`);
}
);
});

What the function does is it simply finds an existing user 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/user/:id’.And after hitting this route the email of the user will be changed to “yashyk@gmail.com” which you can verify either by hitting the read route or by checking into the database itself.

Users 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 app.js file and start designing the expected path that the browser would hit.

app.delete(“/users/:id”, (req, res) => {
const id = parseInt(req.params.id);
pool.query(“DELETE FROM users WHERE id = $1”, [id], (error, results) => {
if (error) {
console.log(error);
}
res.status(200).send(`User deleted with ID: ${id}`);
});
});

What the function does is it simply deletes an existing user 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/user: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 PostgreSQL

  • PostgreSQL can run dynamic websites and web apps as a LAMP stack option
  • PostgreSQL’s write-ahead logging makes it a highly fault-tolerant database
  • PostgreSQL source code is freely available under an open source license. This allows you the freedom to use, modify, and implement it as per your business needs.
  • PostgreSQL supports geographic objects so you can use it for location-based services and geographic information systems
  • PostgreSQL supports geographic objects so it can be used as a geospatial data store for location-based services and geographic information systems
  • To learn Postgres, you don’t need much training as its easy to use
  • Low maintenance administration for both embedded and enterprise use.

Cons of PostgreSQL

  • Postgres is not owned by one organization. So, it has had trouble getting its name out there despite being fully featured and comparable to other DBMS systems
  • Changes made for speed improvement requires more work than MySQL as PostgreSQL focuses on compatibility
  • Many open source apps support MySQL, but may not support PostgreSQL
  • On performance metrics, it is slower than MySQL.

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 Kesarwani for helping us to grow day by day. He is expert in Node Js and loves to solve competitive programming.

Categories: Nodejs

3 Comments

AffiliateLabz · February 15, 2020 at 6:55 pm

Great content! Super high-quality! Keep it up! 🙂

Danae Millette · June 1, 2020 at 12:45 pm

Hiya, I am really glad I have found this information. Today bloggers publish only about gossip and net stuff and this is actually irritating. A good blog with interesting content, this is what I need. Thank you for making this website, and I’ll be visiting again. Do you do newsletters by email?

Mona · June 30, 2020 at 7:20 am

Hiya, I’m really glad I’ve found this info. Nowadays bloggers publish only about gossip and internet stuff and this is actually frustrating. A good site with exciting content, this is what I need. Thanks for making this website, and I’ll be visiting again. Do you do newsletters by email?

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