Environment: Oracle XE 18c, CentOS Linux 7, APEX 19.2, MySQL 5.6, Node.js 12.14.1
Using APEX (Oracle Application Express) you can quickly build nice-looking web applications on top of an Oracle database. More recent versions of APEX also support connectivity to non-Oracle databases through RESTful API’s.
In this blog post I will show how you can build reports in APEX 19.2 with data coming from a MySQL database that is REST-enabled using Node.js.
I assume that you already have installed MySQL and APEX. In my case both MySQL, Oracle XE 18c and APEX are running on the same host, a CentOS 7 Linux server. For the installation of APEX 19.2 on Oracle XE 18c I refer to one of my previous blog posts.
First, I created the following demo table in my MySQL database. The goal of this blog post is to create an APEX report using the data from this table:
CREATE TABLE DEMO_PEOPLE
( ID INT NOT NULL AUTO_INCREMENT,
RATING INT,
NAME VARCHAR(255),
COUNTRY VARCHAR(255),
FROM_YR INT,
TO_YR INT,
LINK VARCHAR(255),
CATEGORY VARCHAR(10),
GENDER VARCHAR(1),
FLEX1 VARCHAR(1000),
FLEX2 VARCHAR(1000),
FLEX3 VARCHAR(1000),
PRIMARY KEY (ID)
) ;
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100293,30.17526,'Christopher Columbus','Italy',1451,1506,'https://en.wikipedia.org/wiki/Christopher_Columbus','A','M',null,null,null);
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100294,30.167197,'Johann Sebastian Bach','Germany',1685,1750,'https://en.wikipedia.org/wiki/Johann_Sebastian_Bach','A','M',null,null,null);
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100295,30.109331,'Ludwig van Beethoven','Germany',1770,1827,'https://en.wikipedia.org/wiki/Ludwig_van_Beethoven','A','M',null,null,null);
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100296,30.036677,'Cleopatra VII of Egypt','Egypt',-69,-30,'https://en.wikipedia.org/wiki/Cleopatra','P','F',null,null,null);
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100297,29.96003,'Galileo Galilei','Italy',1564,1642,'https://en.wikipedia.org/wiki/Galileo_Galilei','S_T','M',null,null,null);
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100298,29.884558,'Euclid','Unknown',-325,-265,'https://en.wikipedia.org/wiki/Euclid','S_T','M',null,null,null);
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100299,29.859917,'Nero','Italy',37,68,'https://en.wikipedia.org/wiki/Nero','P','M',null,null,null);
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100300,29.842897,'Karl Marx','Germany',1818,1883,'https://en.wikipedia.org/wiki/Karl_Marx','S_T','M',null,null,null);
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100301,29.771886,'Marco Polo','Italy',1254,1324,'https://en.wikipedia.org/wiki/Marco_Polo','A','M',null,null,null);
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100302,29.742594,'Cicero','Italy',-106,-43,'https://en.wikipedia.org/wiki/Cicero','P','M',null,null,null);
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100303,29.738438,'Vincent van Gogh','Netherlands',1853,1890,'https://en.wikipedia.org/wiki/Vincent_van_Gogh','A','M',null,null,null);
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100304,29.636065,'Hippocrates','Greece',-460,-375,'https://en.wikipedia.org/wiki/Hippocrates','S_T','M',null,null,null);
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100305,29.629269,'Sigmund Freud','Czech Republic',1856,1939,'https://en.wikipedia.org/wiki/Sigmund_Freud','S_T','M',null,null,null);
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100306,29.595729,'Pablo Picasso','Spain',1881,1973,'https://en.wikipedia.org/wiki/Pablo_Picasso','A','M',null,null,null);
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100307,29.519164,'Qin Shi Huang','China',-259,-210,'https://en.wikipedia.org/wiki/Qin_Shi_Huang','P','M',null,null,null);
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100308,29.395324,'Tutankhamun','Egypt',-1341,-1323,'https://en.wikipedia.org/wiki/Tutankhamun','P','M',null,null,null);
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100309,29.330534,'Marcus Aurelius','Italy',121,180,'https://en.wikipedia.org/wiki/Marcus_Aurelius','P','M',null,null,null);
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100310,29.254359,'Blaise Pascal','France',1623,1662,'https://en.wikipedia.org/wiki/Blaise_Pascal','S_T','M',null,null,null);
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100311,29.241855,'David','Palestine',-1040,-970,'https://en.wikipedia.org/wiki/David','P','M',null,null,null);
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100312,29.210766,'Raphael','Italy',1483,1520,'https://en.wikipedia.org/wiki/Raphael','A','M',null,null,null);
Insert into DEMO_PEOPLE (ID,RATING,NAME,COUNTRY,FROM_YR,TO_YR,LINK,CATEGORY,GENDER,FLEX1,FLEX2,FLEX3) values (100313,29.197855,'Charles Darwin','United Kingdom',1809,1882,'https://en.wikipedia.org/wiki/Charles_Darwin','S_T','M',null,null,null);
To be able to share data between MySQL and APEX we need to build a RESTful API on top of the MySQL database. You can do this with Node.js. Node.js is (simply said) a server-side version of JavaScript. It comes with many different modules, for example to connect to databases, to build web services and so on. For the installation of Node.js on CentOS 7 I refer to one of my previous blog posts. It’s actually pretty straightforward.
I’m going to put all the .json files for this project in a new folder called /home/mhoys/nodejs/rest_api. For this example you need to install the following Node.js modules: express, body-parser, mysql and dotenv. The “npm init” command creates a package.json file that will hold your project dependencies and metadata.
$ mkdir -p /home/mhoys/nodejs/rest_api
$ cd /home/mhoys/nodejs/rest_api
$ npm init
$ npm install express body-parser mysql dotenv
$ npm list
First we are going to create a file called “.env“. This file will hold the environment variables for the database connection and the web server.
$ vi .env
DB_HOST=localhost
DB_USER=matthias
DB_PASSWORD=<password>
DB_DATABASE=matthias
SERVER_PORT=3000
“localhost” is the hostname of my MySQL database, “matthias” is the name of the database account to connect to the database (with the password listed in DB_PASSWORD), and “matthias” is the name of the MySQL database holding the table DEMO_PEOPLE (see higher).
Next, create a file called “mysql_config.js“. In this file the MySQL configuration variables will be read from the .env file and exported:
$ vi mysql_config.js
const dotenv = require('dotenv').config({path: './.env'});
if (dotenv.error) {
console.log(dotenv.error);
}
//console.log(dotenv.parsed);
// Get db_host variable from .env file
const host = process.env.DB_HOST;
// Get db_user variable from .env file
const user = process.env.DB_USER;
// Get db_password variable from .env file
const password = process.env.DB_PASSWORD;
// Get db_database variable from .env file
const database = process.env.DB_DATABASE;
// export the variables for use in other scripts
module.exports.host = host;
module.exports.user = user;
module.exports.password = password;
module.exports.database = database;
Now create another file “server_config.js” which will hold the web server configuration variables:
$ vi server_config.js
const dotenv = require('dotenv').config({path: './.env'});
if (dotenv.error) {
console.log(dotenv.error);
}
//console.log(dotenv.parsed);
// Get server_port variable from .env file
const port = process.env.SERVER_PORT;
module.exports.port = port;
Create another file “queries.js” that will be used to store the database queries for this demo.
$ vi queries.js
const demo_people_query = 'select ID, NAME, GENDER, COUNTRY, FROM_YR, TO_YR, LINK from DEMO_PEOPLE';
const demo_people_by_country_query = 'select COUNTRY, count(*) as people_count from DEMO_PEOPLE group by COUNTRY order by count(*) desc';
module.exports.demo_people_query = demo_people_query;
module.exports.demo_people_by_country_query = demo_people_by_country_query;
The first query selects all records from the DEMO_PEOPLE table. The second query counts the number of persons in the DEMO_PEOPLE table grouped by country.
Next, create the main file “server.js“:
$ vi server.js
const mysql = require('mysql');
const mysql_config = require('./mysql_config');
const queries = require('./queries');
const express = require('express');
const app = express();
const server_config = require('./server_config');
const bodyParser = require('body-parser');
const conn = mysql.createConnection({
host : mysql_config.host,
user : mysql_config.user,
password : mysql_config.password,
database: mysql_config.database
});
conn.connect();
app.use(bodyParser.json({ type: 'application/*+json' }));
app.get('/api/demo_people', function(request, response){
conn.query(queries.demo_people_query, function(err, results){
if ( err ){
response.status(400).send(err);
} else {
response.send(results);
}
});
});
app.get('/api/demo_people_by_country', function(request, response){
conn.query(queries.demo_people_by_country_query, function(err, results){
if ( err ){
response.status(400).send(err);
} else {
response.send(results);
}
});
});
app.listen(server_config.port, function () {
console.log('Express server is listening on port ' + server_config.port);
});
You should now have the following files in the rest_api folder:
$ ls -la /home/mhoys/nodejs/rest_api/
.env
mysql_config.js
node_modules
package.json
package-lock.json
queries.js
server_config.js
server.js
Let’s see if the server starts without errors and if you can access the two API’s from a web browser:
$ node ./server.js
Express server is listening on port 3000
Now open a web browser and browse to:
http://<hostname>:3000/api/demo_people
http://<hostname>:3000/api/demo_people_by_country
You should see data from the MySQL table returned in JSON format.
If you cannot reach the node server, it could be that the CentOS firewall is blocking port 3000. You can open it with the following command:
$ sudo firewall-cmd --permanent --add-port=3000/tcp
success
$ sudo firewall-cmd --reload
success
$ sudo firewall-cmd --list-ports
Another way to test the API’s is by using Postman:
- Start Postman
- Click on New > Request
- Fill in a name and click on Save to My requests
- Click on the new request, copy the URL http://hostname:3000/api/demo_people to the GET window and click on Send
- Check if you see the correct data in the Body window:
Now that we have our API’s in place, let’s create a new APEX application with two reports using data from these API’s.
Log in to your APEX 19.2 workspace using a developer account:
Click on App Builder > Create > New Application.
Enter a Name and click on Create Application. Do not add additional pages yet.
Now in the App Builder for your new application, click on Shared Components > Web Source Modules (under Data Sources):
Click on Create to create a new Web Source Module.
Click on “from scratch”.
Now select Simple HTTP, fill in a name and the URL for the demo people API:
Click on Next and Discover (leave the Authentication disabled).
If all goes well, you should see the DEMO_PEOPLE data in the Web Source Discovery window:
Click on Create Web Source.
Repeat these steps to create another web source module for the “demo people by country” API.
Now we are ready to build two reports in our APEX application using the two web source modules that we just defined.
Click on App Builder > your application > Create Page.
Select Report > Interactive Report.
Give the new page a name and click on Next.
Create a new navigation menu entry and click on Next:
As Data Source, choose Web Source and select the first of the two web source modules that you created earlier. Drag all columns to the right and rearrange them if needed:
Click on Create and now run the page or the application, you should see the report page with data from the DEMO_PEOPLE table:
If you need to rearrange columns (the order from the database query is not always respected), you can do this from the Actions > Columns menu in the report, then click on Actions > Report > Save Report to overwrite the default report settings.
Repeat these steps to add another interactive report to your APEX application, now using the “demo people by country” web source module:
Now let’s create a chart instead of a report.
Click on Actions > Chart inside the report.
Select Pie as chart type, Country as Label, People Count as Value and Value – Descending as Sort option:
Nice! :-)
Finally click on Actions > Report > Save Report > As Default Report Settings > Primary to save the chart as default report view.
That’s it! This very simple demo shows how you can use Node.js to REST-enable a MySQL database so that the data can be visualised in APEX using Web Source Modules.
Some additional things that can be done to build further on this demo:
- use HTTPS instead of HTTP to better secure the data stream
- add a layer of API authentication
- use express.Router() to build more complex routes to GET, POST, PUT and DELETE requests (only GET is covered in this demo)
- ….
HTH
Matthias
Matthias Hoys 