Data Sources

Applications need a place to store and manager their data. Apex Designer apps can use many different data sources. When you create a new project in Apex Designer, a database is automatically created and a data source is added for it. This is a PostgreSQL database and since it is the default data source, any business object that you add to the project will use this database.

Many projects need no more than this default data source. You can, however, change the default data source to use a different type of database or other source for your data. A project can also have more than one data source, where some business objects use one and others use the other (more on this below).

To access the Data Source page, click Settings settings on the ribbon and then Data Sources on the left.

Configuring a Data Source

You can add a new data source by typing a name in the field at the bottom of the page and pressing enter.

Configure the data source with these parameters:

Name is the name of the data source, which is unique within the project

Description further identifies the data source

Color sets how this data source is shown in the Business Objects Diagram, using a color name or hex code, like "#7fff00"

Connector sets the type to use for the data source (e.g. mysql, memory, rest or salesforce)

Implementation selects how the settings will be defined: Fields, Connection String or Code (see below)

Active Data Source indicates the default data source for the project

Implementation

Choose the best implementation option based on the values that you need to include the the configuration of your data source. See Examples below for additional samples.

Fields

A predefined set of fields are used to populate the generated configuration for the datasource. Each field is added to the JSON configuration, along with any values from Addition Options.

Connection String

Some connectors, including PostgreSQL, MySQL and SQL Server, support using a connection string that defines some or all of the parameters for the data source. For example, a SQL Server connection string includes the user name, password, server name, port and database name:

mssql://test:mypassword@localhost:1433/dev

Code

You can also use JavaScript code to set connector parameters. The code must set parameters on the "overrides" object. In this example, a SQL Server data source example named MSSQL uses code to decrypt a password and set it as part of the url parameter:

const debug = require('debug')('jv-customer-due-diligence:mssqlDatasource');
debug.log = console.log.bind(console);

const decrypt = require('decrypt');

let username = process?.env?.mssqlUser?.replace(/\\/g, '\\') || '';
var password = decrypt(process.env.mssqlPassword);

overrides['MSSQL'] = {
    database: process.env.mssqlDatabase,
    name: 'MSSQL',
    url:
        'mssql://' + encodeURIComponent(username) + ':' +
        encodeURIComponent(password) + '@' + process.env.mssqlHost + ':' +
        process.env.mssqlPort + '/' + process.env.mssqlDatabase
};

debug('overrides', overrides['MSSQL']);

Multiple Data Sources

Some applications need to use more than one datasource. For example, consider an application to manage incoming invoices. The app could use one data source for details of the invoice, but another for purchase orders.

By default, business objects are set to use the default data source. To select a different data source, open the Business Object Properties using the settingssettingsbutton from a business object, then select the data source using the Datasource dropdown.

Libraries

Any project can have data sources defined, including Libraries. When a library includes a data source, an application that uses it can make use of that data source.

For example, the Camunda Library includes a data source that allows interaction with the Camunda process engine. The Business Objects defined in the Camunda library are configured to use the Camunda data source.

Other libraries do not have a data source defined within them, yet have Business Objects in the library. For example, the Apex Support Library has several business objects that are all set to use the default data source. If an application uses this library, these business objects will be persisted in the default data source defined by the application.

Connectors

Data sources in Apex Designer make use of the Loopback API framework. There are dozens of connectors available for Loopback. The following connectors can be selected when configuring a data source in Apex Designer:

dashdb is the dashDB cloud-based data warehousing service based on DB2
db2 is IBM's DB2 relational database for Linux, Unix and Windows
db2z is IBM's DB2 relational database for Z-series systems
ibmi is IBM's DB2 relational database for i-series systems
memory is the build-in memory connector used only if data does not need to be persisted
mongodb is the no-SQL open source database MongoDB
mssql is Microsoft's SQL Server relation database
mysql is the open-source relational database MySql
oracle is the Oracle relational database
postgresql is the PostgreSQL open-source relational database
rest allows you to connect to a REST API
soap allows you to connect to a SOAP API
salesforce is the cloud-based CRM system from Salesforce

Other connectors can also be used, but require additional implementation. These are the additional connectors supported by Loopback:

Cassandra connector
Cloudant connector
CouchDB 2.x connector
gRPC connector
Informix connector
OpenAPI connector
Redis connector
Redis key-value connector
SQLite3 connector
z/OS Connect Enterprise Edition connector
Email connector
JSON RPC connector
MQ Light connector
Push connector
Remote connector
REST connector
SOAP connector
Storage connector
Swagger connector

There are also additional connectors that are maintained by others in the open-source community:

If you would like any of these additional connectors added to Apex Designer, just click the "Ask a Question" button and tell us about it.

Example Configurations

Below are a few examples code implementations for data sources.

DB2z

let dsn = 'DATABASE=' + process.env.db2Database;
dsn += ';HOSTNAME=' + process.env.db2Host;
dsn += ';UID=' + process.env.db2User;
dsn += ';PWD=' + process.env.db2Password;
dsn += ';PORT=' + process.env.db2Port;
dsn += ';PROTOCOL=TCPIP';
dsn += ';CurrentSchema=' + process.env.db2Schema;

overrides['DB2Z'] = {
	name: 'DB2Z',
	connector: 'db2z',
	hostname: process.env.db2Host,
	port: process.env.db2Port,
	database: process.env.db2Database,
	username: process.env.db2User,
	password: process.env.db2Password,
	schema: process.env.db2Schema,
	debug: process.env.db2Debug,
	dsn: dsn
};

SQL Server

This is a simplified version of the mssql implementation above.

overrides['MSSQL'] = {
	host: process.env.mssqlHost,
	port: Number(process.env.mssqlPort),
	username: username,
	password: password,
	database: process.env.mssqlDatabase,
	name: 'MSSQL',
	lazyConnect: false,
	ssl: true
};

Note that some passwords in our examples reference environment variables; appropriate care should be taken in how you store and handle credentials.

Define how your app accesses data