Example analysis of Express connecting to MySQL and database connection pool

Express connection to MySQL

Preparation work

Open webstorm new project and select express to create an express project.

After the creation is successful, the page will be as follows:

In order to connect to the mysql database, you need to import the mysql module.

Create configuration file

Create a config file in the project, create a congfigdb.js file in the config file to connect to the database, and write in the file ：

var mysql = {
    host: "127.0.0.1",//这是数据库的地址
    user: "root",//需要用户的名字
    password: "root23",//用户密码 ，如果你没有密码，直接双引号就是
    database: "info",//数据库名字
    port:3306//数据库使用的端口号
}
module.exports = mysql;//用module.exports暴露出这个接口

Create the interface file for operating the database

const express = require('express');
const router = express.Router();
//导入MySQL 模块
const mysql = require('mysql');
//导入配置文件
const db = require('../config/configdb');

router.get('test',(req,res)=>{
    //创建数据库连接对象
    let conn = mysql.createConnection(db);
    //查询bookinfo中所有数据
    conn.query('select * from student',(err,results,fieldes)=>{
        //fieldes表示具体的字段
        if(err){
            throw err;
        }
        res.send(results);
    })
    //关闭数据库链接
    conn.end((err)=>{
        console.log(err);
})
module.exports = router;

After completion, add in the app.js file:

var dbRouter = require('./routes/option')
app.use('/db',dbRouter);

Test in Postman as follows:

Database connection pool technology

What is a database connection pool

The database connection pool establishes a sufficient number of database connection objects when the program starts, and connects these connection objects A pool is formed, and the program dynamically applies for, uses, and releases connection objects in the pool.

What is the role of database connection pool?

The database connection pool is responsible for allocating, managing and releasing database connection objects. It allows an application to reuse an existing database connection object. rather than recreating one.

Database connection pool technology example

1. Import the mysql module

var mysql = require('mysql');

2. Create a database connection pool

Create a connection pool

var pool = mysql.createPool(options);

options parameter is an object that contains many attribute configurations. The object is used to specify various options that are used uniformly by connections in the connection pool.

//创建数据库连接池
const pool = mysql.createPool({
    connectionLimit:20,
    host:'localhost',
    port:3306,
    user:'root',
    password:'123456',
    database:'info'
})
//导出数据库连接池对象
module.exports = pool;

connectionLimit: used to specify the maximum number of links in the connection pool, the default attribute value is 10.
queueLimit: used to specify the allowed The maximum number of pending connections. If the number of pending connections exceeds this value, an error will be thrown immediately. The default attribute value is 0. This represents the maximum number of pending connections that are not allowed.
multipleStatements: Whether to allow the execution of multiple sql statements, the default value is false
host: The address of the database server
user: Connection Database username
password: Password to connect to the database
database: Database name

3. Get the database link object

pool.getConnection((err, conn) => {
    if (err) {
        console.log(err)
    } else {
        let sql = 'select * from bookinfo';
        conn.query(sql, (err, results) => {
            if (err) {
                console.log(err)
            } else {
                res.send(results);
                conn.release();
            }
        })
    }
})

4. Release the database connection object

conn.release();

Complete instance

const express = require('express');
const pool = require('../config/dbmysql');

const router = express.Router();
/*
* http://localhost:3000/dbmysql/books
* */
router.get('/books',(req,res)=>{
    //从数据库连接池中获取数据库连接对象
    pool.getConnection((err,conn)=>{
        if(err){
            console.log(err)
        }else {
            let sql = 'select * from bookinfo';
            conn.query(sql,(err,results)=>{
                if (err) {
                    console.log(err)
                }else {
                    res.send(results);
                    conn.release();
                }
            })
        }
    })
})
module.exports = router;

Result test:

The above is the detailed content of Example analysis of Express connecting to MySQL and database connection pool. For more information, please follow other related articles on the PHP Chinese website!