Question 89999 in PostgreSQL Question posted in
The official documentation can be found here.

POSTGRES Column Not Updating with knex – Postgresql

I have a web application built on Node.js and I use Knex as my db tool. It has all worked perfectly for months, but now, one of the columns in my db is not updating correctly. I have the columns ‘base’,’bonus’,’production_bonus’,’deductions’ and ‘commission_earned’. The ‘commission_earned’ column is calculated on the backend of the application and then the values are sent to the db with knex. It is working perfectly for the initial creation of the record, but when I edit the record, it is not updating the ‘commission_earned’ column correctly. It updates the other values correctly, and when I console.log() the value to be stored for commission_earned it shows the correct value, it just is not updating it.

I have included screenshots of my code below as well as an example for you to see. I tried copying the column name from postgres to ensure it wasn’t misspelled and it still doesn’t work. So far, I have tried the following:

  • Ensured that all of the column names are being used correctly
  • Tried using a separate update call to update the value after updating the other values
  • Created a postgres function with a trigger that should update the column value any time a row is added
  • Tried using knex.raw to run the query that way
  • Tried placing the update method after the where clause in knex

I am stuck at this point and have no idea what to do.

P.S I know this db is not properly normalized, I built it when I was first learning how to build with Node

This is my index.js

When I console.log myData.commission_earned, it returns the correct value, but it does not update it in the db

app.post("/editScore", async (req,res) => {
    var score_id = req.body.scoreID;
    const myData = await calculateCommission.getData(req.body);
    knex('commissions').update(myData).where('id',score_id)
    .then( () => {
        res.redirect("/commissionLog")   
        })
});

Below is the code for the initial log, it is working correctly, and they both calculate the commission_earned with the same function

app.post("/submitCommission", async (req,res) => {
    //take the data sent in the request and send it to the commissionCalc file for processing
    const myData = await calculateCommission.getData(req.body);

        //Insert values into DB
    knex("commissions").insert(myData)
    .then( () => {
        res.redirect("/commissionLog")   
    })
})

I know it is not an issue with the calculation itself since I am able to console.log() and see that the correct value is being returned. And like I mentioned, it is updating the other values correctly, just not the commission_earned column.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I was finally able to get this to work by doing the following

    app.post("/editScore", async (req,res) => {
var score_id = req.body.scoreID;
calculateCommission.getData(req.body).then( myData => {
    knex('commissions').update(myData).where('id',score_id)
    .then( () => {
        knex('commissions').update('commission_earned',myData.commission_earned).where('id',score_id).then( () => {
            res.redirect("/commissionLog")  
        })   
    })
})

    });


  2. 0

    Gratz for you to solved it! however i still don’t think it’s normal for you to do 2 operations in your database with the same data, it feel like you have to hard code to make it work and i am sure there is better and more elegant solution, i check again in the Knex documentation, how about you try again but "destructure" out your data? like this:

      app.post("/editScore", async (req,res) => {
     const score_id = req.body.scoreID;
     const myData = await calculateCommission.getData(req.body);
     await knex('commissions')
        .where('id',score_id)
        .update({
            foo1: myData.foo1,
            foo2: myData.foo2,
            foo3: myData.foo3, //...since i don't know how is your data stucture
            commission_earned: myData.commission_earned
        })
     res.redirect("/commissionLog")

    Btw you can remove your Async keyword in your answer since you use only promise, as for me i am using here async/await, but this is just personal preference.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search