You may want to use master data managed by an external system in Questetra BPM Suite. In this article, we are going to use data that has been managed by Amazon RDS (Relational Database Service) as the external options master for Search Select Box in Questetra BPM Suite.
What is a Search Select Box?
A Search Select Box is one of the Data Item types. When you enter a search string and press the search button, a list of options that contains the search string out from the master data will be shown and you can select one from the list to enter the data.
Benefits of using RDS as an External Master
When you use RDS as an external master, you can take advantage of its capability of handling a large amount of data, and changes in the data can be reflected in real-time. For more information on how to manage master data and the advantages and disadvantages of each method please refer to the article Several Ways to Manage Master Data with Questetra. (the method introduced in this article corresponds to “Pattern 1”)
AWS Services to Use
The method introduced in this article uses the following services from AWS (Amazon Web Service). The purpose is shown in parentheses.
- RDS (database)
- RDS Proxy (database connection proxy)
- Secrets Manager (management of authentication information)
- Lambda (application creation)
- API GatewayAPI Gateway (Create API)
How it works
The general mechanism is as shown in the figure below.
When the Search Select Box sends a GET request to the API Gateway, the event is sent to Lambda. Based on the event, Lambda queries RDS for data and returns the result.
Although not shown in the figure, the connection to RDS goes through RDS Proxy and the authentication information is managed by Secrets Manager.
You can connect to RDS directly from Lambda without going through the RDS Proxy, but this can lead to a large number of connections being initiated and consuming resources, and by going through the RDS Proxy you can manage your connections more efficiently.
Outline of Procedure
Using the API Gateway and Lambda, create an API to retrieve the RDS data. Set the endpoint of the API you created as the options data URL of the search select box and use the RDS as an external master.
Outline of Procedure
- Create a database with RDS
- Configure settings to connect to the database via RDS Proxy
- Create a Lambda function (this article uses Node.js)
- Attach an RDS Proxy to your Lambda function
- Set up the API Gateway as a trigger for your Lambda function
- Create an App that uses a Search Select Box in Questetra BPM Suite
Required Environment
You need the following to proceed with the steps in this article:
- MySQL client (used to create the database)
- Node.js / npm command (used to create Lambda function code)
Procedure
1. Create a Database with RDS
1-1. Database creation
Create the database in RDS. Please select the engine type and edition which RDS Proxy supports (RDS Proxy restrictions). In this case, Amazon Aurora compatible with MySQL 5.6 is selected.
Enter the DB cluster identifier and Master username. In this case, DB cluster identifier is “sample-database-1” and Master username is “admin”.
If you click [Create database] and wait for a while, you will see “Successfully created database {DB cluster identifier} ” at the top.
Click [View credential details] and make a note of the Master Username, Master Password, and Endpoint.
1-2. Connect to the database and create a table
Connect to the database with the following mysql command (type the master password after the command):
mysql -h {ENDPOINT} -P {PORT NUMBER} -u {MASTER USERNAME} -pThe port number is displayed by clicking the database identifier in the database list. By default, it is set to 3306.
If the connection is successful the following will be displayed.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.10 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>If the connection fails check the database instance settings (Public accessibility permittance, VPC security groups settings). For specific instructions, see Using Amazon RDS as an External Master (Troubleshooting).
If you can connect to the database, first check the character code settings to prevent garbled characters (You can skip this step if the data does not include Double-byte characters). Type mysql> followed by the following mysql command to execute it.
show variables like 'char%';It is good if the value of these variables, character_set_client, character_set_connection, character_set_database, character_set_results, and character_set_server is utf8 as shown below.
+--------------------------+--------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /rdsdbbin/oscar-5.6.10a.200340.0/share/charsets/ |
+--------------------------+--------------------------------------------------+If there are parameters that are not utf8, create a parameter group in RDS and apply it to your database. For the specific procedure, see Using Amazon RDS as an External Master (Troubleshooting).
After setting the character code, create the database schema and table. In this example, we will create a database schema in the name “sample_db” and create a table in the name “nations” in it.
Creating the database schema
Create a database schema named “sample_db” by executing the following SQL statement after mysql>
create database sample_db;Switching database schema
Execute the following SQL statement to switch to the “sample_db” created earlier.
use sample_db;Creating table
Create a table with two columns, value and display. value is the choice ID and display is the display label.
CREATE TABLE `nations` (
`value` VARCHAR(10) NOT NULL PRIMARY KEY,
`display` VARCHAR(100) NOT NULL
) DEFAULT CHARSET=utf8;Inserting data
Here, insert 4 rows of data.
INSERT INTO nations (value, display)
VALUES
("JP", "JAPAN"),
("US", "USA"),
("UK", "UNITED KINGDOM"),
("AU", "AUSTRALIA");2. Configure the Connection to the Database via RDS Proxy
2-1. Save database credentials as a secret
Open Secrets Manager and click Save a new secret.
With ‘Credentials for RDS database’ selected, enter the database master username and master password to the Username and Password respectively. Select your DB instance and click Next.
Enter the secret name. Here name it “sample-rds-secret”. Click Next.
Leave the other defaults and click Next to save.
Once the secret is created, open the details and make a note of the Secret ARN.
2-2．Create IAM role so RDS Proxy to read secrets
First, create a policy for the IAM role. Open IAM, select Policies in Access management in the left menu, and click Create policy.
Select the JSON tab, describe the policy as shown below (enter the secret ARN you just created in Resource) and proceed to Review policy.
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "VisualEditor0",
"Effect": "Allow",
"Action": [
"secretsmanager:GetResourcePolicy",
"secretsmanager:GetSecretValue",
"secretsmanager:DescribeSecret",
"secretsmanager:ListSecretVersionIds"
],
"Resource": [
"{SECRET ARN}"
]
},
{
"Sid": "VisualEditor1",
"Effect": "Allow",
"Action": [
"secretsmanager:GetRandomPassword",
"secretsmanager:ListSecrets"
],
"Resource": "*"
}
]
}
Enter a name for your policy and click Create Policy. Here we will call it “AllowAccessToSampleRdsSecret”.
After creating the policy, create an IAM role. Select Roles in Access Management on the left menu of the IAM page and click Create role.
Select AWS service, and select RDS and RDS – Add Role to Database in Select your use case, then proceed to Next Permission.
Select the policy you just created and go to Next: Tags.
Go to Next: Review without adding any tags. Enter the Role name and click Create Role. In this case, it is “rds-getting-secret-role”.
Once the role is created, open the details and make a note of the Role ARN.
3. Creating Lambda Function
3-1. Creating Lambda Function
Open Lambda, select Function in the left menu, and click Create Function.
Select “Author from scratch” and enter the function name. In this example, name it “myRdsFunction”. Select Node.js 12.x for the runtime and click Create Function.
3-2. Creating function code locally
We will create the function code locally. This article uses the following two packages.
npm init -yInstall the two packages (promise-mysql and xmlbuilder) respectively with the following command.
npm install {PACKAGE NAME} -saveCreate the file “index.js” and write the code.
Code example (index.js)
Below is an example of the code.
const mysql = require('promise-mysql');
const builder = require('xmlbuilder');
const db_config = {
host : process.env['endpoint'],
user : process.env['user'],
password : process.env['password'],
database : process.env['db']
};
const table = process.env['table'];
const pool = mysql.createPool(db_config);
exports.handler = async (event) => {
let response = {};
let connection;
try {
connection = await (await pool).getConnection();
console.log("Connected");
} catch (e) {
console.log(e);
response = formatError(e);
return response;
}
console.log("Starting query ...");
try {
const {sql, inserts} = buildSql(event);
const results = await connection.query(sql, inserts);
const xml = buildXml(results);
response = formatResponse(xml);
} catch (e) {
console.log(e);
response = formatError(e);
} finally {
await connection.release();
console.log("Connection released");
return response;
}
};
function buildSql (event) {
let sql = "SELECT * FROM ??";
let inserts = [table];
let conditions = new Array();
if (event.queryStringParameters && event.queryStringParameters.query) {
const query = event.queryStringParameters.query;
conditions.push('display LIKE ?');
inserts.push(`%${query}%`);
}
if (event.queryStringParameters && event.queryStringParameters.parent) {
const parentItemId = event.queryStringParameters.parent;
conditions.push('value LIKE ?');
inserts.push(`${parentItemId}%`);
}
const condNum = conditions.length;
if (condNum >= 1) {
sql += ` WHERE ${conditions[0]}`;
if (condNum == 2) {
sql += ` AND ${conditions[1]}`;
}
} else if (event.multiValueQueryStringParameters && event.multiValueQueryStringParameters.values) {
const values = event.multiValueQueryStringParameters.values;
sql += " WHERE value IN (?";
for (let i = 1; i < values.length; i++) {
sql += ", ?";
}
sql += ")";
inserts = inserts.concat(values);
}
sql += ';';
return {sql, inserts};
}
function buildXml (results) {
const resultsNum = results.length;
let root = builder.create('items');
for (let i = 0; i < resultsNum; i++) {
let item = root.ele('item');
item.att('value', results[i].value);
item.att('display', results[i].display);
}
const xml = root.end({ pretty: true});
return xml;
}
function formatResponse (body) {
const response = {
"statusCode": 200,
"headers": {
"Content-Type": "text/plain; charset=utf-8"
},
"isBase64Encoded": false,
"body": body,
};
return response;
}
function formatError (error) {
const response = {
"statusCode": error.statusCode,
"headers": {
"Content-Type": "text/plain",
"x-amzn-ErrorType": error.code
},
"isBase64Encoded": false,
"body": error.code + ": " + error.message
};
return response;
}This code gets the connection information to the database from the environment variable of the Lambda function (lines 4-7). I will explain how to set environment variables later.
When a GET request is sent to the API Gateway, Lambda receives it as an event. It creates a SQL SELECT statement based on the request parameters (line 25), queries the database (line 26), formats the result into R3190: Format of Choice XML (line 27), and returns it as a response.
The three request parameters are as follows:
- query: Search string. Returns the rows that contain the search string in display (display label).
- parent: Choice ID of the parent choice. Returns rows with a prefixed value (choice ID). This parameter does not need to be considered if you don’t set any parent choices.
- values: Parameters used when searching by directly specifying a value (choice ID).
3-2. Uploading as a .zip file
Create a .zip file by running the following command in your local directory (in environments such as Windows where the zip command is not available, right-click the directory and zip it into a .zip file):
zip -r myRdsFunction.zip .Open the Lambda function and go to “Upload a .zip file” from “Action” at the upper right of the function code.
Select the .zip file you created and click Save to upload.
4. Attaching the RDS Proxy to your Lambda Function
Attach the RDS Proxy to the Lambda function and configure the connection relationship.
4-1. Attach the RDS Proxy
Open your Lambda function and click Add database proxy in the Database Proxy block at the bottom.
Create a new database proxy. The proxy identifier is “sample-rds-proxy” in this example. Select the database you created in “RDS DB Instance”, enter the ARN of the secret you created in “Secret”, and select the IAM role you also created. For “Authentication”, select Password and click Add.
View the proxy details and make a note of the proxy endpoint.
4-2. Configure environment variables for Lambda function
In the Lambda function code in this article we read the necessary information from the environment variables and connect it to the database. We will set the database information in the environment variables of the Lambda function.
Open your Lambda function and click Manage Environment Variables in the Environment Variables block.
Set the key and value as shown in the table below and click Save.
| Key | value |
| db | Database schema name |
| endpoint | RDS Proxy endpoint |
| user | Database master username |
| password | Database master password |
| table | table name |
4-3. Set up a VPC for your Lambda function
In order for your Lambda function to use the RDS Proxy you must place the Lambda function in the same VPC (virtual network) as your RDS or RDS Proxy
First, give the Lambda function’s execution role a policy to access your VPC. Open your Lambda function and click Edit in the upper right corner of the Basic settings block.
Click “View the…roles…” at the bottom.
Click Attach policies.
Search for and select AWSLambdaVPCAccessExecutionRole and click Attach Policies.
Return to the Lambda function screen again and click Edit in the VPC block.
Select Custom VPC, set the same VPC, subnet, and security group as your RDS, and click Save (You can find the VPC settings in RDS on the Connections & Security tab of the database instance).
5. Configure the API Gateway as a trigger for your Lambda function
5-1. API Gateway Settings
Open your Lambda function and click Add Trigger in the Designer block of the Configuration tab.
Select API Gateway and Create an API. Select REST API as the API type. Set the security to “Open” and click Add.
Make a note of the endpoint once you have added the API Gateway. The endpoint is visible when you select API Gateway in the Lambda function’s Designer block.
5-2. Operation check in a browser
Let’s access the API Gateway endpoint with a browser and check the operation. If you get the XML as shown below, it’s a success.
If you see an error, it may be because your Lambda function timeout is too short. See the article Using Amazon RDS as an External Master (Troubleshooting) and try changing the timeout setting.
If characters are garbled, again look at Using Amazon RDS as an External Master (Troubleshooting).
6. Create an App that Uses a Search Select Box in Questetra BPM Suite
Create an App in Questetra BPM Suite and add “Search Select Box” to the data items (Display name is “Search Select”). Select “Get choices list via HTTP” in “Type of data source”, enter the API endpoint in “URL”, and click “Apply and Close”.
Let’s release the App and check the form. Enter the search string and press the search button to display the options that match the conditions.
What did you think? We used Amazon RDS this time, but you can use other databases as external masters in a similar way. Please consider using an external service according to the nature and purpose of the master data.
Pingback: Using Amazon RDS as an External Master (Terraform) – Questetra Support
Pingback: Using Amazon RDS as an External Master (Troubleshooting) – Questetra Support