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.

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 `pjsdemo`.`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 `pjsdemo`.`offices`
(`OFFICECODE`,
`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

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

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 and add the following 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

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