CODEWITHSUNDEEP
javascriptsql-servernode.js
Brigante
Brigante

Reputation: 1981

Javascript Node API best way to build SQL queries

I'm building a simple API in JS running on Node. It's connecting to a MS SQL DB to fetch articles and return them as JSON. It's all working perfectly but I feel like I could write the queries much simpler.

At the moment I have several if statements. Is there any way of making the queries dynamic so I don't have to have an if statement for each query?

...

app.get('/articles', function (req, res) {

    // Our URL parameters
    var articles = req.query.articles;
    var countryID = req.query.countryID;
    var offset = req.query.offset;

    // Set up the database connection
    var connection = new sql.Connection(db_config, function(err) {

        // Log any errors to the console
        if (err) console.log(err);

        // Connect using prepared statement
        var ps = new sql.PreparedStatement(connection);

        // Our variable is an Int
        ps.input('articles', sql.Int);
        ps.input('countryID', sql.Int);
        ps.input('offset', sql.Int);
        ps.input('live', sql.VarChar(5));

        // Build the query and pass in the parameters

        // @articles, @countryID and @offset are empty
        if (isEmpty(articles) && isEmpty(countryID) && isEmpty(offset)) {
            var query = 'SELECT TOP 15 * FROM dbo.articlesCountryTown WHERE articleLive = (@live) ORDER BY PublishDate DESC';
            //console.log('Using query 1');

        // @articles and @offset are empty, @countryID is present
        } else if (isEmpty(articles) && countryID && isEmpty(offset)) {
            var query = 'SELECT TOP 15 * FROM dbo.articlesCountryTown WHERE CntryId = (@countryID) AND articleLive = (@live) ORDER BY PublishDate DESC';
            //console.log('Using query 2');

        // @articles is present, @countryID and @offset are empty
        } else if (articles && isEmpty(countryID) && isEmpty(offset)) {
            var query = 'SELECT TOP (@articles) * FROM dbo.articlesCountryTown WHERE articleLive = (@live) ORDER BY PublishDate DESC';
            //console.log('Using query 3');

        // @articles and @countryID are empty, @offset is present
        } else if (isEmpty(articles) && isEmpty(countryID) && offset) {
            var query = 'SELECT TOP 15 * FROM (select *, ROW_NUMBER() OVER (ORDER BY PublishDate DESC) as r_n_n from dbo.articlesCountryTown WHERE articleLive = (@live)) xx WHERE r_n_n >= (@offset + 1)';
            //console.log('Using query 4');

        // @articles and @countryID are present, @offset is empty
        } else if (articles && countryID && isEmpty(offset)) {
            var query = 'SELECT TOP (@articles) * FROM dbo.articlesCountryTown WHERE CntryId = (@countryID) AND articleLive = (@live) ORDER BY PublishDate DESC';
            //console.log('Using query 5');

        // @articles and @offset are present, @countryID is empty
        } else if (articles && isEmpty(countryID) && offset) {
            var query = 'SELECT TOP (@articles) * FROM (select *, ROW_NUMBER() OVER (ORDER BY PublishDate DESC) as r_n_n from dbo.articlesCountryTown WHERE articleLive = (@live)) xx WHERE r_n_n >= (@offset + 1)';
            //console.log('Using query 6');

        // @articles, @countryID and @offset are all present
        } else {
            var query = 'SELECT TOP (@articles) * FROM (select *, ROW_NUMBER() OVER (ORDER BY PublishDate DESC) as r_n_n from dbo.articlesCountryTown WHERE CntryId = (@countryID) AND articleLive = (@live)) xx WHERE r_n_n >= (@offset + 1)';
            //console.log('Using query 7');
        }

        // Prepare the query
        ps.prepare(query, function(err) {
            // Log any errors to the console
            if (err) console.log(err);

            // Pass in the parameters and execute the query
            ps.execute({articles: req.query.articles, countryID: req.query.countryID, offset: req.query.offset, live: 'true'}, function(err, recordset) {
                // Return the JSON
                res.json(recordset);

                // Close the connection
                ps.unprepare(function(err) {
                    // Log any errors to the console
                    if (err) console.log(err);
                });
            });
        });
    });
});

...

Upvotes: 0

Views: 610

Answers (2)

meehocz
meehocz

Reputation: 184

If you are able to pass null values to @articles, @countryId and @offset, you should be OK with just one query:

SELECT TOP (ISNULL(@articles, 15)) * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY PublishDate DESC) as r_n_n from dbo.articlesCountryTown 
    WHERE CntryId = CASE WHEN @countryId IS NULL THEN CntryId ELSE @countryId END
    AND articleLive = (@live)
) xx 
WHERE r_n_n >= (ISNULL(@offset, 0) + 1)

Even better, wrap this into a stored procedure with parameters defaulted to null. It's always better to use stored procedures than to play with strings at server level.

Upvotes: 3

Avinash
Avinash

Reputation: 2195

Try node module node-mysql. node-mysql module link

  • there is a format method which will help you to create and execute dynamic queries.
  • it will return objects which can be converted to json data.

Example:

DB.format('select * from users where id = ?' [userId]);

Upvotes: 0

Related Questions