Problem invoking mssql module connection in a separate files nodejs

Question

I'm have a bad experience using mssql package which is return the connection as a promise so to be able to use a method like query I should to first wait for connection then use it something like

const sql = require('mssql')
const sqlConfig = {
  user: process.env.DB_USER,
  password: process.env.DB_PWD,
  database: process.env.DB_NAME,
  server: 'localhost',
  pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 30000
  },
  options: {
    encrypt: true, // for azure
    trustServerCertificate: false // change to true for local dev / self-signed certs
  }
}
sql.connect(config).then(async pool => {
  const query = await pool.query`select * from table_name`;
  console.log(query)
})

But certainly I won't use that connection in a single file I want to export it to use it in a different routes files so for that case I found 2 solutions who isn't the best practice or not a good programming experience in my opinion first is introduced in the package docs which is connect to the SQL server first then run your node server and attach the connection in the app locals and I think it's really so bad solution it's stopping the whole server on the database connection what if the connection failed that's means that the whole server won't start even if there's a static files that doesn't require a database connection the user won't be able to access it anymore here's the solution provided

const express = require('express')
const sql = require('mssql')
const config  = {/*...*/}
//instantiate a connection pool
const appPool = new sql.ConnectionPool(config)
//require route handlers and use the same connection pool everywhere
const route1 = require('./routes/route1')
const app = express()
app.get('/path', route1)

//connect the pool and start the web server when done
appPool.connect().then(function(pool) {
  app.locals.db = pool;
  const server = app.listen(3000, function () {
    const host = server.address().address
    const port = server.address().port
    console.log('Example app listening at http://%s:%s', host, port)
  })
}).catch(function(err) {
  console.error('Error creating connection pool', err)
});

So to access it I will use something like the following

const express = require('express');
const router = express.Router();

router.get('/', async (req, res, next) => {
  req.app.locals.db.query('SELECT TOP 10 * FROM table_name', function(err, recordset) {
    if (err) {
      console.error(err)
      res.status(500).send('SERVER ERROR')
      return
    }
    res.status(200).json({ message: 'success' })
  })
})

That was the first solution and the second solution that I found is exporting the connection and in each time I want to use something like query method I can't call it directly I should to repeat a static step who is const pool = await connection; const query = await connection.query('my query string') in fact that solution is better than the first but there's a step I should to repeat it in each time I think that this isn't a good programmer experience here's the code example

const sql = require('mssql');
const { normalizePort } = require('./handlers');
const {
  SQL_SERVER_USER,
  SQL_SERVER_PASSWORD,
  SQL_SERVER,
  SQL_SERVER_DATABASE,
  SQL_SERVER_PORT
} = require('./configurations/appConfig');

const config = {
  user: SQL_SERVER_USER,
  password: SQL_SERVER_PASSWORD,
  database: SQL_SERVER_DATABASE,
  server: SQL_SERVER,
  port: normalizePort(SQL_SERVER_PORT),
  pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 30000
  },
  options: {
    encrypt: true,
    trustServerCertificate: true, // change to true for local dev / self-signed certs
  }
}
const connection = sql.connect(sqlConfig)

module.exports = connection;

Let's assume that the filename of the code above is db.js and then in a route file which is called production.js there's 3 routes so let's import the connection above and use it in that route

const express = require('express');
const router = express.Router();
const connection = require('../db.js')


router.get('/products', async (req, res, next) => {
  const pool = await connection;
  const query = await pool.query`select * from products`;
  res.setHeader("Content-Type", 'application/json; charset=utf-8');
  res.status(200).send(JSON.stringify(query, null, 4));
});

router.post('/brands', async (req, res, next) => {
  const pool = await connection;
  const query = await pool.query`select * from brands`;
  res.setHeader("Content-Type", 'application/json; charset=utf-8');
  res.status(200).send(JSON.stringify(query, null, 4));
});

router.post('/categories', async (req, res, next) => {
  const pool = await connection;
  const query = await pool.query`select * from categories`;
  res.setHeader("Content-Type", 'application/json; charset=utf-8');
  res.status(200).send(JSON.stringify(query, null, 4));
});

module.exports = router

In the example above in each route I should to call const pool = await connection; that repetitive code I think it's not the best practice and not a good programmer experience I'm looking for a better solution where I can import the connection and directly access the query method without extra steps.

Mahmoud Y3c · Accepted Answer · 2023-07-26T15:55:57.377

Seems like you have a hard time using mssql module after some search i thought what if i have a function that's returning an object and that object contains a function called query but just wait the query function already doesn't exist and that function won't return it you have to wait some time until loading that object that's contains query method then you can called the query method and execute it so if you called that function dot query it's will be undefined and if you invoked it you will get a pretty error who is TypeError: function_name(...).query is not a function to fix that problem you can use Proxy object so when your function invoked it's will directly return a new Proxy object from that proxy object you can access the called method name then return a function to access the arguments something like the following basic example

const obj = {
  sum: n => n + 2,
}

function proxyfire() {
  return new Proxy({}, {
    //in the following property param you will receive the invoked method name
    get: function(target, property) {
      console.log(property);
      //now let's get the called method arguments
      return function() {
        console.log(arguments);
        if(obj[property]) {
          return obj[property](...arguments)
        }
        return () => null
      }
    }
  })
}

const sum = proxyfire().sum(1)
console.log(sum)//result is 3

in the example above we called sum method who isn't included in the returned result but it's already exist in the obj so when calling the proxyfire().sum(1) the proxyfire method will work as a middleware or brige between the obj and your invoked method and you can access the called method name and it's arguments by returning a new function we can use the example above to handle your problem of repeating await connection for many times let's take a look and use one of your examples then add the proxyfire on it who will work as a bridge between your direct invoking and the waiting untill the connection is already done and ready to call query method

const sql = require('mssql');
const { normalizePort } = require('./handlers');
const {
  SQL_SERVER_USER,
  SQL_SERVER_PASSWORD,
  SQL_SERVER,
  SQL_SERVER_DATABASE,
  SQL_SERVER_PORT
} = require('./configurations/appConfig');

const sqlConfig = {
  user: SQL_SERVER_USER,
  password: SQL_SERVER_PASSWORD,
  database: SQL_SERVER_DATABASE,
  server: SQL_SERVER,
  port: normalizePort(SQL_SERVER_PORT),
  pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 30000
  },
  options: {
    encrypt: true,
    trustServerCertificate: true, // change to true for local dev / self-signed certs
  }
}

const connection = sql.connect(sqlConfig);


function proxyfire() {
  return new Proxy({}, {
    //getting the called property
    get: function (target, prop) {
      //let's get the called function arguments to send it
      return function () {
        return connection.then(poolConnection => {
          // Check if the method exists in the object
          if (poolConnection[prop]) {
            // Return a function that invokes the method with the provided arguments
            return poolConnection[prop](...arguments);
          }
          //method unavailable
          return () => null;
        });
      }
    }
  });
}

module.exports = proxyfire;

the file above is called db.js and we want to import and use it in production.js route so let's use it

const express = require('express');
const router = express.Router();
const proxyfire = require('../db.js');
const pool = proxyfire()


router.get('/products', async (req, res, next) => {
  const query = await pool.query`select * from products`;
  res.setHeader("Content-Type", 'application/json; charset=utf-8');
  res.status(200).send(JSON.stringify(query, null, 4));
});

router.post('/brands', async (req, res, next) => {
  const query = await pool.query`select * from brands`;
  res.setHeader("Content-Type", 'application/json; charset=utf-8');
  res.status(200).send(JSON.stringify(query, null, 4));
});

router.post('/categories', async (req, res, next) => {
  const query = await pool.query`select * from categories`;
  res.setHeader("Content-Type", 'application/json; charset=utf-8');
  res.status(200).send(JSON.stringify(query, null, 4));
});

module.exports = router

as you can see you can directly call query method and receive your query response all you need is just to call proxyfire method at the top of your route then use it as you wish

here's a complete usage example

const sql = require('mssql');
const { normalizePort } = require('../handlers/handlers');
const {
  SQL_SERVER_USER,
  SQL_SERVER_PASSWORD,
  SQL_SERVER,
  SQL_SERVER_DATABASE,
  SQL_SERVER_PORT
} = require('../configurations/appConfig');

const config = {
  user: SQL_SERVER_USER,
  password: SQL_SERVER_PASSWORD,
  database: SQL_SERVER_DATABASE,
  server: SQL_SERVER,
  port: normalizePort(SQL_SERVER_PORT),
  pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 30000
  },
  options: {
    encrypt: true,
    trustServerCertificate: true, // change to true for local dev / self-signed certs
  }
}

const connection = sql.connect(config);

module.exports = (function () {
  return new Proxy({}, {
    //getting the called property
    get: function (target, prop) {
      //let's get the called function arguments to send it
      return async function () {
        const poolConnection = await connection;
        // Check if the method exists in the object
        if (poolConnection[prop]) {
          // Return a function that invokes the method with the provided arguments
          return poolConnection[prop](...arguments);
        }
        //method unavailable
        return () => null;
      }
    }
  });
})();

by this example you don't need even to call proxyfire at the top of your route module just require and use it

const express = require('express');
const router = express.Router();
const pool = require('../db.js');


router.get('/products', async (req, res, next) => {
  const query = await pool.query`select * from products`;
  res.setHeader("Content-Type", 'application/json; charset=utf-8');
  res.status(200).send(JSON.stringify(query, null, 4));
});

router.post('/brands', async (req, res, next) => {
  const query = await pool.query`select * from brands`;
  res.setHeader("Content-Type", 'application/json; charset=utf-8');
  res.status(200).send(JSON.stringify(query, null, 4));
});

router.post('/categories', async (req, res, next) => {
  const query = await pool.query`select * from categories`;
  res.setHeader("Content-Type", 'application/json; charset=utf-8');
  res.status(200).send(JSON.stringify(query, null, 4));
});

module.exports = router

wow thanks for your hard work it's working perfectly but i still looking forward to get another way — Mahmoud Y3c, Jul 26 '23 at 09:00

Problem invoking mssql module connection in a separate files nodejs

1 Answers1