Using TypeScript, how do I strongly type mysql query results

Question

I'm new to Typescript and can't figure out if I'm strongly typing the results of my query correctly or not. Here's the essence of my code...

import mysql2, {Pool} from "mysql2";
const pool: Pool = mysql2.createPool({...}).promise();

interface IUser {
    uid   : number;
    uname : string;
}

class UserController {

    public async getUser(id: number): Promise<IUser> {
        const [rows]: Array<{rows: IUser}> = await pool.query(
            "SELECT * FROM `users` WHERE `email` = ?",["[email protected]"]);        
        return rows;
    }
}

The TypeScript compiler (3.3.1) complains about my return rows statement.

TS2740: Type '{rows: IUser;}' is missing the following properties from type 'IUser': uid and uname.

If I ignore the return with // @ts-ignore everything works great. I get my object back just fine without any errors.

Am I doing something wrong?

---

I made some changes, but I'm honestly confused as to why TypeScript doesn't complain. It doesn't seem right at all.

    class UserController {

        public async getUser(id: number): Promise<{rows: IUser}> {
            const [rows]: Array<{rows: IUser}> = await pool.query(
                "SELECT * FROM `users` LIMIT 3");        
            return rows;
        }
    }

Is this right???

---

So, no that's all wrong.

When I thought about query returning [rows, fields] it started too make a little more sense. I think @joesph-climber is correct with some tweaked syntax.

This works and makes sense to me...

    class UserController {

        public async getUser(id: number): Promise<Array<{rows: IUser}>> {
            const [rows]: [Array<{rows: IUser}>] = await pool.query(
                "SELECT * FROM `users` LIMIT 3");        
            return rows;
        }
    }

This also works and is probably more readily understandable.

    class UserController {

        public async getUser(id: number): Promise<IUser[]> {
            const [rows]: [IUser[]] = await pool.query(
                "SELECT * FROM `users` LIMIT 3");        
            return rows;
        }
    }

Answer 1

Using [email protected], [email protected], @types/mysql2

The interface must extend RowDataPacket:

interface IUser extends RowDataPacket {
  ssid: string
}

From there you can pass in the type:

const [rows]: [IUser[], FieldPacket[]] = await connection.query<IUser[]>("SELECT ssid FROM user", [])

or simply

const [rows] = await connection.query<IUser[]>("SELECT ssid FROM user", [])

Answer 2

Using pool.query<type> was not enough for me when trying to set variables that were going to be used later because the datatype RowDataPacket was still infringing on my types...

I ended up creating a wrapper for the pool's query function. Looks like this:

type Row = import("mysql2").RowDataPacket
type Ok = import("mysql2").OkPacket
type dbDefaults = Row[] | Row[][] | Ok[] | Ok
type dbQuery<T> = T & dbDefaults

export const db = {
  query: async <T>(query: string, params?: Array<any>): Promise<[T, any]> => {
    return pool.promise().query<dbQuery<T>>(query, params)
  },
}

This allows me to use the query function in other parts of the project similar to other answers as

let result = await db.query<desiredType>("SELECT", [optionalArgs])

but it strips away the type information for RowDataPacket, etc...