NodeJS with SQL Server Transactions

Back to: Mastering Node.js and SQL Server

Transactions are crucial for maintaining data consistency in a database. In this example, we’ll demonstrate how to work with SQL Server transactions in a Node.js application using the mssql package. We’ll perform a simple transaction that involves inserting records into two related tables.

Prerequisites

1. Node.js and npm: Ensure you have Node.js and npm installed. You can download them from the official website (https://nodejs.org/).
2. SQL Server: Have access to a SQL Server database instance. You can use Microsoft SQL Server or SQL Server Express, depending on your needs.

Step 1: Set Up Your Node.js Project

Create a new directory for your Node.js project and navigate to it in your terminal.

mkdir node-sql-server-transactions
cd node-sql-server-transactions
npm init -y

Step 2: Install Dependencies

Install the mssql package, which will allow us to interact with SQL Server:

npm install mssql

Step 3: Create a Node.js Script

In your project directory, create a JavaScript file, e.g., app.js, to write your Node.js code.

const sql = require('mssql');

// Database configuration
const config = {
  user: 'your_username',
  password: 'your_password',
  server: 'localhost', // Change this to your SQL Server instance
  database: 'your_database_name',
};

// Insert query for table1
const insertQuery1 = 'INSERT INTO Table1 (Name) VALUES (@name)';

// Insert query for table2
const insertQuery2 = 'INSERT INTO Table2 (Table1ID, Value) VALUES (@table1id, @value)';

async function runTransaction() {
  try {
    // Connect to the SQL Server database
    await sql.connect(config);

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

    // Create a new request from the transaction
    const request = new sql.Request(transaction);

    // Set parameters for the first insert
    request.input('name', sql.VarChar, 'John Doe');

    // Execute the first insert query
    const result1 = await request.query(insertQuery1);

    // Get the newly inserted ID
    const table1Id = result1.recordset[0].ID;

    // Set parameters for the second insert
    request.input('table1id', sql.Int, table1Id);
    request.input('value', sql.Int, 42);

    // Execute the second insert query
    await request.query(insertQuery2);

    // Commit the transaction
    await transaction.commit();

    console.log('Transaction committed successfully.');
  } catch (err) {
    console.error('Transaction failed. Rolling back.', err);

    // Rollback the transaction in case of an error
    await transaction.rollback();
  } finally {
    // Close the database connection
    sql.close();
  }
}

// Call the function to run the transaction
runTransaction();

In this script:

• We import the mssql module and configure the database connection with your credentials and database information.
• We define two SQL insert queries for two related tables (Table1 and Table2).
• Inside the runTransaction function, we perform the following steps:
  1. Connect to the SQL Server database.
  2. Begin a transaction.
  3. Create a request object from the transaction.
  4. Execute the first insert query, retrieve the newly inserted ID.
  5. Execute the second insert query with the retrieved ID.
  6. Commit the transaction if all queries succeed. Otherwise, roll back the transaction in case of an error.
• Finally, we call the runTransaction function to execute the transaction.

Step 4: Run Your Node.js Script

Save your app.js file and run your Node.js script using the following command:

node app.js

This script will insert records into two related tables within a transaction. If any part of the transaction fails, it will be rolled back, ensuring data consistency.

Conclusion

In this example, you’ve learned how to work with SQL Server transactions in a Node.js application using the mssql package. Transactions are essential for maintaining data integrity in complex database operations. You can extend this example to handle more complex transactional scenarios as per your application’s requirements.