TypeScript with MySQL

May 4, 2022

I replicate Go with Database to TypeScript. It is the same REST API with the same responses. I only use two node modules here; Express and MySQL.

I think I don't need to explain SQL and MySQL anymore. You can read my previous articles. You can use the same Postman Collection and SQL file. You can read Simple REST API with TypeScript to set up.

index.ts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 import express, { Express, Request, Response } from 'express';
import mysql, { Connection, MysqlError } from 'mysql';

const app: Express = express();
const port: number = 8000;
const connection: Connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : 'root',
  database : 'go_database'
});

app.use(express.json())

app.listen(port, () => {
  console.log(`Example app listening on port ${port}`);
})

We imported MySQL and established the connection. By default, MySQL runs on port 3306, so we don't need to define any port.

Index

1
2
3
4
5
6
7
8
9
10
11
12
13 app.get('/', (req: Request, res: Response) => {
  connection.query("SELECT * FROM articles", (err: MysqlError | null, result: any) => {
    if (err) {
      console.error(err)

      res.status(500).end()

      return
    }

    res.status(200).json(result).end()
  });
})

Create

1
2
3
4
5
6
7
8
9
10
11
12
13
14 app.post('/', (req: Request, res: Response) => {
  connection.query("INSERT INTO articles SET ?", req.body, (err: MysqlError | null, result: any) => {
    if (err) {
      console.error(err)

      res.status(500).end()

      return
    }

    req.body.id = result.insertId
    res.status(200).json(req.body).end()
  });
})

Read

1
2
3
4
5
6
7
8
9
10
11
12
13 app.get('/:id', (req: Request, res: Response) => {
  connection.query("SELECT * FROM articles WHERE id = ?", [req.params.id], (err: MysqlError | null, result: any) => {
    if (err) {
      console.error(err)

      res.status(500).end()

      return
    }

    res.status(200).json(result).end()
  });
})

Update

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 app.put('/:id', (req: Request, res: Response) => {
  req.body.id = Number(req.params.id)
  connection.query(
    "UPDATE articles SET title = ?, content = ? WHERE id = ?",
    [req.body.title, req.body.content, req.body.id],
    (err: MysqlError | null) => {
    if (err) {
      console.error(err)

      res.status(500).end()

      return
    }

    res.status(200).json(req.body).end()
  });
})

Delete

1
2
3
4
5
6
7
8
9
10
11
12
13 app.delete('/:id', (req: Request, res: Response) => {
  connection.query("DELETE FROM articles WHERE id = ?", [req.params.id], (err: MysqlError | null) => {
    if (err) {
      console.error(err)

      res.status(500).end()

      return
    }

    res.status(200).end()
  });
})

Closing

We made the connection and then ran the SQL query. We returned 500 Internal Server Error for an error. We don't need to make a connection (eg; connection.connect()) every time we run a query() because it is already invoked on the query().

You can get the source code at https://github.com/aristorinjuang/ts-with-mysql. I also provided the updated Postman collection.