Connect a SQL Server database to a Node.js project

Written by

Walter G.

Published on

November 16 2023

Filed under

database

When you think of a Node.js project you typically think about either a PERN stack or a MERN stack implementation. Meaning you are either working with PostgreSQL or MongoDB as your database of choice.

However, thanks to the tedious module, which is a pure JavaScript implementation of the TDS protocol, you can indeed connect to your Node.js application to a SQL Server database if need be.

This can be a useful approach for those looking to either migrate a .NET application over to a Node.js environment, or for those who absolutely need to use SQL Server but who don't necessarily want to write any C# or VB.NET code.

What is tedious?

The Tedious module is a pure JS implementation of the TDS protocol. The TDS protocol, or Tabular Data Stream Protocol, is the protocol that is responsible for the interaction with a database server and handles the authentication negotiation between the client.

You can install the tedious library from NPM using the following command:

npm i tedious

Though for this example, you won't have to as installing the 'mssql' library will do that for us. I'm just mentioning here for reference.

1.  Install 'mssql' npm library

The mssql NPM library is a SQL Server client for Node.js that supports the Tedious driver mentioned above, and it's the one that we'll be using to connect to our database.

npm install mssql

This is a relatively robust library that can handle everything from basic queries to pool management to transactions. You can find examples for each scenario over here.

For this example though, I'll mainly be focusing on setting it up, configuring it and running basic queries just to start things off.

2. Create a db.js utility file

Let's start off by creating an individual file to handle connecting to our database. 

Your file should look something like the following:

const sql = require('mssql');

const dbConfig = {
  server: 'your server address',
  database: 'name of your database',
  user: 'username',
  password: 'password',
  encrypt: false
};

async function connectToDatabase() {
  try {
    await sql.connect(dbConfig);
    console.log('Connected to SQL Server');
  } catch (error) {
    console.error('Error connecting to SQL Server:', error);
  }
}

module.exports = {
  sql,
  connectToDatabase,
};

We'll start off by creating a configuration object where we'll specify the server address and needed credentials in order to connect to the database.

Because you're dealing with sensitive information, you'll want to implement this logic on the backend of your codebase and of course externalize any sensitive information to your .env files.

For the sake of simplicity though, I'm skipping that step. But don't you skip it, especially not on production code.

The connectToDatabase() function is kept super simple and mainly just calls the connect method passing in the appropriate configuration object. If the connection fails for any reason, you will want to implement your own custom logic based on the connection errors returned.

Note the encrypt property in the configuration and how I've set it to 'false'. This worked for me personally when working on a hosted GoDaddy SQL Server database, however, the official documentation recommends setting that property to true if you are working on Windows Azure.

3. Import function and query

For this example I am using a Next.js application and relying on the api routes feature to make my function calls and connect to the database.

Whichever framework you choose to use however, will follow a similar structure.

First off, let's import the needed modules from our db.js file above. 

import { connectToDatabase, sql } from '../../utils/db';

We'll be relying on the global sql object regardless of how many queries we need to call.

And the following handler method (used in our api route) will handle connecting to the database and running a standard SQL query in order to retrieve data.

export default async function handler(req, res) {
   await connectToDatabase();

  try {
    // Execute a SQL query
    const result = await sql.query`SELECT TOP 10 * FROM PollResult`;

    // Send the query result as JSON
    res.status(200).json(result.recordset);
  } catch (error) {
    console.error('Error executing SQL query:', error);
    res.status(500).json({ error: 'Internal Server Error' });
  }
  }

Once the query is called, the resulting recordset object will contain each of the rows returned back from the query.

Also note the ES6 tagged template literal syntax when adding the query.

4. Tagged template literals

Tagged template literals are an ES6 feature that allow you to parse template literals (those strings enclosed with the backtick symbol) with a function.

const result = await sql.query`SELECT TOP 10 * FROM PollResult`;

In this particular example, sql.query is the function that acts as a tag for the template literal. This function is designed to process the template literal that follows it. Notice how it does not require the parenthesis to be added to the string element.

Because of this syntax all values being passed in as parameters are sanitized against SQL injection.

5. Conclusion

Just a few years ago you really didn't have any way of connecting SQL Server to anything other than a Windows environment. And if you needed to for whatever reason, you typically had to build a middle layer yourself to handle the back and forth communication.

It's definitely great to see the progress being in the cross-platform realm of databases and frameworks.

For me personally, this is a great first step into fully migrating this particular blog, which is written in ASP.NET and is almost 10 years old into something sleeker and more modern, such as a Next.js application.

If you found this article helpful, let me know. And if you had no idea that you could connect a SQL Server database to Node (much as I did) then also let me know.

Comments

No messages posted yet

Add comment