Connecting to Microsoft SQL Server in NodeJS Applications

by Michael Szul on

Although I have familiarity with a lot of programming languages, in my professional career, most of my jobs have centered on ASP.NET and C# development (since roughly 2004). Being entrenched in the Microsoft ecosystem for 15 years means that Visual Studio, IIS, and SQL Server are all a part of the application architecture.

Microsoft has shown a significant love for Node.JS and JavaScript in recent years, and personally, I have done a lot of TypeScript and JavaScript development between the Bot Framework and the Metron front-end library tools and framework. I still love the entire Microsoft ecosystem though, using Visual Studio Code for editing, and still building web applications that target IIS and SQL Server.

Recently, I needed to prototype some web services, and instead of spinning up an entire ASP.NET Web API application, I decided to go smaller and simpler with a Restify API application, but because our ecosystem is Microsoft-based, I needed it to connect to a SQL Server back-end, and I was happy to see that there has been a lot invested in getting more traditional Microsoft applications playing nice with Node.JS. Hard to believe that when Node.JS first launched, it wasn't available for Windows.

We start by installing the "mssql" module:

npm install mssql --save

I'm going to use TypeScript, so let's grab the typings module as well:

npm install @types/mssql --save-dev

I actually started this application with a handful of tests (in straight JavaScript) to check connectivity. Here's an example:

const assert =  require("assert");
      const { config } = require("dotenv");
      const { ConnectionPool } = require("mssql");
      const exams = require("../dist/models/exam");
      
      config();
      
      const c = {
          user: process.env.USER,
          password: process.env.PASSWORD,
          server: process.env.SERVER,
          database: process.env.DATABASE
      };
      
      describe("SQL Server connection tests", () => {
          it("should connect to the database", async () => {
              const pool = await new ConnectionPool(c).connect();
              assert.notEqual(pool, null);
              pool.close();
          });
      });

You can see that we are using the "assert" package for tests, and we installed the "dotenv" package to add to our environment variables. We are also including the ConnectionPool object from the "mssql" module.

The first thing we do is call config() so that our .env file is added to our environment variables.

Next, we create a configuration object. We need this to pass our connection string to the connection pool. The process.env.* properties are key/value pairs in our .env file.

In the test block, describe() and it() are a part of the "assert" package for testing. Our code starts with the creation of the connection pool:

const pool = await new ConnectionPool(c).connect();

We await the asynchronous connect() function that comes off of the new connection pool object, and we pass the configuration for the connection string to the connection pool.

The next two lines are just checking that the pool isn't null, and then closing the pool. Make sure you close the pool, or your tests won't complete.

assert.notEqual(pool, null);
      pool.close();

What about selecting data? Let's do another test:

it("should return results", async () => {
          const pool = await new ConnectionPool(c).connect();
          const result = await pool.request()
              .query("select top 10 * from Exam");
          assert.notEqual(result, null);
          pool.close();
      });

The difference between this test and the previous one is the query:

const result = await pool.request()
          .query("select top 10 * from Exam");
      });

Here we create a request to the connection pool, and call query() on it, passing in a simple query. The result will contain a couple of properties you can investigate: One of which is a recordset. This recordset is an object that contains an array of the results.

Here's another example (that isn't a test) passing in parameters:

export async function fetch(ComputingID?: string, ObjectTypeID: number = 3, AttributeTypeID: number = 26, StudentActionPause: number = 11): Promise<Exam[]> {
          try {
              const pool = await new ConnectionPool(CONNECTION).connect();
              const result = await pool.request()
                  .input("CompID", VarChar, ComputingID)
                  .input("ObjectTypeID", BigInt, ObjectTypeID)
                  .input("AttributeTypeID", BigInt, AttributeTypeID)
                  .query(`SELECT DISTINCT
                              e.*
                              FROM [MedEdTesting].[dbo].[Exam] e
                              WHERE
                                      e.[StudentCompID] = @CompID
                                      AND e.[ObjectTypeID] = @ObjectTypeID
                                      AND a.[AttributeTypeID] = @AttributeTypeID
                                  Order by
                                  e.DateToClose ASC
                                  ,e.ExamFeedbackClosed ASC`);
              pool.close();
              return Promise.resolve(result.recordset);
          }
          catch(e) {
              return Promise.reject(e);
          }
      }

Here's we're using TypeScript, so you'll notice the type definitions. Since we are using await within the function, the function needs to be async, which also means we need to return a Promise. Our Promise has a generic representing an array of Exam objects. We use input() to add parameters to the SQL statement, and use the @ inside of the statement before each parameter that is passed in. Once the query is executed, we resolve the Promise, and pass back the record set from the result.

The beauty of using TypeScript is that you can create an interface to represent the model much the same way you create properties on a model file in .NET's Entity Framework. This allows you to return typed objects that you can easily verify and work with in any calling function.