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 like queries, inserts, updates, and deletions.

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

• A valid user
• Listening for TCP calls
• With 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:

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

Connection Configuration

To establish a connection with our MSSQL database, we must first configure it with our server details. Additionally, there are extra options that may be necessary depending on your database configuration.

Let’s see 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 the database connection:

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();

Trusted Connection

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

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 because msnodesqlv8 is a CommonJS library, which can cause usage issues if we don’t import it with 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 securely and effectively.

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

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);
  }
}

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();
  }
}

Download the Code

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