Error while inserting a row in a transaction

[hyperloo]

Hi, I need one clarification, have one query and a blocker. I recently started using mssql package in my Node-Express Application for accessing the DB.

I went through various docs, implementation and tutorial or how establishing a connection and using it. Here are the few things that I am confused upon.

clarification

Is it a good practice to keep a connection open across the application, i.e. I have my current implementation like this.

    global.sql = await mssql.connect(config, function (err) { /*LOGS*/});

And wherever, I query, I query like

  function getItems(){
      await sql.query`select * from tbl where val in (${values})
   }

Is it the right way of doing things, or should I do it like this?

  function getItems(){
      const sql = mssql.connect()
      await sql.query`select * from tbl where val in (${values})
   }

Query:

I was going through the doc in the NPM readme.

There queries are done in 2 ways:

1. await sql.query`select * from mytable where id = ${value}`

2. await new sql.Request().query('select 1 as number')

What is the difference between both, and which one has to be used when?

Blocker

I am able to run a insert query by

   await sql.query`insert into tbl (val1, val2) values (${item}, ${userId})`
  // sql is the connection for the global variable as mentioned above

I tried creating the above mentioned query in Transaction. For that I have used this

   transaction = new sql.Transaction()
   await transaction.begin();
   let request = new sql.Request(transaction);
   await request.query(/* insert into tbl .... */)

It was working fine, but after some time, when I retried, the query started giving timeout with error Timeout: Request failed to complete in 15000ms

Can't understand why this is happening?

I tried running the same query from the sql server management studio, and it was working as expected

Software versions

• NodeJS: 18.12.0
• node-mssql: 9.0.1
• SQL Server: 2095

[dhensby]

The only thing I can see is you aren't committing your transaction after performing the query.

Please provide a full and self container reproducible code sample for this issue.

[dhensby]

closing as no reply