Creating REST API with NodeJS and SQL Server/Azure SQL

SQL Server has built-in JSON operators that transform table data to JSON and extremely simplify implementation of NodeJS REST API that read data from the database. Here you can see how it works in action.

Most of the modern web applications use some kind of REST API that read data from database and provide results formatted as JSON to some clients. One of the biggest problem in web development is getting results from database and returning these results as JSON.

Usually, you would execute some query that fetches the data from database (for example SELECT * FROM Product), and then map this to some objects and serialize objects as JSON response that will be returned to the client (for example Angular or React app).

If you are building an app that uses SQL Server 2016+ or Azure SQL as storage layer, then you can use a new SQL syntax that instructs SQL Server to return results formatted as JSON instead of tabular stream:

SELECT * FROM Product
FOR JSON PATH

FOR JSON query option is designed to simplify development of REST API for Single-page applications. Now you can execute any SQL query, add FOR JSON PATH clause, and you will get the JSON that you need to return by some REST API.

As an example, if you are creating NodeJS REST API using Express framework, you don’t need to worry about data access, create model or view model object for every REST API call — just execute a query, tell SQL server or Azure SQL that you want the results to be returned in JSON format and return the JSON as a result of your API call.

Let’s see this in action.

Setup environment

I will assume that you are familiar with NodeJS, Express, and SQL Server, and that you have already downloaded and installed all packages, created a minimal app, and a database with a table. If you are not familiar with NodeJS and Express you might take a look at Linda’s beginner guide.

First you will need a data access middle-ware that executes queries on database and fetches results. You would need to open NPM and get the following package:

npm install express4-tedious --save

This package is a data access middleware that enables you to execute SQL queries using Tedious SQL Server driver in Express4 NodeJS application.

In order to initialize data access middleware, you need to add sql method to your request when you initialize express app and routes:

var express = require('express');
var tediousExpress = require('express4-tedious');
var app = express();
var connection = { ... };
app.use(function (req, res, next) {
    req.sql = tediousExpress(connection);
    next();
});

This sql function will be called whenever you want to execute some SQL query from your app. Once you add this code, you are able to easily create REST API in your Express application

Create REST API

In Express applications, you have a router that waits for a user to call some URL and then executes a function that will handle HTTP request mapped to that URL, and return some data to the user. The following router waits for a user to invoke /Products Url, and then process the request:

router.get('/Products', function (req, res) {
     req.sql("select * from Product for json path")
         .into(res);
});

The interesting thing is that you need just one statement to execute SQL query and return results to the user.

You can notice the sql() method that prepares the SQL query that you want to execute in database and into() method puts the results returned by SQL query into response that will be returned to the user.

Just one statement is required to convert results of your SQL query to response of REST API!

Or, imagine that you need to get an information about the product by id. The REST API that executes SQL query that selects one product by id, return result as JSON text. The function of the router would look like:

router.get('Product/:id', function (req, res) {
   req.sql("select * from Product where id = @id for json path, without_array_wrapper")
       .param('id', req.params.id, TYPES.Int)
       .into(res, '{}');
});

In this example, I’m constructing the SQL query that will get the product by id, add the parameter retrieved from request, and put the results into response.

Conclusion

FOR JSON clause in SQL Server and Azure SQL extremely simplifies development of REST API for NodeJS developers who work with SQL Server or Azure cloud. The only things that you need to simplify your apps are:

1. The latest version of SQL server or Azure SQL Database.
2. Express4-Tedious connector that simplifies data access for NodeJS applications that are working with SQL Server.

You can find example of simple todo NodeJS/Express app that is working with SQL server on the official SQL Server GitHub samples.