TypeScript correctly type a queryresult MySQL

Question

I am using the mysql2/promise npm package for connecting and doing queries to a MySQL database. I got confused trying to set the correct typing for my query result, because I don't know in advance what type the result will be.

I have created a database class that has an async query method.

// Database.ts
import mysql, { Pool } from "mysql2/promise"; // import mysql2/promise

export class Database implements IDatabase {
  logger: ILogger;
  pool: Pool;

  constructor(logger: ILogger) {
      this.logger = logger;
      // pool connection is set up here
  }

  async query(sql: string, options?: unknown): Promise<unknown> { // type?
      const [rows] = await this.pool.query(sql, options);
      return rows;
  }
}

In my server code, I would like to be able do something like this:

// server.ts
import { Database } from "./core/Database";
const db = new Database(logger);

app.get("/", async (req, res) => {
  const sql = `
      select *
      from users;
  `;
  const users: IUser[] = await db.query(sql); // get people

  // do some logic x

  // send response
  res.json({
      result: x
  });
});

Using unknown doesn't work because I can't assign it to my type, using any does, but feels wrong. Is there a clean way to do this?

Answer 1

Type the function as:

async query<T = unknown>(sql: string, options?: unknown): Promise<T[]> { 

Then use the function this way:

const users = await db.query<IUser>(sql);

Answer 2

With help of @Evert and this answer, I found a solution

I created following types:

export type DbDefaults = RowDataPacket[] | RowDataPacket[][] | OkPacket[] | OkPacket;
export type DbQueryResult<T> = T & DbDefaults;

Rewrote my method like this:

async query<T>(sql: string, options?: unknown): Promise<DbQueryResult<T[]>> {
  const [result] = await this.pool.query<DbQueryResult<T[]>>(sql, options);
  return result;
}

I can use it like this now:

const sql = `
  select *
  from users;
`;

const people = await db.query<IUser>(sql);

Just casting as T is also possible.

public async query<T>(sql: QueryString, parameters?: unknown[]): Promise<T[]> {
  const [rows] = await this.pool.query(sql, parameters);
  return rows as T[];
}