mysql2 return value confusion

user_13081589 0
Viewed 2863

I'm still new to mysql and backend overall so i'm sorry if it's a dumb question.

I am using node/mysql2 to access mysql DB. Promisified connection pool method is used for connection. When i try this code:

    const result = await db.query(
        `
        SELECT
            id
        FROM
            User
        WHERE
            name = ?
        
        `,
        [name]
    );
    return result[0];

the return value is this array [ [ TextRow { id: 53 } ], [ ColumnDefinition { _buf: <Buffer 01 00 00 01 01 28 00 00 02 03 64 65 66 06 64 65 76 69 6c 7a 04 55 73 65 72 04 55 73 65 72 02 69 64 02 69 64 0c 3f 00 0b 00 00 00 03 0b 42 00 00 00 05 ... 24 more bytes>, _clientEncoding: 'utf8', _catalogLength: 3, _catalogStart: 10, _schemaLength: 6, _schemaStart: 14, _tableLength: 4, _tableStart: 21, _orgTableLength: 4, _orgTableStart: 26, _orgNameLength: 2, _orgNameStart: 34, characterSet: 63, encoding: 'binary', name: 'id', columnLength: 11, columnType: 3, flags: 16907, decimals: 0 } ] ]

result on terminal: enter image description here

but in fact it is an object. Because console.log('result is ', typeof result) shows me an object. This is the first thing that is confusing me. Why does console.log shows an object type but its return value is an array? Another thing is that when i try to access TextRow i simply can't. What is TextRow here doing? This also confuses me a lot. If anyone could help clarify this to me i would have appreciated very much

EDIT: added a screenshot of result for better reading

node.js object mysql arrays javascript
2 Answers

First of. Arrays in JavaScript are objects.

If you would do console.log(typeof []) you would object too.

There are 2 data structures in JavaScript

  1. Primitive data types = undefined, boolean, number, string etc
  2. Structural types = Object, Array, Set, etc.

See: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Data_structures

If you want to check if something is an array you have to use insanceof or check the constructor, but that's material for another answer. Basically if you do typeof on arrays you will get object. Dont' let that confuse you.

So result is REALLY an array. By looking at it we see that it that contains two arrays

  1. First array contains the actual data
  2. Second array contains column definitions

The text TextRow and ColumnDefinition are just a descriptive text that gets logged out to describe the object. You can't do anything with the text itself, just the objects.

So..... if you want only the rows with the data, then you only have to get the first element in your result array, if you want the definitions then get the second element in the array

const rows = result[0]
const defs = result[1]

You can then loop through the rows (if you want)

rows.forEach(item => {
 console.log('id', item.id)
})

That's why your code has result[0] - because its only returning the rows.

user_51894720

As mysql2 is quite different from mysql because it return a promise as this document: Here.

So I edited your code like this, hope it works for you:

const [rows]= await db.query(
    `
    SELECT
        id
    FROM
        User
    WHERE
        name = ?
    
    `,
    [name]
);
return rows.map((row) => { return row.id  };
user_65343380
Related