Using MySQL in Node.js with TypeScript
Tags: programming, webdev, tutorial, typescript
Published: 2022-07-07 18:27:27

---
title: Using MySQL in Node.js with TypeScript
published: true
description: Improve your programming experience in Node.js by using typed SQL queries.
tags: programming, webdev, tutorial, typescript
# cover_image: https://direct_url_to_image.jpg
# Use a ratio of 100:42 for best results.
---

This post was originally published on my [blog](https://larswaechter.dev/blog/nodejs-ts-mysql/).

---

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](https://www.npmjs.com/package/mysql2) npm package to run SQL queries in Node.js.

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

```sql
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](https://www.typescriptlang.org/docs/handbook/interfaces.html) and set the properties according to the table columns:

```typescript
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:

```typescript
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](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Promise) and not as [callback](https://developer.mozilla.org/en-US/docs/Glossary/Callback_function).

Notice that the `readById()` method returns a single instance of `IUser` by using the [optional chaining](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/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](https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping) like [TypeORM](https://typeorm.io/) for example.