High speed Synchronous and Asynchronous access to InterSystems Cache/IRIS and YottaDB from Node.js.
High speed Synchronous and Asynchronous access to InterSystems Cache/IRIS and YottaDB from Node.js.
Chris Munt cmunt@mgateway.com
21 May 2024, MGateway Ltd http://www.mgateway.com
Contents
mg-dbx is a Node.js addon written in C++. It is distributed as C++ source code and the NPM installation procedure will expect a C++ compiler to be present on the target system.
Linux systems can use the freely available GNU C++ compiler (g++) which can be installed as follows.
Ubuntu:
apt-get install g++
Red Hat and CentOS:
yum install gcc-c++
Apple OS X can use the freely available Xcode development environment.
There are two options for Windows, both of which are free:
If the Windows machine is not set up for systems development, building native Addon modules for this platform from C++ source can be quite arduous. There is some helpful advice available at:
Alternatively there are built Windows x64 binaries available from:
Assuming that Node.js is already installed and a C++ compiler is available to the installation process:
npm install mg-dbx
This command will create the mg-dbx addon (mg-dbx.node).
Before connecting to a local (relative to Node.js) InterSystems database via its API, check that the InterSystems 'Callin API' is enabled. From the InterSystems Database Management Portal select System Administration; then Security; then Services. Look for the row containing %Service_Callin and check that it is Enabled. Edit the row and mark this service as Enabled if necessary.
The M support routines are required for:
If none of the above apply you do not need to install these routines - proceed to Connecting to the database.
Two M routines need to be installed (%zmgsi and %zmgsis). These can be found in the Service Integration Gateway (mgsi) GitHub source code repository (https://github.com/chrisemunt/mgsi). Note that it is not necessary to install the whole Service Integration Gateway, just the two M routines held in that repository.
Log in to the %SYS Namespace and install the zmgsi routines held in /isc/zmgsi_isc.ro.
do $system.OBJ.Load("/isc/zmgsi_isc.ro","ck")
Change to your development UCI and check the installation:
do ^%zmgsi
MGateway Ltd - Service Integration Gateway
Version: 4.5; Revision 31 (18 November 2023)
The instructions given here assume a standard 'out of the box' installation of YottaDB (version 1.38) deployed in the following location:
/usr/local/lib/yottadb/r138
The primary default location for routines:
/root/.yottadb/r1.38_x86_64/r
Copy all the routines (i.e. all files with an 'm' extension) held in the GitHub /yottadb directory to:
/root/.yottadb/r1.38_x86_64/r
Change directory to the following location and start a YottaDB command shell:
cd /usr/local/lib/yottadb/r138
./ydb
Link all the zmgsi routines and check the installation:
do ylink^%zmgsi
do ^%zmgsi
MGateway Ltd - Service Integration Gateway
Version: 4.5; Revision 31 (18 November 2023)
Note that the version of zmgsi is successfully displayed.
Finally, add the following lines to the interface file (zmgsi.ci in the example used in the db.open() method).
sqlemg: ydb_string_t * sqlemg^%zmgsis(I:ydb_string_t*, I:ydb_string_t *, I:ydb_string_t *)
sqlrow: ydb_string_t * sqlrow^%zmgsis(I:ydb_string_t*, I:ydb_string_t *, I:ydb_string_t *)
sqldel: ydb_string_t * sqldel^%zmgsis(I:ydb_string_t*, I:ydb_string_t *)
ifc_zmgsis: ydb_string_t * ifc^%zmgsis(I:ydb_string_t*, I:ydb_string_t *, I:ydb_string_t*)
A copy of this file can be downloaded from the /unix directory of the mgsi GitHub repository here
The default TCP server port for zmgsi is 7041. If you wish to use an alternative port then modify the following instructions accordingly.
Start the DB Superserver using the following command:
do start^%zmgsi(0)
To use a server TCP port other than 7041, specify it in the start-up command (as opposed to using zero to indicate the default port of 7041).
Most mg-dbx methods are capable of operating either synchronously or asynchronously. For an operation to complete asynchronously, simply supply a suitable callback as the last argument in the call.
The first step is to add mg-dbx to your Node.js script
var dbx = require('mg-dbx').dbx;
And optionally (as required):
var mglobal = require('mg-dbx').mglobal;
var mcursor = require('mg-dbx').mcursor;
var mclass = require('mg-dbx').mclass;
var db = new dbx();
In the following examples, modify all paths (and any user names and passwords) to match those of your own installation.
Assuming Cache is installed under /opt/cache20181/
var open = db.open({
type: "Cache",
path:"/opt/cache20181/mgr",
username: "_SYSTEM",
password: "SYS",
namespace: "USER"
});
Assuming Cache is accessed via localhost listening on TCP port 7041
var open = db.open({
type: "Cache",
host: "localhost",
tcp_port: 7041,
username: "_SYSTEM",
password: "SYS",
namespace: "USER"
});
Assuming IRIS is installed under /opt/IRIS20181/
var open = db.open({
type: "IRIS",
path:"/opt/IRIS20181/mgr",
username: "_SYSTEM",
password: "SYS",
namespace: "USER"
});
Assuming IRIS is accessed via localhost listening on TCP port 7041
var open = db.open({
type: "IRIS",
host: "localhost",
tcp_port: 7041,
username: "_SYSTEM",
password: "SYS",
namespace: "USER"
});
Assuming an 'out of the box' YottaDB installation under /usr/local/lib/yottadb/r138.
var envvars = "";
envvars = envvars + "ydb_dir=/root/.yottadb\n"
envvars = envvars + "ydb_rel=r1.38_x86_64\n"
envvars = envvars + "ydb_gbldir=/root/.yottadb/r1.38_x86_64/g/yottadb.gld\n"
envvars = envvars + "ydb_routines=/root/.yottadb/r1.38_x86_64/o*(/root/.yottadb/r1.38_x86_64/r /root/.yottadb/r) /usr/local/lib/yottadb/r138/libyottadbutil.so\n"
envvars = envvars + "ydb_ci=/usr/local/lib/yottadb/r138/zmgsi.ci\n"
envvars = envvars + "\n"
var open = db.open({
type: "YottaDB",
path: "/usr/local/lib/yottadb/r138",
env_vars: envvars
});
Assuming YottaDB is accessed via localhost listening on TCP port 7041
var open = db.open({
type: "YottaDB",
host: "localhost",
tcp_port: 7041,
});
multithreaded: A boolean value to be set to 'true' or 'false' (default: multithreaded: true). Set this property to 'true' if the application uses multithreaded techniques in JavaScript (e.g. V8 worker threads).
timeout: The timeout (in seconds) to be applied to database operations invoked via network based connections. The default value is 10 seconds.
dberror_exceptions: A boolean value to be set to 'true' or 'false' (default: dberror_exceptions: false). Set this property to 'true' to instruct mg-dbx to throw Node.js exceptions if synchronous invocation of database operations result in an error condition. If this property is not set, any error condition resulting from the previous database operation can be retrieved using the db.geterrormessage() method.
var result = db.version();
Example:
console.log("\nmg-dbx Version: " + db.version());
current_namespace = db.namespace([<new_namespace>]);
Example 1 (Get the current Namespace):
var nspace = db.namespace();
Example 2 (Change the current Namespace):
var new_nspace = db.namespace("SAMPLES");
UTF-8 is the default character encoding for mg-dbx. The other option is the 8-bit ASCII character set (characters of the range ASCII 0 to ASCII 255). Native Unicode (as UTF-16) is supported for InterSystems DB Servers. The ASCII character set is a better option when exchanging single-byte binary data with the database.
current_charset = db.charset([<new_charset>]);
Example 1 (Get the current character set):
var charset = db.charset();
Example 2 (Change the current character set):
var new_charset = db.charset('ascii');
Example 3 (Native Unicode support for InterSystems DB Servers):
var new_charset = db.charset('utf-16');
new_timeout = db.settimeout(<new_timeout>);
Specify a new timeout value (in seconds) for the connection. If the operation is successful this method will return the new value for the timeout.
Example (Set the timeout to 30 seconds):
var new_timeout = db.settimeout(30);
error_message = db.geterrormessage();
This method will return the error message (as a string) associated with the previous database operation. An empty string will be returned if the previous operation completed successfully.
db.close();
global = new mglobal(db, <global_name>[, <fixed_key>]);
Or:
global = db.mglobal(<global_name>[, <fixed_key>]);
Example (using a global named "Person"):
var person = db.mglobal("Person");
Synchronous:
var result = <global>.set(<key>, <data>);
Asynchronous:
<global>.set(<key>, <data>, callback(<error>, <result>));
Example:
person.set(1, "John Smith");
Synchronous:
var result = <global>.get(<key>);
Asynchronous:
<global>.get(<key>, callback(<error>, <result>));
Example:
var name = person.get(1);
Synchronous:
var result = <global>.delete(<key>);
Asynchronous:
<global>.delete(<key>, callback(<error>, <result>));
Example:
var name = person.delete(1);
Synchronous:
var result = <global>.defined(<key>);
Asynchronous:
<global>.defined(<key>, callback(<error>, <result>));
Example:
var name = person.defined(1);
Synchronous:
var result = <global>.next(<key>);
Asynchronous:
<global>.next(<key>, callback(<error>, <result>));
Example:
var key = "";
while ((key = person.next(key)) != "") {
console.log("\nPerson: " + key + ' : ' + person.get(key));
}
Synchronous:
var result = <global>.previous(<key>);
Asynchronous:
<global>.previous(<key>, callback(<error>, <result>));
Example:
var key = "";
while ((key = person.previous(key)) != "") {
console.log("\nPerson: " + key + ' : ' + person.get(key));
}
Synchronous:
var result = <global>.increment(<key>, <increment_value>);
Asynchronous:
<global>.increment(<key>, <increment_value>, callback(<error>, <result>));
Example (increment the value of the "counter" node by 1.5 and return the new value):
var result = person.increment("counter", 1.5);
Synchronous:
var result = <global>.lock(<key>, <timeout>);
Asynchronous:
<global>.lock(<key>, <timeout>, callback(<error>, <result>));
Example (lock global node '1' with a timeout of 30 seconds):
var result = person.lock(1, 30);
Synchronous:
var result = <global>.unlock(<key>);
Asynchronous:
<global>.unlock(<key>, callback(<error>, <result>));
Example (unlock global node '1'):
var result = person.unlock(1);
Synchronous (merge from global2 to global1):
var result = <global1>.merge([<key1>,] <global2> [, <key2>]);
Asynchronous (merge from global2 to global1):
<global1>.defined([<key1>,] <global2> [, <key2>], callback(<error>, <result>));
Example 1 (merge ^MyGlobal2 to ^MyGlobal1):
global1 = new mglobal(db, 'MyGlobal1');
global2 = new mglobal(db, 'MyGlobal2');
global1.merge(global2);
Example 2 (merge ^MyGlobal2(0) to ^MyGlobal1(1)):
global1 = new mglobal(db, 'MyGlobal1', 1);
global2 = new mglobal(db, 'MyGlobal2', 0);
global1.merge(global2);
Alternatively:
global1 = new mglobal(db, 'MyGlobal1');
global2 = new mglobal(db, 'MyGlobal2');
global1.merge(1, global2, 0);
<global>.reset(<global_name>[, <fixed_key>]);
Example:
// Process orders for customer #1
customer_orders = db.mglobal("Customer", 1, "orders")
do_work ...
// Process orders for customer #2
customer_orders.reset("Customer", 2, "orders");
do_work ...
This facility provides high-performance techniques for traversing records held in database globals.
The first task is to specify the 'query' for the global traverse.
query = new mcursor(db, {global: <global_name>, key: [<seed_key>]}[, {<options>}]);
Or:
query = db.mglobalquery({global: <global_name>, key: [<seed_key>]}[, {<options>}]);
The 'options' object can contain the following properties:
multilevel: A boolean value (default: multilevel: false). Set to 'true' to return all descendant nodes from the specified 'seed_key'.
getdata: A boolean value (default: getdata: false). Set to 'true' to return any data values associated with each global node returned.
format: Format for output (default: not specified). If the output consists of multiple data elements, the return value (by default) is a JavaScript object made up of a 'key' array and an associated 'data' value. Set to "url" to return such data as a single URL escaped string including all key values ('key[1->n]') and any associated 'data' value.
Example (return all keys and names from the 'Person' global):
query = db.mglobalquery({global: "Person", key: [""]}, {multilevel: false, getdata: true});
In key order:
result = query.next();
In reverse key order:
result = query.previous();
In all cases these methods will return 'null' when the end of the dataset is reached.
Example 1 (return all key values from the 'Person' global - returns a simple variable):
query = db.mglobalquery({global: "Person", key: [""]});
while ((result = query.next()) !== null) {
console.log("result: " + result);
}
Example 2 (return all key values and names from the 'Person' global - returns an object):
query = db.mglobalquery({global: "Person", key: [""]}, {multilevel: false, getdata: true});
while ((result = query.next()) !== null) {
console.log("result: " + JSON.stringify(result, null, '\t'));
}
Example 3 (return all key values and names from the 'Person' global - returns a string):
query = db.mglobalquery({global: "Person", key: [""]}, {multilevel: false, getdata: true, format: "url"});
while ((result = query.next()) !== null) {
console.log("result: " + result);
}
Example 4 (return all key values and names from the 'Person' global, including any descendant nodes):
query = db.mglobalquery({global: "Person", key: [""]}, {multilevel: true, getdata: true});
while ((result = query.next()) !== null) {
console.log("result: " + JSON.stringify(result, null, '\t'));
}
query = db.mglobalquery({global: <seed_global_name>}, {globaldirectory: true});
Example (return all global names held in the current directory)
query = db.mglobalquery({global: ""}, {globaldirectory: true});
while ((result = query.next()) !== null) {
console.log("result: " + result);
}
Synchronous:
result = db.function(<function>, <parameters>);
Asynchronous:
db.function(<function>, <parameters>, callback(<error>, <result>));
Example:
M routine called 'math':
add(a, b) ; Add two numbers together
quit (a+b)
JavaScript invocation:
result = db.function("add^math", 2, 3);
M DB Servers implement Transaction Processing by means of the methods described in this section. When implementing transactions, care should be taken with JavaScript operations that are invoked asynchronously. All the Transaction Processing methods describe here can only be invoked synchronously.
result = db.tstart(<parameters>);
Example:
result = db.tstart();
result = db.tlevel(<parameters>);
Example:
tlevel = db.tlevel();
result = db.tcommit(<parameters>);
Example:
result = db.tcommit();
result = db.trollback(<parameters>);
Example:
result = db.trollback();
Synchronous:
result = new mclass(db, <class_name>, <classmethod_name>, <parameters>);
Or:
result = db.classmethod(<class_name>, <classmethod_name>, <parameters>);
Asynchronous:
db.classmethod(<class_name>, <classmethod_name>, <parameters>, callback(<error>, <result>));
Example (Encode a date to internal storage format):
result = db.classmethod("%Library.Date", "DisplayToLogical", "10/10/2019");
The following simple class will be used to illustrate this facility.
Class User.Person Extends %Persistent
{
Property Number As %Integer;
Property Name As %String;
Property DateOfBirth As %Date;
Method Age(AtDate As %Integer) As %Integer
{
Quit (AtDate - ..DateOfBirth) \ 365.25
}
}
person = db.classmethod("User.Person", "%New");
Add Data:
result = person.setproperty("Number", 1);
result = person.setproperty("Name", "John Smith");
result = person.setproperty("DateOfBirth", "12/8/1995");
Save the object record:
result = person.method("%Save");
Retrieve data for object %Id of 1.
person = db.classmethod("User.Person", "%OpenId", 1);
Return properties:
var number = person.getproperty("Number");
var name = person.getproperty("Name");
var dob = person.getproperty("DateOfBirth");
Calculate person's age at a particular date:
today = db.classmethod("%Library.Date", "DisplayToLogical", "10/10/2019");
var age = person.method("Age", today);
Once created, it is possible to reuse containers holding previously instantiated objects using the reset() method. Using this technique helps to reduce memory usage in the Node.js environment.
Example 1 Reset a container to hold a new instance:
person.reset("User.Person", "%New");
Example 2 Reset a container to hold an existing instance (object %Id of 2):
person.reset("User.Person", "%OpenId", 2);
mg-dbx provides direct access to the Open Source MGSQL engine (https://github.com/chrisemunt/mgsql) and InterSystems SQL (IRIS and Cache).
The first task is to specify the SQL query.
query = new mcursor(db, {sql: <sql_statement>[, type: <sql_engine>]});
Or:
query = db.sql({sql: <sql_statement>[, type: <sql_engine>]});
Example 1 (using MGSQL):
query = db.sql({sql: "select * from person"});
Example 2 (using InterSystems SQL):
query = db.sql({sql: "select * from SQLUser.person", type: "Cache"});
Synchronous:
var result = <query>.execute();
Asynchronous:
<query>.execute(callback(<error>, <result>));
The result of query execution is an object containing the return code and state and any associated error message. The familiar ODBC return and status codes are used.
Example 1 (successful execution):
{
"sqlcode": 0,
"sqlstate": "00000",
"columns": [
{
"name": "Number",
"type": "INTEGER"
},
"name": "Name",
"type": "VARCHAR"
},
"name": "DateOfBirth",
"type": "DATE"
}
]
}
Example 2 (unsuccessful execution):
{
"sqlcode": -1,
"sqlstate": "HY000",
"error": "no such table 'person'"
}
In result-set order:
result = query.next();
In reverse result-set order:
result = query.previous();
In all cases these methods will return 'null' when the end of the dataset is reached.
Example:
while ((row = query.next()) !== null) {
console.log("row: " + JSON.stringify(result, null, '\t'));
}
The output for each iteration is a row of the generated SQL result-set. For example:
{
"number": 1,
"name": "John Smith",
}
For 'select' queries that generate a result-set it is good practice to invoke the 'cleanup' method at the end to delete the result-set held in the database.
Synchronous:
var result = <query>.cleanup();
Asynchronous:
<query>.cleanup(callback(<error>, <result>));
Synchronous:
<query>.reset({sql: <sql_statement>[, type: <sql_engine>]);
Asynchronous:
<query>.reset({sql: <sql_statement>[, type: <sql_engine>], callback(<error>, <result>));
In mg-dbx the default character encoding scheme is UTF-8. When transmitting binary data between the database and Node.js there are two options.
On the input (to the database) side all mg-dbx function arguments can be presented as Node.js Buffers and mg-dbx will automatically detect that an argument is a Buffer and process it accordingly.
On the output side the following functions can be used to return the output as a Node.js Buffer.
dbx::function_bx
dbx::classmethod_bx
mglobal::get_bx
mclass::classmethod_bx
mclass::method_bx
mclass::getproperty_bx
These functions work the same way as their non '_bx' suffixed counterparts. The only difference is that they will return data as a Node.js Buffer as opposed to a type of String.
The following two examples illustrate the two schemes for receiving binary data from the database.
Example 1: Receive binary data from a DB function as a Node.js 8-bit character stream
<db>.charset('ascii');
var stream_str8 = <db>.function(<function>, <parameters>);
<db>.charset('utf-8'); // reset character encoding
Example 2: Receive binary data from a DB function as a Node.js Buffer
var stream_buffer = <db>.function_bx(<function>, <parameters>);
mg-dbx functionality can now be used with Node.js/V8 worker threads. This enhancement is available with Node.js v12 (and later).
Use the following constructs for instantiating mg-dbx objects in multi-threaded applications:
// Use:
var <global> = new mglobal(<db>, <global>);
// Instead of:
var <global> = <db>.mglobal(<global>);
// Use:
var <cursor> = new mcursor(<db>, <global_query>);
// Instead of:
var <cursor> = <db>.mglobalquery(<global_query>)
// Use:
var <class> = new mclass(<db>, <classmethod>);
// Instead of:
var <class> = <db>.classmethod(<classmethod>);
// Use:
var <sql> = new mcursor(<db>, <sqlquery>);
// Instead of:
var <sql> = <db>.sql(<sqlquery>);
The following scheme illustrates how mg-dbx should be used in threaded Node.js applications.
const { Worker, isMainThread, parentPort, threadId } = require('worker_threads');
if (isMainThread) {
// start the threads
const worker1 = new Worker(__filename);
const worker2 = new Worker(__filename);
// process messages received from threads
worker1.on('message', (message) => {
console.log(message);
});
worker2.on('message', (message) => {
console.log(message);
});
} else {
var dbx = require('mg-dbx').dbx;
// And as required ...
var mglobal = require('mg-dbx').mglobal;
var mcursor = require('mg-dbx').mcursor;
var mclass = require('mg-dbx').mclass;
var db = new dbx();
db.open(<parameters>);
var global = new mglobal(db, <global>);
// do some work
var result = db.close();
// tell the parent that we're done
parentPort.postMessage("threadId=" + threadId + " Done");
}
mg-dbx provides an Event Log facility for recording errors in a physical file and, as an aid to debugging, recording the mg-dbx functions called by the application. This Log facility can also be used by Node.js applications.
To use this facility, the Event Log file must be specified using the following function:
db.setloglevel(<log_file>, <Log_level>, <log_filter>);
Where:
Examples:
db.setloglevel("c:/temp/mg-dbx.log", "e", "");
db.setloglevel("/tmp/mg-dbx.log", "ft", "dbx::set,mglobal::set,mcursor::execute");
Node.js applications can write their own messages to the Event Log using the following function:
db.logmessage(<message>, <title>);
Logging can be switched off by calling the setloglevel function without specifying a log level. For example:
db.setloglevel("c:/temp/mg-dbx.log");
Copyright (c) 2018-2024 MGateway Ltd,
Surrey UK.
All rights reserved.
http://www.mgateway.com
Email: cmunt@mgateway.com
Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.
Extend the processing of InterSystems Object References (orefs) to cater for instances of an object embedded as a property in other objects. For example, consider two classes: Patient and Doctor where an instance of a Doctor may be embedded in a Patient record (On the Server: "Property MyDoctor As Doctor"). And on the Node.js side...
var patient = db.classmethod("User.Patient", "%OpenId", patient_id);
var doctor = patient.getproperty("MyDoctor");
var doctor_name = doctor.getproperty("Name");
Correct a fault in the processing of output values returned from YottaDB functions that led to output string values not being terminated correctly. The result being unexpected characters appended to function outputs.
FAQs
High speed Synchronous and Asynchronous access to InterSystems Cache/IRIS and YottaDB from Node.js.
We found that mg-dbx demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Research
Security News
Socket’s threat research team has detected six malicious npm packages typosquatting popular libraries to insert SSH backdoors.
Security News
MITRE's 2024 CWE Top 25 highlights critical software vulnerabilities like XSS, SQL Injection, and CSRF, reflecting shifts due to a refined ranking methodology.
Security News
In this segment of the Risky Business podcast, Feross Aboukhadijeh and Patrick Gray discuss the challenges of tracking malware discovered in open source softare.