Common Methods for JavaScript MySQL operations in Node. js, node. jsmysql

Common Methods for JavaScript MySQL operations in Node. js, node. jsmysql

1. Create a database connection: createConnection (Object) Method
This method accepts an object as a parameter. This object has four common attributes: host, user, password, and database. The parameter is the same as the parameter used to connect to a database in php. The attribute list is as follows:

• Host: The host Name of the database to be connected. (default: localhost)
• Port: Connection port. (default: 3306)
• LocalAddress: IP Address used for TCP connection. (optional)
• SocketPath: The Path to the unix domain. This parameter is ignored when host and port are used.
• User: user Name of the MySQL user.
• Password: the password of the MySQL user.
• Database: the name of the database to be linked to (optional ).
• Charset: connected character set. (default: 'utf8 _ GENERAL_CI '. Set this value to uppercase !)
• Timezone: the time zone for storing the local time. (default: 'local ')
• StringifyObjects: whether to serialize the object. See issue #501. (default: 'false ')
• InsecureAuth: whether to allow the old authentication method to connect to the database instance. (default: false)
• TypeCast: determines whether to convert the column value to the column value of the local JavaScript type. (default: true)
• QueryFormat: format a custom query statement.
• SupportBigNumbers: this parameter must be enabled when the database processes large numbers (long integers and decimals) (default: false ).
• BigNumberStrings: Enable supportBigNumbers and bigNumberStrings and force these numbers to be returned as strings (default: false ).
• DateStrings: the mandatory DATE type (TIMESTAMP, DATETIME, Date) is returned as a string, rather than a javascript DATE object. (default: false)
• Debug: whether debugging is enabled. (default value: false)
• MultipleStatements: whether to allow multiple query statements to be passed in a query. (Default: false)
• Flags: link flag.

You can also use a string to connect to a database, for example:

Copy codeThe Code is as follows:

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

Ii. end database connection end () and destroy ()
End () accepts a callback function and is triggered only after the query is completed. If a query error occurs, the link is terminated and the error is passed to the callback function for processing.
Destroy () immediately terminates the database connection. Even if the query is not completed, the subsequent callback function will not be triggered.

3. Create a connection pool createPool (Object)

The Object and createConnection parameters are the same.
You can listen to connection events and set session values.

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

Connection. release () releases the link to the connection pool. To close the connection and delete it, use connection. destroy ()
In addition to accepting the same parameters as connection, the pool also accepts several extended parameters.

CreateConnection: The function used to create a link. (Default: mysql. createConnection)
WaitForConnections: determines the action of the pool when no connection pool or the maximum number of connections reaches. if it is set to true, the link will be put into the queue. If it is set to false, an error will be returned immediately. (Default: true)
ConnectionLimit: Maximum number of connections. (Default: 10)
QueueLimit: the maximum length of the connection request in the connection pool. If the length is exceeded, an error is returned. If the value is 0, there is no limit. (Default: 0)

Iv. Connection Pool Cluster
Allow different host links

// create   var poolCluster = mysql.createPoolCluster();    poolCluster.add(config); // anonymous group   poolCluster.add('MASTER', masterConfig);   poolCluster.add('SLAVE1', slave1Config);   poolCluster.add('SLAVE2', slave2Config);    // Target Group : ALL(anonymous, MASTER, SLAVE1-2), Selector : round-robin(default)   poolCluster.getConnection(function (err, connection) {});    // Target Group : MASTER, Selector : round-robin   poolCluster.getConnection('MASTER', function (err, connection) {});    // Target Group : SLAVE1-2, Selector : order   // If can't connect to SLAVE1, return SLAVE2. (remove SLAVE1 in the cluster)   poolCluster.on('remove', function (nodeId) {    console.log('REMOVED NODE : ' + nodeId); // nodeId = SLAVE1    });    poolCluster.getConnection('SLAVE*', 'ORDER', function (err, connection) {});    // of namespace : of(pattern, selector)   poolCluster.of('*').getConnection(function (err, connection) {});    var pool = poolCluster.of('SLAVE*', 'RANDOM');   pool.getConnection(function (err, connection) {});   pool.getConnection(function (err, connection) {});    // destroy   poolCluster.end(); 

Optional parameter of the linked Cluster

CanRetry: If the value is true, allow retry upon connection failure (Default: true)
RemoveNodeErrorCount: The errorCount value increases when the connection fails. When the errorCount value is greater than removeNodeErrorCount, a node is deleted from PoolCluster. (Default: 5)

• DefaultSelector: Default selector. (Default: RR)
• RR: loop. (Round-Robin)
• RANDOM: select a node using a RANDOM function.
• ORDER: select the first available node unconditionally.

5. Switch user/change connection status

Mysql allows users to be switched when the connection is disconnected.

  connection.changeUser({user : 'john'}, function(err) {     if (err) throw err;   }); 

Parameters

• User: new user (the default value is the previous one ).
• Password: the new password of the new user (the default is the previous one ).
• Charset: new character set (the default value is the previous one ).
• Database: name of the new database (the default value is the previous one ).

6. Handling server disconnection

var db_config = {     host: 'localhost',     user: 'root',     password: '',     database: 'example'   };    var connection;    function handleDisconnect() {    connection = mysql.createConnection(db_config); // Recreate the connection, since                            // the old one cannot be reused.     connection.connect(function(err) {       // The server is either down     if(err) {                   // or restarting (takes a while sometimes).      console.log('error when connecting to db:', err);      setTimeout(handleDisconnect, 2000); // We introduce a delay before attempting to reconnect,     }                   // to avoid a hot loop, and to allow our node script to    });                   // process asynchronous requests in the meantime.                        // If you're also serving http, display a 503 error.    connection.on('error', function(err) {     console.log('db error', err);     if(err.code === 'PROTOCOL_CONNECTION_LOST') { // Connection to the MySQL server is usually      handleDisconnect();             // lost due to either server restart, or a     } else {                   // connnection idle timeout (the wait_timeout      throw err;                 // server variable configures this)     }    });   }    handleDisconnect(); 

VII. Escape query value
To avoid SQL injection attacks, You must escape the data submitted by the user. You can use connection. escape () or pool. escape ()
For example:

Var userId = 'some user provided value'; var SQL = 'select * FROM users WHERE id = '+ connection. escape (userId); connection. query (SQL, function (err, results ){//...}); or use? As the placeholder connection. query ('select * FROM users WHERE id =? ', [UserId], function (err, results ){//...}); the Conversion Result of different types of values, Numbers, remains unchanged. Booleans is converted to the string 'true'/'false' Date object and converted to the string 'yyyy-mm-dd HH: ii: ss 'buffers' is converted to a 6-digit string Strings without changing Arrays => ['A', 'B'] to 'A ', 'B' nested array [['A', 'B'], ['C', 'D'] to ('A', 'B '), ('C', 'D') Objects to key = 'val 'pairs. converts a nested object to a string. undefined/null ==> NULL NaN/Infinity remains unchanged. mySQL does not support these values. unless supported by tools, inserting these values may cause errors. conversion instance: var post = {Id: 1, title: 'Hello mysql'}; var query = connection. query ('insert INTO posts SET? ', Post, function (err, result) {// Neat! }); Console. log (query. SQL); // INSERT INTO posts SET 'id' = 1, 'title' = 'Hello mysql'

Or manually convert

  var query = "SELECT * FROM posts WHERE title=" + mysql.escape("Hello MySQL");    console.log(query); // SELECT * FROM posts WHERE title='Hello MySQL' 

VIII. Convert query identifiers
If you cannot trust the SQL identifier (Database Name, table name, and column name), you can use the conversion method mysql. escapeId (identifier );

Var sorter = 'date'; var query = 'select * FROM posts ORDER by' + mysql. escapeId (sorter); console. log (query); // SELECT * FROM posts order by 'date' supports conversion of multiple var sorter = 'date '; var query = 'select * FROM posts ORDER by' + mysql. escapeId ('posts. '+ sorter); console. log (query); // SELECT * FROM posts order by 'posts '. 'date' can be used ?? As the placeholder for the identifier var userId = 1; var columns = ['username', 'email ']; var query = connection. query ('select ?? FROM ?? WHERE id =? ', [Columns, 'users', userId], function (err, results ){//...}); console. log (query. SQL); // SELECT 'username', 'email 'FROM 'users' WHERE id = 1

9. Prepare for query
You can use mysql. format to prepare query statements. This function automatically selects appropriate method escape parameters.

Var SQL = "SELECT * FROM ?? WHERE ?? =? "; Var inserts = ['users', 'id', userId]; SQL = mysql. format (SQL, inserts); 10. Custom Formatting Function connection. config. queryFormat = function (query, values) {if (! Values) return query; return query. replace (/\ :( \ w +)/g, function (txt, key) {if (values. hasOwnProperty (key) {return this. escape (values [key]);} return txt ;}. bind (this) ;}; connection. query ("UPDATE posts SET title =: title", {title: "Hello MySQL "});

10. Obtain the id of the inserted row
Obtains the id of the inserted row when the auto-increment primary key is used, for example:

connection.query('INSERT INTO posts SET ?', {title: 'test'}, function(err, result) {    if (err) throw err;     console.log(result.insertId);   }); 

11. Stream Processing
Sometimes you want to select a large number of rows and process them when the data arrives, you can use this method.

var query = connection.query('SELECT * FROM posts');   query    .on('error', function(err) {     // Handle error, an 'end' event will be emitted after this as well    })    .on('fields', function(fields) {     // the field packets for the rows to follow    })    .on('result', function(row) {     // Pausing the connnection is useful if your processing involves I/O     connection.pause();      processRow(row, function() {      connection.resume();     });    })    .on('end', function() {     // all rows have been received    }); 

12. Hybrid query statement (Multi-statement query)
Because hybrid queries are vulnerable to SQL injection attacks, they are not allowed by default. You can use var connection = mysql. createConnection ({multipleStatements: true}) to enable this function.
Hybrid query instance:

connection.query('SELECT 1; SELECT 2', function(err, results) {    if (err) throw err;     // `results` is an array with one element for every statement in the query:    console.log(results[0]); // [{1: 1}]    console.log(results[1]); // [{2: 2}]   }); 

You can also use stream processing to process the mixed query results:

var query = connection.query('SELECT 1; SELECT 2');    query    .on('fields', function(fields, index) {     // the fields for the result rows that follow    })    .on('result', function(row, index) {     // index refers to the statement this result belongs to (starts at 0)    }); 

If one of the query statements has an Error, the Error object will contain the id of the err. index indicating the Error statement, and the entire query will be terminated.
The stream processing method of mixed query results is experimental and unstable.

XIII. Transaction Processing
Simple transaction processing at the connection level

connection.beginTransaction(function(err) {    if (err) { throw err; }    connection.query('INSERT INTO posts SET title=?', title, function(err, result) {     if (err) {       connection.rollback(function() {       throw err;      });     }      var log = 'Post ' + result.insertId + ' added';      connection.query('INSERT INTO log SET data=?', log, function(err, result) {      if (err) {        connection.rollback(function() {        throw err;       });      }       connection.commit(function(err) {       if (err) {         connection.rollback(function() {         throw err;        });       }       console.log('success!');      });     });    });   }); 

14. Error Handling

err.code = string err.fatal => boolean 

PS: The pitfalls encountered when using the mysql Module
I previously wrote a small program Node News, and used the MySQL database to test it locally. After running on the server for a period of time, you may find that the page remains in the waiting status when you open the page until Nginx returns a timeout error. So I checked it on the server and found that the program is still running, and I can correctly record each request, and then modify the code for tracking and debugging. It turned out that when I was querying the database, the callback has never been executed, and the program will be hung there.

After thinking for a long time, I couldn't understand that the Shenma mysql module didn't execute the callback. Finally, I suddenly remembered to go through the error log and found a "No reconnection after connection lost" error that was not captured, it turns out that the connection is lost. I read the document and issues on github. As mentioned above, the connection will not be automatically reconnected after the connection is lost, and an error event will be triggered. I quickly added the automatic reconnection function for the program after disconnection, and now it has been running normally for more than 10 days.

MySQL has a variable named wait_timeout, which indicates the operation timeout time. When the connection is not active after a certain period of time, the connection is automatically closed. The default value is 28800 (8 hours ).

Code for automatic database reconnection:

Function handleError (err) {if (err) {// automatically reconnects if (err. code = 'Protocol _ CONNECTION_LOST ') {connect ();} else {console. error (err. stack | err) ;}}// connect to the database function connect () {db = mysql. createConnection (config); db. connect (handleError); db. on ('error', handleError);} var db; connect ();

Most of the Code circulating on the Internet that uses the mysql module often ignores this issue, so that a group of people may step on it accidentally.
Some children's shoes replied and asked what would happen when using the pool. So I checked the source code of the mysql module: the latest version that can be installed in npm is 2.0.0-alpha7 and mysql is used. the pool created by createPool () cannot automatically handle the connection disconnection issue, but the version on github has been fixed (it should have not been released to npm ), when a connection error event is triggered, the connection object is removed from the connection pool. (Source Code: https://github.com/felixge/node-mysql/blob/master/lib/Pool.js#L119)
Code for using the pool:

var mysql = require('mysql');var pool = mysql.createPool(config);pool.getConnection(function(err, connection) { // Use the connection connection.query( 'SELECT something FROM sometable', function(err, rows) {  // And done with the connection.  connection.end();  // Don't use the connection here, it has been returned to the pool. });});

Articles you may be interested in:

• How to build a Windows + node. js + mysql environment using Node-MySQL
• Node. js to operate mysql (add, delete, modify, and query)
• Example of calling mysql stored procedure in Node. js
• Node. js Development Guide-Node. js connects to MySQL and performs database operations
• Simple example in Node. js Development Guide (mysql version)