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 select API file. The file will be opened and the Canvas, Parameters, API Routes, and Properties panel will be initialized.