Shulou(Shulou.com)06/01 Report--

Mysql "> Building node Services (2): operating MySQL

In order to store and query complex information, server systems often need database operations. Database is divided into relational database and non-relational database, relational database has MySQL, Oracle, SQL Server and so on, non-relational database has Redis (often used for cache), MongoDB and so on. MySQL is a very popular database at present. This article will introduce how to operate MySQL database in node service.

1. Installation depends on npm install mysql-- save

Or

Yarn add mysql II. Establish a connection

If you want to operate the database, you need to establish a connection with the database, and then operate the database through the connection. MySQL can connect to the database in the following ways:

Mysql.createConnection () establishes a connection per request mysql.createPool () creates connection pool, obtains connection from connection pool mysql.createPoolCluster () to create connection pool cluster, connection pool cluster can provide multiple host connections

The first method is recommended in mysqljs documents: one connection per request, but the frequent establishment and closure of database connections will greatly reduce the performance of the system, so I chose to use connection pooling. If you have higher performance requirements, install MySQL cluster, you can choose to use connection pooling cluster.

1. Database configuration

Add database-related configurations to a common configuration file to facilitate project initialization.

Config.jsmodule.exports = {… / / mysql database configuration mysql: {/ / host host: 'localhost', / / port port: 3306, / / username user:' root', / / password password: '123456database, / / database name database:' server-demo' / / maximum number of connections allowed to be created by connection pool The default value is 10 connectionLimit: 50, / / the maximum number of connections allowed to be suspended. The default value is 0, which means there is no limit to the number of pending connections queueLimit: 0}}.

ConnectionLimit and queueLimit are configuration items specific to data connection pooling.

ConnectionLimit is the maximum number of connections allowed to be created by the connection pool, and the default value is 10. When a connection is obtained, an idle connection is returned directly if there is an idle connection in the connection pool. If all connections are occupied, it is determined whether the number of connections in the connection pool has reached the maximum number allowed, if not, a new connection is created, and if it has been reached, the request to obtain the connection is suspended, waiting for the connection released after other requests have completed the operation. QueueLimit refers to the maximum number of connections allowed to be suspended. The default value is 0, which means there is no limit to the number of pending connections. When all connections allowed to be created in the connection pool are occupied, the request to obtain the connection is suspended, waiting for the available connection, all pending requests form a queue, and queueLimit refers to the maximum length of the queue. It is important to note that when queueLimit is 0, it does not mean that suspensions are not allowed, but that there is no limit to the number of suspensions. two。 Create connection pool db/pool.js/** * database connection pool * / const mysql = require ('mysql'); const config = require ('.. / config'); / / create database connection pool const pool = mysql.createPool (config.mysql); pool.on ('acquire', function (connection) {console.log (`get database connection [${connection.threadId}] `);}) Pool.on ('connection', function (connection) {console.log (`create database connection [${connection.threadId}] `);}); pool.on (' enqueue', function () {console.log ('waiting for available database connection'); pool.on ('release', function (connection) {console.log (`database connection [${connection.threadId}] has been released`);}); module.exports = pool

After you create the database connection pool pool, you can obtain the database connection through pool, and you can learn about the connection usage in the connection pool by listening for the events of the connection pool.

If connectionLimit is set to 2queueLimit and set to 0, when there are five requests to obtain database connections at the same time, the event log of the thread pool is as follows:

Waiting for available database connection to create database connection [1011] to obtain database connection [1011] database connection [1011] released to obtain database connection [1011] create database connection [1012] obtain database connection [1012] database connection [1011] released to obtain database connection Then [1011] database connection [1012] released to obtain database connection [1012] database connection [1011] released database connection [1012] released

Since the maximum number of connections allowed in the thread pool is 2, 2 of the 5 requests will be able to get a connection, and the other 3 requests will suspend waiting for available connections. Because of the high cost of creating a database connection, the thread pool is lazy when creating a connection, that is, it is not created until then. The request to get the connection first releases the connection after completing the operation, puts it back to the connection pool, and then the pending request removes the idle connection from the thread pool for operation.

Third, perform the operation

Since the interfaces of mysql module are callback mode, the interfaces are simply encapsulated as Promise for convenience. The relevant methods are encapsulated as follows:

Const pool = require ('. / pool'); / / get connection function getConnection () {return new Promise ((resolve, reject) = > {pool.getConnection ((err, connection) = > {if (err) {console.error ('failed to get database connection!' , err) reject (err);} else {resolve (connection);}});});} / / start database transaction function beginTransaction (connection) {return new Promise ((resolve, reject) = > {connection.beginTransaction (err = > {if (err) {reject (err)) } else {resolve ();}});} / / submit database operation function commit (connection) {return new Promise ((resolve, reject) = > {connection.commit (err = > {if (err) {reject (err);} else {resolve ()) }});})} / rollback database operation function rollback (connection) {return new Promise ((resolve, reject) = > {connection.rollback (err = > {if (err) {reject (err);} else {resolve ();}});})} 1. Perform normal operations

For normal operations that do not need to use transactions, after obtaining the database connection connection, use connection to perform database operations, and release the connection to the connection pool after completion.

Db/execute.js/** * perform database operations [for queries that do not require transactions and operations for adding, deleting and modifying individual entries] * example: * let func = async function (conn, projectId, memberId) {...}; * await execute (func, projectId, memberId) * @ param func specific database operation asynchronous method (the first parameter must be the database connection object connection) * @ param params func method parameter (excluding the first parameter connection) * @ returns {Promise.} the return value after the execution of the func method * / 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. Perform transaction operations

For many businesses, transaction operations need to be performed, for example: bank transfer, An account transfers 100 yuan to B account, this business operation needs to perform two steps, subtract 100 yuan from An account, and then add 100 yuan to B account. The two sub-operations must be executed successfully to complete the complete business operation. If any sub-operation fails, the previous operation needs to be undone and rolled back.

For operations that need to use transactions, after getting the database connection connection, you first need to call connection.beginTransaction () to start the transaction, then use connection to perform multi-step operations, and then execute connection.commit () to commit, then execute a transaction operation. If an exception occurs during execution, connection.rollback () is executed to roll back the operation.

Db/execute.js/** * perform database transaction operations [suitable for adding, deleting or modifying multiple operations. If an exception occurs in the intermediate data operation, all previous database operations will be rolled back] * example: * let func = async function (conn) {...}; * await executeTransaction (func) * @ param func specific database operation asynchronous method (the first parameter must be the database connection object connection) * @ returns {Promise.} return value after func method execution * / 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 ('transaction execution failed, operation rollback'); await rollback (connection); throw err;} finally {connection & & connection.release & & connection.release ();} 4. Add, delete, modify and check

Addition, deletion, modification and query is the basic atomic operation to deal with data, and these operations are simply encapsulated according to the characteristics of the operation.

Db/curd.js/** * query operation * @ param connection connection * @ param sql SQL statement * @ param val SQL parameters * @ returns {Promise} data array queried by resolve * / function query (connection, sql, val) {/ / console.info ('sql executes query operation:\ n operations, sql,'\ n operations, val) Return new Promise ((resolve, reject) = > {connection.query (sql, val, (err, rows) = > {if (err) {console.error ('sql failed!' , sql,'\ nneighbor, val); reject (err);} else {let results = JSON.parse (JSON.stringify (rows)); resolve (results);}});}) } / * query single data operation * @ param connection connection * @ param sql SQL statement * @ param val SQL Parameter * @ returns {Promise} data object * / 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))}) } / * add data operation * @ param connection connection * @ param sql SQL statement * @ param val SQL parameter * @ param {boolean} skipId skip automatically add ID, false: automatically add id,true: do not add id * @ returns {Promise} resolve automatically generated 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 executes insert operation:\ noperations, sql,'\ noperations, val) Connection.query (sql, val, (err, results) = > {if (err) {console.error ('sql execution failed!' , sql,'\ nneighbor, val); reject (err);} else {resolve (id);}});}) } / * update operation * @ param connection connection * @ param sql SQL statement * @ param val SQL parameter * @ returns {Promise} number of rows of resolve update data * / function update (connection, sql, val) {/ / console.info ('sql performs update operation:\ n operations, sql,'\ n operations, val) Return new Promise ((resolve, reject) = > {connection.query (sql, val, (err, results) = > {if (err) {console.error ('sql failed!' , sql,'\ nneighbor, val); reject (err);} else {resolve (results.affectedRows);}});}) } / * delete operation * @ param connection connection * @ param sql SQL statement * @ param val SQL parameter * @ returns {Promise} number of rows deleted by resolve * / function del (connection, sql, val) {/ / console.info ('sql executes delete operation:\ n operations, sql,'\ n operations, val) Return new Promise ((resolve, reject) = > {connection.query (sql, val, (err, results) = > {if (err) {console.error ('sql failed!' , sql,'\ nneighbor, val); reject (err);} else {/ / console.log ('delete result', results); resolve (results.affectedRows);}});});} 5. Code layering

Code layering can reduce the coupling degree of the code and improve reusability and maintainability. Here, the code is divided into three layers: Dao layer, Service layer and Controller layer.

DAO layer: mainly responsible for data persistence; Service layer: mainly responsible for the logical design of business modules, the business implementation of this layer, you can call the DAO layer interface; Controller layer: responsible for the specific business module process control, in this layer you can call the Service layer interface. 1.DAO layer dao/userDao.jsconst {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 layer service/userService.js

Simply call a DAO layer method: 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

For more complex business logic, you can use anonymous functions:

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;});}

If you want to perform a transaction operation, you need to use the executeTransaction method:

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 | | []; / / newly added role array const addList = []; / / removed role array 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 layer controler/userController.jsconst UserService = require ('. / service/userService') Class UserControler {static async getUserById (ctx) {/ / user ID const id = ctx.params.id; / / whether it contains user role information. If withRoles is "1", it is necessary to include role information 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: 'user does not exist!'} … } module.exports = UserControler

This example is based on the Koa framework, and routes need to be added after the implementation of the controller layer is completed:

Const router = new KoaRouter (); const UserController = require ('. / controler/userControler'); / / get the user router.get ('/ users/:id', UserController.getUserById) of the specified ID; / / get all user router.get ('/ users', UserControler.getUsers)

How to use the Koa framework is not described here. After the route is added, start the service, and then use these APIs. If the port started by the local service is 3000, the API request address is as follows:

Http://localhost:3000/users/3571a123-0454-49b4-a2bc-8b30a37f0b14http://localhost:3000/users/3571a123-0454-49b4-a2bc-8b30a37f0b14?withRoles=1http://localhost:3000/users/ VI. Description

This article introduces the basic use of the mysql module, simply encapsulates it, and provides examples. In addition to using the mysql module to manipulate the database, you can also use the mysql2 module. The basic usage of mysql2 is the same as that of mysql, and mysql2 also supports Promise, which is more convenient to use. The code related to this article has been submitted to GitHub for reference, project address: https://github.com/liulinsp/node-server-typeorm-demo.

Author: Liu Lin

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.