使用Node.js撰寫Express API，連結以Docker啟動的MySQL資料庫

說明如何用Docker啟動MySQL資料庫，並用Node.js搭配Express撰寫存取資料庫的API。

原始碼請參考Github：連結

使用Docker啟動MySQL：

docker run --name mysql \
-p 3306:3306 \
-v $(pwd)/datadir:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=adminpw \
-d \
mysql:8.0.19

使用的是8.0.19版的MySQL，要注意的是指定了當前目錄的datadir資料夾作為資料庫的volume，保存資料庫相關檔案。

運行中的mysql container

接著要

1. 用root的身分登入MySQL
2. create一個database，存放API會使用的資料
3. create一個user，準備給node.js連線使用
4. grant這個database的權限給該user

docker exec -it mysql bash
mysql -u root -p
# Enter password: adminpw
create database `mydb`;
create user 'appuser' identified with mysql_native_password by 'password';
grant all privileges on mydb.* to 'appuser';
flush privileges;

注意在create user的時候，要指定identified with mysql_native_password，否則node會無法連線到mysql，會出現以下Error：

ER_NOT_SUPPORTED_AUTH_MODE

然後，先登出root，重新以user的身分登入資料庫，並建立一張table。

exit
mysql -u appuser -p
# Enter password: password
use mydb;
create table products (
  id int not null AUTO_INCREMENT,
  name varchar(50) not null,
  remark varchar(200),
  price int not null,
  primary key(id)
);

至此，已準備好登入帳號、資料庫及資料表，可以來寫點js了。

先建立好要存放程式碼的資料夾，並安裝會使用到的套件，主要是mysql這個套件。

mkdir app && cd app
npm init -y
npm install --save express
npm install --save body-parser
npm install --save mysql

建立connection：

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'appuser',
  password: 'password',
  database: 'mydb'
});

使用return promise的方式進行資料庫的query操作：

function query(sql, args) {
  return new Promise((resolve, reject) => {
    connection.query(sql, args, function (err, result, fields) {
      if (err) {
        reject(err);
      } else {
        resolve(result);
      }
    });
  });
}

讀取一筆資料，GET /product/:id：

app.get('/product/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const result = await db.query(`SELECT * FROM products WHERE id = ?`, id);
    res.send(result);
  } catch(err) {
    res.status(500).send(err.message);
  }
});

注意到在query的時候，使用了?作為參數的替代，這是為了防止SQL Injection。更多資訊可以參考這裡。

讀取所有資料，GET /products：

app.get('/products', async (req, res) => {
  try {
    const result = await db.query('SELECT * FROM products');
    res.send(result);
  } catch(err) {
    res.status(500).send(err.message);
}

建立一筆資料，POST /product：

app.post('/product', async (req, res) => {
  const { name, remark, price } = req.body;
  try {
    const result = await db.query('INSERT INTO products(name, remark, price) VALUES(?, ?, ?)', [name, remark, price]);
    const { insertId } = result;
    res.send(insertId);
  } catch(err) {
    res.status(500).send(err.message);
  }
});

注意到insert的return是insertId，有助於api的使用者掌握insert進資料庫的自動生成的primary key為何。

更新一筆資料，PUT /product/:id：

app.put('/product/:id', async (req, res) => {
  const { id } = req.params;
  const { name, remark, price } = req.body;
  try {
    const result = await db.query('UPDATE products SET name = ?, remark = ?, price = ? WHERE id = ?', [name, remark, price, id]);
    res.send(result);
  } catch (err) {
    res.status(500).send(err.message);
  }
});

刪除一筆資料，DELETE /product/:id：

app.delete('/product/:id', async (req, res) => {
  const { id } = req.params;
  try {
    const result = await db.query('DELETE FROM products WHERE id = ?', id);
    res.send(result);
  } catch (err) {
    res.status(500).send(err.message);
  }
});