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.

Photo by Nikolay Tarashchenko on Unsplash

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;

Example output from the commads above

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 :

nodeOracleRestApi/checkConnection.js

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.

Top 5 Node.js and Express JS online courses for Web Developers

3.Create Your Rest API application.

Create a file with name index.js and paste the following code :

nodeOracleRestApi/index.js

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 /employess
app.get('/employees', function (req, res) {
//fetch all employees from db and send it
selectAllEmployees(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 employees
result = await connection.execute(query);
} catch (err) {
//send error message
return res.send(err.message);
} finally {
if (connection) {
try {
// Always close connections
await connection.close();
console.log('close connection success');
} catch (err) {
console.error(err.message);
}
}
if (result.rows.length == 0) {
//query return zero employees
return res.send('query send no rows');
} else {
//send all employees
return 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 .

Top 5 RESTful Web Services with Spring and Java Courses for Experienced Developers

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 ?id
let id = req.query.id;
// id param if it is number
if (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_id
result = await connection.execute(query, [id]);
} catch (err) {
//send error message
return res.send(err.message);
} finally {
if (connection) {
try {
// Always close connections
await connection.close();
} catch (err) {
return console.error(err.message);
}
}
if (result.rows.length == 0) {
//query return zero employees
return res.send('query send no rows');
} else {
//send all employees
return 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

References:

1. node.js
2. Oracle 18c XE
3. Express framework

You Might Also Like:

Regular Expressions Cheat Sheet in Node.js

How to Schedule Cron Jobs and Set Health Checks in Node.js