Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

When to open the connection using node-mysql module?

Tags:

node.js

mysql

I found a very good module (node-mysql) to connect to Mysql database.

The module is very good, I Only have a question about "WHEN" to open the connection to Mysql.

I always have used php-mysql before starting with node, for each request i opened a connection...then query....then close.

Is the same with node? for each request do I have to open a connection and then close it? or can i use persistent connection?

Thank you

like image 639
Dail Avatar asked Jul 07 '11 08:07

Dail


People also ask

Which module is required to connect to MySQL using node?

To access a MySQL database with Node.js, you need a MySQL driver. This tutorial will use the "mysql" module, downloaded from NPM. Now you have downloaded and installed a mysql database driver.

What is the procedure to open a connection to the MySQL?

To Connect to a MySQL Database Expand the Drivers node from the Database Explorer. Right-click the MySQL (Connector/J driver) and choose Connect Using.... The New Database Connection dialog box is displayed. In the Basic Setting tab, enter the Database's URL <HOST>:<PORT>/<DB> in the corresponding text field.


2 Answers

The open-query-close pattern generally relies on connection pooling to perform well. Node-mysql doesn't have any built in connection pooling, so if you use this pattern you'll be paying the cost of establishing a new connection each time you run a query (which may or may not be fine in your case).

Because node is single threaded, you can get away with a single persistent connection (especially since node-mysql will attempt to reconnect if the connection dies), but there are possible problems with that approach if you intend to use transactions (since all users of the node client are sharing the same connection and so same transaction state). Also, a single connection can be a limit in throughput since only one sql command can be executed at a time.

So, for transactional safety and for performance, the best case is really to use some sort of pooling. You could build a simple pool yourself in your app or investigate what other packages are out there to provide that capability. But either open-query-close, or persistent connection approaches may work in your case also.

like image 106
Geoff Chappell Avatar answered Oct 15 '22 18:10

Geoff Chappell


felixge/node-mysql now has connection pooling (at the time of this writing.)

https://github.com/felixge/node-mysql#pooling-connections

Here's a sample code from the above link:

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

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

So to answer your question (and same as @Geoff Chappell's answer): best case would be to utilize pooling to manage connections.

like image 34
rfk Avatar answered Oct 15 '22 17:10

rfk