Question 113164 in Mysql Question posted in
The official MySQL documentation can be found here.

Strongly typing results from mysql2 in Typescript

I am working on an application using NextJS and Typescript and am attempting to determine the best way to properly type my 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 (?, ?)",
    ["[email protected]", (await hashedPassword).toString()]
  );

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

export default Test;

I am wanting to strongly type insertId to remove all warnings and errors through ESLint, but unfortunately, every effort I have made has been unsuccessful. The error I am getting 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 implementation attempts was this SO response but I could not get it to work…

Any and all help is greatly appreciated!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    So, I managed to solve my own problem after tackling it for a while.

    It turns out, I was doing my checks incorrectly.

    Before calling auth.insertId, you'll want to include the following check:

    if (!auth || !("insertId" in auth)) {
  // Do something
}

    That way, you don't actually have to type anything, because it can get super complicated when you are attempting to do that with mysql.


  2. 0

    I did as well get myself in the same problem as you, after looking up in the mysql2 types files, I found you can pass the result type of the query.

    For example:

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

    For inserts and updates you can be using the OkPacket type and for selects you can use RowDataPacket. You can type it even further by implementing RowDataPacket to the expected responsey type of the query and passing it in the query function.

    For example:

    export interface Post {
  title: string;
}

export interface PostRow extends RowDataPacket, Post {}

    Then when you are querying you can pass as follows:

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

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search