A RESTful Introduction

The topic for the IDUG Content Committee for February 2018 is “RESTful”.

If you have been watching the progress of Db2 over the last few years then this is a topic which you will have seen mentioned many times. This is particularly true in the Db2 for z/OS space, which appears to be leading the way with direct support for native RESTful services.

We hope to bring you all the latest information of the state of RESTful support across the Db2 family as the month progresses.   However, in this short article we’ll say a little about what REST is and how it has been possible to provide RESTful support for data in Db2 long before the product had functionality built in.

Origins of REST

Although it really only has gained a lot of attention in the last few years, the terminology has been around since 2000, when it was the subject of Roy Fielding’s academic dissertation at the University of California “Architectural Styles and the Design of Network-based Software Architectures”. In its broadest sense it is an architectural style of any networked hypermedia application, but it is usually used to mean a style of building web services.

The appeal of REST techniques is largely that they produce applications which are more lightweight and easier to maintain than previous web service architectures based around WSDL and SOAP technologies.

Their adoption has been accelerated by the increase in internet-connected mobile devices, which rely on RESTful services to deliver their ever increasing information needs.

What is REST?

Whenever you look this question up, the first thing you are told is that REST stands for “Representational State Transfer” – to be honest, this tells most people virtually nothing!!!

The REST architectural style describes six constraints which must be conformed to if an application is to be considered fully RESTful.   These are –

  • Client-server
  • Uniform Interface
  • Stateless
  • Cacheable
  • Layered System
  • Code on Demand (optional)

What this resolves to in practice is the following –

  • The use of consistent URIs to specify resources which can be accessed
  • The use of HTTP verbs (GET, PUT, POST, DELETE) to invoke different actions on these resources
  • The return of information from the calls thus defined in a well known format, normally JSON

So, for example, a URI might define an employee in the database –

http://myserver.com/employee/123

If invoked using the GET verb this could return a JSON document describing the employee with ID=123. Calling the same URI with the POST verb could insert or update this resource.

URIs could also be defined for collections of information.  For example –

http://myserver.com/employees

would enable interfacing with the collection of employees.    Issuing a GET against this URI would provide a list of all employees.   Issuing a POST against the same URI would add a new employee, with the post data providing the employee details to add.

URIs often resolve to database resources, but this is not necessarily the case.   For example, in the above case there might be an EMPLOYEE table with a key field where the employee has a key value of 123.  In many simply cases this will be the case, and indeed many frameworks which provide RESTful capabilities enforce this type of relationship. But the mapping might be much more complex than this.

Framework RESTful Support

It wasn’t long until various web development frameworks started to add support for RESTful functionality. Just about every web development framework these days offers REST support.

One of the early frameworks to add RESTful support, was Ruby on Rails : it added REST support in Rails 1.2 in January 2007.  Rails support for REST is still one of the most elegant, if one of the most opinionated (as you’d expect from Rails).  For example, the following small code snippet adds a full set of RESTful routes for employees –

ActionController::Routing::Routes.draw do |map|
  map.resources :employees
end

Rails goes beyond this easy support – when you generate a model or scaffold with its default generation facilities all the RESTful routing for this model are generated by default.  It also makes it very easy to add additional special processing routes if required –

ActionController::Routing::Routes.draw do |map|
  map.resources :employees, :collection => { :recent => :get }
end

This adds an extra routine /employees/recent, invoked using GET, to the default routes.  There is much more possible very easily within the framework.  It would be fair to say that much of the implementation of RESTful support in many frameworks has been influenced to a greater or lesser extent to the Rails implementation.  This is to such an extent that when you come to look at frameworks for other languages such as node.js you will often find them classified by how close they are to Rails.

Since most frameworks provide Db2 support, it therefore became very easy to add RESTful services on top of Db2 databases. In the remainder of this example, we’ll look at using one such framework to produce some RESTful services on top of a Db2 database.

RESTful Implementation Using Node.js / Express.js

In this example, we’ve chosen to demonstrate how to implement some simple RESTful services using the Javascript-base node.js framework, in conjunction with Express.js.

Node.js is a very popular choice for server-side execution of Javascript, and is often used to produce fast, lightweight web applications.   Express.js is the standard web application framework for node.js and is in turn used as the basis for more sophisticated or opinionated frameworks.

In this example, we are building our application on Linux.  We have installed a Db2 server using standard techniques, and have installed node.js using the Linux package manager.  This not only installs the basic node.js support but also provides npm, the node.js package manager.

The first thing we need to install, via npm, is Express (including the Express generator facilities) –

sudo npm install -g express-generator

This installs Express and its generator globally within NPM (installing NPM modules globally means that they will be available to all node.js applications on the box rather than installed individually for each application).

At this point we are ready to start defining our application.   We create a directory called apps, change to that directory and then issue a command to create an application called myapp –

express -e -v ejs myapp

Here I’ve made the personal choice of the EJS templating system for views, since I find that the best option available.  Express allows a choice of templating systems, which offer different ways of embedding JavaScript output into results.

This will generate a basic application in the myapp directory.  The first thing we need to do is to introduce the Db2 support into the application.   We do this by editing the package.json file and add a line with the details of the Db2 driver we need –

“ibm_db”: “>= 2.2.1”

Normally at this point we would simply run “npm install” and this would download and install all the NPM packages that we need to satisfy the dependencies.   For Db2 this would even download and configure an IBM Data Server client for use with node.js.   However the client version pulled in by this process is not the latest (not sure why it has not been kept up to date) and so it is better to point to a more up to date client we have installed ourselves.   To do this we have to set the environment variable IBM_DB_HOME before running “npm install” –

export IBM_DB_HOME=/home/db2inst1/sqllib;npm install
> ibm_db@2.2.1 install /home/philip/apps/myapp/node_modules/ibm_db
> node installer/driverInstall.js
 
IBM_DB_HOME environment variable have already been set to -> /home/db2inst1/sqllib
 
Downloading of clidriver skipped - build is in progress...
 
make: Entering directory '/home/philip/apps/myapp/node_modules/ibm_db/build'
  CXX(target) Release/obj.target/odbc_bindings/src/odbc.o
  CXX(target) Release/obj.target/odbc_bindings/src/odbc_connection.o
  CXX(target) Release/obj.target/odbc_bindings/src/odbc_statement.o
  CXX(target) Release/obj.target/odbc_bindings/src/odbc_result.o
  SOLINK_MODULE(target) Release/obj.target/odbc_bindings.node
  COPY Release/odbc_bindings.node
make: Leaving directory '/home/philip/apps/myapp/node_modules/ibm_db/build'
 
npm notice created a lockfile as package-lock.json. You should commit this file.
added 155 packages in 15.626s

In our case we are actually pointing to a local Db2 server instance, but you could point to a local client install instead.

At this point we are ready to test the basic application.   We need to source the db2profile before running “npm start”. This starts an application which we can access via http://localhost:3000 and which will display a very simply message “Express : welcome to Express”.

The basic structure of the application is as follows –

philip@philip-desktop:~/apps/airbasejs$ ls -l
total 68
-rw-rw-r--   1 philip philip  1256 Feb  1 20:19 app.js
drwxr-xr-x   2 philip philip  4096 Feb  1 20:19 bin
drwxrwxr-x 145 philip philip  4096 Feb  1 20:21 node_modules
-rw-rw-r--   1 philip philip   352 Feb  1 20:20 package.json
-rw-rw-r--   1 philip philip 39209 Feb  1 20:21 package-lock.json
drwxr-xr-x   5 philip philip  4096 Feb  1 20:19 public
drwxr-xr-x   2 philip philip  4096 Feb  1 20:19 routes
drwxr-xr-x   2 philip philip  4096 Feb  1 20:19 views

As we are concerned with RESTful facilities, the routes subdirectory is of most interest to us.  Within that we will find two files index.js and users.js.   The contents of routes/index.js is as follows –

var express = require('express');
var router = express.Router();
 
/* GET home page. */
router.get('/', function(req, res, next) {
  res.render('index', { title: 'Express' });
});
 
module.exports = router;

This is the script which handles the transformations of the various URIs into the functionality each URI supports.   It’s a lot more labour intensive than Ruby on Rails, but the process is still fairly obvious. When  the root URI is invoked using the GET verb, it outputs the results by rendering the “index” view (file views/index.ejs).   We can edit the contents of this index file and see the results instantly.

What we want to do is add a new RESTful route to the index.js file to display some Db2 data –

var express = require('express');
var router = express.Router();
 
/* GET home page. */
router.get('/', function(req, res, next) {
  res.render('index', { title: 'Express' });
});
 
/* Add DB2 Test */
router.get('/db2test', function(req,res) {
  const ibmdb = require('ibm_db');
  ibmdb.open("DATABASE=SAMPLE;HOSTNAME=localhost;UID=db2inst1;PWD=xxxxxxxx;PORT=50000;PROTOCOL=TCPIP",
    (err,conn) => {
      if (err) return console.log(err);
      conn.query('SELECT * FROM DEPT',
        (err,data) => {
           if (err) console.log(err);
           else {
             console.log(data);
             res.render('db2test', {
               title: "Db2 Test",
               data: data
             });
       }
     conn.close(function() {
       console.log('done');
     });
    });
  });   
  // res.render('db2test', { title: 'DB2 Test' });
});     
 
module.exports = router; 

What we have done is added support for a new /db2test URL, also on the GET verb.  This runs a simple SELECT from the DEPT table in the SAMPLE database and returns the data to the web page in JSON format.  

This is a fairly simple example, but can be quickly extended.   If we want to handle a POST then we need to code router.post rather than router.get.  

Obviously, this example is fairly close to the “bare metal” in terms of coding.   There are lots of frameworks and libraries that make it easier and less repetitive to implement resources, as well as a great variety of higher level database abstractions to make things better as well. In fact, one problem with node.js is that there are so many different libraries and frameworks offering different solutions to the same problem that it is difficult to make a choice.   Maybe there is something to be said for the opinionated, but to my mind less consuming, world of Ruby on Rails.

Summary

We’ve briefly tried to describe what REST is, and offer some context to the rest of the month’s content.  Then we’ve showed how it is possible to build RESTful services on top of Db2 without anything other than standard application development libraries and frameworks.  

In the rest of the month, we’ll take a look at some of the technologies in the Db2 space that make this easier. Hopefully this article has helped

Recent Stories
Microservice Architectures and Performance Models

Improving Performance in Your Data Warehouse – Part 1

Simple DBA Tools for the Db2 DBA