The type error about the return of mysql2/promise query

Question

I'm using the mysql2/promise library in typescript, however, I can't understand the type of the return of the query method, and I can't solve an error.
My code is below.

import * as mysql from "mysql2/promise";

let mysql_connection: mysql.Connection = await mysql.createConnection({
   //Some options here.
});

let backend_mysql_res: [(mysql.RowDataPacket[][] | mysql.RowDataPacket[] | mysql.OkPacket | mysql.OkPacket[] | mysql.ResultSetHeader), mysql.FieldPacket[]] = await mysql_connection.query(`SELECT * FROM data.table ORDER BY time DESC LIMIT 1`);

//The error occurred this line. 
console.log(backend_mysql_res[0][0]);

And the error message about the backend_mysql_res[0][0] in the last line of the above code is below.

TS7053: Element implicitly has an 'any' type because expression of type '0' can't be used to index type 'RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader'.   Property '0' does not exist on type 'RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader'.

The error message above describes type '0', however, what is the type '0'? Isn't this type number?
So, I can't find the solution to the problem.

What are the proper types of the response of the query method of mysql2/promise? Furthermore, the above type specification of let backend_mysql_res ... is what I wrote according to the suggestion from the IDE. But I think this is a little too long. So is there a way to sophisticate code?

I just began to learn the typescript these days.
Anyone who can help me.

Answer 1

It could be not the best solution, however you can use:

let backend_mysql_res: any = await mysql_connection.query(`SELECT * FROM data.table ORDER BY time DESC LIMIT 1`);

That should give you access to the fields you need. Use:

console.log(backend_mysql_res)

to see the results.

Answer 2

---

I am using a Custom function. Not sure if this is the solution you are looking for or not but it does work for a single query. Still working on multiple query support.

Firstly, i have created a wrapper for the mysql connection query. Which we can find it as below in typescript. Now, there might be a better way so feel free to suggest an edit on this.

import {
  createPool
} from "mysql2/promise";
var pool = createPool({
  waitForConnections: true,
  connectionLimit: 10,
  host: process.env.DBHOST,
  port: parseInt(process.env.DBPORT),
  user: process.env.DBUSER,
  password: process.env.DBPASSWORD,
  database: process.env.DB_NAME,
  multipleStatements: true,
  connectTimeout: 30000,
});
export const fireQuery = async <
  T extends RowDataPacket[][] | RowDataPacket[] | OkPacket | OkPacket[] | ResultSetHeader > (QUERY: string, PARAMS: any[] = []): Promise < T extends RowDataPacket[][] | RowDataPacket[] | OkPacket | OkPacket[] | ResultSetHeader ? T : RowDataPacket[][] | RowDataPacket[] | OkPacket | OkPacket[] | ResultSetHeader > => {
    const connection = await pool.getConnection();
    try {
      console.log("starting transaction...", QUERY, PARAMS);
      await connection.beginTransaction();

      console.log("running query...");
      let queryResult = await connection.query <
        T extends |
        OkPacket |
        ResultSetHeader |
        RowDataPacket[] |
        RowDataPacket[][] |
        OkPacket[] ?
        T : OkPacket | ResultSetHeader | RowDataPacket[] | RowDataPacket[][] | OkPacket[] > (QUERY, PARAMS);
      console.log("committing transaction...");
      await connection.commit();
      console.log("transaction committed.");
      return queryResult[0];
    } catch (error) {
      Sentry.captureException(error);
      connection.rollback();
      console.error("an error occurred:", error);
      throw error;
    } finally {
      connection.release();
    }
  };

Which can be used as below.

For Single query

export interface ISelectQueryInterface extends RowDataPacket {
col1: string;
col2: number;
col3: boolean;
}

const getUserResult = await fireQuery<UserRDataType[]>("select col1, col2, col3 from table where mobileNo = ?", [data.mobileNo])

for multiple queries, it's a bit hacky as we are first defining data type to any and then providing schema for it for our use case.

export interface ISelectQueryInterface1 extends RowDataPacket {
    col1: string;
    col2: number;
    col3: boolean;
}
export interface ISelectQueryInterface2 extends RowDataPacket {
    col4: string;
    col5: number;
    col6: boolean;
}
export interface ISelectQueryInterface3 extends RowDataPacket {
    col7: string;
    col8: number;
}
const output: [ISelectQueryInterface1[], ISelectQueryInterface2[], ISelectQueryInterface3[]] = await fireQuery<any>("select col1, col2, col3 from table where mobileNo = ?;select col4, col5, col6 from table where email = ?;select col7, col8 from table;", ["1234567890", "[email protected]"]);