databaseConnections

This option specifies a list of database connections for use with Profound.js's database access APIs. This option must be set to an Array. Each element is an Object with the following properties:

• name: (String/required): A unique name that will be used to refer to the database connection using the pjs.getDB() API, or by using properties in the Designer.
• default: (Boolean/optional): If set to true, this connection will be the default. Only one connection can be marked as default. If no connections are marked as default, then the first connection in the array will be the default.
• driver: (String/required): Valid values are "IBMi" (DB2 for IBM i), "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", and "oracledb". See the NPM pages for mysql, mssql, and oracledb for installation instructions.
• driverOptions: (Object/optional): This value is optional for the IBMi driver, invalid for the jsonDB driver, and required for the mysql, mssql, and oracledb drivers. 
  • For IBMi, the object properties/values must match the attribute names/values documented in the DB2/i SQL Call Level Interface.
    • See below for additional properties.
  • See the driver authors' documentation for mysql, mssql, and oracledb.

• credentialsFile: (String/optional): Path to a file containing an encrypted user id / password created by the "store_credentials" utility provided with Profound.js. This is an alternative to specifying the user and password in clear text in the driverOptions object.  (This option is only for the mysql, mssql, and oracledb drivers.  For DB2 for IBM i connections using the Profound.js Connector on IBM i use the connectorCredentials or connectorUser/connectorPassword configuration options instead.) 

  Availability

  The credentialsFile option will be available in Profound.js versions 5.4.0 and newer.

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

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 using this option, it will always override the "user" and "password" items that may already be defined in the "driverOptions" object.

Driver Comparison

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

Additional Driver Options for IBM i DB2

databaseConnections: [
  {
    name: "myIBMi",
    default: true,
    driver: "IBMi",
	driverOptions: {
		// Five minutes of waiting (300 sec). Particularly useful when conducting load tests to accommodate many queued requests.
		acquireTimeout: 300,		// Optional: Number of seconds to wait for a database connection to become available;			default is 10
		sessionMinPoolSize: 10,		// Optional: Minimum number of database connections to start for interactive sessions;			default is 20
		sessionMaxPoolSize: 1000,	// Optional: Maximum number of database connections to allow for interactive sessions;			default is 2000
		apiMinPoolSize: 10,			// Optional: Minimum number of database connections to start for API sessions;					default is 5
		apiMaxPoolSize: 20			// Optional: Maximum number of database connections to allow for API sessions;					default is 100
	}
  }
]

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 to connect to a MongoDB database and retrieve certain documents from this database might look as follows:

// 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;

Example

databaseConnections: [
  {
    name: "sales",
    driver: "mysql",
    driverOptions: {
      host: "my_hostname",
      user: "my_user",
      password: "my_password",
      database: "sales"
    }
  },
  {
    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: {
      server: "my_hostname", // Use "my_hostname\\my_instance_name" to connect to a named MS SQL instance.
      user: "my_user",
      password: "my_password",
      database: "crm"
    }
  },
  {
    name: "research",
    driver: "mysql",
	credentialsFile: "/profoundjs/research_credentials",
    driverOptions: {
      host: "my_hostname",
      database: "development"
    }
  }
]

Configuration for Profound.js Spaces

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

• 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 database connection names "workspace" and "workspace_ibmi" are reserved and can't be used.
• If a default connection is not specified, the built-in workspace database is the default database. If a different default is specified, the built-in workspace database can still be accessed by using name "workspace" with pjs.getDB(). 

The setting is applied when the workspace server is started. After making changes, use the option Server→Restart App Server in the IDE to apply the configuration. The setting is not validated until server startup time, and the server will fail to start if there are any problems. To troubleshoot, check the server logs using the option 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", // Use "my_hostname\\my_instance_name" to connect to a named MS SQL instance.
      "user": "my_user",
      "password": "my_password",
      "database": "crm"
    }
  }
]