Express.js Connects to MySQL to Implement Book App

Express.js 串接 MySQL 實作書本管理 App

@ Express.js

前言

先前系列提到使用 Express.js 簡單的創造一個 CRUD App 並用 MVC 分層，這次流程差不多，目標是把後端接上本地的 MySQL 伺服器並使用 MySQL2🔗，一個專為 Node.js 應用設計的 MySQL 資料庫連接工具。

設置本地 MySQL

到 MySQL 官網🔗 進行安裝，建議安裝 MySQL workbench 對新手比較友好(GUI 方便操作)，輸入以下 SQL 創建一個 books 資料庫並在裡面添加 books 書本相關表格：

CREATE DATABASE `books`;


CREATE TABLE `books`, `books` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(45) NOT NULL,
  `desc` VARCHAR(255) NOT NULL,
  `cover` VARCHAR(45) NULL,
  PRIMARY KEY ( id )
);

簡單的查詢和新增：

SELECT * FROM books.books;


INSERT INTO `books`.`books` (`id`, `title`, `desc`, `cover`) VALUES ('1', 'title content', 'desc content', 'cover content');

設置 Express

在現有的 Express App 中使用 MySQL2 createConnection🔗 連接本地 MySQL。

app.js
import express from 'express';
import mysql from 'mysql2';
import cors from 'cors';
import 'dotenv/config';


const app = express();
app.use(cors());
app.use(express.json());


const db = mysql
  .createConnection({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE,
  })
  .promise();


app.listen(process.env.SERVER_PORT, () => {
  console.log('Connected to backend.');
});

建構 API

簡單查詢 Simple Queries

寫個簡單的「撈取所有書本資料」 API，connection.query 是 Simple Queries🔗，簡單來說：

  • 動態構建查詢： 查詢語句在運行時拼接，依賴輸入值構建最終的 SQL 字符串。
  • 易受 SQL Injection 攻擊： 如果用戶輸入未經過適當的處理（如 userInput 包含惡意 SQL），可能導致 SQL 注入攻擊。
  • 無法重複使用查詢計劃： 每次執行查詢時，必須重新解析和最佳化查詢。
app.get('/api/books', async (req, res) => {
  try {
    const sql = 'SELECT * FROM books';
    const [result] = await connection.query(sql);
    res.status(200).json(result);
  } catch (err) {
    console.error(err);
    return res.status(500).json({ error: 'Failed to fetch books' });
  }
});

預處理 Prepared Statements

換作是需要參數的 SQL 最好使用 Prepared Statements🔗，預編譯的查詢模板，使用佔位符來代替動態值。數據庫會對查詢模板進行編譯，然後執行時插入具體值。

  • 預編譯查詢模板： 查詢的結構（如字段名和表名）在第一步就被數據庫解析，後續執行時只需插入值，不需要重新解析 SQL 語法。
  • 防止 SQL Injection： 用戶輸入的值不直接拼接到查詢中，而是作為參數傳遞給數據庫，數據庫將它們當作純數據處理，無法改變查詢邏輯。
  • 提升性能（在重複執行場景）： 由於查詢計劃被預編譯，可以多次重用，不需要每次執行都進行解析和最佳化。
app.post('/api/books', async (req, res) => {
  try {
    const sql = 'INSERT INTO books(`title`, `desc`, `cover`) VALUES (?, ?, ?)';
    const values = [req.body.title, req.body.desc, req.body.cover];
    const [result] = await connection.execute(sql, values);
    res.status(201).json({ message: 'Book created successfully', bookId: result.insertId });
  } catch (err) {
    console.error(err);
    return res.status(500).json({ error: 'Failed to create book' });
  }
});

總結

本篇教學主要受 React Node.js MySQL CRUD Tutorial for Beginners - Lama Dev🔗 的影片教學啟發，不過採用較新的套件與翻了一下文件了解細節 API 差異，並只關注如何與 MySQL 互動的部分。

剩下刪除與更新書籍的動作其實也只是重複先前的查詢，替換 SQL 即差不多，具體代碼倉庫可以參考 mysql-book🔗。

app.delete('/api/books/:id', async (req, res) => {
  try {
    const sql = 'DELETE FROM books WHERE id = ?';
    const bookId = req.params.id;
    const [result] = await connection.execute(sql, [bookId]);
    if (result.affectedRows === 0) {
      return res.status(404).json({ error: 'Book not found' });
    }
    res.status(200).json({ message: 'Book deleted successfully' });
  } catch (err) {
    console.error(err);
    return res.status(500).json({ error: 'Failed to delete book' });
  }
});


app.put('/api/books/:id', async (req, res) => {
  try {
    const bookId = req.params.id;
    const sql = 'UPDATE books SET `title`= ?, `desc`= ?, `cover`= ? WHERE id = ?';
    const values = [req.body.title, req.body.desc, req.body.cover];
    const [result] = await connection.execute(sql, [...values, bookId]);
    if (result.affectedRows === 0) {
      return res.status(404).json({ error: 'Book not found' });
    }
    res.status(200).json({ message: 'Book updated successfully' });
  } catch (err) {
    console.error(err);
    return res.status(500).json({ error: 'Failed to update book' });
  }
});

延伸閱讀