Querying SQL Server with Node.js
Connect, Query, Display and Close
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.
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.
- SQL Server Management Studio is also 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.
- Visual Studio Community Edition 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.
My SQL Server Setup
My SQL Server Instance and Database (Company) with a single table (Employees.)
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.)
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.
- Create a the folder in which you wish to work
- Open VSCode and from the File menu, Open that folder.
- Go to the Terminal Menu and choose New Terminal.
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.
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.
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.
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.
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!
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.