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
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With