This option was added in Profound.js 5.0.0. It replaces the dbDriver and connectionDetails options, which are now deprecated. Existing configurations using dbDriver and connectionDetails continue to work in Profound.js 5.0.0 and later, but those options do not allow for multiple database connections. It is not valid to use both databaseConnections and dbDriver/connectionDetails in the same configuration.

Description

This option specifies a list of database connections for use with the Profound.js database access APIs. It must be set to an array, where each element is an object with the properties below.

Properties

name (String, required) - A unique name used to refer to the connection with the pjs.getDB() API, or through properties in the Designer;
default (Boolean, optional) - If set to true, this connection is the default. Only one connection can be marked as default. If none is marked, the first connection in the array is the default;

driver (String, required) - Valid values are "IBMi" (Db2 for IBM i), "odbc" (any database reachable through ODBC), "mysql" (MySQL/MariaDB), "mssql" (Microsoft SQL Server), "oracledb" (Oracle Database), and "jsonDB" (Profound.js JSON database). The corresponding driver package must be installed separately in order to use "mysql", "mssql", "oracledb", and "odbc";
type (String, required for "odbc") - The target database type: "db2i", "mysql", "mssql", or "oracledb". It is not valid for any other driver (see Using ODBC Database Driver);
driverOptions (Object) - Optional for the IBMi driver, invalid for the jsonDB driver, and required for mysql, mssql, oracledb, and odbc:
- For IBMi, the properties and values must match the attribute names and values documented in the Db2 for i SQL Call Level Interface, plus the additional options described below;
- For the other drivers, see the driver authors’ documentation for mysql, mssql, oracledb, and odbc.
credentialsFile (String, optional) - Path to a file containing an encrypted user id and password created by the store_credentials.js utility supplied with Profound.js. This is an alternative to specifying the user and password in clear text in the driverOptions object.

The credentialsFile option is available in Profound.js versions 5.4.0 and newer, and requires an encryptionKey to be configured. It is intended for the mysql, mssql, oracledb, and odbc drivers – for Db2 for IBM i connections using the Profound.js Connector on IBM i, use connectorCredentials or connectorUser/connectorPassword instead.

There is no limit on the number or types of database connections, except that there can be only one connection for the IBMi driver.

Default Value

When this option is not specified, it defaults to:

databaseConnections: [
  {
    name: "default",
    default: true,
    driver: "IBMi"
  }
]

Creating a Credentials File

To create a credentials file, run the following command from your Profound.js installation directory and enter the desired user id and password when prompted:

node store_credentials.js

The credentials file is decrypted when the Profound.js server starts, and the user id and password are added to the driverOptions object like this:

user: "decrypted_userid",
password: "decrypted_password",

When this option is used, it always overrides any user and password items already defined in the driverOptions object.

Driver Comparison

Profound.js Database Driver	driver Name	Record Level Access	SQL	Required Components	Supported API

Profound.js Database Driver	driver Name	Record Level Access	SQL	Required Components	Supported API
IBM i Db2	`"IBMi"`	Yes	Yes	Profound.js Connector on IBM i	All Record Level Access API, All SQL API, Data API
IBM i Db2 via ODBC (see Using ODBC Database Driver)	`"odbc"`	Yes	Yes	odbc npm package and an ODBC driver for the target database	All SQL API, Data API (No direct program/procedure call or CL command)
Offline JSON Store (see offline capabilities)	`"jsonDB"`	Yes	No	No extra components required	Record Level Access API (No commitment control or record locking)
MySQL / MariaDB / Amazon Aurora	`"mysql"`	Yes	Yes	Profound.js Connector for MySQL and the mysql npm package	All SQL API, Record Level Access API (No commitment control, record locking, or access by RRN), Data API
Microsoft SQL Server	`"mssql"`	Yes	Yes	Profound.js Connector for MS SQL Server and the mssql npm package	All SQL API, Record Level Access API (No commitment control, record locking, or access by RRN), Data API
Oracle	`"oracledb"`	Yes	Yes	Profound.js Connector for Oracle and the oracledb npm package	All SQL API, Record Level Access API (No commitment control, record locking, or access by RRN), Data API

For IBM i Db2, if the IBM i system is remote in relation to the Profound.js server and you are not starting your session from Genie, also set the connectorURL and connectorCredentials configuration settings.

Additional Driver Options for IBM i Db2

These options tune the Db2 connection pool. Raise the pool sizes when many sessions or API calls run concurrently:

databaseConnections: [
  {
    name: "myIBMi",
    default: true,
    driver: "IBMi",
    driverOptions: {
      // Five minutes of waiting (300 sec). Particularly useful during load tests,
      // to accommodate many queued requests.
      acquireTimeout: 300,       // Seconds to wait for a connection to become available. Default: 10
      sessionMinPoolSize: 10,    // Minimum connections started for interactive sessions.  Default: 20
      sessionMaxPoolSize: 1000,  // Maximum connections allowed for interactive sessions.  Default: 2000
      apiMinPoolSize: 10,        // Minimum connections started for API sessions.          Default: 5
      apiMaxPoolSize: 20,        // Maximum connections allowed for API sessions.          Default: 100
      setJobDate: true           // Added in 7.1.0. Set the job date when a connection is
                                 // acquired from the pool.                                Default: false
    }
  }
]

Additional Driver Options for Microsoft SQL Server

Starting with Profound.js version 7.28.0, SSL certificate verification is enabled by default for MSSQL connections. If your SQL Server uses a self-signed certificate, set trustServerCertificate to true.

databaseConnections: [
  {
    name: "crm",
    driver: "mssql",
    driverOptions: {
      server: "my_hostname",
      user: "my_user",
      password: "my_password",
      database: "crm",
      options: {
        trustServerCertificate: true  // Set to true for self-signed certificates
      }
    }
  }
]

Full Example

An instance with five connections: a MySQL sales database, the single IBM i connection (marked as the default), an Oracle legacy database, a Microsoft SQL Server CRM database, and a second MySQL database whose credentials come from an encrypted file rather than the configuration:

databaseConnections: [
  {
    // MySQL connection, referenced in code as pjs.getDB("sales").
    name: "sales",
    driver: "mysql",
    driverOptions: {
      host: "my_hostname",
      user: "my_user",
      password: "my_password",
      database: "sales"
    }
  },
  {
    // The single IBM i connection, and the default for this instance.
    name: "inventory",
    driver: "IBMi",
    default: true,
    driverOptions: {
      SQL_ATTR_COMMIT: "SQL_TXN_NO_COMMIT",
      SQL_ATTR_DATE_FMT: "SQL_FMT_USA"
    }
  },
  {
    name: "legacy",
    driver: "oracledb",
    driverOptions: {
      user: "legacy_user",
      password: "legacy_password",
      connectString: "my_hostname/ORCLCDB.my_domain"
    }
  },
  {
    name: "crm",
    driver: "mssql",
    driverOptions: {
      // Use "my_hostname\\my_instance_name" to connect to a named MS SQL instance.
      server: "my_hostname",
      user: "my_user",
      password: "my_password",
      database: "crm",
      options: {
        trustServerCertificate: true  // Set to true for self-signed certificates
      }
    }
  },
  {
    // No user/password here - they come from the encrypted credentials file,
    // which requires an encryptionKey to be configured.
    name: "research",
    driver: "mysql",
    credentialsFile: "/profoundjs/research_credentials",
    driverOptions: {
      host: "my_hostname",
      database: "development"
    }
  }
]

Connecting to Other Databases

Connecting to databases for which a driver does not exist is accomplished through standard npm packages and Fibers.

For example, a Profound.js module that connects to a MongoDB database and retrieves certain documents might look like this:

// Bring in Mongo Client from the 'mongodb' NPM package
const MongoClient = require('mongodb').MongoClient;

// Connection URL
const url = 'mongodb://localhost:27017';

// Database Name
const dbName = 'myproject';

function getMongoDocs() {
  var connect = pjs.fiber.wrap(MongoClient.connect, MongoClient);
  var client = connect(url);
  var db = client.db(dbName);
  var collection = db.collection('documents');
  var findDocs = collection.find({'someproperty': 'somevalue'});
  var findDocsToArray = pjs.fiber.wrap(findDocs.toArray, findDocs);
  var docs = findDocsToArray();

  console.log(docs);

  client.close();
  return docs;
}

exports.run = getMongoDocs;

Configuration for Profound.js Spaces

In Profound.js Spaces, this setting is configured by adding a property called databaseConnections to the workspace settings file at Workspace Files/.noderun/settings.json. The following differences apply:

The setting must be specified in JSON format, meaning all property names must be quoted;
The IBM i driver does not work in Profound.js Spaces. Applications can only connect to IBM i when called via the NODERUN command or program;
The connection names workspace and workspace_ibmi are reserved and cannot be used;
If a default connection is not specified, the built-in workspace database is the default. If a different default is specified, the built-in workspace database can still be accessed by using the name "workspace" with pjs.getDB().

The setting is applied when the workspace server is started. After making changes, use Server→Restart App Server in the IDE to apply the configuration. The setting is not validated until server startup time, and the server fails to start if there are any problems. To troubleshoot, check the server logs using Server→View Server Logs.

Example for Profound.js Spaces

"databaseConnections": [
  {
    "name": "sales",
    "driver": "mysql",
    "driverOptions": {
      "host": "my_hostname",
      "user": "my_user",
      "password": "my_password",
      "database": "sales"
    }
  },
  {
    "name": "legacy",
    "driver": "oracledb",
    "driverOptions": {
      "user": "legacy_user",
      "password": "legacy_password",
      "connectString": "my_hostname/ORCLCDB.my_domain"
    }
  },
  {
    "name": "crm",
    "driver": "mssql",
    "driverOptions": {
      "server": "my_hostname",
      "user": "my_user",
      "password": "my_password",
      "database": "crm",
      "options": {
        "trustServerCertificate": true
      }
    }
  }
]

For information on how to modify this setting, see Configuration File.

databaseConnections