DB Write-Back Part 4: Build a Standalone JavaScript Program to Query SQL Azure

Querying a database from JavaScript requires us to employ a few new concepts and a library or two. JavaScript was initially designed to render HTML in browsers, but the language has been augmented over time to be capable of doing work on servers as well. Connectivity to a database server is asynchronous and must be executed on the server side.

Before we build a write-back extension, we will build a stand-alone JavaScript web app that queries a SQL Server. This should provide the reader with a simplified code base to build understanding. Once we have that nailed down, we will bring it into our extension.

Configuring Local Machine Infrastructure

To set up the infrastructure specific to this article on your local machine, take the following steps:

  • Create a new part4 directory under the project directory from part1-3.
  • Open a command prompt (terminal) in the new directory.
  • From the command prompt runnpm install express-generator -g

    The -g flag means to do a global rather than local installation. Express generator will quickly create an application skeleton for us, saving a lot of time building files and directories from scratch.

  • Type “express –no-view dbjs” (those are two dashes preceding no-view) … at the command prompt. Express will create a directory called dbjs and a skeleton project within the new directory.

  • Stop the node.js server process that you were running for part 3. We will start a new web server below, and it will source its web content from a different directory structure.

  • Follow the instructions that Express provides (this is for Windows; other platforms will be slightly different):

    change directory:

    > cd dbjs

    install dependencies:

    > npm install

    run the app:

    > npm start

  • Now open a browser to http://localhost:3000 . You should see a simple “Welcome to Express” page.

  • Close the browser page and CTRL+C in the command line to stop the node web server you’ve just tested.

  • We’ll run our node server with nodemon so that we don’t have to restart it manually every time we make a change to our server side code, so in the command line, run:

    npm install nodemon -g

  • We’ll need it in a minute, so install the Microsoft SQL Server client for Node.js by runningnpm install mssql” from the command prompt. NPM (node package manager) is the “world’s largest software library” and this command line utility will install or update whatever libraries you need to use for your project.

We will use SQL Azure as our database. If you do not already have one, follow these instructions to set up a sample database. Don’t forget to create a firewall rule to enable connectivity to the Azure SQL instance from your current network. Also, don’t forget to save that firewall rule once you have it configured. Of course you can use a local or on-premise instance of SQL Server if you would prefer.

Adding Database Query Code

The necessary files can be copied from github to the directory we created in the infrastructure steps above. Place db.js and appDB.js in the javascripts subfolder of the public folder.

Edit the index.html file adding the following appDB.js script source line to the head and adding the following new div to the body.

<!--add this to the head section of index.html-->
<script src="./javascripts/appDB.js"></script> 
<!-- Add this to the bottom of the body of the index.html file-->
<div>
  <span id="result">This text should be replaced with data.</span>
</div>

So index.html will look like this:

<html>

<head>
  <title>Express</title>
  <link rel="stylesheet" href="/stylesheets/style.css">

  <!-- jQuery -->                                                    
  <script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
  
  <script src="./javascripts/appDB.js"></script>  <!-- NEW  -->
</head>

<body>
  <h1>Express</h1>
  <p>Welcome to Express</p>

  <!-- NEW  -->
  <div>
    <span id="result">This text should be replaced with data.</span>
  </div>

</body>

</html>

Either replace app.js with the one from github or add these lines of JavaScript above the export (last) line:

const db = require("./public/javascripts/db");
app.post("/getData", db.query);

App.js will look like this. All but two lines were automatically created by Express Generator:

var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');

var indexRouter = require('./routes/index');
var usersRouter = require('./routes/users');

var app = express();

app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/', indexRouter);
app.use('/users', usersRouter);

const db = require("./public/javascripts/db");
app.post("/getData", db.query);

module.exports = app;

Rename the credentialsExample.js file to credentials.js, and edit the file to add your credentials and Azure SQL database name.

Now start the app with “nodemon app.js at your command line in the directory of our new web app.

Browse to http://localhost:3000, and if all is configured correctly you should see this:

What have we done here?

The app.js file is used to configure our little web app. A route is used by Express to direct requests. App.js already had routes configured for the root (‘/’, when you browse to localhost:3000) and /users (as in localhost:3000/users) from our initial setup. We don’t have any code implemented to handle a request to /users, so nothing will happen at that url. We added a route for /getData, specifying that when it is called, the code in our db.js file is to be run.

When we browse to http://localhost:3000, our Node server responds by looking up the route for ‘/’, the root of our site, and passing that request to ./routes/index as configured in app.js. This causes the server to load and run ./routes/index.js, which renders index.html to the browser.

The browser receives the index.html document from the server and parses it. From the head section, it invokes appDB.js. The browser retrieves that file from the server and executes it. In the fetchData function of appDB.js, we see fetch(“/getData”… which tells the browser to call back (using a POST request as opposed to a GET request) to the server at the localhost:3000/getData path.

Back on the server line 21 of app.js declares what to do when a POST to /getData is received. It calls the query function in the db.js file. Our connection to the database is from code running on the web server, not from our browser. As you are getting used to this “running on the server” vs “running on the browser” idea, you might insert some console.log(“I’m doing this now”) statements throughout these javascript files. If the code is running on the server, the text will appear on the command line where you started the web server with nodemon app.js. If it is running on the browser, it will appear in the browser’s debug console (CTRL+SHIFT+I on Chrome in Windows), or in the extension debugger if you’re working with an extension.

The db.js file contains our database vendor specific functionality. The code comes from a sample on the mssql npm page. This is where you would swap out the SQL Server specific code for AWS, Google, Oracle, MySQL or other database connectivity code. In this file we are using the mssql package to create a connection pool, send a query, receive the result, and close the connection.

The query result from SQL Server is sent from our Node web server to the appDB.js code still running in our browser. The “.then” syntax below the fetch clause is a way JavaScript works with asynchronous processes like querying a database. The fetchData function parses the result set from JSON into text and updates the value of the “result” HTML tag. On my machine, I can watch the page render and a second later see the “This text should be replaced with data” replaced with our data. We are not worrying about formatting this data at this point, because lazy.

Summary

Writing to a database requires a JavaScript developer to learn a new set of server side skills. In this article we created a connection to and queried a SQL Azure table, outputting the results to a web page.

In part 5, we will take these concepts and add them to the extension concepts from part 3 to make extension that writes back to a database.

Author: Larry Clark

Larry has been a Tableau employee since October of 2018. Prior to Tableau, Larry was a BI consultant for Catapult Systems. Prior to Catapult, Larry was in DB/BI technical sales for Microsoft for >20 years.

Leave a Reply

Your email address will not be published. Required fields are marked *