## 搭建node服務（二）：操作MySQL

> 為了進行復雜資訊的儲存和查詢，服務端系統往往需要資料庫操作。資料庫分為關係型資料庫和非關係型資料庫，關係型資料庫有MySQL、Oracle、SQL Server等，非關係型資料庫有Redis（常用來做快取）、MongoDB等。MySQL是目前很流行的資料庫，本文將要介紹如何在node服務中進行MySQL資料庫操作。

## 一、	安裝依賴
```
npm install mysql --save
```

或者

```
yarn add mysql
```

## 二、建立連線
要想進行資料庫操作就需要和資料庫建立連線，然後透過連線進行資料庫的操作。MySQL的資料庫連線方式有以下幾種：

- mysql.createConnection() 每次請求建立一個連線
- mysql.createPool() 建立連線池，從連線池中獲取連線
- mysql.createPoolCluster() 建立連線池叢集，連線池叢集可以提供多個主機連線

mysqljs文件中推薦使用第一種方式：每次請求建立一個連線，但是由於頻繁的建立、關閉資料庫連線，會極大的降低系統的效能，所以我選擇了使用連線池的方式，如果對效能有更高的要求，安裝了MySQL 叢集，可以選擇使用連線池叢集。

### 1.	資料庫配置
將資料庫相關的配置新增到公用的配置檔案中，方便專案的初始化。

- config.js

```
module.exports = {
    …
    // mysql資料庫配置
    mysql: {
        // 主機
        host: 'localhost',
        // 埠
        port: 3306,
        // 使用者名稱
        user: 'root',
        // 密碼
        password: '123456',
        // 資料庫名
        database: 'server-demo',
        // 連線池允許建立的最大連線數，預設值為10
        connectionLimit: 50,
        // 允許掛起的最大連線數,預設值為0,代表掛起的連線數無限制
        queueLimit: 0
    }
};

```
**connectionLimit 和 queueLimit 是資料連線池特有的配置項。**

- connectionLimit 是指連線池允許建立的最大連線數，預設值為10。當獲取連線時，如果連線池中有空閒的連線則直接返回一個空閒連線。如果所有連線都被佔用，則判斷連線池中的連線數是否達到了允許的最大數，如果未達到則建立新的連線，如果已達到則獲取連線的請求掛起，等待其他請求完成操作後釋放的連線。
- queueLimit 是指允許掛起的最大連線數，預設值為0，代表掛起的連線數無限制。當連線池中允許建立的所有連線都被佔用時，獲取連線的請求掛起，等待可用的連線，所有掛起的請求形成一個佇列，queueLimit則是指這個佇列的最大長度。需要注意的是，當queueLimit為0時並不表示不允許掛起，而是表示對掛起的數目沒有限制。

### 2.	建立連線池
- db/pool.js

```
/**
 * 資料庫連線池
 */
const mysql = require('mysql');
const config = require('../config');

// 建立資料庫連線池
const pool = mysql.createPool(config.mysql);

pool.on('acquire', function (connection) {
    console.log(`獲取資料庫連線 [${connection.threadId}]`);
});
pool.on('connection', function (connection) {
    console.log(`建立資料庫連線 [${connection.threadId}]`);
});
pool.on('enqueue', function () {
    console.log('正在等待可用資料庫連線');
});
pool.on('release', function (connection) {
    console.log(`資料庫連線 [${connection.threadId}] 已釋放`);
});

module.exports = pool;
```

建立資料庫連線池pool後，就可以透過pool獲取資料庫連線了，另外透過監聽連線池的事件可以瞭解連線池中連線的使用情況。
如果將connectionLimit 設為2，queueLimit 設為0，當同時有5個請求獲取資料庫連線時，執行緒池的事件日誌如下：

```
正在等待可用資料庫連線
正在等待可用資料庫連線
正在等待可用資料庫連線
建立資料庫連線 [1011]
獲取資料庫連線 [1011]
資料庫連線 [1011] 已釋放
獲取資料庫連線 [1011]
建立資料庫連線 [1012]
獲取資料庫連線 [1012]
資料庫連線 [1011] 已釋放
獲取資料庫連線 [1011]
資料庫連線 [1012] 已釋放
獲取資料庫連線 [1012]
資料庫連線 [1011] 已釋放
資料庫連線 [1012] 已釋放
```

由於執行緒池允許的最大連線數是2，5個請求中會有2個請求能夠得到連線，另外3個請求掛起等待可用連線。由於建立資料庫連線的代價比較大，執行緒池在建立連線時採用懶漢式，也就是，用到時才建立。先得到連線的請求在完成操作後釋放連線，放回到連線池，然後掛起的請求從執行緒池取出空閒的連線進行操作。

## 三、執行操作
由於mysql 模組的介面都為回撥方式的，為了操作方便簡單地將介面封裝為Promise，相關方法封裝如下：

```
const pool = require('./pool');

// 獲取連線
function getConnection () {
    return new Promise((resolve, reject) => {
        pool.getConnection((err, connection) => {
            if (err) {
                console.error('獲取資料庫連線失敗！', err)
                reject(err);
            } else {
                resolve(connection);
            }
        });
    });
}

// 開始資料庫事務
function beginTransaction (connection) {
    return new Promise((resolve, reject) => {
        connection.beginTransaction(err => {
            if (err) {
                reject(err);
            } else {
                resolve();
            }
        });
    });
}

// 提交資料庫操作
function commit (connection) {
    return new Promise((resolve, reject) => {
        connection.commit(err => {
            if (err) {
                reject(err);
            } else {
                resolve();
            }
        });
    })
}

// 回滾資料庫操作
function rollback (connection) {
    return new Promise((resolve, reject) => {
        connection.rollback(err => {
            if (err) {
                reject(err);
            } else {
                resolve();
            }
        });
    })
}
```

### 1.	執行普通操作
對於不需要使用事務的普通操作，獲取資料庫連線connection後，使用connection進行資料庫操作，完成後釋放連線到連線池，則執行完成一次操作。

- db/execute.js

```

/**
 * 執行資料庫操作【適用於不需要事務的查詢以及單條的增、刪、改操作】
 * 示例：
 * let func = async function(conn, projectId, memberId) { ... };
 * await execute( func, projectId, memberId);
 * @param func 具體的資料庫操作非同步方法（第一個引數必須為資料庫連線物件connection）
 * @param params func方法的引數（不包含第一個引數 connection）
 * @returns {Promise.} func方法執行後的返回值
 */
async function execute (func, ...params) {
    let connection = null;
    try {
        connection = await getConnection()
        let result = await func(connection, ...params);
        return result
    } finally {
        connection && connection.release && connection.release();
    }
}
```

### 2.	執行事務操作

對於很多業務都需要執行事務操作，例如：銀行轉賬，A賬戶轉賬給B賬戶 100元，這個業務操作需要執行兩步，從A賬戶減去100元，然後給B賬戶增加100元。兩個子操作必須全部執行成功才能完成完整的業務操作，如果任意子操作執行失敗就需要撤銷之前的操作，進行回滾。

對於需要使用事務的操作，獲取資料庫連線connection後，首先需要呼叫connection.beginTransaction() 開始事務，然後使用connection進行多步操作，完成後執行connection.commit() 進行提交，則執行完成一次事務操作。如果在執行過程中出現了異常，則執行connection.rollback() 進行回滾操作。

- db/execute.js

```
/**
 * 執行資料庫事務操作【適用於增、刪、改多個操作的執行，如果中間資料操作出現異常則之前的資料庫操作全部回滾】
 * 示例：
 * let func = async function(conn) { ... };
 * await executeTransaction(func);
 * @param func 具體的資料庫操作非同步方法（第一個引數必須為資料庫連線物件connection）
 * @returns {Promise.} func方法執行後的返回值
 */
async function executeTransaction(func) {
    const connection = await getConnection();
    await beginTransaction(connection);

## 四、增刪改查

增刪改查是處理資料的基本原子操作，將這些操作根據操作的特點進行簡單的封裝。

- db/curd.js

```
/**
 * 查詢操作
 * @param connection 連線
 * @param sql SQL語句
 * @param val SQL引數
 * @returns {Promise} resolve查詢到的資料陣列
 */
function query (connection, sql, val) {
    // console.info('sql執行query操作：\n', sql, '\n', val);
    return new Promise((resolve, reject) => {
        connection.query(sql, val, (err, rows) => {
            if (err) {
                console.error('sql執行失敗！', sql, '\n', val);
                reject(err);
            } else {
                let results = JSON.parse(JSON.stringify(rows));
                resolve(results);
            }
        });
    });
}

/**
 * 查詢單條資料操作
 * @param connection 連線
 * @param sql SQL語句
 * @param val SQL引數
 * @returns {Promise} resolve查詢到的資料物件
 */
function queryOne (connection, sql, val) {
    return new Promise((resolve, reject) => {
        query(connection, sql, val).then(
            results => {
                let result = results.length > 0 ? results[0] : null;
                resolve(result);
            },
            err => reject(err)
        )
    });
}

/**
 * 新增資料操作
 * @param connection 連線
 * @param sql SQL語句
 * @param val SQL引數
 * @param {boolean} skipId 跳過自動新增ID, false: 自動新增id，true: 不新增id
 * @returns {Promise} resolve 自動生成的id
 */
function insert (connection, sql, val, skipId) {
    let id = val.id;
    if (!id && !skipId) {
        id = uuid();
        val = {id, ...val};
    }
    return new Promise((resolve, reject) => {
        // console.info('sql執行insert操作：\n', sql, '\n', val);
        connection.query(sql, val, (err, results) => {
            if (err) {
                console.error('sql執行失敗！', sql, '\n', val);
                reject(err);
            } else {
                resolve(id);
            }
        });
    });
}

/**
 * 更新操作
 * @param connection 連線
 * @param sql SQL語句
 * @param val SQL引數
 * @returns {Promise} resolve 更新資料的行數
 */
function update (connection, sql, val) {
    // console.info('sql執行update操作：\n', sql, '\n', val);
    return new Promise((resolve, reject) => {
        connection.query(sql, val, (err, results) => {
            if (err) {
                console.error('sql執行失敗！', sql, '\n', val);
                reject(err);
            } else {
                resolve(results.affectedRows);
            }
        });
    });
}

/**
 * 刪除操作
 * @param connection 連線
 * @param sql SQL語句
 * @param val SQL引數
 * @returns {Promise} resolve 刪除資料的行數
 */
function del (connection, sql, val) {
    // console.info('sql執行delete操作：\n', sql, '\n', val);
    return new Promise((resolve, reject) => {
        connection.query(sql, val, (err, results) => {
            if (err) {
                console.error('sql執行失敗！', sql, '\n', val);
                reject(err);
            } else {
                // console.log('delete result', results);
                resolve(results.affectedRows);
            }
        });
    });
}
```

## 五、程式碼分層

將程式碼分層可以降低程式碼的耦合度，提高可複用性、可維護性，這裡將程式碼分成了3層：**Dao層、Service層和Controller層。**

- **DAO層：**主要負責資料持久化工作；
- **Service層：**主要負責業務模組的邏輯設計，此層的業務實現，可以呼叫DAO層的介面；
- **Controller層：**負責具體的業務模組流程的控制，在此層可以呼叫Service層的介面。

### 1.DAO層

- dao/userDao.js

```
const { query, queryOne, update, insert, del } = require('../db/curd');

class UserDao {
    static async queryUserById (connection, id) {
        const sql = `SELECT user.id, user.account, user.name, user.email, user.phone,
                          user.birthday, user.enable, user.deleteFlag, user.creator,
                          user.createTime, user.updater, user.updateTime
                   FROM sys_user user
                   WHERE user.id = ?`;
        const user = await queryOne(connection, sql, id);
        return user;
    }
    …
}

module.exports = UserDao;
```

### 2.Service層

- service/userService.js
簡單呼叫一個DAO層方法：

```
const { execute, executeTransaction } = require('../db/execute');
const UserDao = require('../dao/userDao');

class UserService {
    static async findUserById (id) {
        return await execute(UserDao.queryUserById, id);
}
…
}

module.exports = UserService;
```

對於複雜些的業務邏輯可以使用匿名函式來實現：

```
static async findUserWithRoles (id) {
    return await execute (async connection => {
        const user = await UserDao.queryUserById(connection, id);
        if (user) {
            user.roles = await RoleDao.queryRolesByUserId(connection, id);
        }
        return user;
    });
}
```

如果要執行事務操作，則需要使用executeTransaction 方法：

```
static async updateUserRoleRelations (userId, roleIds) {
    return await executeTransaction(async connection => {
        const relations = await UserDao.queryUserRoleRelations(connection, userId);
        const oldRoleIds = relations.map(item => item.roleId);
        const newRoleIds = roleIds || [];
        // 新增的角色陣列
        const addList = [];
        // 移除的角色陣列
        const removeList = [];
        newRoleIds.forEach(roleId => {
            if (oldRoleIds.indexOf(roleId) === -1) {
                addList.push(roleId);
            }
        });
        oldRoleIds.forEach(roleId => {
            if (newRoleIds.indexOf(roleId) === -1) {
                removeList.push(roleId);
            }
        });

if (addList.length > 0) {
            await UserDao.insertUserRoleRelations(connection, userId, addList);
        }
        if (removeList.length > 0) {
            await UserDao.deleteUserRoleRelations(connection, userId, removeList);
        }
    });
}
```

### 3.Controller層

- controler/userController.js

```
const UserService = require('../service/userService');

class UserControler {
   static async getUserById (ctx) {
        // 使用者ID
        const id = ctx.params.id;
        // 是否包含使用者角色資訊，如果withRoles 為 "1" 表示需要包含角色資訊
        const withRoles = ctx.query.withRoles;

let user;
        if (withRoles === '1') {
            user = await UserService.findUserWithRoles(id);
        } else {
            user = await UserService.findUserById(id);
        }
        if (user) {
            ctx.body = user;
        } else {
            ctx.body = {
                code: 1004,
                msg: '使用者不存在!'
            }
        }
    }
    …
}

module.exports = UserControler;
```

此示例基於Koa框架，controller 層實現完成後需要新增路由：

```
const router = new KoaRouter();
const UserController = require('./controler/userControler');

// 獲取指定ID的使用者
router.get('/users/:id', UserController.getUserById);

// 獲取所有使用者
router.get('/users', UserControler.getUsers);
```

對於Koa框架如何使用，這裡不再介紹，路由新增完畢後，啟動服務，即可使用這些介面，如果本地服務啟動的埠為3000，介面請求地址如下：

- http://localhost:3000/users/3571a123-0454-49b4-a2bc-8b30a37f0b14
- http://localhost:3000/users/3571a123-0454-49b4-a2bc-8b30a37f0b14?withRoles=1
- http://localhost:3000/users/

## 六、說明
本文介紹了mysql模組的基本使用，對其進行了簡單封裝，並提供了使用示例。除了使用mysql模組來運算元據庫，也可以使用mysql2模組，mysql2的基本用法與mysql一致，另外mysql2還支援Promise，使用起來更方便。本文相關的程式碼已提交到GitHub以供參考，專案地址：[https://github.com/liulinsp/node-server-typeorm-demo](https://github.com/liulinsp/node-server-typeorm-demo)。
> 作者：劉琳

搭建node服務（二）：操作MySQL

相關文章