How can I get all insert Ids when inserting multiple rows in a table with an auto increment primary key?

[Ks89]

I'm running a query to insert multiple rows at the same time in a table with an auto increment primary key.
How can I retrieve the array of instertIds as a result?

For instance, I inserted 3 rows and the first id is 27:

affectedRows: ResultSetHeader {
  fieldCount: 0,
  affectedRows: 3,
  insertId: 27,
  info: 'Records: 3  Duplicates: 0  Warnings: 0',
  serverStatus: 3,
  warningStatus: 0
}

Is there a way to get [27, 28, 29] as a result?
If not, which is the recommended way?

Thanks

[sidorares]

I don't think you can get it as response to a query/execute of insert

The only option I can think of is to iterate in a stored procedure and on each insert save LAST_INSERT_ID() to a variable or temp table, and then return select from that table

[Ks89]

mmm interesting.
Probably, I should find a different approach. Since my query is part of a transaction, I should read the uncommitted results with a select in some way.
Do you have a suggestion?

[wellwelwel]

@Ks89, a way would be to perform the insertions one by one, for example:

Instead:

  const [result] = await conn.execute<ResultSetHeader>(
    'INSERT INTO `users`(`name`) VALUES(?), (?), (?), (?);',
    ['Josh', 'John', 'Marie', 'Gween'],
  );

  console.log(result.insertId); // 1

Do it:

  const names = ['Josh', 'John', 'Marie', 'Gween'];
  const insertedIds = [];

  for (const name of names) {
    const [result] = await conn.execute<ResultSetHeader>(
      'INSERT INTO `users`(`name`) VALUES(?);',
      [name],
    );

    insertedIds.push(result.insertId);
  }

  console.log(insertedIds); // [ 1, 2, 3, 4 ]

For JS, you can just remove the <ResultSetHeader> 🙋🏻‍♂️

[Ks89]

Yes, this is another solution, however what about performance?

[wellwelwel]

@Ks89, you can test and compare it, for example:

const names = ['Josh', 'John', 'Marie', 'Gween'];

await conn.execute(
  'INSERT INTO `users`(`name`) VALUES(?), (?), (?), (?);',
  names,
);

const [insertedIds] = await conn.execute<RowDataPacket[]>({
  sql: 'SELECT `id` FROM `users` WHERE `name` IN(?, ?, ?, ?);',
  rowsAsArray: true,
  values: names,
});

console.log(insertedIds.map((user) => user[0])); // [ 1, 2, 3, 4 ]

/** Or without `rowAsArray`: */
// console.log(insertedIds.map((user) => user.id)); // [ 1, 2, 3, 4 ]

[Ks89]

@wellwelwel but how can I set READ UNCOMMITTED in the current transaction?
Is there either a specific function in node-mysql2 or a suggested approach?

[Ks89]

I'm trying to use SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED as described HERE but it isn't working.

const res = await connection.query('SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED');
logger.debug('db - setReadUncommitted (connection) - res: ', res);

The result is "{ '0': ResultSetHeader { fieldCount: 0, affectedRows: 0, insertId: 0, info: '', serverStatus: 2, warningStatus: 0 }, '1': undefined }" but I don't know if this is a success or not.

This is part of my code:

export async function withTransaction(callback: any) {
  const connection = await pool.promise().getConnection();
  try {
      console.log('withTransaction - setReadUncommitted');
      await setReadUncommitted(connection);
      console.log('withTransaction - beginTransaction');
      await connection.beginTransaction();
      console.log('withTransaction - callback');
      await callback(connection);
      console.log('withTransaction - commit');
      await connection.commit();
      await connection.release();
      console.log('withTransaction - release');
    } catch (err) {
      await connection.rollback();
      console.log('withTransaction - rollback');
      await connection.release();
      console.log('withTransaction - release');
      throw err;
    }
}

I created an insert utility function thats run this:

const [affectedRows] = await connection.query(query, params);
logger.debug('db - insert (connection) - affectedRows: ', affectedRows);
return (affectedRows as ResultSetHeader).insertId;

and this is my code with the transaction:

await withTransaction(async function callback(connection: any) {
let insertInstanceId;
    try {
      const insertId: any = await insert(
        createInstancesQuery,
        [ownerId, name, description],
        connection
      );
      console.log('create - insertId = ', insertId);
      insertInstanceId = insertId;
      console.log('create - insertInstanceId = ', insertInstanceId);
    } catch (err) {
      console.error('create - error ', err);
      throw err;
    }

    const results: Array<Instance> = await findInstances();
    console.log('results = ', results);
}

export const findInstances = async () => {
  return await query<Instance>('SELECT i.* FROM mydb.instances;', []);
};

I'm simply inserting rows in a table and then reading them before closing the transaction.
When transaction is closed, I can query my db to see the expected results, but not while in the middle of the transaction.
What am I missing? Is it something related to mysql library?

[Ks89]

@wellwelwel oh, I posted, before reading your next post.
The answer is yes. I'm using a transaction and I have to insert some rows in a table. Since I cannot obtain the inserted ids, I have to read those rows before closing the transaction. To do that I need to change the TRANSACTION ISOLATION LEVEL , but it's not working

[wellwelwel]

Hm, for me it works fine.

That is my complete test code:

import mysql, {
  ConnectionOptions,
  ResultSetHeader,
  RowDataPacket,
} from 'mysql2/promise';
import { performance } from 'perf_hooks';

(async () => {
  const access: ConnectionOptions = {
    database: 'mysql2_examples',
  };

  const conn = await mysql.createPool(access).getConnection();

  await conn.query('DROP TABLE IF EXISTS `users`;');

  await conn.query(
    'CREATE TABLE `users` (`id` INT(11) AUTO_INCREMENT, `name` VARCHAR(50), PRIMARY KEY (`id`));',
  );

  const names = ['Josh', 'John', 'Marie', 'Gween'];

  /** CASE A */
  {
    const start = performance.now();

    await conn.query(
      'SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED',
    );
    await conn.beginTransaction();

    try {
      await conn.execute(
        'INSERT INTO `users`(`name`) VALUES(?), (?), (?), (?);',
        names,
      );

      const [insertedIds] = await conn.execute<RowDataPacket[]>({
        sql: 'SELECT `id` FROM `users` WHERE `name` IN(?, ?, ?, ?);',
        rowsAsArray: false,
        values: names,
      });

      console.log(insertedIds.map((user) => user.id));

      conn.commit();
    } catch (error) {
      conn.rollback();
    } finally {
      conn.release();
    }

    const end = performance.now();

    console.log('Case A', end - start, 'ms');
  }

  /** CASE B */
  {
    const start = performance.now();

    await conn.query(
      'SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED',
    );
    await conn.beginTransaction();

    try {
      const names = ['Josh', 'John', 'Marie', 'Gween'];
      const insertedIds = [];

      for (const name of names) {
        const [result] = await conn.execute<ResultSetHeader>(
          'INSERT INTO `users`(`name`) VALUES(?);',
          [name],
        );

        insertedIds.push(result.insertId);
      }

      console.log(insertedIds);

      conn.commit();
    } catch (error) {
      conn.rollback();
    }

    const end = performance.now();

    console.log('Case B', end - start, 'ms');
  }

  conn.destroy();
})();

This prints:

[ 1, 2, 3, 4 ]
Case A 10.791214000433683 ms
[ 5, 6, 7, 8 ]
Case B 8.271267000585794 ms

[Ks89]

@wellwelwel
You are right, it's working. There is something wrong in my code. I have to do a full review.
Thank you for the support. 👍