This is the 9th day of my participation in Gwen Challenge

The MySQL JS library is used to connect to the database.

The JS code shared in this article is not intended for use in a build environment.

In a production environment, back-end services manage database connections through a database connection pool.

When the interface needs to fetch data from the database, it takes an active connection from the pool, releases the connection and puts it back into the pool after the database has fetched data/performed data change operations.

  

As shown above, when a business thread processes data, a connection is used; On the right, the service is finished, and the connection is released and put back into the connection pool.

The JS code for a connection pool is shared here

const mysql = require("mysql");

const port = 3306

// Create a database connection pool to connect to the specified IP address
const pool = mysql.createPool({
    host: 'localhost'.port: port,
    user: 'root'.password: '12345678'.database: 'demo20210330'.multipleStatements: true
});

// Define SQL execution functions
// And allows the user to enter the connection callback onSuccess and the error callback onError
const runSql = function(sql, onSuccess, onError) {
    pool.getConnection(function(err, conn) {
        if (err) {
            onError(err);
            return;
        }
        conn.query(sql, function(err, data) {
            try {
                if (err) {
                    onError(err);
                } else{ onSuccess(data); }}catch(err) {
                onError(err);
            } finally {
                console.log('release connection'); conn.release(); }}); }); };// Check the pool status
const showPool = function() {
    console.log('pool: ', pool);
};

module.exports.show = showPool;
module.exports.runSql = runSql;
Copy the code

The tool’s JS library runSql function releases the link in finally every time the SQL is executed, regardless of the result.

PS: The database password is too simple. You are advised to change the password to a more complex one.

Save the code as dbpool.js and go to the Node terminal. Then enter the following code and view the result:

var pool = require(‘./dbpool’)

pool.runSql(“select * from company;” , (e)=>console.log(e)

PS: The following database was created in advance in the previous article

Type pool.show() to view connection pool status, such as connection information, idle database connections, and so on.

   

Note: Do not manually call destroy for connection pool connections.

The role of connection pooling

Because of the destruction of the connection, it is expensive to re-establish the connection (TCP connection) when the business needs to call the database to retrieve data.

In contrast, maintaining a long connection saves a lot of time setting up reconnected TCP.

Buffering is implemented, and all database connections are managed uniformly in the cache pool.

The MySQL JS library provides a common connection pooling project: github.com/coopernurse…

A separate analysis will be posted later.