NodeJS REST API

[Kameron Berget]

I am new to node and development in general. I am looking to build an application using node and publishing 2-3 endpoints for standard CRUD ops against a SQL database. My current project is setup as an App Service in Azure. To get started, I created my first GET method. Azure stubbed this out for me into a /API folder. It created two files <apiname>.js and <apiname>.json. Inside, it has the proper method for me to start writing code. 

I have the GET method working. It takes in standard ODATA query params and transforms into a SQL select statement and serves up the data in JSON.  My question is this....

Should the require('mssql'), config and connect statements really be inside the GET? That would mean that I have to repeat the config (connection) settings in each method on each endpoint. Where would be the best place to put this? Also, are simple sql.connect calls ok for large loads or do I need to consider connection pools, etc?  My GET method is below.

module.exports = {

    "get": function (req, res, next) {

        

        var tableConfig = {

    "name": "Meals",

    "schema":"dbo",

    "flavor": "mssql",

    "softDelete":true

    };

     

    var query = {

    "resultLimit": req.query.$top,

    "selections": req.query.$select,

    "filters": req.query.$filter,

    "ordering": req.query.$orderBy

    };

     

    var odQuery = require('azure-odata-sql').format(query, tableConfig);

    var sqlQry = odQuery[0].sql;

        

        var sql = require("mssql");

        // config for your database

        var config = {

            user: 'zumoadmin',

            password: '**********',

            server: '*******.database.windows.net', 

            database: '*********',

            options: {

                encrypt: true

            }

        };

    

        sql.connect(config, function (err) {

        

            if (err) console.log(err);

    

            var request = new sql.Request();  

 

            for (var i = 0; i < odQuery[0].parameters.length; i++) {

                console.log(odQuery[0].parameters[i]);

                request.input(odQuery[0].parameters[i].name, odQuery[0].parameters[i].value);

            }   

            request.query(sqlQry, function (err, recordset) {

                

                if (err) console.log(err);

    

                var data = {

                    "results"   : recordset,

                    "rowcount"  : recordset.length 

                };

                res.json(data);

                

            });

        });

    },

};

[Zlatko]

On Friday, May 12, 2017 at 1:19:26 AM UTC+2, Kameron Berget wrote:

Should the require('mssql'), config and connect statements really be inside the GET? That would mean that I have to repeat the config (connection) settings in each method on each endpoint. Where would be the best place to put this? Also, are simple sql.connect calls ok for large loads or do I need to consider connection pools, etc?  My GET method is below.

Well, for one thing, the require itself  is cached, on process level. So no matter how many times you call require('mssql'), it'll only read it once, at that first require in the first module that actually calls it.

But that is, as you seem to suspect, most likely not the perf problem, but the fact that on each API request, you have to create a new connection. How it is usually done is to have a db module somewhere. Then you export a db.bootstrap or db.initialize function, and possibly a db.client from that module and other db-querying modules use that client. You call the init once when your server is starting, and than all other actual calls to the db do not have to do this step. Just make sure that before the server started serving

One of the exceptions to this would be when you're using something like Azure Functions, or AWS lambda or similar. Usually you cannot persist database connections there so you have to connect on each request. But that's probably something you ask later.

One small disclaimer is that I didn't work with Azure, but I've worked with other bigger infra/platform providers and they usually share those concepts so I'm pretty sure the above should apply to Azure App service.