An example Node.js application which connects to a SQL server and serves up data via HTTP endpoints.
Before leaving my last client, I was asked if it was possible for Node.js to connect to Microsoft’s SQL Server. The reasoning behind this was that the company wanted to move away from a pure Microsoft stack because of the cost and try to consolidate all of its development into just one language: JavaScript.
Having never used Node.js to connect to a Microsoft server, I took this as a challenge and searched through the NPM website until I found a package called Seriate. Seriate describes itself as “a cross platform node module for Microsoft SQL Server based on node-mssql”. After browsing through the documentation, I decided to give Seriate a try. This blog will demonstrate the process I went through to implement Seriate.
The Process
First things first, I created a local instance of the classic Microsoft Northwind demonstration database using Microsoft’s SQL Express server.
The Northwind database is a typical example of having customers, employees, and orders, and has been used across the board for thousands of code examples.
For this example application, I already had Node.js and NPM installed and was quickly able to obtain the seriate package from the NPM repository using the following command:
npm install --save seriate
In a short period of time, I had a Node application server connecting to a local instance of SQL Express and was able to successfully use Postman to test out the various APIs which I created.
Now to the code:
The application I created was a fairly straightforward application server consisting of a server file, API endpoint files, files specific for connecting with seriate, and files containing raw SQL.
The Server.js file is the main entry point for the application. The server file is where we set up the configuration for the SQL server connection and where each API file is registered. Additional configuration is also set in this file for accessing the request body and handling JSON objects from the requests. Because of the nature of the application, CORS has been enabled on the server so that any endpoint will accept an outside request.
The next layer of files are xxxApi.js files, where the xxx denotes the type of data from the database which will be accessed. These files typically use the app variable from the Server.js file to create GET and POST routes for use with other applications outside of this application.
A third layer is typically named after the business entity which will be manipulated via SQL from the application. These files define the queries and the parameters which will be needed when the endpoint is asked for.
The final layer is the actual SQL file where we have written our SQL queries. If the query needs to have anything sent to it, we have parameterized the query value with an @ sign and defined that value in the previous setup layer.
Final thoughts
Having a background with .NET and having have used both classic ADO connections along with the new Entity Framework methods, I found Seriate was actually a very easy and quick way of connecting to SQL Server.
| Reference: | Seriate: The Glue Between SQL Server and Node.js from our WCG partner Keyhole Software at the Keyhole Software blog. |
