Deploying JavaScript using dbjs

Before a JavaScript function can be used as a stored procedure or UDF, two things need to be deployed into the Oracle Database:

  1. the JavaScript source code packed such that it can be loaded by a loader for UMD modules (including CommonJS modules and AMD modules) and
  2. the call specifications for all functions exported by the JavaScript source code.

There are different ways to create a JavaScript module and its call specifications inside the database. In the following, we describe the dbjs utility that comes with this preview release. Its goal is to make it easy for JavaScript developers to deploy JavaScript (or TypeScript) modules into the database.

Bundling JavaScript

A JavaScript module is a bundle of code that can be deployed into the database. With dbjs, you can write your JavaScript code in the same way you would do it for Node.js. Specifically, you can split your code into modules or NPM packages and use the Node.js require function to import functionality that is exported by other modules or packages.

dbjs uses browserify to combine all dependencies into a single bundle that will be sent to the database. Functions exported in the top-level/main package can be made available to the database as stored procedures or UDFs.

The name of the deployed JavaScript bundle/module is derived from the file name and always ends with .js. However, the deploy command allows for overwriting the default derived name.

Compatibility

Many modules or packages that don't do any IO can be easily reused and will run in the database. Modules or packages that use Node.js built-ins such as fs or http can be deployed but accessing those modules at runtime will raise an error because require() will return undefined.

Using any of the Node.js globals such as setTimeout() will also result in a runtime error because the function is not defined in the database.

__dirname and __filename are resolved at deployment time to the path of the directory or file that gets added to the bundle.

Some Node.js built-in modules have been replaced by a shim to work in the database. When you require() any of the following modules you will get a shim that will work in the database:

  • assert (shim: https://www.npmjs.com/package/assert)
  • buffer (shim: https://www.npmjs.com/package/buffer)
  • url (shim: https://www.npmjs.com/package/url)
  • util (shim: https://www.npmjs.com/package/util)
  • console (implemented on top of Oracle's DBMS_OUTPUT Package: https://docs.oracle.com/database/121/ARPLS/d_output.htm#ARPLS036)

TypeScript

TypeScript is a typed superset of JavaScript that compiles to plain JavaScript (http://typescriptlang.org/). Stored procedures and UDFs can be implemented in TypeScript. dbjs will automatically compile TypeScript modules into JavaScript during the deployment step.

Specifically, dbjs uses the browserify plugin tsify in order to compile TypeScript and bundle the resulting JavaScript modules such that they can be deployed into the database.

For example, consider having a TypeScript file `helloworld.ts:

export function helloworld() : string {
  return 'HelloWorld';
}

The following command will automatically compile TypeScript into JavaScript and send the resulting file to the database.

$ dbjs deploy -u scott -p tiger -c localhost:1521/DBML helloworld.ts
+ helloworld.js
└─┬ helloworld
  └── SCALAR FUNCTION HELLOWORLD.HELLOWORLD RETURN VARCHAR2

Call Specifications

A Call Specification provides types for the parameters and the result of a JavaScript function that can be called from the database. It is needed to bridge the dynamically-typed JavaScript world and the statically-typed SQL world.

In the database, call specifications are typically written in PL/SQL. Using dbjs, call specifications can be provided using TypeScript instead.

If a stored procedure or UDF is implemented in TypeScript, dbjs will automatically derive the PL/SQL call specification from the signature of the TypeScript function. For example, in the helloworld.ts TypeScript example above, the call specification is automatically derived.

Otherwise, the user needs to provide a separate TypeScript declaration file. The name of the typescript declaration file should be the same as the corresponding JavaScript source file, but with the suffix .js replaced with the suffix .d.ts. This file needs to contain exported function declarations for the functions that are exported by the bundled JavaScript module.

Please note that by default, all procedures and functions are contained in a PL/SQL package named after the JavaScript or TypeScript file. This behavior can be overwritten using the no-package option.

In the following, we refer to the process of deriving PL/SQL call specifications from TypeScript as transpilation.

Procedures and Functions

A TypeScript declaration is transpiled into a procedure if it does not declare a return type, the return type is void or the return type any is specified. Otherwise, it is transpiled into a function.

A TypeScript declaration file util.d.ts containing

export function salraise(e: number, r: number);

is transpiled into a procedure because its declaration does not provide a return type. It can be invoked by the database using the SQL CALL statement.

CALL util.salraise(7369, 200);

By contrast, a TypeScript declaration file containing

export function salary(e: number): number;

is transpiled into a function that can be invoked as a UDF in a SQL query:

SELECT util.salary(empno) FROM emp;

Types

dbjs uses an implicit mapping between TypeScript types and database types. The following table depicts the implicit mapping between TypeScript types and database types:

TypeScript Type Database Type
string VARCHAR2
number NUMBER
boolean NUMBER

To change the implicit mapping use JSDoc annotations:

/**
 * @oracle( { parameter : "e", sqlType : "BINARY_DOUBLE" } )
 * @oracle( { returnType : "BINARY_DOUBLE" } )
 */
export function salary(e: number): number;

dbjs will transpile the parameter and return type to BINARY_DOUBLE instead of the defaulting to NUMBER.

More details on types and conversions between type systems can be found in the documentation on Data Types.

Renaming Functions

By default, the procedure or UDF in the database will have the same name as the JavaScript function. However, a TypeScript export statement can be used to customize the name in the database.

export { salary as sal };

dbjs Interfaces

dbjs provides a JavaScript API as well as a command-line tool.

API

The API provides the four functions:

  • deploy()
  • drop()
  • functions()
  • modules()

Documentation for those functions can be found in the declaration file index.d.ts of the @types/dbjs package.

Command-line Tool

A list of commands and options of the command-line tool is available by simply calling dbjs.

$ dbjs
Usage: dbjs [cmd] <options>

Commands:
  deploy <file> [moduleName]  deploy a JavaScript or TypeScript module into the
                              database as a library (requires create procedure
                              and any library privileges) and optionally rename
                              it
  drop <moduleName>           drop a JavaScript/TypeScript module from the
                              database (requires drop procedure and any library
                              privileges)
  functions <moduleName>      list all PL/SQL call specifications belonging to a
                              given JavaScript/TypeScript module
  modules                     list the names of all JavaScript and TypeScript
                              modules deployed in the database

Options:
  --version  Show version number                                       [boolean]
  --help     Show help                                                 [boolean]

Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.

The help for the various commands provided by the command-line tool is available through the help command.

$ dbjs help deploy
dbjs deploy <file> [moduleName]

Options:
  --version               Show version number                          [boolean]
  --help                  Show help                                    [boolean]
  --types, -t             the name of the TypeScript declaration file if module
                          is written in JavaScript                      [string]
  --connectionString, -c  the connection string used to connect to the database
                                                             [string] [required]
  --database              the database backend to connect with (e.g. "oracle",
                          "mysql",...)   [string] [required] [default: "oracle"]
  --user, -u              the user used to connect to the database
                                                             [string] [required]
  --password, -p          the password used to connect to the database
                                                             [string] [required]
  --dry, -d               only do a dry run, i.e., don't deploy
                                                      [boolean] [default: false]
  --strict, -s            strict mode, i.e., abort deployment if module already
                          exists or if some exported functions cannot be
                          deployed         [boolean] [required] [default: false]
  --no-package, -f        do not package: deploy exported function as
                          stand-alone top-level function instead of member
                          functions of a package named after the module.
                                           [boolean] [required] [default: false]
  --json, -j              display outcome in JSON format
                                                      [boolean] [default: false]
  --verbosity, -v         verbosity level (i.e. v, vv, vvv, vvvv)        [count]

Automatic Module and Declaration File Resolution

The command-line tool implements the resolution algorithm of the Node.js require() function. This allows for easy deployment of JavaScript packages or modules that have been installed into the node_modules directory by npm.

This is true for JavaScript modules as well as TypeScript declaration files (see https://blogs.msdn.microsoft.com/typescript/2016/06/15/the-future-of-declaration-files/).

$ npm install validator
$ npm install @types/validator
$ dbjs deploy -u scott -p tiger -c localhost:1521/DBML validator
+ validator.js
├─┬ equals
│ └── SCALAR FUNCTION VALIDATOR.EQUALS("p0" IN VARCHAR2, "p1" IN VARCHAR2) RETURN NUMBER
├─┬ isAfter
│ ├── SCALAR FUNCTION VALIDATOR.ISAFTER("p0" IN VARCHAR2) RETURN NUMBER
│ └── SCALAR FUNCTION VALIDATOR.ISAFTER("p0" IN VARCHAR2, "p1" IN VARCHAR2) RETURN NUMBER
├─┬ isAlpha
│ └── SCALAR FUNCTION VALIDATOR.ISALPHA("p0" IN VARCHAR2) RETURN NUMBER
├─┬ isAlphanumeric
│ └── SCALAR FUNCTION VALIDATOR.ISALPHANUMERIC("p0" IN VARCHAR2) RETURN NUMBER
├─┬ isAscii
│ └── SCALAR FUNCTION VALIDATOR.ISASCII("p0" IN VARCHAR2) RETURN NUMBER
├─┬ isBase64
│ └── SCALAR FUNCTION VALIDATOR.ISBASE64("p0" IN VARCHAR2) RETURN NUMBER
├─┬ isBefore
│ ├── SCALAR FUNCTION VALIDATOR.ISBEFORE("p0" IN VARCHAR2) RETURN NUMBER
│ └── SCALAR FUNCTION VALIDATOR.ISBEFORE("p0" IN VARCHAR2, "p1" IN VARCHAR2) RETURN NUMBER
... # many more

dbjs WARN elem: parameter using unsupported type in function declaration of contains
dbjs WARN contains: method skipped
dbjs WARN options: parameter using unsupported type in function declaration of isByteLength
dbjs WARN isByteLength: method skipped
...

In this example, dbjs will automatically discover and deploy the JavaScript module located at node_modules/validator/validator.js and transpile the call specifications contained in node_modules/@types/validator/index.d.ts.

This command will deploy the validator example into the scott schema of the Oracle Database. The database is identified by its connection string. The connection string used by dbjs for the Oracle Database contains the host and port configured for the listener (localhost and 1521 in our case), and the global DB name (DBML in our case).

Please note that not all functions of the validator module can be deployed into the database. For example, the contains function with two parameters cannot be deployed because the second parameter (named elem) has the unsupported type any.

Dropping Modules

The dbjs tool makes it easy to remove a JavaScript module from the database and also removes all of the call specifications for that module.

For example, analogous to the above deploy command, the following command will remove the validator JavaScript bundle as well as all call specifications from the database.

$ dbjs drop -u scott -p tiger -c localhost:1521/DBML validator
- validator.js
├─┬ equals
│ └── SCALAR FUNCTION VALIDATOR.EQUALS("p0" IN VARCHAR2, "p1" IN VARCHAR2) RETURN NUMBER
├─┬ isAfter
│ ├── SCALAR FUNCTION VALIDATOR.ISAFTER("p0" IN VARCHAR2) RETURN NUMBER
│ └── SCALAR FUNCTION VALIDATOR.ISAFTER("p0" IN VARCHAR2, "p1" IN VARCHAR2) RETURN NUMBER
├─┬ isAlpha
│ └── SCALAR FUNCTION VALIDATOR.ISALPHA("p0" IN VARCHAR2) RETURN NUMBER
├─┬ isAlphanumeric
│ └── SCALAR FUNCTION VALIDATOR.ISALPHANUMERIC("p0" IN VARCHAR2) RETURN NUMBER
├─┬ isAscii
│ └── SCALAR FUNCTION VALIDATOR.ISASCII("p0" IN VARCHAR2) RETURN NUMBER
... # many more

Please note that deploy will also call drop if a module with the same name already exists in the database. Using the --strict option of the deploy command will prevent that and raise an error instead.

Listing Modules

You can use the modules command to list all JavaScript modules that have been deployed for a user.

$ dbjs modules -u scott -p tiger -c localhost:1521/DBML

returns the names of all modules:

validator.js

Listing Functions of a Deployed Module

The functions command will list all call specifications that have been created on top of a given module:

$ dbjs functions -u scott -p tiger -c localhost:1521/DBML validator

returns a tree with one entry per call specification per JavaScript function:

validator.js:
├─┬ equals
│ └── SCALAR FUNCTION VALIDATOR.EQUALS("p0" IN VARCHAR2, "p1" IN VARCHAR2) RETURN NUMBER
├─┬ isAfter
│ ├── SCALAR FUNCTION VALIDATOR.ISAFTER("p0" IN VARCHAR2) RETURN NUMBER
│ └── SCALAR FUNCTION VALIDATOR.ISAFTER("p0" IN VARCHAR2, "p1" IN VARCHAR2) RETURN NUMBER
├─┬ isAlpha
│ └── SCALAR FUNCTION VALIDATOR.ISALPHA("p0" IN VARCHAR2) RETURN NUMBER
├─┬ isAlphanumeric
│ └── SCALAR FUNCTION VALIDATOR.ISALPHANUMERIC("p0" IN VARCHAR2) RETURN NUMBER
├─┬ isAscii
│ └── SCALAR FUNCTION VALIDATOR.ISASCII("p0" IN VARCHAR2) RETURN NUMBER
... # many more