hana-cli Sample: How to Build a Node.js Command Line Interface

Introduction

A few weeks ago, we published a new code sample – the hana-developer-cli-tool-example: https://github.com/SAP-samples/hana-developer-cli-tool-example.  This sample is designed to help one learn how to build a developer-centric SAP HANA command line tool, particularly designed to be used when performing SAP HANA development in non-SAP tooling (for example from VS Code). While this is a tool that can be used as-is to help with HANA development by exposing a lot of admin and metadata inspection functionality from a simple command line interface; in the blog entry I’d like to focus on how it was built. This way others could use this sample as a guideline for building any general Node.js based command line interface or how to combine such an interface with a HANA back end.

Before we start dissecting and seeing how this tool was built, it helps to have a general understanding of what it is used for.  The github repo is the main source of explanation and I don’t want to repeat it all here.

https://github.com/SAP-samples/hana-developer-cli-tool-example

But as a short explanation, the tool allows developers to interact with a HANA DB, Schema or HDI container and perform operations like viewing the metadata or actual contents in a table, view, procedure, etc.

If you’d like a more extensive introduction to the tool I’d suggest watching this video:

Or download and install the tool itself to try it out. It can be installed directly from npm:

npm install -g hana-cli

Turning a Node.js Application into a Command Line Interface Tool

For our first task we want to take a normal Node.js Application and turn it into something optimized to be ran from a Command Line Interface. Of course many Node.js applications can be triggered from a command line via node <script name>.  However we want something that can be called as its own standalone tool without having to specify node as the main command. This way we can also pass in our own parameters and options to our command.

For the purposes for examining this starting point, let’s create a new Node.js application first using the npm init command.

What gets generated is a standard Node.js package.json that would run the index.js by default.  But we want to make an addition to the package.json.  We will add a bin section. This will expose a command of our naming (which for this example we will use cli-test) and map that to a JavaScript file (./bin/cli.js in this case).

  "bin": {
    "cli-test": "./bin/cli.js"
  },

In order to read more about this bin feature of the package.json, here is the documentation: https://docs.npmjs.com/files/package.json#bin

The other special feature we will need is a specific first line in the cli.js file to set the environment for execution. This is referred to as the Shebang line. Therefore we will start with the following:

#!/usr/bin/env node
/*eslint no-console: 0, no-process-exit:0*/
/*eslint-env node, es6, module */
console.log('Hello World')

The combination of these two features allows us to use the npm link command (for testing) or npm install -global (for productive usage) to install the command we specified into the path.  This makes our Node.js command we are building accessible from any directory in the file system. This is of course critical if we are building a reusable utility like a HANA developer tool.

Command Line Arguments

We now have a working command line tool, but its kind of limited. We don’t want to have to create separate Node.js projects/packages for each command. For hana-cli we want a single entry point but then the ability to call multiple commands within that context for things like inspecting tables, creating users, viewing logs, etc.

Its possible to build our own command line argument processing, but why reinvent the wheel where there are already some excellent modules available that do this so well. I explored several different options, but ultimately settled on the yargs module:

https://github.com/yargs/yargs

http://yargs.js.org/

Yargs was powerful, easy to get started using and best yet – Pirate Themed! You have to love a project that doesn’t take itself too seriously:

To use Yargs, we add it to the dependencies in our package.json.  Then we run npm install to have that package locally for testing. We can then add it to the coding of our cli.js file. We will begin simple, just allowing Yargs to build a help command and a few other nice build-in features.

With the basic framework in place we can now start adding our own commands. We will begin by adding a single command named status that will just echo back Status to the output via console.log.

A single command is an even better start, but we also need arguments and options to be passed into our commands.  For this we can use the builder function of the command to add these elements. The argv input into our command event handler will then have the values for these options and values passed into the command line as well.

We now have a framework that allows us to define the user interface of our commands, easily capture and process the input, and it is all self documenting via the generated help command.

Scaling the Yargs Command Processing

Our simple example was fine for structuring a learning introduction but for hana-cli we have over 50 base commands. We certainly don’t want all the event callback handlers for each command in a single js file. For the real product we instead used a feature of Yargs that allows each command to be broken out into its on JavaScript file.

https://github.com/SAP-samples/hana-developer-cli-tool-example/blob/master/bin/cli.js#L26

For each of these JavaScript command files, we just have to export the same parameters (like command, aliases, etc) and event handlers (like builder and handler) that we had structured earlier in the cli.js directly to Yargs.

https://github.com/SAP-samples/hana-developer-cli-tool-example/blob/master/bin/version.js

The version.js example above is the simplest approach since its a command without any addition parameters or options. Therefore the builder event is empty. But let’s look at a command that needs a single parameter.  The features.js has the single parameter of admin to tell it if it should connect with administrative authorization.  To enable this option, you just pass an JSON structure with details like option name, alias, data type, etc into the builder export. It is then processed, validated and documented by the Yargs framework.

https://github.com/SAP-samples/hana-developer-cli-tool-example/blob/master/bin/features.js#L10

Options and parameters are good because they allows us to begin to structure some truly robust commands.

For example inspectTable:

hana-cli inspectTable [schema] [table]
[aliases: it, table, insTbl, inspecttable, inspectable]
Return metadata about a DB table

Options:
  --admin, -a, --Admin    Connect via admin (default-env-admin.json)
                                                      [boolean] [default: false]
  --table, -t, --Table    Database Table                                [string]
  --schema, -s, --Schema  schema        [string] [default: "**CURRENT_SCHEMA**"]
  --output, -o, --Output  Output Format for inspection
   [string] [choices: "tbl", "sql", "cds", "json", "yaml", "cdl", "edm", "edmx",
                                             "swgr", "openapi"] [default: "tbl"]

https://github.com/SAP-samples/hana-developer-cli-tool-example/blob/master/bin/inspectTable.js#L6

Here we have a command with two additional parameters – schema and table.  But it also has several options with multiple possible choices and defaults.  Yet the Yargs definition for this command is easy to build:

exports.command = 'inspectTable [schema] [table]';
exports.aliases = ['it', 'table', 'insTbl', 'inspecttable', 'inspectable'];
exports.describe = bundle.getText("inspectTable");
exports.builder = {
  admin: {
    alias: ['a', 'Admin'],
    type: 'boolean',
    default: false,
    desc: bundle.getText("admin")
  },
  table: {
    alias: ['t', 'Table'],
    type: 'string',
    desc: bundle.getText("table")
  },
  schema: {
    alias: ['s', 'Schema'],
    type: 'string',
    default: '**CURRENT_SCHEMA**',
    desc: bundle.getText("schema")
  },
  output: {
    alias: ['o', 'Output'],
    choices: ["tbl", "sql", "cds", "json", "yaml", "cdl", "annos", "edm", "edmx", "swgr", "openapi"],
    default: "tbl",
    type: 'string',
    desc: bundle.getText("outputType")
  }
};

Prompts

We have our powerful command line interface that allows us to have many options and parameters. But what happens when the user forgets to use one of the required parameters? As the following demo shows, we have a rather unfriendly experience.

Of course we could put a bunch of validation logic in the event handler of Yargs. But instead why not use another popular Node.js module to create interactive prompts for any of the required parameters?  This way when the user forgets something, they will be asked for the data from the command line.

That’s a much nicer experience, right?  We can even get very fancy with the prompts and dynamically generate them. For example in the command to run a stored procedure, we want to look up the input scalar parameters for the stored procedure and dynamically generate prompts for them.

While there were several good candidates for modules to choose from to create prompts, I opted for the simple and accurately named prompt module: https://github.com/flatiron/prompt#readme

To add the prompts, we just need to start in the main event handler of Yargs. We will pass in the current command line values from the argv variable into the prompt using the override parameter.  We can build a similar JSON data structure to our Yargs parameter but for prompts we might want as well.

  exports.handler = function (argv) {
    const prompt = require('prompt');
    prompt.override = argv;
    prompt.message = colors.green(bundle.getText("input"));
    prompt.start();
  
    var schema = {
      properties: {
        admin: {
          description: bundle.getText("admin"),   
          type: 'boolean',       
          required: true,
          ask: () =>{
            return false;
        }
        }      
      }
    };
       prompt.get(schema, (err, result) => {
         if(err){
             return console.log(err.message);
         }
         global.startSpinner()
         dbStatus(result);
    });
  }
  

https://github.com/SAP-samples/hana-developer-cli-tool-example/blob/master/bin/features.js#L19

For dynamic input parameters of the Call Procedure command we need to first establish a connection to the database, lookup the procedure interface and then map the database data types to the possible console prompt data types.

  const db = new dbClass(await dbClass.createConnectionFromEnv(dbClass.resolveEnv(argv)));
  let procSchema = await dbClass.schemaCalc(argv, db);
  console.log(`Schema: ${procSchema}, Procedure: ${argv.procedure}`);
  let proc = await dbInspect.getProcedure(db, procSchema, argv.procedure);
  let parameters = await dbInspect.getProcedurePrams(db, proc[0].PROCEDURE_OID);
  for (let parameter of parameters) {
    if (!parameter.TABLE_TYPE_NAME && parameter.PARAMETER_TYPE === 'IN') {
      let type = 'string';
      switch (parameter.DATA_TYPE_NAME) {
        case 'TINYINT':
        case 'SMALLINT':
        case 'INTEGER':
        case 'BIGINIT':
          type = 'integer';
          break;
        case 'DECIMAL':
        case 'REAL':
        case 'DOUBLE':
        case 'SMALLDECIMAL':
          type = 'decimal';
          break;
        case 'BOOLEAN':
          type = 'boolean';
          break;
        default:
          type = 'string';
      }
      schema.properties[parameter.PARAMETER_NAME] = {
        description: parameter.PARAMETER_NAME,
        type: type
      }
    }
  }

https://github.com/SAP-samples/hana-developer-cli-tool-example/blob/master/bin/callProcedure.js#L60

Admittedly I’m getting just a little ahead of myself because we’ve not discussed yet how we establish the connection to HANA or gone into the details of the database interface classes I’m using here for the introspection.  That will actually be the topic of the next blog entry in this series. For now I wanted to at least demonstrate how the dynamic prompts could be built.

Closing

And with that, we have a rather complete user interface. We have overall command navigation, parameters and options, validation, online help, prompting and all of our output formatting can be done via console.log and console.table commands. That’s one of the beautiful things about Command Line utility user interface; you can quickly adjust and expand the user experience without having to spend much time on user interface technology itself.

In the next blog entry in this series I will go into more detail on the HANA specifics and into the building of key commands. But for now I hope you’ve enjoyed this general introduction to building command line interfaces with Node.js and have learned something that could be applied to any number of different application scenarios.