CODEWITHSUNDEEP
javascriptnode.jssql-serverconnection-pooling
Eslam Ali
Eslam Ali

Reputation: 71

How to make multiple connections at once to a SQLServer database by using node.js and connection pooling?

I'm building a node.js application with SQLServer database using pool connection. it have async functions and each one should save one record to the database after they finish. the problem is only about 10 records from 10 async tasks are inserted and the rest give this error "Already connecting to database! Call close before connecting to different database.".

I tried to edit the configurations of the pool but in vane.

var config = {
            host: "localhost",
            user: "test",
            password: "test",
            database: "testDB",
            server: '************',
            pool: {
                max: 20,
                maxWaitingClients:1,
                fifo:true,
                min: 0,
                idleTimeoutMillis: 30000
            }
        };

const pool = new sql.ConnectionPool(config);
pool.on('error', err => {
    console.error('SQLServer Pool Error: ', err.toString());
});

async writeToDB(System, url, PortListening, RequestTest, time, TestType, Request, Response, responseTime, pool) {
        try {
            var query = "INSERT INTO `testTable` " +
                "(SystemName,URL,PortListening,RequestTest,TestDate,TestType,Request,Response,ResponseTime) " +
                "VALUES " +
                "('" + System + "','" + url + "','" + PortListening + "','" + RequestTest + "','" + time + "','" + TestType + "',N'" + Request + "',N'" + Response + "'," + responseTime + ")";

            await pool.connect();
            let result =await  pool.request().query(query);

            return {success: result};
        } catch (err) {
            console.log(System);
            console.error(err);
            return {err: err};
        } finally {
            pool.close();
         }
    }

It should insert all the rows from all the async tasks without any data loss.

Upvotes: 3

Views: 267

Answers (2)

Eslam Ali
Eslam Ali

Reputation: 71

I found out the answer. I should open the pool "pool.connect()" one time when the application start then close it before the application closes. so the code should be something like: 

var config = {
        host: "localhost",
        user: "test",
        password: "test",
        database: "testDB",
        server: '************',
        pool: {
            max: 20,
            maxWaitingClients:1,
            fifo:true,
            min: 0,
            idleTimeoutMillis: 30000
        }
    };

const pool = new sql.ConnectionPool(config);
pool.on('error', err => {
    console.error('SQLServer Pool Error: ', err.toString());
});

pool.connect();

async writeToDB(System, url, PortListening, RequestTest, time, TestType, Request, Response, responseTime, pool) {
        try {
            var query = "INSERT INTO `testTable` " +
                "(SystemName,URL,PortListening,RequestTest,TestDate,TestType,Request,Response,ResponseTime) " +
                "VALUES " +
                "('" + System + "','" + url + "','" + PortListening + "','" + RequestTest + "','" + time + "','" + TestType + "',N'" + Request + "',N'" + Response + "'," + responseTime + ")";



     let result =await  pool.request().query(query);
    } catch (err) {
        return {err: err};
    } 
}

Upvotes: 1

indolentdeveloper
indolentdeveloper

Reputation: 1313

Omit maxWaitingClients from the pool setting. Let the client take care it of automatically.

Upvotes: 1

Related Questions