node mysql2 can't access update results

Question

I'm doing a simple UPDATE with mysql2 :

   UPDATE table1
    SET table1.value1 = ?, table1.value2 = ? 
    WHERE user_id = (
      SELECT user_id 
      FROM user
      WHERE company_id = ?
    )

table1 is related to the user table by user_id, but I only have the company_id so I do a subquery (probably should be a join, but thats another discussion):

const [results, buff] = await connection.execute(query, values);
return results

However, when accessing the results to make sure there are no errors and that only a single row is updated, results returns:

  console.log
    ResultSetHeader {
      fieldCount: 0,
      affectedRows: 1,
      insertId: 0,
      info: 'Rows matched: 1  Changed: 0  Warnings: 0',
      serverStatus: 2,
      warningStatus: 0,
      changedRows: 0
    }

But I can't access the values. If I try results.affectedRows I get

Property 'affectedRows' does not exist on type 'RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[]'.

The only way to make it work is to do results['affectedRows']. Any suggestions?

Have you seen [this](https://github.com/mysqljs/mysql/issues/363#issuecomment-11724676) Git issue thread? — Phani Mahesh, Jun 02 '20 at 15:55
@PhaniMahesh I'm following their async/await suggestion: https://www.npmjs.com/package/mysql2#using-promise-wrapper The only way to get the error is with a try/catch, which I am using. Should I just rely on that for the success of the query? — hamncheez, Jun 02 '20 at 21:37

score 3 · Answer 1 · answered Jun 03 '20 at 03:10

3

So I forgot to mention that this is a typescript error, and I was able to fix it by doing:

const [results, buff] = await connection.execute(query, values);
const json: any = results;
return json.affectedRows //or whatever property I want

This also works:

const [results, buff] = await connection.execute(query, values);
return json as any; //then in the calling fuction access the properties I want

answered Jun 03 '20 at 03:10

hamncheez

709
12
22

I also ended up making an interface for the mysql2 response, so I don't have to use `any` – hamncheez Jun 03 '20 at 03:11
could you please update your answer to show the interface you use? I'm stuck working against the RowDataPacket type – Neil Oct 22 '20 at 14:58
So I'm doing `const [results, buff] = await connection.execute(query, values); return results as IUpdateTokens`, and the interface is `interface IUpdateTokens { fieldCount?: number, affectedRows?: number, insertId?: number, info?: string, serverStatus?: number, warningStatus?: number, changedRows?: number, }` – hamncheez Oct 23 '20 at 20:55
1

okay interesting. I still think that would give my implementation trouble with the RowDataPacket... but I'll take a look soon – Neil Oct 23 '20 at 21:03

score 0 · Answer 2 · answered Mar 22 '23 at 14:47

I recently faced the same problem and the solution I found is to add return type to connection.execute itself, from types provided by mysql2 const result = await pool.execute<OkPacket>(query, values); I reckon you no longer need it, but hopefully useful to someone else. Cheers.

macha_choco · Answer 3 · 2023-06-27T17:36:15.453

perhaps try in this way :

let result = await db.execute($sql, values);

result = JSON.parse(JSON.stringify(result));

console.log(result)

the output will be :

{
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  info: 'Rows matched: 1  Changed: 1  Warnings: 0',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 1
}

this way somehow makes it become a normal object to be accessed

so the object can be accessed like result.affectedRows, result.insertId, ...

node mysql2 can't access update results

3 Answers3