Connecting to an MDB database with Node.js

This tutorial shows you how to connect and work with bSQL in Node.js.

Requirements

• Node.js
• MDB >= 1.0

Installation

Install with npm:

$ npm install google-protobuf grpc
$ npm install mdb-server

Connecting

Before we proceed let's look at the information we need to collect.

Most of these are self-explanatory.

• severAddress: the IP Address of your MDB instance.
• username/password: your MDB credentials. It is important you use your MBD credentials, NOT the credentials associated with your blockpoint portal.
• dbname: the name of the database we are connecting to.
• parameters: used to set interpolateParams, set to true.

We can use this information to connect to our MDB instance. When CreateConnection is called, we establish a connection the MDB instance and new session is created. The code below connects to the "main" database and closes the connection.

import * as mdb from "mdb-server";

// connect to the master database
let conn = await mdb.CreateConnection(
        {
          username: "username",
          password: "password",
          serverAddress: "server_address",
          serverPort: 5461,
          databaseName: "main",
          parameters: new Map([["interpolateParams", true]])
        }
)

// Connect to the database
try {
  await conn.connect()
} catch (err) {
  await conn.close()
}

await conn.close()

Executing statements

In order to execute database commands we can use the exec method. We call exec for all database commands that don't return rows. This includes database mutations, management, security and most analytical commands. When exec is called on a database connection, a new transaction is created an executed atomically.

let resp;
try {
    resp = await conn.exec("CREATE DATABASE main")
} catch (err) {
    await conn.close()
}

Executing Queries.

This example queries from the sys_sessions system blockchain. A few things to note:

• query returns a *Rows object, in order to get the value deserialize must be called.

let rows;
try {
    rows = await conn.query("SELECT * FROM sys_session")
} catch (err) {
    conn.close()
}

for (const row of rows) {
  let rowColumn = row.get("user_id");
  let deserialize = rowColumn.deserialize()
  console.log(deserialize)
}

Storing a file

We can use the node library to store files immutably in our database. The storeFile method takes in a name, local filePath, and extension for the file to be stored.

try {
  resp = await conn.storeFile(imageName, "app/blockpoint.png", "png")
} catch (err) {
  await conn.close()
}

Exporting a file

We can use the node library to export files that we have stored in the database. This is a two-step process that uses exportFile and saveFile. exportFile takes in the name of the file, the output path to write the file, and the file extension.

try {
  let resp = await conn.exportFile(imageName, "app/exports/out_image.png", "png")
  await resp.saveFile()
} catch (err) {
  await conn.close()
}