Node.js SQL Server: Transaction

Summary: in this tutorial, you will learn how to perform an SQL Server transaction using a Node.js application.

This tutorial begins where the Deleting data from a table using Node.js tutorial left off.

Setting up sample data

Step 1. Launch SQL Server Management Studio (SSMS) to connect to the BookStore database.

Step 2. Execute the following statements to insert data into the Books, BookAuthors, Customers, and Inventories tables:

-- Insert books into the Books table
INSERT INTO Books (Title, Publisher, ISBN, PublishedDate)
VALUES ('The Adventures of Tom Sawyer', 'American Publishing Company', '978-0-123456-47-2', '1876-01-01'),
       ('The Adventures of Huckleberry Finn', 'Chatto & Windus', '978-0-123456-48-9', '1884-12-10'),
       ('A Connecticut Yankee in King Arthur''s Court', 'Charles L. Webster And Company', '978-0-123456-49-6', '1889-04-10'),
       ('The Prince and the Pauper', 'James R. Osgood & Co.', '978-0-123456-50-2', '1881-12-01'),
       ('Life on the Mississippi', 'James R. Osgood & Co.', '978-0-123456-51-9', '1883-02-01');

-- Get the generated BookID values for the inserted books
DECLARE @BookID1 INT, @BookID2 INT, @BookID3 INT, @BookID4 INT, @BookID5 INT;

SELECT @BookID1 = BookID FROM Books WHERE ISBN = '978-0-123456-47-2';
SELECT @BookID2 = BookID FROM Books WHERE ISBN = '978-0-123456-48-9';
SELECT @BookID3 = BookID FROM Books WHERE ISBN = '978-0-123456-49-6';
SELECT @BookID4 = BookID FROM Books WHERE ISBN = '978-0-123456-50-2';
SELECT @BookID5 = BookID FROM Books WHERE ISBN = '978-0-123456-51-9';

-- Insert records into the BookAuthors table
INSERT INTO BookAuthors (BookID, AuthorID)
VALUES (@BookID1, 6),
       (@BookID2, 6),
       (@BookID3, 6),
       (@BookID4, 6),
       (@BookID5, 6);


-- Insert inventories
INSERT INTO Inventories (BookID, Qty)
SELECT BookID, ABS(CHECKSUM(NEWID()) % 101) + 100
FROM Books;Code language: SQL (Structured Query Language) (sql)

Perform a transaction

Step 1. Create a new file called transaction.js within the project directory.

Step 2. Define a function createOrder in the transaction.js module that does the following steps:

Check the inventory against the order quantity in the Inventories table.
Create an order by inserting data into the Orders and OrderDetails table.
Reduce the inventory of the book in the Inventories table.

import sql from 'mssql';
import { config } from './config.js';

async function createOrder(customerId, bookId, quantity, price, orderDate) {
  try {
    // Connect to the database
    const pool = await sql.connect(config);

    // Begin transaction
    const transaction = new sql.Transaction(pool);
    await transaction.begin();

    try {
      // Check inventory
      const inventoryResult = await transaction
        .request()
        .input('BookID', sql.Int, bookId).query(`
          SELECT Qty
          FROM Inventories
          WHERE BookID = @BookID
        `);

      const currentQty = inventoryResult.recordset[0]?.Qty;

      if (!currentQty || currentQty < quantity) {
        throw new Error('Not enough inventory to fulfill the order');
      }

      // Insert into Orders table
      const orderResult = await transaction
        .request()
        .input('CustomerID', sql.Int, customerId)
        .input('OrderDate', sql.DateTime, orderDate)
        .input('TotalAmount', sql.Decimal(10, 2), price * quantity).query(`
          INSERT INTO Orders (CustomerID, OrderDate, TotalAmount)
          OUTPUT INSERTED.OrderID
          VALUES (@CustomerID, @OrderDate, @TotalAmount)
        `);

      const orderId = orderResult.recordset[0].OrderID;

      // Insert into OrderDetails table
      await transaction
        .request()
        .input('OrderID', sql.Int, orderId)
        .input('BookID', sql.Int, bookId)
        .input('Quantity', sql.Int, quantity)
        .input('Price', sql.Decimal(10, 2), price).query(`
          INSERT INTO OrderDetails (OrderID, BookID, Quantity, Price)
          VALUES (@OrderID, @BookID, @Quantity, @Price)
        `);

      // Update Inventories table
      await transaction
        .request()
        .input('BookID', sql.Int, bookId)
        .input('Quantity', sql.Int, quantity).query(`
          UPDATE Inventories
          SET Qty = Qty - @Quantity
          WHERE BookID = @BookID
        `);

      // Commit transaction
      await transaction.commit();
      console.log('Order created successfully');
    } catch (err) {
      // Rollback transaction on error
      await transaction.rollback();
      throw err;
    }
  } catch (err) {
    console.error('Error creating order:', err);
  }
}

export { createOrder };Code language: JavaScript (javascript)

How it works.

First, import sql object from mssql package and config object from the config.js module:

import sql from 'mssql';
import { config } from './config.js';Code language: JavaScript (javascript)

Second, define a function createOrder() that creates an order for a customer based on the ordered book specified by book id, quantity of books, price, and order date.

async function createOrder(customerId, bookId, quantity, price, orderDate) {
    //...
};Code language: JavaScript (javascript)

Third, connect to the database using the provided configuration object:

const pool = await sql.connect(config);Code language: JavaScript (javascript)

Fourth, begin a transaction by creating a new Transaction object and calling the begin() method.

const transaction = new sql.Transaction(pool);
await transaction.begin();Code language: JavaScript (javascript)

Fifth, check the inventory by the book ID and return the inventory quantity of the book:

const inventoryResult = await transaction
  .request()
  .input('BookID', sql.Int, bookId).query(`
          SELECT Qty
          FROM Inventories
          WHERE BookID = @BookID
        `);Code language: JavaScript (javascript)

Sixth, if the inventory quantity is less than the order quantity, then issue an error:

const currentQty = inventoryResult.recordset[0]?.Qty;

if (!currentQty || currentQty < quantity) {
  throw new Error('Not enough inventory to fulfill the order');
}Code language: JavaScript (javascript)

Seventh, insert a new row into the Orders table:

const orderResult = await transaction
  .request()
  .input('CustomerID', sql.Int, customerId)
  .input('OrderDate', sql.DateTime, orderDate)
  .input('TotalAmount', sql.Decimal(10, 2), price * quantity).query(`
          INSERT INTO Orders (CustomerID, OrderDate, TotalAmount)
          OUTPUT INSERTED.OrderID
          VALUES (@CustomerID, @OrderDate, @TotalAmount)
        `);Code language: JavaScript (javascript)

Eighth, get the inserted order ID:

const orderId = orderResult.recordset[0].OrderID;Code language: JavaScript (javascript)

Ninth, insert a new row into the OrderDetails table:

await transaction
  .request()
  .input('OrderID', sql.Int, orderId)
  .input('BookID', sql.Int, bookId)
  .input('Quantity', sql.Int, quantity)
  .input('Price', sql.Decimal(10, 2), price).query(`
          INSERT INTO OrderDetails (OrderID, BookID, Quantity, Price)
          VALUES (@OrderID, @BookID, @Quantity, @Price)
        `);Code language: JavaScript (javascript)

Tenth, update the Inventories table by reducing the inventory quantity by the quantity ordered:

await transaction
  .request()
  .input('BookID', sql.Int, bookId)
  .input('Quantity', sql.Int, quantity).query(`
          UPDATE Inventories
          SET Qty = Qty - @Quantity
          WHERE BookID = @BookID
        `);Code language: JavaScript (javascript)

Eleventh, commit the transaction by calling the commit() method of the transaction object:

await transaction.commit();Code language: JavaScript (javascript)

Twelveth, if any error occurs during the transaction, roll it back by calling the rollback() method of the Transaction object:

await transaction.rollback();Code language: JavaScript (javascript)

Additionally, throw an error:

throw err;Code language: JavaScript (javascript)

Step 3. Modify the index.js file to use the createOrder function:

import { createOrder } from './transaction.js';

try {
  await createOrder(1, 1, 5, 9.99, new Date(2024, 6, 16));
} catch (err) {
  console.log(err);
}Code language: JavaScript (javascript)

How it works.

First, import the createOrder function from the transaction.js module:

import { createOrder } from './transaction.js';Code language: JavaScript (javascript)

Second, create a new order by calling the createOrder function:

try {
  await createOrder(1, 1, 500, 9.99, new Date(2024, 6, 16));
} catch (err) {
  console.log(err);
}Code language: JavaScript (javascript)

Step 4. Open your terminal and run the following command to execute the index.js file:

npm startCode language: JavaScript (javascript)

Download the project source code

Summary

Call transaction.begin() method to start a transaction.
Commit a transaction by calling the transaction.commit() method to commit a transaction.
Rollback a transaction by calling the transaction.rollback() method to roll back a transaction.

Was this tutorial helpful?