Transaction for prepared statements using Node mssql

Question

I want to create an Express REST API and will use MSSQL for the database part. I use the mssql module and use the PreparedStatement class.

Sometimes my queries have to be transactional, so I have to work with the Transaction class too.

I found some information about prepared statements here and about transactions here but don't know how to execute transactional queries using prepared statements.

Would someone mind explaining how to use the mssql module with prepared statements within transactions? My query function should provide a functionality giving access to write something like this in my query file

begin transaction
run prepared statement one
run prepared statement two
end transaction

So to reproduce the problem: First I created a database with a table called "integer" and a numeric column "value".

Then I created an empty Node/Typescript project with this code:

import sql, { ConnectionPool, PreparedStatement } from 'mssql';

class App {
    private connectionPool: ConnectionPool;

    constructor() {
        this.connectionPool = new sql.ConnectionPool(databaseConfig);
    }

    public run = async (): Promise<void> => {
        try {
            await this.connectionPool.connect();

            const preparedStatement: PreparedStatement = new sql.PreparedStatement(this.connectionPool);

            preparedStatement.input('number', sql.Numeric(18, 0));

            await preparedStatement.prepare('INSERT INTO integer (value) VALUES (@number)');
            await preparedStatement.execute({ number: 1 });
            await preparedStatement.unprepare();

            console.log('done...');
        } catch (error) {
            throw error;
        }
    }
}

(async () => {
    const app: App = new App();
    await app.run();
})().catch(error => {
    throw error;
});

So far so good. Now I want to run two INSERTs within a transaction but the second one passes in a string so I would expect an error and the first one gets rolled back. My updated run function:

public run = async (): Promise<void> => {
    try {
        await this.connectionPool.connect();

        const transaction: Transaction = new sql.Transaction(this.connectionPool);

        const workingStatement: PreparedStatement = new sql.PreparedStatement(transaction);
        workingStatement.input('number', sql.Numeric(18, 0));

        const invalidStatement: PreparedStatement = new sql.PreparedStatement(transaction);
        invalidStatement.input('number', sql.Numeric(18, 0));

        try {
            await transaction.begin();

            await workingStatement.prepare('INSERT INTO integer (value) VALUES (@number)');
            await workingStatement.execute({ number: 1 });
            await workingStatement.unprepare();

            await invalidStatement.prepare('INSERT INTO integer (value) VALUES (@number)');
            await invalidStatement.execute({ number: false });
            await invalidStatement.unprepare();

            await transaction.commit();
        } catch (error) {
            await transaction.rollback();
            console.log('execution failed...');
        }

        console.log('done...');
    } catch (error) {
        throw error;
    }
}

Now I get this error

(node:18416) UnhandledPromiseRejectionWarning: TransactionError: Can't rollback transaction. There is a request in progress.

And I'm not sure if my syntax is wrong.

The link you've posted says "TIP: You can also create prepared statements in transactions (new sql.PreparedStatement(transaction)), but keep in mind you can't execute other requests in the transaction until you call unprepare.", which seems clear. Do you have problems getting this to work at all or integrating it into your `IDatabase` design? — Nickolay, Aug 30 '19 at 15:07
Thanks @ Nickolay. Yes, I need help setting this up. I know I can pass in the transaction to the prepared statement but how would I chain my prepared statement executions? Would you mind providing a sample code containg a transaction with multiple prepared statements? — , Sep 02 '19 at 06:33
FWIW I'm still not sure I understand the your problem, especially given the most recent edit to the question - I think you can simply pass the `transaction` object in place of `this.connectionPool` to the `PreparedStatement`. Perhaps sharing the attempt to use transactions that didn't work would bring some clarity. — Nickolay, Sep 04 '19 at 09:15
Have you tried options from https://stackoverflow.com/questions/15760067/node-js-mysql-transaction? — computercarguy, Sep 04 '19 at 18:45

score 1 · Accepted Answer · answered Sep 04 '19 at 19:28

1

I believe the error you're hitting happens because you didn't call unprepare as suggested in the docs:

keep in mind you can't execute other requests in the transaction until you call unprepare

Calling unprepare on the statement before rolling back the transaction works for me:

try {
  // ...
  await invalidStatement.prepare('INSERT INTO integer (value) VALUES (@number)');
  try {
    await invalidStatement.execute({ number: false });
  } finally {
    await invalidStatement.unprepare();
  }
} catch (error) {
  // rollback here...
}

answered Sep 04 '19 at 19:28

Nickolay

31,095
13
107
185

Thanks for your reply. This works for me :) This is my updated `run` function, do you think this is correct? I think this is a lot of overhead with `trycatch` no? https://pastebin.com/FBDTqNY2 – Sep 05 '19 at 08:59
After that I can reward you the bounty :) – Sep 05 '19 at 08:59
That's what I tested and what works for me. I'm not a fan of the design forcing one to initialize (and then dispose of) prepared statements for each transaction, but it seems a side effect of the way connection pooling works in this library. – Nickolay Sep 05 '19 at 10:33

Transaction for prepared statements using Node mssql

1 Answers1

Linked