How to use singleton design pattern with mysql connection and pooling in node.js

Question

I'm using node-mysql as the orm. (https://github.com/mysqljs/mysql)

I'm creating a REST API using MySQL and Node.JS. Earlier I did the same using MongoDB. And it's working fine. 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. 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. github.com/chanakaDe/MintRestSQL/blob/master/app/routes/report.js 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

Answer 1

Try change database.js to

var mysql = require('mysql');

mysql.createPool({
  host     : 'localhost', 
  user     : 'root',
  password : 'chanaka',
  database : 'shared_adult'
}).connect();

module.exports = mysql;