Creating REST API for reading data from Microsoft SQL Server in web browser

Creating REST API on top of Microsoft SQL Server has been around for long time. And there are many ways and technologies to do this.

REST API (or RESTful API) is a application programming interface (abbreviated as API) that complies with REST architectural style and is created to allow communication, information flow and interaction with REST (Representational State Transfare) web services. API is used as an interface for communication between two or more programs (or machines) and provides content required from one side to another side (known as consumer and producer; call/request and response).

Getting data from and to database using API is great for building integration with application and retrieving to client all the information in JSON format only once, instead of every time an action occurs on frontend. Yes there are multiple ways to push data to frontend, it can be entity framework, it can be Dapper, it can be T-SQL with procedures layer, it can be REST API, and many many others.

You can think of an API as a mediator between the users or clients and the resources or web services they want to get. It’s also a way for an organization to share resources and information while maintaining security, control, and authentication—determining who gets access to what. 

Another advantage of an API is that you don’t have to know the specifics of caching—how your resource is retrieved or where it comes from.

In General, what you will need to do is:

1. MSSQL Server installed
2. MSSQL Server configuration (Network pipes, Security, user, sp_configure)
3. Node Js. installed
4. Install npm packages
5. Setup configuration and JS script

First we will create a new API database on SQL Server. After that, we will stage a sample dataset:

USE APITest;
GO

CREATE TABLE dbo.UsersAD
( ID INT IDENTITY(1,1) NOT NULL
,EmloyeeID INT 
,SamAccountName VARCHAR(100)
,DisplayName VARCHAR(200)
,Email VARCHAR(100))

INSERT INTO dbo.UsersAD (EmloyeeID, SamAccountName, DisplayName, Email)
          SELECT 21,'MichelH','Michel Houell','michelh@account.com'
UNION ALL SELECT 22,'NielT','Niel Ty','NielT@account.com'
UNION ALL SELECT 25,'ImmanuelK','Immanuel Kan','ImmanuelK@account.com'
UNION ALL SELECT 30,'BillG','Bill William Gate','BillG@account.com'

Make sure to also do the following important steps on your SQL Server:

Set up and configure the security. In my case, I am creating a single user (Windows or SQL):

USE APItest;
GO

CREATE LOGIN tk123  
    WITH PASSWORD = 'tk123';  
GO  

-- Creates a database user for the login created above.  
CREATE USER tk123 FOR LOGIN tk123;  
GO  

CREATE USER [tk123] FROM LOGIN [sntk\tk123];
GO

The next step, what do you to is go to SQL Server Configuration Manager and in Network configuration for your SQL Server (in my case SQL Server is “MSSQLSERVER2019”) and enable TCP/IP protocol.

In the Services, check for SQL Server Browser service is up and running.

After TCP/IP protocol changes, we need to restart SQL Server service in order for changes to be committed. On SQL Server side, this is what needs to be done.

In next couple of steps, we will install Node.js. Go to their website (https://nodejs.org/en/) and download the nodejs engine. I am using LTS (Long Time Support) version and works flawlessly. After installation is completed, there some some of the steps needed to be created. There will be PATH added to your system environment variable and making Node.js run from any user specified directory.

We will need to create a directory to hold your application, and make that your working directory. So anywhere on your client, create a directory, where running user will have access. Run Node.js command prompt:

Go to your D:\ drive (on windows) or preferred location and run:

mkdir SQLAPI
cd SQLAPI

After this, we will need to initialize the node.js application for this directory. And run:

npm init

and install following node packages:

npm install --save express
npm install --save mssql
npm install  --save msnodesqlv8
npm install --save json-beautify

In this folder SQLAPI, we will now create a *.js (JavaScript) files that will be node.js instructions. So we will create two files, one holding the configuration and connection string to SQL Server and the other one will be application that will retrieve the content.

We will name these two files as:
1) config.js (great article by Phil Factor on Redgate Simple Talk on diving prod/dev environments)
2) ReadApp.js

The content of the config.js will be the following. Make sure you change the connection string and set the user and password or leave to trusted_connection.

var config = {
  production: {
     driver: 'msnodesqlv8',
     connectionString: 'Driver=SQL Server Native Client 11.0;Server=sntk\\mssqlserver2019;Database=APITest;Trusted_Connection=yes'
     } 
};
module.exports = config;

The second file will be called ReadApp.js and will have the previous file reference and all the app.get node.js functions:

// Read functions
const express = require('express'); 
const app = express();
const sql = require('mssql/msnodesqlv8') //mssql with MS driver for SQL Server
var beautify = require("json-beautify");
 
var env = process.env.NODE_ENV || 'production';
var sqlConfig = require('./config')[env];
 
// Start server and listen on http://localhost:2908/
var server = app.listen(2908, function() {
  var host = server.address().address
  var port = server.address().port
 
  console.log("app listening at http://%s:%s", host, port)
});
 
const connection = new sql.ConnectionPool(sqlConfig, function(err){
      if (err){
      console.log(err);
      }
    }
)
 
// Input as Integer
app.get('/UsersAD/EmloyeeID/:empID/', function(req, res) {
  connection.connect().then(pool => { 
    var conn=pool.request()
    var forInteger = /\b\d+\b/i; 
    if (forInteger.test(req.params.empID)) {  
       conn.input('input_parameter', sql.Int, req.params.empID)}
    else {conn.input('input_parameter', sql.Int, 32116)} 
    var string = 'SELECT * FROM dbo.UsersAD WHERE  EmloyeeID  = @input_parameter'
    return conn.query(string)
  }).then(result => {
    let rows = result.recordset
    res.setHeader('Access-Control-Allow-Origin', '*')
	  // Result to URL
   res.status(200).type('JSON').send(beautify(rows, null, 2, 100));
		
	  // result to log
	  console.log(beautify(rows, null, 2, 100));
    connection.close();
  }).catch(err => {
    console.log(err);
    res.status(500).send({
      message: err
    })
    connection.close();
  });
});


 // input as VarChar
 app.get('/UsersAD/SamAccountName/:SamAccountName/', function(req, res) {
  connection.connect().then(pool => { 
    var conn=pool.request()
    conn.input('input_parameter', sql.VarChar, req.params.SamAccountName)
    var string = 'SELECT * FROM dbo.UsersAD WHERE  SamAccountName  = @input_parameter'
    return conn.query(string)
  }).then(result => {
    let rows = result.recordset
    res.setHeader('Access-Control-Allow-Origin', '*')
	
	res.status(200).type('JSON').send(beautify(rows, null, 2, 100));
		
	// result to log
	console.log(beautify(rows, null, 2, 100));
    connection.close();
  }).catch(err => {
    console.log(err);
    res.status(500).send({
      message: err
    })
    connection.close();
  });
});

Once you have changed and save both javascript files, you can now run the node application. In the same folder (SQLAPI) run the following command:

 node ReadApp.js

And open the URL location in your browser type:
localhost:2908/UsersAD/EmloyeeID/22
localhost:2908/UsersAD/SamAccountName/BillG

And in the CMD log the output would be:

In next blog post, we will look into the ability to write to database using API.

As always, all the code is available on Github – tomaztk/MSSQLServerRestAPI.

‹ Getting geo data into SQL Server using API and R

Writing data to Microsoft SQL Server from web browser using REST API and Node.js ›

Tagged with: API, API Calls, javascript, Node.JS, SQL, SQL Server
Posted in Uncategorized