Firebase functions NodeJS API to perform GET operations on the Microsoft SQL database

Perform GET operations on SQL Server using NodeJS. We’ll create an API and run queries on our SQL database and host this API in Firebase functions.

Photo by Sunder Muthukumaran on Unsplash

A straightforward and to-the-point tutorial on how to create an API with an SQL database.

Prerequisites — Knowledge of Javascript is a must. We’ll be working with technologies like MS SQL, NodeJS, and ExpressJS.

We’ll be going something as shown in the figure below.

We already have an MS SQL database which we’ll connect from NodeJS and run an SQL query that’ll return the related code from our database in JSON format. We’ll be able to send a request from any user device as our APIs will be stored on the cloud using Firebase Functions.

SQL DB:

I won’t be going into details of how to create an SQL database in this article but you’ll need these configurations about our database which you’ll be connecting with:

server: 'IP_OR_LOCAL_HOST', //ip address of the mssql database
username: 'USERNAME', //username to login to the database
password: 'PASSWORD', //password to login to the database
database: 'YOUR_DB_NAME', //the name of the database to connect to
// port: 0000, //OPTIONAL, port of the database on the server
// timeout: 5, //OPTIONAL, login timeout for the server

So, go ahead and configure SQL DB so that you have the above configurations values for your DB.

Please make sure that you’re using Microsoft SQL Server and not MySQL or PostgreSQL or any others as this article is only focused on MS SQL. I use Azure Data Studio to test the connection of my MS SQL, where I am able to successfully make the connection. Not a necessary step but it’s important to checck if a connection can be made usong our computer first, even if your SQL server is locally running or is hosted on a public IP somewhere.

NodeJS REST API:

We’ll build the API step by step.

Setting up the NodeJS project

Since this is the main focus of this article, I’ll start from scratch by creating a new node project. First, cd into the folder, you want to create the project in and run this command in that folder:

npm init -y

This will create a package.json file in your folder. Open this folder in your favorite editor, I‘ve opened it in VSCode, and modified it by adding the lines in bold:

{
"name": "test-api-app",
"version": "1.0.0",
"description": "MS SQL API test app.",
"main": "index.js",
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},
"dependencies": {
"body-parser": "~1.0.1",
"express": "~4.0.0",
"mssql": "^8.0.2"
},
"devDependencies": {
"nodemon": "^2.0.15"
},
"keywords": [],
"author": "ibjects",
"license": "ISC"
}

After modifying the package.json run this command:

npm install

This will create a .lock file and a folder namednode_modules.

Connection & Creating the first API

Create a file named index.js at the root of the project. and add the following code:

const express = require('express');
const bodyParser = require('body-parser');
const app = express();
app.use(bodyParser.json());

//API START\\
app.get('/getCustomers', (req, res) => {
   const cusCode = req.query.cusCode;
   const sql = require('mssql');
   var config = {
        user: 'USERNAME',
        password: 'PASSSWORD',
        server: 'YOUR_SERVER_IP',
        database: 'DB_NAME',
        synchronize: true,  //optional
        trustServerCertificate: true, //optional
   };
   sql.connect(config, function (err) {
        if (err) { console.log(err); return; }
        var request = new sql.Request();
       request.query(`ADD_YOUR_SQL_WITH_HERE_cusCode AS ${cusCode}`, function (err, recordsets) {
             if (err) { console.log(err); return; }
             if (recordsets) {
                res.status(200).send(recordsets);
             }
        })
    })
});
//API END\\
app.listen(3000, () => {
console.log('Server started on port 3000...');
});

We have created an API path named /getCustomers which will get cusCode in the URL as it’s a get call. After that, we have imported the mssql that we installed. Then I’ve created a config object that holds all the required information, make sure to replace it.

After all of this we made the connection request with sql.connect and upon the success we’re creating a sqlRequest to send a request.query, here don’t forget to put your SQL statement. You can also pass ${cusCode} as a parameter. Upon success, you’ll receive recordsets which you’ll send as a res with 200 status response and the recordsets data. And finally, our app will be listening to port 3000 in dev.

Let’s run it by running this command: node index.js

As you can see I’m getting data in response.

Now we can have the same data hosted on the cloud. Let’s first deploy our first API to the cloud.

Deploying API to firebase functions

You need to have a Firebase project created and configured for the web. That means npm install firebase and initialized the firebase.

Make sure you have the firebase command-line tool installed:

npm i firebase-tools

Now you’ll need to activate firebase hosting first and then we’ll deploy functions. Hosting will give us a URL on the cloud. So, type this in terminal:

firebase init hosting

Do the settings as you want. I had a project already so below are my settings:

Now that we have our hosting setup, we now need to set up cloud functions. To do that we'll run this command:

firebase init functions

Keep the settings as shown below.

This has created a funtions folder for us.

This folder has its own mini-app inside with a package.json. This means we can install all dependencies that we want as we did for our localhost.

Update the functions/package.json with the bold lines of code below:

{
"name": "functions",
... Some Other auto generated info
"engines": {
"node": "16"
},
"main": "index.js",
"dependencies": {
    "express": "~4.0.0",
    "firebase-admin": "^9.8.0",
    "firebase-functions": "^3.14.1",
    "mssql": "^8.0.2"
},
"devDependencies": {
"firebase-functions-test": "^0.2.0",
},
"private": true
}

Then cd into the funtionsfolder and run npm install.

Now before we add our first function code, we need to do some edits to the firebase.json file that should be already created in your project. Update it entirely with this:

{
 "hosting": {
  "public": "public",
  "rewrites": [
    {
      "source": "**",
      "function": "app"
    }
  ],
 "ignore": ["firebase.json", "**/.*", "**/node_modules/**"]
  }
}

You’re already familiar with what /getCustomers is, but then app you’ll see in the next step.

Next open functions/index.js and add this code:

//functions/index.js
const functions = require("firebase-functions");
const express = require('express');
const app = express();
//API START\\
app.get('/getCustomers', (req, res) => {
const cusCode = req.query.cusCode;
const sql = require('mssql');
var config = {
        user: 'USERNAME',
        password: 'PASSSWORD',
        server: 'YOUR_SERVER_IP',
        database: 'DB_NAME',
        synchronize: true,  //optional
        trustServerCertificate: true, //optional
   };
sql.connect(config, function (err) {
if (err) { console.log(err); return; }
var request = new sql.Request();
request.query(`ADD_YOUR_SQL_WITH_HERE_cusCode AS ${cusCode}`, function (err, recordsets) {
if (err) { console.log(err); return; }
             if (recordsets) {
                res.status(200).send(recordsets);
             }
        })
    })
});
//API END\\
exports.app = functions.https.onRequest(app);

In the above code, we made a few changes but most of the code is exactly the same as we did for localhost.

First, we’re importing firebase functions. Then we have removed the app.listen and any body-parser part of the code. And finally, we’re exporting our function with the name app as I mentioned firebase.json in a previous step.

To add another route you can do something like:

//... All other code
app.get('/timeStamp', (req, res) => {
    res.send(`${Date.now()}`);
}
//API END\\
exports.app = functions.https.onRequest(app);

The above code will now enable you to have two end-points i.e. /getCustomers and /timeStamp.

Now to test this theory, run this command outside (cd..) of functions folder:

firebase serve --only functions,hosting

This command is basically doing a local emulation.

Now if I open this URL in the browser: localhost:5000/.../getCustomers I’ll get the JSON I needed.

You can also see the localhost:5000/.../timeStamp. It should show you a timestamp.

So, all look good so far and now it’s time to deploy it to production.

firebase deploy

All looks good, now let’s try opening the Function URL in the browser, which will be something like: https://YOUR_SERVER.cloudfunctions.net/app/getCustomers

We have finished the GET part of this tutorial. Other actions like Create, Update, and Delete… I’ll be adding later. For now, using the same concept you can create as many API calls as you want.

As always if you find this helpful share and press the 👏🏻 button so that others can find it too. If you see a typo feel free to highlight it or if you’re stuck drop a comment and I’ll try my best to help you.

buymeacoffee.com/chaudhrytalha

All my tutorials are free but if you feel like supporting you can buymeacoffee.com/chaudhrytalha

Happy Coding 👨🏻‍💻