Use of Nodejs database-Mysql

Hi I’m Shendi

Use of Nodejs database-Mysql
Nodejs column

The database is indispensable in the backend, and Nodejs naturally has libraries corresponding to the database to use. Mysql database can use the mysql library

Install dependencies

Execute the following command to install mysql dependencies

npm install mysql

Depend on the corresponding github address: https://github.com/mysqljs/mysql

Introduce dependencies through requires('mysql')

Create connection

Create a connection via createConnection

Examples are as follows

var mysql = require('mysql');
var connection = mysql.createConnection({<!-- -->
    host : 'localhost',
    user: 'root',
    password: '123456'
});

Connection options

If you use BIGINT, you need to pay attention here

supportBigNumbers
bigNumberStrings

Name	Default value	Description
host	localhost	To connect Host name of the database
port	3306	Connection Port number
localAddress		TCP connection use Source IP address (optional)
socketPath		The path to the unix domain socket to connect to. host and port will be ignored when used.
user		MySQL user for authentication name
password		The password of the MySQL user
database		The name of the database used to connect (optional)
charset	‘UTF8_GENERAL_CI’	Concatenated character set. This is called a “collation” in MySQL’s SQL level (like utf8_general_ci). If a SQL-level character set (such as utf8mb4) is specified, the default collation of that character set is used
timezone	‘local’	The time zone configured on the MySQL server. This is used to cast server date/time values to JavaScriptDate objects and vice versa. This can be “local”, “Z”, or an offset of the form +HH:MM or -HH:MM
connectTimeout	10000	Timeout in milliseconds before initial connection to the MySQL server
stringifyObjects	false	Convert objects to strings, not to values
insecureAuth	false	Allow connections to MySQL instances that require older (insecure) authentication methods
typeCast	true	Determines whether the column value should be Convert to native JavaScript type
queryFormat		Custom query format function. See Custom format.
supportBigNumbers	false	Handle large numbers in the database This option should be enabled for numbers (BIGINT and DECIMAL columns)
bigNumberStrings	false	If both `supportBigNumbers` and `bigNumberStrings` are enabled, forces large numbers (BIGINT and DECIMAL columns) to be returned as JavaScript String objects. Enable supportBigNumbers, but disable bigNumberStrings, returning large numbers as String objects only if they cannot be accurately represented by [JavaScriptNumberobjects] (https://tc39.es/ecma262/#sec-ecmascript language type is a number type)( This happens when they exceed the range [-2^{53, + 2}53]), otherwise they are returned as number objects. This option is ignored if supportBigNumbers is disabled.
dateStrings	false	Force the date type ( TIMESTAMP, DATETIME, DATE) are returned as strings rather than expanded into JavaScript Date objects. Array of type that can be true/false or retained as string
debug	false	Print protocol details to stdout. Can be true/false or an array of packet type names that should be printed
trace	true	Generate a stack trace on Error, including the call site of the library entry (“long stack trace”). Most calls will have a slight performance hit
localInfile	true	Allow LOAD DATA INFILE to use the LOCAL modifier
multipleStatements	false	Allows multiple mysql statements per query. Use this feature with caution, it may increase the scope of SQL injection attacks
flags		A list of connection flags other than the default. Default flags can also be disabled using a blacklist. See connection flags for more information.
ssl		An object with ssl parameters or A string containing the name of the ssl configuration file.

In addition to the above method of passing object parameters, you can also create it directly through strings.

var connection = mysql.createConnection('mysql://user:pass@host/db?debug=true & amp;charset=BIG5_CHINESE_CI & amp;timezone=-0700');

Connect via connect

connection.connect(function(err) {<!-- -->
  if (err) {<!-- -->
    console.error('Connection failed: ' + err.stack);
    return;
  }

  console.log('Connection successful, id ' + connection.threadId);
});

connection.query('SELECT 1', function (error, results, fields) {<!-- -->
  if (error) throw error;
  // connected!
});

Of course, you can also connect implicitly by using query directly.

connection.query('SELECT 1', function (error, results, fields) {<!-- -->
  if (error) throw error;
  // connected!
});

Through connection pool

This module provides built-in connection pooling instead of creating and managing connections one by one

Use the connection pool directly, so you don’t need to worry about connection issues

Create a connection pool through createPool

Examples are as follows

var mysql = require('mysql');
var pool = mysql.createPool({<!-- -->
  connectionLimit: 10,
  host: 'sdpro.top',
  user: 'Shendi',
  password: '123456',
  database: 'db'
});

pool.query('SELECT 1 + 1 AS solution', function (error, results, fields) {<!-- -->
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

Directly using the pool for operations may use different connections each time. Sometimes the same connection is required to execute multiple statements, such as transactions. In this case, you can obtain a connection from the connection pool through getConnection

Examples are as follows

var mysql = require('mysql');
var pool = mysql.createPool(...);

pool.getConnection(function(err, connection) {<!-- -->
  // no connection
  if (err) throw err;

  // use connection
  connection.query('SELECT something FROM sometable', function (error, results, fields) {<!-- -->
    // Release the connection after the operation is completed (return to the connection pool)
    connection.release();

    // Handle error after the release.
    if (error) throw error;

    // Don't use the connection here, it has been returned to the pool.
  });
});

If you want to close the connection and remove it from the pool then use connection.destroy()

Connection pool options

The Obj option passed when creating the connection pool. The connection pool has all the options for the connection. In addition, there are the following options

Name	Default value	Description
acquireTimeout	10000	The number of milliseconds before a timeout occurs during connection acquisition. This is slightly different from connectTimeout in that getting a pooled connection doesn’t always involve establishing a connection. If a connection request is queued, the time the request spends in the queue does not count towards this timeout
waitForConnections	true	Determines the behavior of the pool when no connections are available and the limit has been reached. If true, the pool will queue connection requests and call it when a connection request becomes available. If false, the pool will call back immediately and return an error
connectionLimit	10	The maximum number of connections created at one time
td>
queueLimit	0	The maximum number of connection requests that the pool can queue before returning an error from getConnection. If set to 0, there is no limit on queued connection requests

Connection pool events

acquire

When a connection is obtained from the pool, the pool emits a “get” event. This is called after all fetch activity has been performed on the connection, just before the connection is handed over to the fetch code’s callback

pool.on('acquire', function (connection) {
  console.log('Connection %d acquired', connection.threadId);
});

connection

When a new connection is established in the pool, the pool emits a “connection” event. If you need to set session variables on the connection before using it, you can listen to the “connection” event

pool.on('connection', function (connection) {
  connection.query('SET SESSION auto_increment_increment=1')
});

enqueue

The pool emits an “enqueue” event when a callback has been queued for an available connection.

pool.on('enqueue', function () {
  console.log('Waiting for available connection slot');
});

release

When a connection is released back to the pool, the pool emits a “release” event. This is called after all publishing activity has been performed on the connection, so the connection will be listed as idle when the event occurs

pool.on('release', function (connection) {
  console.log('Connection %d released', connection.threadId);
});

Add, delete, modify and check operations

Regardless of additions, deletions, modifications or queries, query is used

The most basic way to perform a query is to call the query method on an object (such as a Connection, Pool, or PoolNamespace instance)

The simplest form of query is query(sqlString, callback), where sqlString is the sql statement and callback is the callback function

connection.query('SELECT * FROM `books` WHERE `author` = "David"', function (error, results, fields) {<!-- -->
  // error If an error occurs, it is Error
  // results are the results of the query
  // fields is the returned result field information (if any)
});

The second form, like precompiled, but actually it just uses the same connection.eescape method internally. Prevent sql injection (called escaping here, see escaping query values)

query(sqlString, values, callback)

where values is a list of parameters, e.g.

connection.query('SELECT * FROM `books` WHERE `author` = ?', ['David'], function (error, results, fields) {<!-- -->
    
});

It can also be like the following

connection.query({<!-- -->
  sql: 'SELECT * FROM `books` WHERE `author` = ?',
  timeout: 40000, // 40s
  values: ['David']
}, function (error, results, fields) {<!-- -->
});

connection.query({<!-- -->
    sql: 'SELECT * FROM `books` WHERE `author` = ?',
    timeout: 40000, // 40s
  },
  ['David'],
  function (error, results, fields) {<!-- -->
    // error will be an Error if one occurred during the query
    // results will contain the results of the query
    // fields will contain information about the returned results fields (if any)
  }
);

// If the query has only one replacement character (?), and the value is not null, undefined, or an array, it can be passed directly to .query as the second parameter
connection.query(
  'SELECT * FROM `books` WHERE `author` = ?',
  'David',
  function (error, results, fields) {<!-- -->
  }
);

Transactions

Use the connection’s beginTransaction to start a transaction and receive a callback function. You need to manually rollback or commit the connection.

Examples are as follows

connection.beginTransaction(function(err) {<!-- -->
  if (err) {<!-- --> throw err; }
  connection.query('INSERT INTO posts SET title=?', title, function (error, results, fields) {<!-- -->
    if (error) {<!-- -->
      return connection.rollback(function() {<!-- -->
        throw error;
      });
    }

    var log = 'Post ' + results.insertId + ' added';

    connection.query('INSERT INTO log SET data=?', log, function (error, results, fields) {<!-- -->
      if (error) {<!-- -->
        return connection.rollback(function() {<!-- -->
          throw error;
        });
      }
      connection.commit(function(err) {<!-- -->
        if (err) {<!-- -->
          return connection.rollback(function() {<!-- -->
            throw err;
          });
        }
        console.log('success!');
      });
    });
  });
});

Tools

The tool classes I encapsulated are as follows

var mysql = require('mysql');

/**
 * Sql tool class<br>
 * @authro arsenic tellurium
 */
class SqlUtil {<!-- -->
    /** connection pool */
    static pool = mysql.createPool({<!-- -->
        host : 'localhost',
        port: 3306,
        user: 'root',
        password: 'pwd',
        database : 'db',
        supportBigNumbers : true,
        multipleStatements: true,
        connectionLimit: 5,
    });

    /**
     * Execute sql statement. Promise.
     * Example
     * exec("SELECT * FROM xx WHERE id=?", [1]).then((results,fields) => {
     * console.log(results, fields);
     * }).catch((err) => {
     * console.error("Query failed:", err);
     * });
     * @param {any} obj sql string or parameter object,
     * @param {Array} escaping escape list, corresponding to the question mark of the sql string, optional
     */
    static exec(obj, escaping) {<!-- -->
        return new Promise((resolve, reject) => {<!-- -->
            if (typeof obj == 'string') obj = {<!-- --> sql: obj };
            if (escaping) obj.values = escaping;

            SqlUtil.pool.query(obj, function (error, results, fields) {<!-- -->
                if (error) {<!-- -->
                    reject(error);
                } else {<!-- -->
                    resolve(results, fields);
                }
            });
        });
    }

}

module.exports=SqlUtil;

Mount it globally and all js will be available

global.SqlUtil = require("./SqlUtil");

END