Create API that gathers data from multiple DB tables

This example will guide you through creating a simple REST API endpoint that retrieves and returns data from two database tables. For our example we will use an Employee and an Office table.

This example queries each table separately to demonstrate how easily you can add multiple steps. These same results can be achieved with a single step by joining the tables as shown in this example: Create API that joins two tables and returns results

In this example we will build the logic manually, however, you can also get started quicker by using the Autogenerating APIs from tables option for one of the tables first.

You can use the SQL code below to create and populate the tables used in the example or use your own tables and adjust the steps accordingly.

MSSQL, MySQL, MariaDB Table Definitions

CREATE TABLE employees ( 
	empNumber INT NOT NULL , 
	lastName VARCHAR(50) NOT NULL , 
	firstName VARCHAR(50) NOT NULL , 
	extension VARCHAR(10) NOT NULL , 
	email VARCHAR(100) NOT NULL , 
	officeCode VARCHAR(10) NOT NULL , 
	reportsTo INT DEFAULT NULL , 
	jobTitle VARCHAR(50) NOT NULL , 
	PRIMARY KEY( empNumber) ) ; 
  
CREATE TABLE offices ( 
	officeCode VARCHAR(10) NOT NULL , 
	city VARCHAR(50) NOT NULL , 
	phone VARCHAR(50) NOT NULL , 
	address1 VARCHAR(50) NOT NULL , 
	address2 VARCHAR(50) DEFAULT NULL , 
	state VARCHAR(50) DEFAULT NULL , 
	country VARCHAR(50) NOT NULL , 
	postalCode VARCHAR(15) NOT NULL , 
	territory VARCHAR(10) NOT NULL , 
	PRIMARY KEY( officeCode) )  ;

DB2 Table Definitions

CREATE TABLE EMPLOYEES ( 

	EMPNUMBER INTEGER NOT NULL , 
	LASTNAME VARCHAR(50) CCSID 37 NOT NULL , 
	FIRSTNAME VARCHAR(50) CCSID 37 NOT NULL , 
	EXTENSION VARCHAR(10) CCSID 37 NOT NULL , 
	EMAIL VARCHAR(100) CCSID 37 NOT NULL , 
	OFFICECODE VARCHAR(10) CCSID 37 NOT NULL , 
	REPORTSTO INTEGER DEFAULT NULL , 
	JOBTITLE VARCHAR(50) CCSID 37 NOT NULL , 
	CONSTRAINT Q_DEMOLIB_EMPLOYEES_EMPLO00001_00001 PRIMARY KEY( EMPNUMBER 	) )   
          RCDFMT EMPLOYEES  ; 
   
CREATE TABLE OFFICES ( 
	OFFICECODE VARCHAR(10) CCSID 37 NOT NULL , 
	CITY VARCHAR(50) CCSID 37 NOT NULL , 
	PHONE VARCHAR(50) CCSID 37 NOT NULL , 
	ADDRESS1 VARCHAR(50) CCSID 37 NOT NULL , 
	ADDRESS2 VARCHAR(50) CCSID 37 DEFAULT NULL , 
	STATE VARCHAR(50) CCSID 37 DEFAULT NULL , 
	COUNTRY VARCHAR(50) CCSID 37 NOT NULL , 
	POSTALCODE VARCHAR(15) CCSID 37 NOT NULL , 
	TERRITORY VARCHAR(10) CCSID 37 NOT NULL , 
	CONSTRAINT Q_DEMOLIB_OFFICES_OFFICECODE_00001 PRIMARY KEY( OFFICECODE 	) )   
	RCDFMT OFFICES    ;

Data Insert

INSERT INTO `employees`
(`empNumber`,
`lastName`,
`firstName`,
`extension`,
`email`,
`officeCode`,
`reportsTo`,
`jobTitle`)
VALUES
(1056,'Patterson','Mary','x4611','mpatterso@classicmodelcars.com',1,1002,'VP Sales'),
(1076,'Firrelli','Jeff','x9273','jfirrelli@classicmodelcars.com',1,1002,'VP Marketing'),
(1088,'Patterson','William','x4871','wpatterson@classicmodelcars.com',6,1056,'Sales Manager (APAC)'),
(1102,'Bondur','Gerard','x5408','gbondur@classicmodelcars.com',4,1056,'Sale Manager (EMEA)'),
(1143,'Bow','Anthony','x5428','abow@classicmodelcars.com',1,1056,'Sales Manager (NA)'),
(1165,'Jennings','Leslie','x3291','ljennings@classicmodelcars.com',1,1143,'Sales Rep'),
(1166,'Thompson','Leslie','x4065','lthompson@classicmodelcars.com',1,1143,'Sales Rep'),
(1188,'Firrelli','Julie','x2173','jfirrelli@classicmodelcars.com',2,1143,'Sales Rep'),
(1216,'Patterson','Steve','x4334','spatterson@classicmodelcars.com',2,1143,'Sales Rep'),
(1286,'Tseng','Foon Yue','x2248','ftseng@classicmodelcars.com',3,1143,'Sales Rep'),
(1323,'Vanauf','George','x4102','gvanauf@classicmodelcars.com',3,1143,'Sales Rep'),
(1337,'Bondur','Loui','x6493','lbondur@classicmodelcars.com',4,1102,'Sales Rep'),
(1370,'Hernandez','Gerard','x2028','ghernande@classicmodelcars.com',4,1102,'Sales Rep'),
(1401,'Castillo','Pamela','x2759','pcastillo@classicmodelcars.com',4,1102,'Sales Rep'),
(1501,'Bott','Larry','x2311','lbott@classicmodelcars.com',7,1102,'Sales Rep'),
(1504,'Jones','Barry','x102','bjones@classicmodelcars.com',7,1102,'Sales Rep'),
(1611,'Fixter','Andy','x101','afixter@classicmodelcars.com',6,1088,'Sales Rep');

INSERT INTO `offices`
(`empNumber`,
`city`,
`phone`,
`address1`,
`address2`,
`state`,
`country`,
`postalCode`,
`territory`)
VALUES
(1,'San Francisco','+1 650 219 4782','100 Market Street','Suite 300','CA','USA','94080','NA'),
(2,'Boston','+1 215 837 0825','1550 Court Place','Suite 102','MA','USA','02107','NA'),
(3,'New York City','+1 212 555 3000','523 East 53rd Street','apt. 5A','NY','USA','10022','NA'),
(4,'Paris','+33 14 723 4404','43 Rue Jouffroy Dabbans','','','France','75017','EMEA'),
(5,'Tokyo','+81 33 224 5000','4-1 Kioicho','','Chiyoda-Ku','Japan','102-8578','Japan'),
(6,'Sydney','+61 2 9264 2451','5-11 Wentworth Avenue','Floor #2','','Australia','NSW 2010','APAC'),
(7,'London','+44 20 7877 2041','25 Old Broad Blvd','Level 7','','UK','EC2N 1HN','EMEA'),
(8,'Hiddenite','+1 555 555 1212','221 Craftmaster Rd','-','NC','USA','28636','NA');

Open the workspace

(If you haven't created an example workspace, create a workspace called papihello. See Using Profound API Workspaces)

Open the example workspace by pointing your browser to the URL:

http://[HOST]:[PORT]/ide/papihello

Where host is the server you installed Profound.js on and port is the port number Profound.js is running on. If the installation is on your PC or Mac computer, use localhost.

OR open the workspace from the ide with the Open button and then select the example workspace:

Create API file

Create a new API json file by clicking the New button and selecting API file. The file will be opened and the Canvas, Parameters, API Routes, and Properties panel will be initialized.

Set Properties

On the left hand lower panel select the General Info tab and fill on the following values

Name: employee
Summary: get employee information
HTTP Method: get
HTTP Path: /employee/:employeeNumber

Notice the colon before the employeeNumber. This indicates that employeeNumber is a required path parameter.
Specifying a parameter this way will automatically add it to the Input Parameters list.
Category, Sub Category, Tag, and Description all help improve documentation and organization.

Input Parameters

It is always best to create parameters before building logic because the parameters will then appear as drop down options when adding logic steps.

This example is a basic GET API that requires an Employee Number as a parameter and will return some information about the employee and the office they are associated with.

You will notice that adding the :employeeNumber to the end of the HTTP path automatically added an employeeNumber parameter to the Input Parameters. These autogenerated path parameters can only be removed by changing the path. The default Type is string so we need to change it integer.

Double click on employeeNumber in the Input Parameters tab to open the Edit Parameter dialog

If you wish to use a query parameter instead of a path parameter or want to add additional parameters click the green plus sign in the Input Parameter panel to open the API Parameter dialog.

Fill in the following properties:

Name: employeeNumber
From: query
Data Type: integer
Check the Required checkbox
Example: 1088
Description: Employee Number to request data about

^{Adding a valid example and description makes testing the API easier and improves the autogenerated documentation.}

Output Parameters

Switch to the Output Parameters tab. Our API output will have a result JSON object that contains the values from the employee and office tables. The result object will allow us to query the database results from one of the tables directly into the API output.

Click the green plus sign to open the Edit Parameter dialog and complete the properties as shown.

After the result object parameter is added you can add child parameters for the data. Do this by right clicking on the result parameter in the list and selecting Add Child Parameter

Repeat this step to add the following child parameters

Save

At this point you should save your API file by clicking the Save button in the menu ribbon. The first time you use save you will be presented a save dialog with the File Name defaulting to Unnamed.api.json. You should change the Unnamed portion to an appropriate file name, like Employee.api.json in this case. Click Save.

Add Low Code Logic

Retrieve data from employee table

Click on Add Step... in the canvas.

In the What would you like to do? drop down expand the Database section and click on Retrieve One Database Record

In the Which columns section drop down the list and select the columns to include

In the Selection Criteria expression drop down the list and check the empNumber field. (You can use this to select multiple criteria fields and use the AND/OR selector to build the appropriate logic.

You can also type your criteria directly into the text area. Add a ? for each parameter and a Parameter Value selection box will be added below for each ? in the statement.

For the Parameter Value drop down the API input section and select input["employeeNumber"]

We will be using a value from this query for the next query so we will place the record into a work variable.

Select Work variable from the list and enter a variable name.

Then click OK.

Retrieve data from the offices table

Click the Add Step... button

Select the Retrieve One Database Record and then set the database, table, and columns

In this example we are using two tables in the same database. This could be done just as easily if the two tables were in completely different database.

Also, because the tables are in the same database, the data from both tables could be retrieved with a single step using a join statement. We've used two steps for sake of the example.

For the parameter value for "officeCode = ?", this time you will drop down the Work variables section. There will be a variable for each column you selected in the previous step.

Select the employeeResult["officeCode"] variable

This time for Where do you want to place the record? you can place the record directly into the API output result object. The columns names will automatically be mapped to the matching child parameters.

From the drop down select API output

Then from the Enter API output property name select output["result"]

Completing the API Output parameters

Selecting the results from the offices query directly into the API output will populate the matching child parameters. However, we still need to get the employee tables results from the work variable into the API output.

There is a widget specifically from that purpose.

Click the Add Step... button

For What would you like to do? select Set API Output

A dialog will open showing the list of output parameters that you set up in the Output Parameters tab

Although you can not see it in the dialog, the office parameters are already set.

For the employee parameters you will need to set them from the work variable

Click the blue arrow next to result["empNumber"] to drop down the list of options. Expand the Work variables section and select employeeResult["empNumber"]

Repeat this for the remaining employee parameters

Save

Testing the API

When you save the API it will be live and you can test it using the Test tab in the lower right panel.

Because we added an example value when creating the parameters, the example value is prefilled in the test tab. You can edit the employeeNumber value to test other results.

Click the Execute button

Scrolling down in the Test tab you will find the Response body section that will show the data that was returned

Live Test

Because this example is a simple GET API, you can test that it is live by copying the Request URL and pasting it in another browser window

The browser will display the data that was returned