0

I'm trying to optimize (and safeguard against crashes/errors) my application which will need to execute hundreds or thousands of SQL queries daily, and I'm wondering what the best design/setup is for mssql.

I know it probably wouldn't be efficient to use await sql.connect() and for each query (this seems to be slower as well).

What I've done before is the following:

sql.js

const sql = require('mssql');
const config = require('./util/db-config.js');

async function connect () {
    await sql.connect(config)
}

async function someDatabaseFunction () {
    await sql.query('sql statement').then((table) => {
        //do stuff here
    }
}
module.exports = {connect, someDatabaseFunction}

app.js

const express = require('express');
const {connect, someDatabaseFunction} = require('./sql.js'); 
const app = express();

connect()

app.post('/someroute', async (req, res) => {
    await someDatabaseFunction()
});

app.listen(3000, () => console.log('Running on port 3000!'))

So basically I do const sql = require('mssql') on a global scope (is that what it's called?) in sql.js, and run the connect() function on startup of the app, and then any functions inside sql.js use the global connection pool to execute queries.

This has worked for me before but as I develop for larger scale and more critical operations, I worry about what might happen if the connection to the database breaks/fails... Is there like a "best practice" for this kind of setup?

Andriy M Etcheverry
  • 11
  • 2
  • `await sql.connect()` is not necessarily slow, due to connection pooling, you just need to make sure to close and dispose it after you're finished (and create a new one each time). Make sure you always use `await` – Charlieface Jun 24 '22 at 10:28

2 Answers2

0

You should begin by wrapping your db calls in try/catch statements to be able to catch failed queries and crashes. Then handle them by reseting the connection or rectifying/cancelling the query.

It will still be a lot perfectible, especially if you need to abstract things to scale up, but it should increase a bit the robustness of the application.

Propper error handling is always good practice!

lafusew
  • 161
  • 1
  • 9
  • Ah yes, I forgot to include this in the example code but I do do this, I guess I'm just not sure what the best way of going about resetting the connection is... – Andriy M Etcheverry Jun 24 '22 at 09:41
  • Await for an other db.connect() on db connection errors seems ok. However I didn't go further on my answer because I'm not experienced enough to give you best practices for your specific case. – lafusew Jun 24 '22 at 10:00
0

you probably can get around this issue by doing the following in your index file

/** all of your routes get configured over here*/

connect().then(
 app.listen(3000=>console.log('Running on port 3000!'))
).catch((err)=>{

 console.log("Unable to connect to db", err);
 process.exit(1);
})

basically what this does is we try establishing a connection to the db when the server starts , if not then we gracefully shut down the process.

you ideally want something to restart the application and also set up healthchecks for a lb if you have one to direct traffic else where if the application is not healthy.

see here for restarting your application : Node JS auto restart all forever JS process when server goes down / crashes

for nodejs best practices see here: https://github.com/goldbergyoni/nodebestpractices

Darren
  • 468
  • 3
  • 12