Using MySQL in Node.js with TypeScript

For a long time I was using MySQL with TypeScript without knowing that you can make use of the diamond <> operator to get a typed result from a SQL query. That changed from today on and I want it to share with you. To do so, I’m using the mysql2 npm package to run SQL queries in Node.js.

First of all, let’s create a simple MySQL table:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(50) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  admin BOOLEAN NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Easy as that.

Next, we have to define a type for the query results which is based on the table we just created in the previous step. Therefore, we define an interface and set the properties according to the table columns:

import { RowDataPacket } from "mysql2"

export interface IUser extends RowDataPacket {
  id?: number
  email: string
  password: string
  admin: boolean
  created_at: Date
}

Don’t forget to extend the RowDataPacket type from the mysql npm package, otherwise you’ll get an error in the next step.

Now, we can use this interface for our SQL queries. Let’s create a wrapper class that includes basic CRUD operations:

import { OkPacket } from "mysql2"

import { connection } from "./db"

export class UserRepository {
  readAll(): Promise<IUser[]> {
    return new Promise((resolve, reject) => {
      connection.query<IUser[]>("SELECT * FROM users", (err, res) => {
        if (err) reject(err)
        else resolve(res)
      })
    })
  }

  readById(user_id: number): Promise<IUser | undefined> {
    return new Promise((resolve, reject) => {
      connection.query<IUser[]>(
        "SELECT * FROM users WHERE id = ?",
        [user_id],
        (err, res) => {
          if (err) reject(err)
          else resolve(res?.[0])
        }
      )
    })
  }

  create(user: IUser): Promise<IUser> {
    return new Promise((resolve, reject) => {
      connection.query<OkPacket>(
        "INSERT INTO users (email, password, admin) VALUES(?,?,?)",
        [user.email, user.password, user.admin],
        (err, res) => {
          if (err) reject(err)
          else
            this.readById(res.insertId)
              .then(user => resolve(user!))
              .catch(reject)
        }
      )
    })
  }

  update(user: IUser): Promise<IUser | undefined> {
    return new Promise((resolve, reject) => {
      connection.query<OkPacket>(
        "UPDATE users SET email = ?, password = ?, admin = ? WHERE id = ?",
        [user.email, user.password, user.admin, user.id],
        (err, res) => {
          if (err) reject(err)
          else
            this.readById(user.id!)
              .then(resolve)
              .catch(reject)
        }
      )
    })
  }

  remove(user_id: number): Promise<number> {
    return new Promise((resolve, reject) => {
      connection.query<OkPacket>(
        "DELETE FROM users WHERE id = ?",
        [user_id],
        (err, res) => {
          if (err) reject(err)
          else resolve(res.affectedRows)
        }
      )
    })
  }
}

When running connection.query() we use the diamond operator <> to specify the type for the query result, our interface. Using the new Promise((resolve, reject) => {/* ... */}) construct we return the result as Promise and not as callback.

Notice that the readById() method returns a single instance of IUser by using the optional chaining (?.) operator. The methods create() and update() return the user that was just created / updated by reading the res.insertId / user.id value. The method remove() returns the number of affected rows, which should be 1 if everything worked fine.

One benefit of using this technique is that IDEs and editors like VSC suggest the table colums of the query results via IntelliSense now when working with them. On the other hand, your have some overhead because you have to define the TypeScript interfaces according to your database tables. This might become much more complicated when using more complex queries.

An alternative might be to use an ORM like TypeORM for example.