How to use a MSSQL database with Node.js

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

It offers a simple interface to perform operations such as queries, inserts, updates, and deletions.

To make it work you will need to have an accessible MSSQL database, and configured with

• Valid user

• TCP listening
• SQL Browser enabled

Connecting 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, you 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. While less common than Tedious, it works wonderfully.

Connection Configuration

To establish a connection to our MSSQL database, we must first configure it with the data from our server. In addition, there are additional options that may be necessary depending on your database configuration.

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

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

::: tip In this example, replace 'your_user', 'your_password', 'localhost', and 'your_database_name' with the specific values from your environment. :::

Establishing the Connection

Once we have our configuration, we can use mssql to establish the 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 finished
    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 connectAndQuery function 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 is best to install the msnodesqlv8 driver for node-sql.

npm install msnodesqlv8

Now you 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 the file of our app, we need to change the import because msnodesqlv8 is a CommonJS library, which causes usage problems if we do not import it as 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 fetchData() {
  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 query results will be printed to the console.

Parameterized Query

Parameterized queries are useful to avoid 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 used in the query to filter the results by the specified name.

Transactions

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

async function performTransaction() {
  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.