Strongly type mysql2 query results in Typescript

Question

I'm developing an application using NextJS and Typescript and trying to determine the best way to correctly type MySQL responses. Here is the API endpoint:

import { hash } from "bcrypt";
import type { NextApiRequest, NextApiResponse } from "next";
import randomstring from "randomstring";

import { executeQuery } from "../../../lib/db";

const Test = async (req: NextApiRequest, res: NextApiResponse) => {
  // Manage password generation
  const password = randomstring.generate(16);
  const hashedPassword = hash(password, 10);

  // Create new auth using email and password
  const auth = await executeQuery(
    "INSERT INTO auth (email, password) VALUES (?, ?)",
    ["test@test.com", (await hashedPassword).toString()]
  );

  res.statusCode = 200;
  res.json(auth.insertId);
};

export default Test;

I would like to strongly type insertId via ESLint to remove all warnings and errors, but unfortunately, all my efforts have been unsuccessful. The error I receive is:

Property 'insertId' does not exist on type 'RowDataPacket[] | RowDataPacket[][] | OkPacket | OkPacket[] | ResultSetHeader | { error: unknown; }'.
  Property 'insertId' does not exist on type 'RowDataPacket[]'.ts(2339)

My executeQuery function is defined as:

import mysql from "mysql2/promise";

export const executeQuery = async (query: string, params: unknown[] = []) => {
  try {
    const db = await mysql.createConnection({
      host: process.env.MYSQL_HOST,
      database: process.env.MYSQL_DATABASE,
      user: process.env.MYSQL_USER,
      password: process.env.MYSQL_PASSWORD,
    });

    const [results] = await db.execute(query, params);
    db.end();

    return results;
  } catch (error) {
    return { error };
  }
};

One of my attempts at implementation is this SO response, but I can't get it to work...

Any and all help is greatly appreciated!

Answer 1

I also encountered the same problem as you. After searching for the mysql2 type file, I found that you can pass the result type of the query.

For example:

connection.query('INSERT INTO posts SET ?', {title: 'test'},
function (error, results, fields) {
  if (error) throw error;
  console.log(results.insertId);
});

For inserts and updates you can use the OkPacket type, and for selections you can use the RowDataPacket type. You can type it further by implementing RowDataPacket as the expected response type of the query and passing it into the query function.

For example:

export interface Post {
  title: string;
}

export interface PostRow extends RowDataPacket, Post {}

Then you can pass it like this when querying:

connection.query('SELECT * FROM posts WHERE id = 1',
function (error, results, fields) {
  if (error) throw error;
  // ...
});

Here's my 2 cents, hope it helps someone in the future.