Querying SQL Server with Node.js

Connect, Query, Display and Close

gravity well
Nov 2 · 5 min read
Photo by Tobias Fischer on Unsplash

JavaScript’s server-side language, node.js, is a powerful language to use for interacting with databases.

My typical use case in this realm is to have an AWS Lambda function, running node.js, acting as a middle-ware component between my front-end and the back-end database, performing one or more CRUD operations.

Before we can get to the point of creating a middle-ware component, we need to have a foundation from which to work.

In this article we will be going over the minimum requirements to connect to a Microsoft SQL Server database, request data from a table (query) and then close the connection.

Once we have the fundamental methodology, in future articles, we can focus on firming it up, expanding it, and creating an entire solid CRUD component.

Why not show me the whole set of CRUD operations? Because I like to start with something simple and build from there.

Assumptions

I will assume you already have access to a SQL Server database with at least one table that you want to query. If not,

  • SQL Server Express and Developer Editions are free! and can be downloaded here.

I will be writing straight node.js code so will assume you have access to Visual Studio Code (VSCode) or Visual Studio. If not,

  • VSCode is free! and can be downloaded here.

and have node.js installed. If not,

  • Nodejs can be downloaded here.

To keep this article short and to the point, I will not be covering how to install and configure these items. Just what is needed for this article.

The Setup

My SQL Server Setup

My SQL Server Instance and Database (Company) with a single table (Employees.)

SQL Server Instance, Database and Table

Our goal will be to retrieve the EmpId, FirstName, LastName and Title of Employees with a PerformanceRating of 4.

The SQL Query

As written and tested in SQL Server Management Studio (SSMS.)

The SQL Query

Let’s Get Started

Installing the SQL Server Drivers (mssql) for node.js.

I will assume you are already setup to run node.js. If not, here is a great tutorial for getting set up using VSCode.

  1. Create a the folder in which you wish to work
Opens PowerShell

4. In the terminal window, type npm init and accept the defaults to create the package.json file.

5. The important step. Type npm install mssql to install the drivers. This sets up a node_modules folder you may want to explore.

We are ready to roll!

Code and Result

Since our package.json is using index.js as the main file, create a file called index.js. (Feel free to name the file as desired and modify your package.json.)

The entire code and result is below. Look over this code, read the information below and then type it in to your index.js.

Explanation

First we accessed the database drivers for SQl Server, line 3.

Next, lines 6–11, we set up the configuration required to tell our code how to find our database instance, the necessary credentials to access it and the database we wanted to access.

The next step, line 14, makes the connection. We could have done just this and stopped, skipping lines 19–31, if we wanted to test the connection first. I actually recommend you do this if you have trouble. If you have typed in the query code, just comment it out.

To create the query, we create a Request Object. We specify the required parameters for the query method of the Request Object.

The last thing we do is to close the connection. Keep it open if you want to perform more operations.

Viola! Data

The Data returned is a actually a relatively complex object. To work with this in more detail, which you will want to do, you will need to parse this out.

Notice line 29 (just added) and the output of the raw data Object.

Raw data.

Conclusion

To consolidate our steps, we installed the drivers, specified what to connect to and how we wanted to connect to it. Opened the connection, queried and closed the connection.

Could we do more? Could it be more robust. Oh yes!

In future articles we will be performing not only additional CRUD operations but getting this in to an AWS Lambda function, connecting a front-end using the AWS JavaScript SDK and, essentially, ending up with a full-stack application.

We have just scratched the surface of a lot of power.

Thank you for taking the time to read and code along.

Problems or questions on any of the items mentioned, send a question. My goal is your success.

Supplementary Items

JavaScript in Plain English

Learn the web's most important programming language.

gravity well

Written by

Long time Software Developer, Trainer, Consultant. Keeping up to date. I’ve noticed in over 26 years of programming, one’s current skills have a shelf life.

JavaScript in Plain English

Learn the web's most important programming language.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade