Skip to Content
Technical Articles
Author's profile photo Thomas Jung

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.

Assigned Tags

      18 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Michael Schliebner
      Michael Schliebner

      "Where there is shell, there shall be hope!"

      This introduction of building command line interfaces with Node.js/Shebang is a beautiful example to understand how straightforward it can be applied into a scenario in need of a handy toolbox
      eg a one stop for a bunch of commands.

      Local Process Robots here love it. Thank you!

       

      Author's profile photo jyothir aditya k
      jyothir aditya k

      Thanks !

      Author's profile photo Volker Buzek
      Volker Buzek

      very cool walk-through!

      or -as DJ Adams and the crew of #handsOnSAPdev will probably agree- more proof that eventually "the future is terminal" 🙂

      Author's profile photo DJ Adams
      DJ Adams

      Most definitely!

      Author's profile photo Sergio Guerrero
      Sergio Guerrero

      absolutely fantastic thanks for sharing

      Author's profile photo Wonseok Choe
      Wonseok Choe

      Can't get the result of hana-cli callProcedure as json?

      If try with "--quiet", it is returned as a js array in terminal.

      How to convert js array to json easily?

       

      I used to:

      (echo 'INPUT_PARAM_1_VALUE'|hana-cli cp -p CUSTOM_STORED_PROCEDURE --quiet |sed -n '1d;p' ) > ~/projects/out.jsarray;
      echo "const fs = require('fs');console.log(JSON.stringify(eval('array='+fs.readFileSync('$HOME/projects/out.jsarray'))))"|nodejs > $HOME/projects/out.json;
      jq -r . $HOME/projects/out.json
      Author's profile photo Thomas Jung
      Thomas Jung
      Blog Post Author

      Not sure I understand.  JavaScript array is valid JSON.  Do you just want the results to be within an Object wrapper instead of an array? Procedures always return an array of 0:n output parameters.

      Author's profile photo Wonseok Choe
      Wonseok Choe

      It would be nice if the call procedure return output type could be specified as json.
      js array is useful in javascript, but inconvenient in terminal.

      Author's profile photo Momen Allouh
      Momen Allouh

      Hi Thomas,

      Quick question, I am trying to install ( hana-cli ) to a CAP app and getting the below error. Can you please help ?

      I am following your tutorial "Create an SAP Cloud Application Programming Model Project for SAP HANA Cloud" and I am stuck at this step.

      hana-cli%20install%20error

      hana-cli install error

      Author's profile photo Thomas Jung
      Thomas Jung
      Blog Post Author

      Are you installing this locally or within the Business Application Studio?
      I just tried installing locally and it worked fine (both hana-cli in whole and just test install that package in the error - universalify).

      This error seems to be coming from npm itself - the package universalify is a third party dependency from somewhere within the dependency tree (not something I'm using directly).  However it's being returned as empty. That might be a temporary issue with npm itself.  But I'm able to access that package just fine.

      Author's profile photo Momen Allouh
      Momen Allouh

      in BAS, I am using my trial BTP account.

      the npm -v is 6.14.15

       

      Author's profile photo Thomas Jung
      Thomas Jung
      Blog Post Author

      BAS does have it's own NPM registry cache.  To me from this error and the fact it can't be recreated when using the public npm directly; it seems like there is something wrong in the cache.  I don't have any control over that.  I can only suggest trying again later.

      Author's profile photo Momen Allouh
      Momen Allouh

      Can you please tell me the manual steps that " hana-cli createModule " command will do ? so I can do it manually and continue the tutorial.

      I need to learn how to build CAP app with Hana so I can use it in my Integration Flow from CPI.

       

       

      Author's profile photo Thomas Jung
      Thomas Jung
      Blog Post Author

      Ok something seems to have been wrong in the npm shrinkwrap of that version that only impacted Linux.  I've pushed a new version of hana-cli to npm.  Perform a "npm uninstall -g hana-cli" and then "npm install -g hana-cli".  That should bypass the npm cache and allow the newest version to install cleanly. I was able to do so in the Business Application Studio just now without issue.

       

      But if you just want to know what the command is doing, you can have a look at the source code. Mostly it creates two configuration files. One is a package.json in the /db target folder.  The content for it can be found here:

      https://github.com/SAP-samples/hana-developer-cli-tool-example/blob/main/bin/createModule.js#L72

      The second file is created in the /db/src/ folder and named .hdiconfig. It's content for HANA Cloud can be found here:
      https://github.com/SAP-samples/hana-developer-cli-tool-example/blob/main/bin/createModule.js#L113

      Author's profile photo Momen Allouh
      Momen Allouh

      👍Thanks, it works after restarting the BAS space.

      It took some time to install many dependencies in -g global.

      Author's profile photo HP Seitz
      HP Seitz

      I can confirm that the new version 2.202201.5 solves this issue also for macOS.

      Thomas you should move on to Linux/macOS 🙂

      Author's profile photo Yasin Ilkbahar
      Yasin Ilkbahar

      Hi  Thomas Jung

      thanks for the great tutorial and valuable information.

      I tried the installation of Hana-cli on my M1 Arm Mac and facing unfortunately some issues during installation (see below). Seems to be that the tool is not fully compatible on M1 Macs!? Will this be supported soon?

      Thank you,

      Yasin

      Installation%20failed

      Installation failed

      Author's profile photo Thomas Jung
      Thomas Jung
      Blog Post Author

      The @sap/hana-client does not supply a universal binary and only support x86_64 on Mac OS currently: 2996882 - Universal binary for MacOS SAP HANA Client (Apple silicon based computers) - SAP ONE Support Launchpad

      Nothing I can really do about the availability of a critical dependency like that. I've seen some community members suggest instead installing the x86_64 version of Node.js and let it be emulated via Rosetta.