KNEX Undefined binding(s) detected when compiling UPDATE query

Question

I am using Knex to try to update a record in a mySQL table. I receive the following error:

"UnhandledPromiseRejectionWarning: Error: Undefined binding(s) detected when compiling UPDATE query: update purchases set name = ?, email = ?, password = ?, purchase_id = ? where purchase_id = ?"

The offending code is:

const update = async data => {
  let purchase_id = data.purchaseId;
  let result = await knex("purchases")
    .where({ purchase_id })
    .update(data);
  return result;
};

What confuses me is that all of the fields mentioned in the error (name, email, password, and purchase_id) are defined in the data object I am passing to my Knex call. This is what data logs to just before the Knex call:

{ name: 'sdfs', 
  email: 'fds', 
  password: 'fds', 
  purchase_id: 39 }

I have read through similar questions (e.g. KNEX Undefined binding(s) detected when compiling SELECT query ), but in those cases the problem seems to be that undefined variables are being passed to KNEX, whereas that doesn't seem to be the case for me.

What might I be doing wrong?

Here is my table description:

mysql> describe purchases;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| purchase_id   | int(11)      | NO   | PRI | NULL    | auto_increment |
| name          | varchar(100) | YES  |     | NULL    |                |
| email         | varchar(100) | YES  |     | NULL    |                |
| password      | varchar(100) | YES  |     | NULL    |                |
| address_1     | varchar(150) | YES  |     | NULL    |                |
| address_2     | varchar(150) | YES  |     | NULL    |                |
| city          | varchar(50)  | YES  |     | NULL    |                |
| state         | varchar(30)  | YES  |     | NULL    |                |
| zip_code      | varchar(15)  | YES  |     | NULL    |                |
| phone         | varchar(15)  | YES  |     | NULL    |                |
| cc_number     | int(11)      | YES  |     | NULL    |                |
| cc_expiration | varchar(10)  | YES  |     | NULL    |                |
| cc_cvv        | int(11)      | YES  |     | NULL    |                |
| billing_zip   | varchar(15)  | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
14 rows in set (0.06 sec)

I'm pretty sure the problem is elsewhere in code that is not shown in this issue. Please show what `.toSQL()` prints out from `knex("purchases") .where({ purchase_id }) .update(data).toSQL()`, If you could add complete runnable example code it would be easy to tell where the problem is. — Mikael Lepistö, Dec 19 '18 at 06:59

score 1 · Accepted Answer · answered Dec 19 '18 at 07:09

Example code has some inconsistencies in data attributes.

let purchase_id = data.purchaseId;

Above you are converting camel case purchaseId to snake case just for .where() statement, but then you are passing data directly to .update(data) which should report that column purchaseId is not found from database.

Instead of that error you are mentioning about bindings being undefided, which means that your problem occurs already before query has been sent.

So... the problem in this case is not in the code shown in question, but elsewhere. Please add complete example how you execute and pass paramters to the code:

const update = async data => {
  let purchase_id = data.purchaseId;
  let result = await knex("purchases")
    .where({ purchase_id })
    .update(data);
  return result;
};

and I'll be able to tell what is wrong. Also if you are using some custom wrapIndentifiers / postProcessResult implementations, those can be reason for the failure.

Converting to uniformly using snake case in my client and server apps fixed the problem. I'm having difficulty figuring out when the undefined binding error was fixed. In the process of converting everything to snake case, I started to get the error you expected (`purchaseId` field not found in table). Before I started converting, `.toSQL()` caused the same undefined binding error. — mrwnt10, Dec 19 '18 at 15:11

score 0 · Answer 2 · answered Aug 31 '22 at 13:24

0

It happens when you are trying to use data in the query which is not defined. Happened to me while updating the table.

update "message" set "mStatus" = $1 where "mId" = $2 returning "mStatus"

When I checked, my "mId" value was undefined.

answered Aug 31 '22 at 13:24

Abdul Mattee

154
8

KNEX Undefined binding(s) detected when compiling UPDATE query

2 Answers2