I recently was working on a project that required interacting with a MSSQL database through a Node application. I waded through the depths of the internet to piece together my solution, taking insights from a variety of published solutions that didn’t quite meet my requirements. After some cycles of frustration/exhaustion, my Node API is now talking to my MSSQL server happily, and I can lay out the solution I came to with explanations for my future self (and anyone else who may need to do something similar).
Before I get into the implementation details, it’s probably important to recognize what some of my constraints were going into the project. First, the goal of (this part of) the project was to use Node to connect to a SQL Server database, create a users table, and perform basic lookups and insertions to lay the foundation for user authentication. Before I knew anything about how to accomplish my goal of interacting with my database using Node, I knew that my base tech stack was going to look like this:
- Node: web API backend
- Express: web framework for Node
- Passport: authentication middleware for Node
- MSSQL: database server
Interacting with SQL in Node
It became clear very quickly that SQL and Node are not used together nearly as often as relational databases such as MongoDB with Node. The documentation/tutorial situation on the topic was thus disparate and generally lacking. There are several packages published on npm with the goal of connecting and interacting with a SQL server and database in Node.
I will make it clear that my main priority for choosing libraries was documentation. It would not have been a good use of my time, in this particular project, to have to read library source code to understand how to accomplish basic tasks. After being led down the google search rabbit hole for this list, I ended up landing on tedious, since it is officially supported by Microsoft and seemed to have decent documentation. I ended up finding express4-tedious through one of the Microsoft/Azure code samples, and this seemed promising but had no documentation past a very basic use case. In browsing github issues for tedious, I came across some survey results that involved input from users who use Node to interact with SQL server (bingo). Despite the fact that there were a relatively small amount of total responses and the responder audience was clearly skewed by those who were already exposed to tedious in some sense, it was helpful to see how others are solving similar problems.
The results showed that tedious was the most popular driver used to connect Node apps to SQL server, but I noticed some responses that indicated that responders were using tedious via sequelize. Sequelize is a promise-based ORM for Node that supports mssql (among others), and its extensive documentation and even some blog tutorials were enough to convince me.
It is important to note that sequelize is intended for use WITH a mssql driver, so tedious and sequelize are used together:
npm install tedious sequelize
Connecting to a SQL Server and Database
Ok, I still didn’t have much other than a starting point with tedious and sequelize. To begin with, I needed to create a connection to the SQL server. First I created a new Sequelize instance in my main app.js Node file and tested the connection using the sequelize authenticate method:
Cool, now I know I can connect to the database. Next step is to do something useful with it…
Sequelize has the concept of database models, where we can define the shape of the data we are going to store in our database. The models correspond to tables in the database. In my case, I knew I needed a user table so I created a models folder in my project and added a user.js file to this folder. Here I defined my User model (and therefore my user table).
Since I will likely have other models as well as User eventually, I need a place to bring together all the models in one place. In the models folder, I made an index.js file to do exactly this.
I use the sequelize define method as well as sequelize types to create our user definition. The entries (email, password, etc) will correspond to columns in my user table in my database. Then I load all the models in the models folder into a single exported object in models/index.js.
So now I have
- Two installed packages (sequelize, tedious)
- A test connection using sequelize
- A user model
But if I look at my database it’s still empty, so I need to create the user table in the database. Since I have my model structure, I can hand off the work to sequelize using the sync method. This method will sync (create tables for) all models that aren’t already in the database.
In my case, I’m using Express (specifically the Express generator) to set up my Node application so the best place to sync the database is before the server starts listening to its designated port. With the Express generator, this happens in the bin/www file:
Now, I can interact with my database and tables through the models export from anywhere in the Node app.
Querying the Database
Now that I have my models set up and my database synchronized, I can query the database tables anywhere in the app by importing models. For example:
The methods findOne and create are both provided by the Sequelize library, and return model instances.
I will do a follow up post with more details on implementing authentication using Node, Express, Passport, Sequelize, and MSSQL. But for now, this is the basic gist of how to get going with connecting to, setting up tables in, and querying a database.