搭建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: &39;, // 埠 port: 3306, // 用戶名 user: &39;, // 密碼 password: &39;, // 資料庫名 database: &39;, // 連接池允許創建的最大連接數，默認值為10 connectionLimit: 50, // 允許掛起的最大連接數,默認值為0,代表掛起的連接數無限制 queueLimit: 0 }};

connectionLimit 和 queueLimit 是數據連接池特有的配置項。

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

2.創建連接池

• db/pool.js

/** * 資料庫連接池 */const mysql = require(&39;);const config = require(&39;);// 創建資料庫連接池const pool = mysql.createPool(config.mysql);pool.on(&39;, function (connection) { console.log(`獲取資料庫連接 [${connection.threadId}]`);});pool.on(&39;, function (connection) { console.log(`創建資料庫連接 [${connection.threadId}]`);});pool.on(&39;, function () { console.log(&39;);});pool.on(&39;, 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(&39;);// 獲取連接function getConnection () { return new Promise((resolve, reject) => { pool.getConnection((err, connection) => { if (err) { console.error(&39;, 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); let result = null; try { result = await func(connection); await commit(connection); return result } catch (err) { console.error(&39;); await rollback(connection); throw err; } finally { connection && connection.release && connection.release(); }}

四、增刪改查

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

• db/curd.js

/** * 查詢操作 * @param connection 連接 * @param sql SQL語句 * @param val SQL參數 * @returns {Promise} resolve查詢到的數據數組 */function query (connection, sql, val) { // console.info(&39;, sql, &39;, val); return new Promise((resolve, reject) => { connection.query(sql, val, (err, rows) => { if (err) { console.error(&39;, sql, &39;, 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(&39;, sql, &39;, val); connection.query(sql, val, (err, results) => { if (err) { console.error(&39;, sql, &39;, val); reject(err); } else { resolve(id); } }); });}/** * 更新操作 * @param connection 連接 * @param sql SQL語句 * @param val SQL參數 * @returns {Promise} resolve 更新數據的行數 */function update (connection, sql, val) { // console.info(&39;, sql, &39;, val); return new Promise((resolve, reject) => { connection.query(sql, val, (err, results) => { if (err) { console.error(&39;, sql, &39;, 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(&39;, sql, &39;, val); return new Promise((resolve, reject) => { connection.query(sql, val, (err, results) => { if (err) { console.error(&39;, sql, &39;, val); reject(err); } else { // console.log(&39;, 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(&39;);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(&39;);const UserDao = require(&39;);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(&39;);class UserControler { static async getUserById (ctx) { // 用戶ID const id = ctx.params.id; // 是否包含用戶角色信息，如果withRoles 為 &34; 表示需要包含角色信息 const withRoles = ctx.query.withRoles; let user; if (withRoles === &39;) { user = await UserService.findUserWithRoles(id); } else { user = await UserService.findUserById(id); } if (user) { ctx.body = user; } else { ctx.body = { code: 1004, msg: &39; } } } …}module.exports = UserControler;

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

const router = new KoaRouter();const UserController = require(&39;);// 獲取指定ID的用戶router.get(&39;, UserController.getUserById);// 獲取所有用戶router.get(&39;, 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)。

拓展閱讀：