Using TypeScript, how do I strongly type mysql query results
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;
}
}
Solution 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", [])
Solution 2:
// the function should return a Promise that resolves to
{ uid: number, uname: string }
// but typeof rows is
{ rows: { uid: number, uname: string } }
Executing the example, the result I get is something like:
[ TextRow { uid: 1, uname: 'foo', email: '[email protected]' } ]
So pool.query is returning an array with an array of IUser as first element.
Returning multiple users:
class UserController {
// returns all users
public async getUsers(): Promise<Array<IUser>> {
const [rows]: [Array<IUser>] = await pool.query(
"SELECT * FROM `user`", []);
return rows; // rows is Array<IUser> so it matches the promise type
}
}
Returning a specific user:
class UserController {
public async getUser(id: number): Promise<IUser> { // Note the
const [rows]: [Array<IUser>] =
await pool.query("SELECT * FROM `user` WHERE `email` = ?",
["[email protected]"]);
// If email is unique as it would be expected it will
// return a single value inside an array
// rows is still Array<IUser>
return rows[0]; // rows[0] is an IUser
}
}
Solution 3:
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...
Solution 4:
I had the same error so I search in google and founded this example: https://github.com/types/mysql2/blob/master/test/promise.ts
so my code was like this
const [user] = await pool.query<RowDataPacket[]>("SELECT * FROM Users WHERE email = ?", [email]);
if(!user.length) {
throw {
error: true,
message: 'no user founded'
}
}
With this, I can use the autocomplete of vs code and have no errors
Solution 5:
The approach in the accepted answer didn't work for me ([email protected], [email protected]). This code
interface IUserRow {
ssid: string
}
const [rows]: [IUserRow[]] = await pool.query(
'select ssid from user where id = ?',
[session.userId],
)
gives me
error TS2322: Type '[RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[], FieldPacket[]]' is not assignable to type '[IUserRow[]]'.
Types of property '0' are incompatible.
Type 'RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[]' is not assignable to type 'IUserRow[]'.
Type 'RowDataPacket[]' is not assignable to type 'IUserRow[]'.
Property 'ssid' is missing in type 'RowDataPacket' but required in type 'IUserRow'.
with [rows] highlighted.
I was able to get everything working with:
interface IUserRow {
ssid: string
}
const [rows] = await pool.query(
'select ssid from user where id = ?',
[session.userId],
)
// This first if statement is a type guard against rows possibly being OkPacket
if (Array.isArray(rows) && rows.length > 0) {
// The casting works fine here
const userRow = rows[0] as IUserRow
if (userRow.ssid !== session.ssid) {
...
}
}
I am no Typescript expert so there is probably a much better way to do this! Still, I hope I can potentially save someone frustration if they also have problems with the accepted answer.