Apex Designer Documentation
What's New
Getting Started
Projects
Business Objects
Processes
User Interfaces
App Behaviors
Files
Dependencies
Project Settings
Libraries
Patterns and Best Practices
App Showcase

Data Sources

Define how your app accesses data

Blank Profile Image
Written by Jim Volpe
Updated 21 days ago
7 min read

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.

Data source definition page

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.

Data source configuration fields

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.

Multiple data sources on the relationship diagram

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. 

Business object properties dialog

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:

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. 

Powered by Apex Designer
Terms of ServicePrivacy Policy
© 2017-2022 Apex Process Consultants