REST API with node.js CRUD with Postgres Database

Question

I am new in node.js and javascript except CRUD Operations. I want to create a rest api with node.js with postgresql database, so that the methods PUT POST UPDATE DELETE work through JSON. Sory if my question is not clear but here is what i have, and what i cannot accomplish.

apiRoutes.get('/db/:id', function(request, response){
  pg.connect(process.env.DATABASE_URL, function(err, client, done) {        
    client.query('SELECT * FROM test2 WHERE $1 = test2.id;', [request.params.id], function(err, result) {
        done();
         if (err){ 
            console.error(err); response.json({success:"false", message: err}); 
         }
         else{ 
            response.json({success: "true", data: result.rows} ); 
         }
    });
  });
});

and here is the code which is to update the data on database through PUT Method.

 apiRoutes.put('/db/:id', function(request, response){
   var data1 = {name: request.body.name};   
    client.query('UPDATE test2 SET name=($1) WHERE id=($2)', [data1.name], [request.params.id]);
       pg.connect(process.env.DATABASE_URL, function(err, client, done) {
        client.query('SELECT * FROM test2 WHERE $1 = test2.id', [request.params.id], function(err, result) {

        done();
         if (err){ 
            console.error(err); response.json({success:"false", message: err}); 
         }
         else{  

            response.json({success:"true", data: result.rows} ); 
         }
    });
  });
});

When i click PUT in Postman, first it displays the data, including the name, but it deletes from the database, then even if i click raw then edit data, then send it, it wont save that, it returns to null, name = null. Sory if i havent been clear, but if someone understands me just a bit, a little help would be great, and this is important for me, to finish this. Thank you.

vitaly-t · Accepted Answer · 2015-08-06 23:50:00Z

1

You have two problems in your code:

You call done(), disconnecting from the database just before calling the update query, and then calling client.query while being disconnected from the database.
After you fix the first one, you have to change the second one, for update, to take function(err, result) as the last parameter and respond according to the result. You cannot treat an asynchronous call is if it were a synchronous one, like you do it there. And once it is completed, only then you can call done().

If you want to simplify your database usage, have a look at pg-promise.

answered Aug 6, 2015 at 23:50

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rpajaziti Over a year ago

First point i understand very well. Thank you. But second point i dont understand exactly. Can you please explain what did you mean, because i am very new in to this and dont exactly understand it and i need to get this done. Thanks alot.

vitaly-t Over a year ago

When you call your last client.query, you are supposed to wait for the result, which you do not. And when you call response.json, you are using the result from the first query instead, leaving the second one in limbo.

rpajaziti Over a year ago

Thanks alot @Vitaly-t for your help. But i really dont understand what it means to wait for the result. I will edit my second put method, please if you can copy that and just put the code where it belongs, because i tried aloot of things, did aloot of searches but with no result. Thaank you.

vitaly-t Over a year ago

You have changed your code in a such a manner that now it doesn't make sense at all. You are calling an update query before you even connect to the database. Perhaps I could make a valid change in your code, if you provided the logic of what it is exactly you are trying to achieve.

nha · Accepted Answer · 2015-08-22 10:13:06Z

What I wanted to achieve is that if I enter data in the body in Postman throw json the database will update with that data and it will show the new data in the database together with the data already in the database. Here is my solved code:

apiRoutes.put('/db', function(request, response){ 
pg.connect(process.env.DATABASE_URL, function(err, client, done) {

    var data1 = {id: request.body.id, name: request.body.name, lastname: request.body.lastname, address: request.body.address, phonenumber: request.body.phonenumber, email: request.body.email};   
        //Update data in the database
    client.query("UPDATE Contact SET name = $1, lastname = $2, address = $3, phonenumber = $4, email = $5 WHERE id = $6", [data1.name, data1.lastname, data1.address, data1.phonenumber, data1.email, data1.id]);
        //Display after updated
    client.query('SELECT * FROM Contact;', function(err, result) {
        done();
         if (err){ 
            console.error(err); response.json({success:"false", message: err}); 
         }
         else{
            response.json({success:"Data updated successfuly in the database.", data: result.rows} ); 
         }
    });
  });
});

Collectives™ on Stack Overflow

REST API with node.js CRUD with Postgres Database

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related