TypeScript correctly type a queryresult MySQL

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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);

Method 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);

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0