Using Amazon RDS as an External Master (Terraform)

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)
• Lambda (application creation)
• API 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.

Outline of Procedure

Create a database and API using Terraform. After creating the table in the database, set the endpoint of the API as the URL of the options data in the Search Select Box.

Here is the general flow.

1. Get Terraform template from GitHub
2. Set AWS credentials to run Terraform
3. Install the package used by the Lambda function
4. Set template variables
5. Create AWS resources
6. Connect to database and create table
7. 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:

• Terraform
• AWS CLI
• MySQL client (used to create the database)
• Node.js / npm command (used to create Lambda function code)

Procedure

1. Get the Terraform template from GitHub

Download the Terraform template from this GitHub repository.

2. Set AWS credentials for Terraform execution

Open the AWS IAM console and select the user you want to use to run Terraform from “Users” on the left menu.

Click [Create Access Key] on the [Security Credentials] tab, and make a note of the displayed access key ID and secret access key.

Execute the following aws command.

aws configure

When you execute this command, you will be prompted for four pieces of information:

• Access key ID
• Secret access key
• AWS Region
• Output format

Enter the Access key ID and Secret access key that you made a note of earlier, and the AWS region and output format that you want to use as default (reference).

For example, type:

AWS Access Key ID [None]: {ACCESS KEY ID}
AWS Secret Access Key [None]: {SECRET ACCESS KEY}
Default region name [None]: us-east-2
Default output format [None]: json

3. Install the package used by the Lambda function

To install the necessary packages, execute

npm install

in the lambda-src directory of the template downloaded in step 1 (node_modules directory will be created).

4. Set template variables

Create a vars.tfvars file directly under the Choices-AWS-Lambda-RDS directory of the template downloaded in step 1, and specify the values ​​of the variables defined in variables.tf. (Variables that have a default value set in variables.tf do not need to have a value specified in vars.tfvars.)

For example, the contents of the vars.tfvars file might look like this (my_ip is set to the IP address of your PC in CIDR notation so that the mysql command can connect to the database):

aws_region = "us-east-2"
my_ip = "{YOUR PC’s IP ADDRESS（CIDR NOTATION）}"
db_password = "password1234"

5. Create AWS resources

To initialize the Choices-AWS-Lambda-RDS directory as a workspace, execute

terraform init

in the directory, and then execute

terraform plan -var-file=vars.tfvars

to see your changes (this command does not apply any changes). After you see the log and your changes you should see a message saying that you will be creating 21 resources like this:

Plan: 21 to add, 0 to change, 0 to destroy.

If there are no problems, execute

terraform apply -var-file=vars.tfvars

to apply your changes.

Do you want to perform these actions?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value:

The above will be displayed, so you type

yes

to confirm the execution.

AWS resource creation is complete when the following is displayed.

Apply complete! Resources: 21 added, 0 changed, 0 destroyed.

Outputs:

api_url = {API ENDPOINT}
db_endpoint = {DATABASE ENDPOINT}

Make a note of the API and Database endpoints that are displayed.

The AZ (Availability Zone) of the specified region may run out of free space, and the following error may occur.

Error: error creating RDS cluster: InsufficientStorageClusterCapacity: Couldn't create cluster: insufficient capacity in requested AZs

If you get this error, wait a while and try again, or specify a different AZ / Region by referring to Using Amazon RDS as an External Master (Troubleshooting).

6. Connect to the Database and Create the Table

Connect to the database with the following mysqlL command. After executing the command, enter the master password set in the vars.tfvars file.

mysql -h {DATABASE ENDPOINT} -P 3306 -u admin -p

3306 is the port number and admin is the master user name. If you set the master username in the vars.tfvars file, it uses the configured username.

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>

After connecting to the database, create the database schema and tables. Create a database schema in the name, in this example, “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 subsequent to mysql>.

create database sample_db;

Switching database schema

Execute the following SQL statement and switch to the “sample_db” created earlier.

use sample_db;

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

Insert data

For this example, insert 4 lines of data.

INSERT INTO nations (value, display)
  VALUES
    ("JP", "JAPAN"),
    ("US", "USA"),
    ("UK", "UNITED KINGDOM"),
    ("AU", "AUSTRALIA");

After inserting the data, access the API endpoint with a browser and check the operation. If you see the XML as shown below, you’ve succeeded.

If you get garbled characters or get an error, refer to Using Amazon RDS as an External Master (Troubleshooting).

7. Create an Application Using 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