How to use a MSSQL database with Node.js

The mssql module is a Node.js library designed to interact with MSSQL databases (Microsoft SQL Server) asynchronously.

It offers a simple interface for performing operations such as queries, inserts, updates, and deletions.

For it to work, you must have an accessible MSSQL database configured with:

• Valid user
• Listening for TCP calls
• SQL Browser enabled

How to Connect Node.js to MSSQL

First, you need to install the mssql module in your project. You can do this using npm:

npm install mssql

To connect Node.js with Microsoft SQL Server, we can use different TDS drivers. The two main ones are:

• Tedious: This driver is written in pure JavaScript and is compatible with Windows, macOS, and Linux. It is the default driver for Node.js and is widely used in applications running on various platforms.
• Microsoft / Contributors Node V8 Driver for Node.js for SQL Server (v2): This driver is native and optimized for 64-bit Windows or Linux/macOS. Although it is less common than Tedious, it works wonderfully.

Connection Configuration

To establish a connection with our MSSQL database, we first need to configure it with our server details. Additionally, there may be additional options that could be necessary depending on your database configuration.

Let’s look at a basic configuration example using Tedious, which is the default driver.

const dbConfig = {
  user: 'your_user',
  password: 'your_password',
  server: 'localhost',  // It can be an IP address or server name
  port: 1433,
  database: 'your_database_name',
  dialect: "mssql",
  options: {
    encrypt: false,
    trustServerCertificate: true,
    trustedConnection: true,  
  },
};

Establishing the Connection

Once we have our configuration, we can use mssql to establish a connection to the database:

import sql from 'mssql';

import dbConfig from './dbConfig.mjs';

// Function to connect and perform a query
async function connectAndQuery() {
  try {
    // Connect to the database
    await sql.connect(config);

    // SQL Query
    const result = await sql.query`SELECT * FROM YourTable`;

    // Print results
    console.dir(result);

  } catch (err) {
    // Handle errors
    console.error('Error trying to connect:', err);
  } finally {
    // Close the connection when done
    sql.close();
  }
}

// Call the function to connect and query
connectAndQuery();

In this example:

• config contains the connection details, such as user, password, server, and database.
• The function connectAndQuery is responsible for connecting to the database, executing a query (SELECT * FROM YourTable), and displaying the results.

Trusted connection

If you want to use Integrated Security / Trusted Connection, it’s best to install the msnodesqlv8 driver for node-sql.

npm install msnodesqlv8

Now change your configuration file to the following

const sql = require('mssql/msnodesqlv8');

var dbConfig = {
  server: 'localhost',
  port: 1433,
  database: 'course',
  driver: "msnodesqlv8",
  options: {
    trustedConnection: true,    
  }
}

In our app file, we will need to change the import statements because msnodesqlv8 is a CommonJS library, which can cause issues if not imported using require from node:module.

So it would look something like this,

import { createRequire } from 'node:module';
const require = createRequire(import.meta.url);
const sql = require('mssql/msnodesqlv8');

import dbConfig from './dbconnection.mjs';

async function connectAndQuery() {
    try {
      // rest of the content
      ///...
}

SQL Queries

Now that we are connected to our MSSQL database, we can execute SQL queries to interact with the data. mssql provides methods to perform queries safely and effectively.

Simple Query

For a basic query that fetches all records from a table, we can use:

async function queryData() {
  try {
    const result = await sql.query`SELECT * FROM YourTable`;
    console.dir(result);
  } catch (error) {
    console.error('Error executing the query:', error);
  }
}

In this example, YourTable represents the name of the table in your database. The results of the query will be printed to the console.

Parameterized Query

Parameterized queries are useful to prevent SQL injection attacks and to provide a safer and more efficient way to execute queries with variable values.

async function parameterizedQuery() {
  try {
    const name = 'John';
    const result = await sql.query`SELECT * FROM YourTable WHERE Name = ${name}`;
    console.dir(result);
  } catch (error) {
    console.error('Error executing the parameterized query:', error);
  }
}

In this example, name is a parameter that is used in the query to filter results by the specified name.

Transactions

Transactions are important to ensure data integrity when performing operations that need to be atomic.

async function executeTransaction() {
  try {
    await sql.connect(config);
    const transaction = new sql.Transaction();
    await transaction.begin();

    const result1 = await transaction.request().query('INSERT INTO YourTable (field1, field2) VALUES (value1, value2)');
    const result2 = await transaction.request().query('UPDATE AnotherTable SET field = new_value WHERE condition');

    await transaction.commit();
    console.log('Transaction completed.');
  } catch (error) {
    console.error('Error executing the transaction:', error);
    await transaction.rollback();
  } finally {
    sql.close();
  }
}

In this example, a transaction is created where two queries are executed. If any of the queries fail, the transaction is rolled back to maintain data integrity.

Download the code

All the code from this post is available for download on Github