How to get insertId for MySQL using Mysql2 in Node with async and pool?

user_2202952 0
Viewed 4377

I am trying to use async await with mysql2 and pooling but I think I doing things wrong. Below is my code (as I said, I am not really sure if I am doing things right here).

    const pool = mysql.createPool({
      host: 'localhost',
      user: 'root',
      database: 'test',
      password: '',
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0
    });
    
    const promisePool = pool.promise();   // Get a Promise wrapped instance of that pool   
    

app.post('/api/register', (req, res) => {

    async function queryDB() {
    
        try {
          const addUser = await promisePool.execute(
            "INSERT INTO users (company, email, password) VALUES (?,?,?)",
            ['Ikea', 'Ikea@ikea.com', '123'],
          )
          const addAnother = await promisePool.execute(
            "INSERT INTO users (company, email, password) VALUES (?,?,?)",
            ['Google', 'Google@google.com', '123']
          )
    
          console.log(addUser)
          res.status(200).json('Users saved')
          
        } catch (err) {
          console.log(err)
          res.status(409).json('User already exist')
        };
      }
    
      queryDB();
})

The idea is that "addUser" should be saved, and if it is not unique, an error will occur (because the SQL database email column is set to unique) and then addAnother won't start inserting.

What I can't understand is how to get the insertId from the addUser insert? I will need it for the second insert. If I console.log addUser I can see an object like this:

ResultSetHeader {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 72,
  info: '',
  serverStatus: 2,
  warn

So insertId is sent back to me, but I can't seem to reach it. If I try to grab it it says cant read property of undefined. I am feeling that I am doing this all wrong, so how should I do it instead to get the insertID?

async-await mysql2 node.js connection-pooling mysql
3 Answers

It seems like the result was in an array. I solved it with: addUser[0].insertId. I never got LAST_INSERT_ID() to work though.

user_22029520

If you insert multiple rows into the same table, and the table has an AUTO_INCREMENT primary key style column, the server assigns the values for you. You don't need to know the insertId of the first row you insert to get the second row inserted correctly.

The insertId value you need is in addUser.insertId.

          const addUser = await promisePool.execute(
            "INSERT INTO users (company, email, password) VALUES (?,?,?)",
            ['Ikea', 'Ikea@ikea.com', '123'],
          )
          const theInsertIdForAddUser = addUser.insertId

But notice that it also can be retrieved directly in the next MySQL statement via the SQL LAST_INSERT_ID() function. That lets you do an operation like this.

   INSERT INTO users_properties 
           (key, value, userId)
    VALUES ('tel','555-1212',LAST_INSERT_ID());

You don't have to send the value back.

user_2056080 
insertId: 72

You have it in you response.

user_40892120
Related