Property 'length' does not exists on type ... OkPacket in mysql2 module

Question

I have code similar to this - very simple just to show the case.

this.getCode = (code: string): Promise<codeObject | false> => {
        return new Promise((resolve, reject) => {
            pool.query('SELECT * FROM ?? WHERE code = ?', [TABLE, code], function (err, result) {
                if (err) {
                    return reject(err);
                }

                if (result.length === 0) {
                    return resolve(false);
                }
            });
        });
    };

Problem is in line if (result.length === 0) {, the error is error TS2339: Property 'length' does not exist on type 'RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[]'.

I can not find any way to redefine OkPacket to at least have length?: number that would be enought for ignore that error (most selects do not get OkPackets anyway and i dont wanna have to check type on each select if what i got is not OkPacket when i know it isnt)...

Answer 1

Major oversight in the design of the mysql2 typing. The problem is, as the error suggests, that mysql2 defines 4 possible return types for query(). RowDataPacket[] is for individual SELECT queries that return a set of rows. RowDataPacket[][] is used when you have multiple select queries within one statement, and each returns a set of rows. Similarly OkPacket returns the result of an INSERT or UPDATE or any other operation, and OkPacket[] is returned when there are multiple queries of those types in one statement.

So what's happening is, Typescript has no idea which of the 4 types are going to be returned when you execute your query. In order to resolve this, you must specify which one will be returned.

First of all, you need to import the relevant types from the mysql2 package. So start with the following in your database file

import mysql from "mysql2/promise";

const connection = mysql.createPool({
    ...
});

export type RowDataPacket = mysql.RowDataPacket;
export type OkPacket = mysql.OkPacket;
export default connection;

Then when you make your queries, do

import db, { RowDataPacket } from "../db.server";

const [existingUser] = await db.execute<RowDataPacket[]>(`
    SELECT UserID FROM User WHERE Email = ?
`, [email]);

if (existingUser.length) console.log(`User already exists`);

And similarly with multiple select queries,

import db, { RowDataPacket } from "../db.server";

const [existingUsers] = await db.execute<RowDataPacket[][]>(`
    SELECT UserID FROM User WHERE Email = ?;
    SELECT UserID FROM User WHERE Email = ?;
`, [email0, email1]);

if (existingUsers[0].length || existingUsers[1].length) console.log(`User already exists`);

It's annoying, but this is the actual proper way to do it.

Answer 2

For my own use case, the map was not defined in OkPacket, because I needed the following

function (error, results, fields) {

            const data = results.map(() => {
                //... do stuffs..
                return {
                    name: item.name,
                    username: item.username,
                    image: item.image,
                    bio: item.bio,
                    url: item.url,
                }
            })
            resolve([results, fields]);
}

To get rid of the error, I ended up checking if map exists in the results object, and making an early return if not (just the reject() won't fix the error). Modified the code as following

function (error, results, fields) {
            if(!('map' in results)) {
                reject(results);
                return;
            }
            const data = results.map((item) => {
                // ...do stuff...
                return {
                    name: item.name,
                    username: item.username,
                    image: item.image,
                    bio: item.bio,
                    url: item.url,
                }
            })
            resolve([results, fields]);
}

For your case you could check for the length, for example, and make an early return if it does not exist.

if(!('length' in results)) {
     reject(results);
     return;
}

Answer 3

I faced a similar issue and was able to fix the warning that TypeScript gives, "Property 'length' does not exist on type 'OkPacket'" by checking to see if the resulting rows from the query was an array. This was a lot easier for me to do than converting the rows to a string with JSON.stringify().

Using the original example above, I'd change the result.length comparison to this:

if (Array.isArray(result) && result.length === 0) {
   return resolve(false);
}

Answer 4

I also dislike the design choice on this, but i usually use :

const [rows, fields] = await conn.execute(QUERY_STRING) as any
console.log(rows.length)

Answer 5

I hate this error as well. This is my workaround:

const [rows] = await db.query(`mySqlHere`);
let tempResult: any = rows;

console.log(tempResult.length); // OK

Answer 6

I couldn't find a proper way to handle this either, so ended up with this workaround for my similar case:

const [rows,] = await connection.execute("SELECT id FROM users WHERE email = ?", [profile.email]);
if ((rows as any).length === 1) {
   return (rows as any)[0].id;
}
logger.error("Unexpected query response", rows);
throw Error("This code should never be executed");

You can also use user defined type-guards. Like this:

const isRowDataPacket = (rows: RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader): rows is RowDataPacket[] | RowDataPacket[][] => {
    return (rows as RowDataPacket[] | RowDataPacket[][]).length !== undefined
}

But you would still need to make a typecast further to interpret each row correctly:

const [rows,] = await connection.execute("SELECT id FROM users WHERE email = ?", [profile.email]);
if (isRowDataPacket(rows) && rows.length === 1) {
    return (rows[0] as { id: string }).id;
}