Better and more autonomous types for query and execute

[wellwelwel]

Important

This isn't for this moment, only in a future plan, but I would like to keep that idea open.

---

Bringing up an idea for a major release 💡

Reading and basing from various past discussions, the types from query and execute really can be better.

Why?

Both RowDataPacket[][], ResultSetHeader[] and ProcedureCallPacket[] (not currently in use) only happens when the option multiStatements is true and receive multiple queries.

---

How would it be?

TS

• By default the query and execute will be just:

| RowDataPacket[]
| ResultSetHeader 
| ProcedureCallPacket

• Only for multiStatements as true, the types will be:

| RowDataPacket[] 
| RowDataPacket[][] 
| ResultSetHeader 
| ResultSetHeader[] 
| ProcedureCallPacket 
| ProcedureCallPacket[] // not currently in use

---

Also related

A simple optional helper to handle the return from queries 🤹🏻‍♀️

Both JS and TS

A simple helper like isResultSetHeader can ensure the queries return, especially for Procedure Calls.
When the query return is not known, this can be helpful and can also be easily documented.

A basic end-use in practice:

if (mysql.isResultSetHeader(queryResult)) {
  return queryResult.affectedRows;
}

// else: it's a RowDataPacket array

---

About TypeScript

For TypeScript users, this can be particularly useful.
Including, this feature can already be seen from current examples:

node-mysql2/examples/typescript/procedure-call-packet.ts

Lines 18 to 32 in 9051caf

	const isResultSetHeader = (data: unknown): data is ResultSetHeader => {
	if (!data || typeof data !== 'object') return false;
	const keys = [
	'fieldCount',
	'affectedRows',
	'insertId',
	'info',
	'serverStatus',
	'warningStatus',
	'changedRows',
	];
	return keys.every((key) => key in data);
	};

And working here:

node-mysql2/examples/typescript/procedure-call-packet.ts

Lines 78 to 87 in 9051caf

	if (isResultSetHeader(users)) {
	console.log('----------------');
	console.log('Affected Rows:', users.affectedRows);
	} else {
	users.forEach((user) => {
	console.log('----------------');
	console.log('id: ', user.id);
	console.log('name:', user.name);
	});
	}

---

As before, this is just an idea and it's not for this time 🙋🏻‍♂️

[sidorares]

only happens when the option multiStatements is true and receive multiple queries.

also when you call a stored procedure that has multiple statements - no need for the multipleStatements: true flag in that case

Example:

await connection.query(`
drop procedure if exists getUsers;
`);
await connection.query(`
   CREATE PROCEDURE getUsers()
   BEGIN
      SELECT 1 as one;
      SELECT 1 as two;
      SELECT 1 as three;
   END
`);

const [rows, fields] = await connection.query('CALL getUsers()');
console.log(rows, fields);

Prints

[
  [ { one: 1 } ],
  [ { two: 1 } ],
  [ { three: 1 } ],
  ResultSetHeader {
    fieldCount: 0,
    affectedRows: 0,
    insertId: 0,
    info: '',
    serverStatus: 2,
    warningStatus: 0
  }
] [
  [ `one` BIGINT(2) NOT NULL ],
  [ `two` BIGINT(2) NOT NULL ],
  [ `three` BIGINT(2) NOT NULL ],
  undefined
]

[wellwelwel]

Hey @sidorares, for Procedure Calls we can use the ProcedureCallPacket type 🙋🏻‍♂️

Using your example:

const [rows] = await conn.query<ProcedureCallPacket<RowDataPacket[]>>(
  'CALL getUsers()',
);

rows.forEach((users) => {
  if (!isResultSetHeader(users)) {
    users.forEach((user) => {
      console.log(user);
    });
  }
});

/**
 * { one: 1 }
 * { two: 1 }
 * { three: 1 }
 */

The return from a Procedure Call is [RowDataPacket[], ResultSetHeader] and this case works in current version (except for isResultSetHeader), also it's possible to follow a practical example here 🤹🏻‍♀️

From TypeScript documentation, you can follow about the ProcedureCallPacket type here.