What's the proper way to use Typescript to run queries?

[raisen]

I read #1329 but still cannot find a way to run queries without using OkPacket[] as type argument to avoid the warning below:

const [results] = await db.execute<OkPacket[]>('select * from users'); // this solves the warning
console.log(results.length);

Should I create an interface that automatically uses OkPacket[] as type argument for all the execute and query calls?

[haroldiedema]

Almost.

Use RowDataPacket[] for SELECT statements, while using ResultSetHeader when using UPDATE, INSERT and DELETE queries. The latter contains affectedRows and lastInsertId properties.

See #1393 also.

@sidorares Would it be an idea to introduce specific return types for .query() for SELECT statements to always return RowDataPacket-arrays while using .execute() always returns a ResultSetHeader or OkPacket? That way the user won't always have to explicitly define the actual return type. I'm also unsure about the practical differences between an OkPacket and ResultSetHeader 🤔

[richbai90]

This is what I'm using currently. It's an augmentation of the existing module. It only changes the query methods, not the execute ones, but you can do something similar with the execute methods. The benefit of these types is that it can infer the rowsAsArray value and return the appropriate type in either case. You can use it just by creating a .d.ts file of some name at the root directory.

import {
  RowDataPacket,
  FieldPacket,
  QueryOptions,
} from "mysql2/promise";

declare module "mysql2/promise" {
  interface QueryOptionsRowsAsArray extends QueryOptions {
    rowsAsArray: true;
  }
  interface Pool extends NodeJS.EventEmitter {
    query<T extends RowDataPacket[]>(sql: string): Promise<[T, FieldPacket[]]>;
    query<T extends RowDataPacket[]>(
      sql: string,
      values: any | any[] | { [param: string]: any }
    ): Promise<[T, FieldPacket[]]>;
    query<T extends QueryOptions>(
      options: T
    ): Promise<[T extends infer P ? P extends QueryOptionsRowsAsArray ? RowDataPacket[][] : RowDataPacket[] : never, FieldPacket[]]>;
    query<T extends QueryOptions>(
      options: T,
      values: any | any[] | { [param: string]: any }
    ): Promise<[T extends infer P ? P extends QueryOptionsRowsAsArray ? RowDataPacket[][] : RowDataPacket[] : never, FieldPacket[]]>;
  }
}

[lukaszprus]

Almost.

Use RowDataPacket[] for SELECT statements, while using ResultSetHeader when using UPDATE, INSERT and DELETE queries. The latter contains affectedRows and lastInsertId properties.

@haroldiedema And how can you be more strict about typing the query result? Let's say you have some User interface. How do you achieve something like db.execute<User[]>('select * from users')?

[yuxizhe]

@raisen I think you can use the 'in' operator to check

const [results] = await db.execute('')
if ("length" in results) {
    console.log(result.length)
}

https://www.typescriptlang.org/docs/handbook/advanced-types.html#type-guards-and-differentiating-types