Build a REST API with Node.js and Oracle 18c.
In this tutorial, we’ll go through on how to build a REST API with node.js and Oracle 18c XE.
1. Install and Configure Oracle 18c XE database.
Download and install Oracle 18c XE for your OS.
After the install completes open a bash shell and type the following commands:
//connect to oracle db
sqlplus / as sysdba//change container
ALTER SESSION SET CONTAINER = xepdb1;//check for the container
SHOW con_name;//unlock hr user change <PASSWORD> field with your password
ALTER USER hr IDENTIFIED BY <PASSWORD> ACCOUNT unlock;//connect to hr schema
connect hr/<PASSWORD> @xepdb1;//query user tables
select table_name from user_tables;
At this stage we have unlock the HR schema and we are ready to write some code.
If you have issues with the install or the connection with the database, check a useful article :
How To Unlock HR User (Sample Schema) In Oracle Database 18c
2.Connect to Oracle 18c XE with node.js
Create a new folder:
mkdir nodeOracleRestApi
Create a new node.js project:
cd nodeOracleRestApi
npm init -y
Install express and oracledb libraries with npm:
npm i express
npm i oracledb
We use express for our web application framework and oracledb to connect with oracle 18c xe.
Create a file with name checkConnection.js and paste the following code :
Run your node app:
node checkConnection.js
You must see at your console :
connected to database
close connection success
Congratulations! You have successfully connected at Oracle 18c XE database.
3.Create Your Rest API application.
Create a file with name index.js and paste the following code :
3.1 Get all employees.
Our first REST API end point will send all employees from hr.employees table:
//GET request to fetch all employeess
http://localhost:3000/employees
The code that serves /employees end point is :
//GET /employessapp.get('/employees', function (req, res) {//fetch all employees from db and send itselectAllEmployees(req, res);})
The above code accepts http get requests at /employees and calls selectAllEmployees(req, res) function.
async function selectAllEmployees(req, res) {try {connection = await oracledb.getConnection({user: "hr",password: password,connectString: "localhost:1521/xepdb1"});console.log('connected to database');let query = 'SELECT * FROM employees';// run query to get all employeesresult = await connection.execute(query);} catch (err) {//send error messagereturn res.send(err.message);} finally {if (connection) {try {// Always close connectionsawait connection.close();console.log('close connection success');} catch (err) {console.error(err.message);}}if (result.rows.length == 0) {//query return zero employeesreturn res.send('query send no rows');} else {//send all employeesreturn res.send(result.rows);}}}
At selectAllEmployees(req, res) function we open a connection with database, run a query to get all employees, close the connection with database and finally send a response to client with the data from query.
Run your code :
node index.js
Now hit your REST API end point from a browser http://localhost:3000/employees and you will see all 106 employees .
3.2 Get an employee with id parameter.
The second REST API end point will send an employee from hr.employees table with id parameter :
//GET request to fetch /employee?id=<id employee>
http://localhost:3000/employee?id=101
The code that serves /employee?id=<employee_id> end point is :
//GET /employee?id=<id employee>app.get('/employee', function (req, res) {//get query param ?idlet id = req.query.id;// id param if it is numberif (isNaN(id)) {res.send('Query param id is not number')return}selectEmployeesById(req, res, id);})
The above code accepts http GET requests at /employee?id=<employee_id> and calls selectEmployeesById(req, res, id) function.
async function selectEmployeesById(req, res, id) {try {connection = await oracledb.getConnection({user: "hr",password: password,connectString: "localhost:1521/xepdb1"});let query = 'SELECT * FROM employees where employee_id=:id';// run query to get employee with employee_idresult = await connection.execute(query, [id]);} catch (err) {//send error messagereturn res.send(err.message);} finally {if (connection) {try {// Always close connectionsawait connection.close();} catch (err) {return console.error(err.message);}}if (result.rows.length == 0) {//query return zero employeesreturn res.send('query send no rows');} else {//send all employeesreturn res.send(result.rows);}}}
At selectEmployeesById(req, res, id) function we open a connection with database, run a query to get employee with id=<employee_id>, close the connection with database and finally send a response to client with the data from query.
Run your code :
node index.js
Now hit your REST API end point from a browser http://localhost:3000/employee?id=101 and you will see the employee with employee_id=101 .
4.Conclusion
This was a sample REST API application that connects to Oracle 18c XE with Node.js and accepts two separate http GET requests:
fetch all employees
GET /employees
fetch an employee with id=<employee_id>
GET /employee?id=<employee_id>
You can download the sample app from github:
https://github.com/petranb2/nodeOracleRestApi