Mysql: How to use singleton design pattern with mysql connection and pooling.

[chanakaDe]

I'm creating a REST API using MySQL and Node.JS. Earlier I did the same using MongoDB. And it's working fine. https://github.com/chanakaDe/Mint-REST. Now I want to do the same using MySQL. I did most of the part and having a little issue. There are many classes and I need to use mysql connection in a centralized way. As singleton design pattern.

This is the my new repo. https://github.com/chanakaDe/MintRestSQL. And I will show where I want to use those patterns. Here I have the database file. And I created a new connection pool. https://github.com/chanakaDe/MintRestSQL/blob/master/app/util/database.js.

Now I want to use this connection/pool inside my controller classes. Because I cannot create a connection in each and every controller class. No ? These are my two controllers for now.
https://github.com/chanakaDe/MintRestSQL/blob/master/app/routes/report.js
https://github.com/chanakaDe/MintRestSQL/blob/master/app/routes/api.js

Please show me a better way to do this. I am new to node-mysql. But it's a great way to use MySQL inside Node.JS environments even for production grade systems. So I want to make a good API using those standards. Is there any way to use singleton pattern or something like that and centralized the connection and use it in all the controllers ????

IT WILL TAKE SOME TIME TO CHECK MY FILES AND UNDERSTAND THE CODE. BUT PLEASE CHECK IT AND GIVE ME A SOLUTION. I TRIED MANY THINGS, BUT DIDN'T WORK :(

You are welcome to pull the repo and make any update :+1:

[sidorares]

var mysql = require('mysql');
var pool;
module.exports = {
    getPool: function () {
      if (pool) return pool;
      pool = mysql.createPool({
        host     : 'localhost',
        user     : 'root',
        password : 'chanaka',
        database : 'shared_adult'
      });
      return pool;
    }
};

[chanakaDe]

Thanks and how should I call this in my controllers ????

[chanakaDe]

@sidorares I tried calling with "db.pool" , but it's not working :( Is there any specific way please

[sidorares]

Thanks and how should I call this in my controllers ????

var db = require('./app/util/database.js');
var pool = db.getPool(); // re-uses existing if already created or creates new one
pool.getConnection(function(err, connection) {
   // don't forget to check error
   connection.query('select 1+1', function(err, rows) {
     // don't forget to check error
     // ...
     // use your data - response from mysql is in rows    
   });
});

I tried calling with "db.pool" , but it's not working :( Is there any specific way please

did you return pool from your function?

[chanakaDe]

@sidorares WOW :+1: This worked really well...
Thanks a lot brother :+1:
And node-mysql is really great.
I'm creating a new stock and account management system using node-mysql

[chanakaDe]

@sidorares Everything is fine. But one issue is there.
I have this rest URL to get all the saved data with limit and offset.

http://localhost:3000/sys/report/income/2/0

It's available in following file in line 9.
https://github.com/chanakaDe/MintRestSQL/blob/master/app/routes/report.js

When I'm reloading the page very quickly, data is not receiving. Server like stuck. Then again I have to restart the server. But no error in the server too. I use POSTMAN to check this. I send the request very soon. I mean I pressed the "send" button again and again. Then it gets stuck always. Why ?

Is that a issue with my connection pool ?

[chanakaDe]

I think I got that. I just had to use "connection.release();" method.
This is great :+1:

Thanks :+1:

[chanakaDe]

Hi @sidorares :+1: Have another question.
This is my income table.
{incomeID, incomeDate, incomeCaption, incomeAmount}.

incomeDate is standard mysql 'date' and it's saved as this "2016-05-15".
What I want is group incomes by month and add in an array. Specially if we take a certain month, income can be 0. So how can I do this ? If there's no income from 3rd month, array must add 0 in the 3rd location. As example :

[1000,3200,0,452,1254,5478,1254,0,1254,2145,214,0]

There are 12 elements for 12 months.

We can do group by function as normal, but how to add zeroes if there's no any income from that month ? Please help me. This may be not related to your framework. But it's a great help if you can :+1: I did many things and tried a lot. Not working.

[sidorares]

hi @chanakaDe , yes your question is generic sql and not related directly to this library. Probably better place to ask this question is http://stackoverflow.com/questions/tagged/mysql or http://stackoverflow.com/questions/tagged/sql

[chanakaDe]

@sidorares Hai,
I did that as you told. And thank you very much for those above code snippets and also advises.
http://stackoverflow.com/questions/38561355/how-to-group-by-month-and-return-zero-if-no-value-for-certain-month