Employee Management System with Oracle

Build a full-stack REST API and browser UI backed by Oracle XE 21c.

Introduction

30 Second Summary

Every company needs to track its people. Who works where, what they earn, when someone joins or leaves.

In this project, you will build a full-stack Employee Management System with a browser-based frontend, a Node.js backend API, and an Oracle XE 21c database storing all the data.

What You'll Build

You will open your browser, see a live table of employees, add a new hire through a form, delete someone who left, and pull a department report showing headcount and average salary per department.

By the end of this project, you'll have:

  • A live employee table that pulls data from your Oracle database and displays names, roles, salaries, and departments in real time.
  • An Add Employee form that inserts a new row into the database and shows the new hire in the table instantly with no page refresh.
  • A Department Report that runs a JOIN and GROUP BY query behind the scenes, showing headcount and average salary per department at the click of a button.
  • Secret Mission: Add an "Update Salary" feature powered by a PL/SQL stored procedure, pushing business logic into the database layer.

Are there any prerequisites?

This project assumes you already have Oracle XE 21c and SQL Developer installed on your machine. You also need Node.js 18 or higher, and basic familiarity with SQL and JavaScript.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

You're going to wire together a browser frontend, a Node.js backend, and an Oracle database into one working system. Taking a moment to name what you're doing helps you stay oriented as the pieces come together.

Set Up Your Node.js Project

Before you can write any API code, you need a project structure in place. Your Node.js server will eventually receive requests from the browser and run SQL against your Oracle database.

In this step, you'll scaffold the project folder, install Express (the web framework) and the oracledb driver, and confirm the server starts successfully.

In this step, get ready to:
  • Create and initialize the project folder.
  • Install Express and the Oracle database driver.
  • Create and run a basic server to confirm everything works.
Create and initialize the project folder
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder, move into it, and initialize it with npm by running these commands:
mkdir employee-mgmt && cd employee-mgmt && npm init -y

You should see output showing a generated package.json file with default values (name, version, description, and so on).

What does npm init -y do?

The npm init command creates a package.json file, which tracks your project's name, version, and dependencies.

The -y flag accepts all default values so you skip the interactive prompts.

Install Express and oracledb
  • Install the two packages your project needs by running:
npm install express oracledb

You should see output confirming packages were added (something like "added X packages").

What are these packages?

Express is a web framework that handles HTTP requests and routing. It turns your Node.js process into a web server that can respond to browser requests.

oracledb is the official Oracle database driver for Node.js. It connects directly to Oracle Database 12.1+ using Thin mode, which means no extra Oracle Client libraries are needed.

Seeing permission errors or network failures?

Make sure you're inside the employee-mgmt folder (run pwd to check). If you see EACCES errors, try running the command with elevated permissions.

help me fix npm install errors for express and oracledb

Create the Express server

With your packages installed, you can now write the entry point that starts your web server.

  • Open the employee-mgmt folder in your code editor.
  • Create a new file called server.js in the root of the employee-mgmt folder.
  • Paste the following code into server.js:
const express = require('express');
const path = require('path');

// Create the Express app and set the port
const app = express();
const PORT = 3000;

// Root route to confirm the server is running
app.get('/', (req, res) => {
  res.send('Server is running');
});

// Start listening for requests
app.listen(PORT, () => {
  console.log(`Server running on http://localhost:${PORT}`);
});

What does this code do?

  • require('express') loads the Express framework.
  • app.get('/') defines a route so when someone visits the root URL, the server responds with the text "Server is running".
  • app.listen(PORT) starts the server on port 3000 and prints a confirmation message.
  • Save server.js.

Before you run this, what do you expect to see printed in the terminal?

  • Start the server by running:
node server.js

You should see this message printed in your terminal:

Server running on http://localhost:3000
  • Open your browser and go to http://localhost:3000.

You should see the text "Server is running" displayed on the page.

Don't see the message or getting an error?

If you see "Cannot find module 'express'", make sure you ran npm install express oracledb inside the employee-mgmt folder. Check that a node_modules folder exists there.

If you see "EADDRINUSE", another process is already using port 3000. Close that process or change the PORT value in server.js.

help me fix my Node.js Express server that won't start

✔️ Awesome, I've got everything!

Great. Double check you've saved server.js and that your server is printing the confirmation message.

ⓧ I'd like to double check the full code

Here is the complete server.js file you should have:

const express = require('express');
const path = require('path');

const app = express();
const PORT = 3000;

app.get('/', (req, res) => {
  res.send('Server is running');
});

app.listen(PORT, () => {
  console.log(`Server running on http://localhost:${PORT}`);
});

Your server is alive and responding to requests. Next up, you'll design the database schema that this server will eventually query.

Create the Database Schema in SQL Developer

Your Express server is running and ready to serve requests. But right now it has no data to serve.

Without database tables, the API has nothing to read from or write to. In this step, you will design a two-table schema in Oracle with a foreign key relationship so your data has integrity from day one.

In this step, get ready to:
  • Connect to Oracle XE 21c in SQL Developer and create a dedicated project user.
  • Create the departments and employees tables with a foreign key relationship.
  • Insert seed data and verify it with a JOIN query.
Connect to Oracle XE and create the project user

You need a dedicated database user for this project so your tables live in their own schema, separate from system objects.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type SQL Developer and press Enter to open it.
  • In the Connections pane on the left, click the green + icon to create a new connection.
  • Fill in the following fields:
  • Connection Name: XE System
  • Username: SYSTEM
  • Password: the password you set when you installed Oracle XE.
  • Hostname: localhost
  • Port: 1521
  • Service Name: XEPDB1
  • Click Test to verify the connection shows Success, then click Connect.

Why XEPDB1?

Oracle XE 21c uses a pluggable database architecture. XEPDB1 is the default pluggable database where your application data belongs. The root container (CDB$ROOT) is for administrative tasks only.

  • Create the emp_admin user by running this SQL in the worksheet:
CREATE USER emp_admin IDENTIFIED BY your-password-here;
GRANT CREATE SESSION TO emp_admin;
GRANT RESOURCE TO emp_admin;
ALTER USER emp_admin QUOTA UNLIMITED ON USERS;

What does this SQL do?

CREATE USER makes a new database user called emp_admin. Replace your-password-here with a password you will remember. You will need it in the next step when connecting from Node.js.

CREATE SESSION lets the user log in. RESOURCE gives them permission to create tables. The QUOTA UNLIMITED line ensures the user can actually store data in the default tablespace.

  • Click the Run Script button (or press F5) to execute all four statements.
  • You should see User EMP_ADMIN created, Grant succeeded, and User EMP_ADMIN altered in the Script Output panel.

Seeing ORA-01920: user name conflicts?

This means a user called emp_admin already exists from a previous attempt. Run DROP USER emp_admin CASCADE; first, then re-run the CREATE USER block.

If you see insufficient privileges, make sure you are connected as SYSTEM (check the connection name in the top-left dropdown).

Help me fix my Oracle user creation error

Now create a separate connection for your new user so all tables live under the emp_admin schema.

  • Click the green + icon again to create another new connection.
  • Fill in the following fields:
  • Connection Name: emp_admin
  • Username: emp_admin
  • Password: the password you chose in the CREATE USER statement.
  • Hostname: localhost
  • Port: 1521
  • Service Name: XEPDB1
  • Click Test to verify Success, then click Connect.
Create the departments and employees tables

Your schema uses two tables linked by a foreign key. Departments must be created first because employees reference them.

  • Make sure you are connected as emp_admin (check the connection dropdown at the top of the worksheet).
  • Create the departments table by running this SQL:
CREATE TABLE departments (
  dept_id NUMBER PRIMARY KEY,
  dept_name VARCHAR2(100)
);

What does this table store?

The departments table holds a unique dept_id and a human-readable dept_name. Each department gets a row here. Employees will reference these IDs through a foreign key.

  • Click Run Script (F5). You should see Table DEPARTMENTS created in the Script Output.
  • Now create the employees table by running this SQL:
CREATE TABLE employees (
  emp_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  first_name VARCHAR2(100),
  last_name VARCHAR2(100),
  job_title VARCHAR2(100),
  salary NUMBER,
  dept_id NUMBER REFERENCES departments(dept_id)
);

What does this table store?

GENERATED ALWAYS AS IDENTITY means Oracle automatically assigns a unique emp_id to each new employee. You never supply this value manually.

The REFERENCES departments(dept_id) clause creates a foreign key. Oracle will reject any INSERT that uses a dept_id that does not exist in the departments table.

  • Click Run Script (F5). You should see Table EMPLOYEES created in the Script Output.

Seeing ORA-00955: name already used?

This means the table already exists from a previous attempt. Run DROP TABLE employees; and DROP TABLE departments; (in that order, since employees depends on departments), then re-run the CREATE TABLE statements.

Help me fix my CREATE TABLE error

Insert seed data and verify with a JOIN

Empty tables are hard to test against. You will add 3 departments and 5 employees so the API has real data to return.

  • Insert the three departments by running this SQL:
INSERT INTO departments (dept_id, dept_name) VALUES (1, 'Engineering');
INSERT INTO departments (dept_id, dept_name) VALUES (2, 'Marketing');
INSERT INTO departments (dept_id, dept_name) VALUES (3, 'Sales');
COMMIT;
  • Click Run Script (F5). You should see three 1 row inserted messages followed by Commit complete.
  • Now insert five employees spread across the departments by running this SQL:
INSERT INTO employees (first_name, last_name, job_title, salary, dept_id)
  VALUES ('Alice', 'Johnson', 'Software Engineer', 95000, 1);
INSERT INTO employees (first_name, last_name, job_title, salary, dept_id)
  VALUES ('Bob', 'Smith', 'Marketing Manager', 85000, 2);
INSERT INTO employees (first_name, last_name, job_title, salary, dept_id)
  VALUES ('Carol', 'Williams', 'Sales Representative', 70000, 3);
INSERT INTO employees (first_name, last_name, job_title, salary, dept_id)
  VALUES ('David', 'Brown', 'DevOps Engineer', 100000, 1);
INSERT INTO employees (first_name, last_name, job_title, salary, dept_id)
  VALUES ('Eve', 'Davis', 'Content Strategist', 78000, 2);
COMMIT;

Why no emp_id in the INSERT?

Because emp_id is defined as GENERATED ALWAYS AS IDENTITY, Oracle assigns the IDs automatically. You only supply the columns you control.

The COMMIT at the end makes the changes permanent. Without it, the data would only be visible in your current session.

  • Click Run Script (F5). You should see five 1 row inserted messages followed by Commit complete.

Now prove everything is wired correctly. Before you run the next query, predict what you expect to see. How many rows? What columns?

  • Run this JOIN query to see employees with their department names:
SELECT e.first_name, e.last_name, e.job_title, e.salary, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

What does this JOIN do?

The JOIN combines rows from employees and departments where the dept_id values match. Instead of seeing a cryptic department number, you get the readable department name alongside each employee.

This is the same query your Node.js API will run in the next step to serve employee data to the browser.

  • Click Run Script (F5).

You should see 5 rows returned with columns for first name, last name, job title, salary, and department name. Alice and David show up under Engineering, Bob and Eve under Marketing, and Carol under Sales.

Seeing 0 rows or an error?

If you see no rows selected, your INSERTs may not have committed. Run COMMIT; and try the SELECT again.

If you see table or view does not exist, make sure you are connected as emp_admin (not SYSTEM). The tables live in the emp_admin schema.

Help me fix my JOIN query results

Your database is loaded and verified. Next up, you will connect your Node.js API to Oracle and build the GET endpoint that serves this data as JSON to the browser.

Connect the API to Oracle and Build the GET Endpoint

You have a database full of employee records and a basic Express server that says hello. Right now, those two worlds are completely disconnected.

The whole point of a backend is to run SQL and return results as JSON. In this step, you will wire Node.js directly to your Oracle database and build a GET endpoint that delivers real employee data to the browser.

In this step, get ready to:
  • Create a reusable database helper module that connects to Oracle XE.
  • Build a GET route that runs a JOIN query and returns employee data as JSON.
  • Wire the route into your server and confirm real data arrives in the browser.
Create the database helper module

Every route that needs data will talk to Oracle. Instead of repeating connection logic in each route, you will create a single db.js module that handles opening a connection, running a query, and closing the connection.

  • Create a new file called db.js in the root of your employee-mgmt folder by running:
touch db.js
  • Confirm the file exists by running:
ls

You should see db.js alongside server.js, package.json, and node_modules.

  • Open db.js in your editor and paste the following code:
const oracledb = require('oracledb');

// Connection details for Oracle XE 21c
const dbConfig = {
  user: 'emp_admin',
  password: 'your-password-here',
  connectString: 'localhost:1521/XEPDB1'
};

// Reusable helper: opens connection, runs SQL, closes connection
async function run(sql, binds = [], options = {}) {
  let connection;
  try {
    connection = await oracledb.getConnection(dbConfig);
    const result = await connection.execute(sql, binds, options);
    if (options.autoCommit === undefined && sql.trim().toUpperCase().startsWith('SELECT')) {
      return result;
    }
    await connection.commit();
    return result;
  } finally {
    if (connection) {
      await connection.close();
    }
  }
}

module.exports = { run, dbConfig };

What does this code do?

The dbConfig object holds your Oracle XE connection credentials. The connectString points to the XEPDB1 pluggable database on port 1521.

The run() function opens a connection, executes whatever SQL you pass in, auto-commits for non-SELECT statements, and always closes the connection in the finally block. This pattern prevents connection leaks.

  • Replace 'your-password-here' with the actual password you set for the emp_admin user in SQL Developer.
  • Save db.js.

Why use Thin mode?

The oracledb package defaults to Thin mode, which connects directly to Oracle Database 12.1+ without requiring separate Oracle Client libraries. Because you have Oracle XE 21c running locally, Thin mode works out of the box with zero extra configuration.

Seeing a connection error when you test later?

If you see an error mentioning the connection was refused, your Oracle listener may not be running. Open Windows Services and confirm OracleServiceXE is started.

If you see an invalid credential error, double-check the user and password values in dbConfig match exactly what you used when creating the emp_admin user in SQL Developer.

Why can't my Node.js app connect to Oracle XE?

Build the GET employees route

With the database helper ready, you need a route file that uses it to run the JOIN query you tested in SQL Developer and return the results as JSON.

  • Create the routes folder and the route file by running:
mkdir routes
touch routes/employees.js
  • Confirm the file exists by running:
ls routes

You should see employees.js listed.

  • Open routes/employees.js in your editor and paste the following code:
const express = require('express');
const router = express.Router();
const { run } = require('../db');
const oracledb = require('oracledb');

// GET all employees with department names
router.get('/', async (req, res) => {
  try {
    const result = await run(
      `SELECT e.emp_id, e.first_name, e.last_name, e.job_title, e.salary, d.dept_name
       FROM employees e
       JOIN departments d ON e.dept_id = d.dept_id
       ORDER BY e.emp_id`,
      [],
      { outFormat: oracledb.OUT_FORMAT_OBJECT }
    );
    res.json(result.rows);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;

What does this route do?

The GET / handler calls your run() helper with the same JOIN query you tested in SQL Developer. The outFormat: oracledb.OUT_FORMAT_OBJECT option tells oracledb to return rows as objects (with column names as keys) instead of plain arrays.

The result.rows array is sent directly to the browser as JSON. If the query fails, the catch block returns a 500 error with the message.

  • Save routes/employees.js.
Wire the route into the server and test

The route file exists but Express does not know about it yet. You need to import it into server.js and mount it at the /api/employees path.

  • Open server.js and replace its entire contents with the following code:
const express = require('express');
const path = require('path');
const employeeRoutes = require('./routes/employees');

const app = express();
const PORT = 3000;

// Parse JSON request bodies
app.use(express.json());
// Serve static files from 'public' folder (created in next step)
app.use(express.static(path.join(__dirname, 'public')));

// Mount employee routes at /api/employees
app.use('/api/employees', employeeRoutes);

app.listen(PORT, () => {
  console.log(`Server running on http://localhost:${PORT}`);
});

What changed in server.js?

  • Imported employeeRoutes from ./routes/employees.
  • Added express.json() middleware so the server can parse JSON request bodies (needed in a later step).
  • Added express.static() pointing at a public folder (you will create it in the next step).
  • Mounted employeeRoutes at /api/employees so any GET request to that path hits your route handler.
  • Removed the old root app.get('/') handler since the static middleware will serve the frontend instead.
  • Save server.js.
  • If your server is still running from the previous step, stop it with Ctrl+C.
  • Restart the server by running:
node server.js

You should see Server running on http://localhost:3000 in the terminal.

Before you open the endpoint, what do you think the browser will show? A plain web page, or raw JSON data?

  • Open your browser and navigate to http://localhost:3000/api/employees.

You should see a JSON array containing your 5 seed employees with their department names. Each object includes uppercase keys like EMP_ID, FIRST_NAME, LAST_NAME, JOB_TITLE, SALARY, and DEPT_NAME.

Why are the column names uppercase?

Oracle returns column names in uppercase by default when you use OUT_FORMAT_OBJECT. Your frontend code will reference these uppercase property names (e.g., emp.EMP_ID) when building the table.

Not seeing JSON data?

  • If you see Cannot GET /api/employees, make sure you saved server.js after adding the route import and restarted the server.
  • If you see a 500 error in the browser, check the terminal for the full error message. A connection refused error means Oracle XE is not running. An invalid credential error means your password in db.js does not match the emp_admin user.
  • If you see an empty array [], confirm you inserted seed data in Step 2 by running SELECT * FROM employees; in SQL Developer.

Help me debug why my /api/employees endpoint is returning an error.

✔️ Awesome, I've got everything!

Great. Double check you have saved all three files: db.js, routes/employees.js, and server.js.

ⓧ I'd like to double check the full code

Here is each file in full. Compare yours against these.

db.js:

const oracledb = require('oracledb');

const dbConfig = {
  user: 'emp_admin',
  password: 'your-password-here',
  connectString: 'localhost:1521/XEPDB1'
};

async function run(sql, binds = [], options = {}) {
  let connection;
  try {
    connection = await oracledb.getConnection(dbConfig);
    const result = await connection.execute(sql, binds, options);
    if (options.autoCommit === undefined && sql.trim().toUpperCase().startsWith('SELECT')) {
      return result;
    }
    await connection.commit();
    return result;
  } finally {
    if (connection) {
      await connection.close();
    }
  }
}

module.exports = { run, dbConfig };

routes/employees.js:

const express = require('express');
const router = express.Router();
const { run } = require('../db');
const oracledb = require('oracledb');

// GET all employees with department names
router.get('/', async (req, res) => {
  try {
    const result = await run(
      `SELECT e.emp_id, e.first_name, e.last_name, e.job_title, e.salary, d.dept_name
       FROM employees e
       JOIN departments d ON e.dept_id = d.dept_id
       ORDER BY e.emp_id`,
      [],
      { outFormat: oracledb.OUT_FORMAT_OBJECT }
    );
    res.json(result.rows);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;

server.js:

const express = require('express');
const path = require('path');
const employeeRoutes = require('./routes/employees');

const app = express();
const PORT = 3000;

app.use(express.json());
app.use(express.static(path.join(__dirname, 'public')));

app.use('/api/employees', employeeRoutes);

app.listen(PORT, () => {
  console.log(`Server running on http://localhost:${PORT}`);
});

Your API is now pulling live data from Oracle and serving it as JSON. Next up, you will build a frontend that fetches this data and displays it in a clean table so users can actually see your employees without reading raw JSON.

Build the Frontend and Display Employees

Your API endpoint returns a clean JSON array of employees, but visiting it in the browser shows raw data that nobody would enjoy reading.

In this step, you will build a browser-based frontend that fetches that data and renders it in a readable HTML table. This closes the full-stack loop: a browser request hits Express, Express queries Oracle, and the results appear on screen.

In this step, get ready to:
  • Create an HTML page with a form, employee table, and report section.
  • Write JavaScript that fetches employee data from your API and populates the table.
  • Add CSS styling and a department report endpoint.
Create the public folder and HTML page

Your server.js already has express.static configured to serve files from a public folder. That folder does not exist yet, so the first job is creating it.

  • Create the public folder inside your employee-mgmt project by running this command:
mkdir public
  • Create public/index.html and paste the following code to set up the page head and the add-employee form:
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Employee Management System</title>
  <link rel="stylesheet" href="style.css">
</head>
<body>
  <h1>Employee Management System</h1>

  <section id="add-employee">
    <h2>Add New Employee</h2>
    <form id="employee-form">
      <input type="text" id="first_name" placeholder="First Name" required>
      <input type="text" id="last_name" placeholder="Last Name" required>
      <input type="text" id="job_title" placeholder="Job Title" required>
      <input type="number" id="salary" placeholder="Salary" required>
      <select id="dept_id" required>
        <option value="">Select Department</option>
        <option value="1">Engineering</option>
        <option value="2">Marketing</option>
        <option value="3">Sales</option>
      </select>
      <button type="submit">Add Employee</button>
    </form>
  </section>

What does this code do?

The <head> links a stylesheet (style.css) that you will create shortly.

The form collects employee details and posts them to the API. The <select> dropdown maps department names to the dept_id values stored in your Oracle departments table.

  • Directly below the form section in the same file, add the employee table section:
  <section id="employee-list">
    <h2>Employees</h2>
    <table id="employees-table">
      <thead>
        <tr>
          <th>ID</th>
          <th>First Name</th>
          <th>Last Name</th>
          <th>Job Title</th>
          <th>Salary</th>
          <th>Department</th>
          <th>Actions</th>
        </tr>
      </thead>
      <tbody></tbody>
    </table>
  </section>

Why is tbody empty?

The <tbody> element starts empty because JavaScript will fill it dynamically with rows fetched from your API. The <thead> defines the column headers that stay visible even before data loads.

  • Save index.html and open http://localhost:3000 in your browser.

You should see the page title, the form inputs, and an empty table with column headers. No restart is needed because Express serves static files fresh on every request.

Seeing a blank page?

  • Confirm you created the public folder inside employee-mgmt (the same folder as server.js).
  • Make sure the file is named exactly index.html (not Index.html or index.htm).
  • Check that your server is still running in the terminal. If it stopped, run node server.js again.

Still stuck? Why isn't my index.html showing up at localhost:3000?

  • Now add the department report section and closing tags below the employee table section:
  <section id="report-section">
    <h2>Department Report</h2>
    <button id="report-btn">Generate Report</button>
    <table id="report-table">
      <thead>
        <tr>
          <th>Department</th>
          <th>Headcount</th>
          <th>Average Salary</th>
        </tr>
      </thead>
      <tbody></tbody>
    </table>
  </section>

  <script src="app.js"></script>
</body>
</html>

What does this section do?

The report section provides a button that will fetch aggregated department data (headcount and average salary) from the API. The <script> tag at the bottom loads app.js, which handles all the fetch logic. Placing the script at the end ensures the DOM elements exist before JavaScript tries to reference them.

  • Save index.html and refresh your browser.

You should now see the full page structure: the form at the top, an empty employee table in the middle, and a Department Report section with a Generate Report button at the bottom.

Fetch and display employees with JavaScript

The HTML structure is in place, but the table is empty. You need JavaScript to call your API and fill the table with real employee data from Oracle.

  • Create public/app.js and paste the following code to grab DOM references and define the loadEmployees function:
const employeesTableBody = document.querySelector('#employees-table tbody');
const reportTableBody = document.querySelector('#report-table tbody');
const employeeForm = document.getElementById('employee-form');
const reportBtn = document.getElementById('report-btn');

// Fetch all employees from the API and render them in the table
async function loadEmployees() {
  const response = await fetch('/api/employees');
  const employees = await response.json();

  employeesTableBody.innerHTML = '';
  employees.forEach(emp => {
    const row = document.createElement('tr');
    row.innerHTML = `
      <td>${emp.EMP_ID}</td>
      <td>${emp.FIRST_NAME}</td>
      <td>${emp.LAST_NAME}</td>
      <td>${emp.JOB_TITLE}</td>
      <td>$${emp.SALARY.toLocaleString()}</td>
      <td>${emp.DEPT_NAME}</td>
      <td><button class="delete-btn" data-id="${emp.EMP_ID}">Delete</button></td>
    `;
    employeesTableBody.appendChild(row);
  });
}

loadEmployees();

What does this code do?

  • The four const lines cache references to DOM elements so you do not query the page repeatedly.
  • The loadEmployees function calls fetch('/api/employees'), converts the response to JSON, then loops through each employee to build a table row.
  • Oracle returns column names in UPPERCASE (EMP_ID, FIRST_NAME, etc.) because oracledb uses uppercase property keys by default with OUT_FORMAT_OBJECT.
  • The final loadEmployees(); call at the bottom triggers the fetch as soon as the page loads.
  • Save app.js and refresh your browser.

You should see your 5 seed employees appear in the table, each row showing their ID, name, job title, salary, department, and a Delete button.

Table still empty?

  • Open your browser's DevTools console (F12) and look for errors. A 500 error means your database connection in db.js may have the wrong password or the Oracle service is not running.
  • A 404 on /api/employees means the server may not be running. Check your terminal and restart with node server.js if needed.
  • Confirm the file is named app.js (matching the <script src="app.js"> tag in your HTML).

Need more help? My employee table is empty even though I created app.js. How do I debug this?

  • Now add the event handlers that will power the form, delete buttons, and report. In app.js, find the loadEmployees(); line at the bottom and add this code directly above it:
// Handle new employee form submission
employeeForm.addEventListener('submit', async (e) => {
  e.preventDefault();
  const data = {
    first_name: document.getElementById('first_name').value,
    last_name: document.getElementById('last_name').value,
    job_title: document.getElementById('job_title').value,
    salary: parseFloat(document.getElementById('salary').value),
    dept_id: parseInt(document.getElementById('dept_id').value)
  };

  await fetch('/api/employees', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify(data)
  });

  employeeForm.reset();
  loadEmployees();
});

What does this handler do?

When the form is submitted, it prevents the default page reload, collects all input values into an object, and sends a POST request with JSON to /api/employees. After the request completes, it clears the form and reloads the table.

This endpoint does not exist yet. You will create the POST route in the next step. For now, submitting the form will produce a console error, which is expected.

  • Still above the loadEmployees(); line, add the delete handler and department report handler:
// Handle delete button clicks using event delegation
employeesTableBody.addEventListener('click', async (e) => {
  if (e.target.classList.contains('delete-btn')) {
    const empId = e.target.dataset.id;
    await fetch(`/api/employees/${empId}`, { method: 'DELETE' });
    loadEmployees();
  }
});

// Handle department report button click
reportBtn.addEventListener('click', async () => {
  const response = await fetch('/api/employees/report');
  const report = await response.json();

  reportTableBody.innerHTML = '';
  report.forEach(row => {
    const tr = document.createElement('tr');
    tr.innerHTML = `
      <td>${row.DEPT_NAME}</td>
      <td>${row.HEADCOUNT}</td>
      <td>$${row.AVG_SALARY.toLocaleString()}</td>
    `;
    reportTableBody.appendChild(tr);
  });
});

What do these handlers do?

  • The delete handler uses event delegation on the table body. When any Delete button is clicked, it reads the data-id attribute to identify which employee to remove, sends a DELETE request, then reloads the table.
  • The report handler fetches aggregated data from /api/employees/report and populates the report table with department headcount and average salary.

The DELETE route does not exist on the backend yet. You will add it in the next step. The report route is coming up next in this step.

  • Save app.js.

✔️ Awesome, I've got everything!

Great. Double check you have saved public/app.js.

ⓧ I'd like to double check the full code

Your public/app.js should contain these sections in order: four DOM reference constants at the top, then the loadEmployees function, then the form submit listener, then the delete click listener, then the report button listener, and finally the loadEmployees(); call at the very bottom. Scroll up and confirm each block is present in that order.

Style the page and add the department report route

The employee table is displaying data, but it looks like plain unstyled HTML. You also need a backend route for the report button to call.

  • Open routes/employees.js and add this report route above the module.exports = router; line:
// GET department report
router.get('/report', async (req, res) => {
  try {
    const result = await run(
      `SELECT d.dept_name, COUNT(e.emp_id) AS headcount, ROUND(AVG(e.salary), 2) AS avg_salary
       FROM departments d
       LEFT JOIN employees e ON d.dept_id = e.dept_id
       GROUP BY d.dept_name
       ORDER BY d.dept_name`,
      [],
      { outFormat: oracledb.OUT_FORMAT_OBJECT }
    );
    res.json(result.rows);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: err.message });
  }
});

What does this query do?

  • The LEFT JOIN ensures departments with zero employees still appear in the report.
  • The GROUP BY aggregates employees per department so COUNT gives headcount and AVG gives average salary.
  • The ROUND(..., 2) keeps the average salary to two decimal places for clean display.
  • Save routes/employees.js.
  • Stop your server with Ctrl+C in the terminal, then restart it by running:
node server.js
  • Refresh your browser and click the Generate Report button.

You should see three rows appear in the Department Report table showing Engineering, Marketing, and Sales with their headcount and average salary values.

Report button not working?

  • Make sure you restarted the server after editing routes/employees.js. Backend changes require a restart.
  • Check the terminal for errors. A syntax error in the route file will prevent the server from starting.
  • Open DevTools and verify the network request to /api/employees/report returns a 200 status.

Need help? My Generate Report button is not showing department data. How do I fix it?

  • Now create public/style.css and paste the following base styles for layout and typography:
* {
  box-sizing: border-box;
  margin: 0;
  padding: 0;
}

body {
  font-family: Arial, sans-serif;
  padding: 20px;
  background-color: #f5f5f5;
}

h1 {
  margin-bottom: 20px;
  color: #333;
}

h2 {
  margin-bottom: 10px;
  color: #555;
}

section {
  background: #fff;
  padding: 20px;
  margin-bottom: 20px;
  border-radius: 8px;
  box-shadow: 0 2px 4px rgba(0, 0, 0, 0.1);
}

What do these styles do?

The reset (*) removes default browser margins and padding so spacing is consistent. Each <section> gets a white card with rounded corners and a subtle shadow, giving the page a clean dashboard feel.

  • Below the section rule, add styles for the form and buttons:
form {
  display: flex;
  gap: 10px;
  flex-wrap: wrap;
}

input, select {
  padding: 8px 12px;
  border: 1px solid #ddd;
  border-radius: 4px;
  font-size: 14px;
}

button {
  padding: 8px 16px;
  background-color: #0066cc;
  color: #fff;
  border: none;
  border-radius: 4px;
  cursor: pointer;
  font-size: 14px;
}

button:hover {
  background-color: #0052a3;
}
  • Save style.css and refresh your browser.

You should see the form inputs arranged in a neat row with rounded borders, and the buttons styled in blue. The page sections now appear as white cards on a grey background.

  • Add the final styles for the delete button and table below what you just wrote:
button.delete-btn {
  background-color: #cc3333;
}

button.delete-btn:hover {
  background-color: #a32929;
}

table {
  width: 100%;
  border-collapse: collapse;
  margin-top: 10px;
}

th, td {
  padding: 10px 12px;
  text-align: left;
  border-bottom: 1px solid #ddd;
}

th {
  background-color: #f0f0f0;
  font-weight: bold;
}

tr:nth-child(even) {
  background-color: #f9f9f9;
}

tr:hover {
  background-color: #e9e9e9;
}

What do these table styles do?

  • The delete-btn class gets a red background so it stands out as a destructive action.
  • The border-collapse: collapse removes double borders between cells.
  • Alternating row colors (tr:nth-child(even)) make it easier to scan across rows of data.
  • Save style.css and refresh your browser.

Before you look at the result, predict: what visual change do you expect to see in the employee table now that the table CSS rules are applied?

You should see a fully styled page. The employee table has clean borders, alternating grey/white row colors, and a hover highlight when you move your mouse over a row. The Delete buttons appear in red.

Styles not applying?

  • Confirm the <link rel="stylesheet" href="style.css"> tag is in your index.html <head>.
  • Make sure the file is named style.css (not styles.css) to match the link tag.
  • Try a hard refresh (Ctrl+Shift+R or Cmd+Shift+R) to bypass browser cache.

Still stuck? My style.css is not being applied to my HTML page. How do I fix this?

✔️ Awesome, I've got everything!

Great. Confirm you have saved all three files: index.html, app.js, and style.css.

ⓧ I'd like to double check the full code

Your public/style.css should contain these sections in order: the universal reset, body styles, heading styles, section card styles, form flex layout, input/select styles, button base styles with hover, delete-button styles with hover, table layout rules, th/td padding and borders, th background, even-row alternating color, and row hover color. Scroll up and confirm each block is present.

For routes/employees.js, confirm you have two routes: the original GET / route for all employees, and the new GET /report route with the GROUP BY query.

Your frontend is now fully wired to the API and displaying live data from Oracle. Next up, you will add the backend routes that let the form and delete buttons actually create and remove employees.

Add Create and Delete (Full CRUD)

Your app displays employee data beautifully, but it cannot change anything in the database.

A read-only app is a demo. Real software engineers need to handle writes. In this step, you will add POST and DELETE endpoints so the form and buttons you built in the previous step actually work.

In this step, get ready to:
  • Add a POST route that inserts new employees into Oracle.
  • Add a DELETE route that removes employees by ID.
  • Test the full create-read-delete loop from the browser.
Add the POST endpoint for creating employees

The frontend form already sends a POST request to /api/employees when submitted. Right now that request hits a 404 because no route handles it.

  • In routes/employees.js, find the line that says module.exports = router; at the bottom of the file.
  • Add the following POST route above that line:
// POST new employee
router.post('/', async (req, res) => {
  const { first_name, last_name, job_title, salary, dept_id } = req.body;
  try {
    const result = await run(
      `INSERT INTO employees (first_name, last_name, job_title, salary, dept_id)
       VALUES (:first_name, :last_name, :job_title, :salary, :dept_id)`,
      { first_name, last_name, job_title, salary, dept_id },
      { autoCommit: true }
    );
    res.status(201).json({ message: 'Employee added', rowsAffected: result.rowsAffected });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: err.message });
  }
});

What does this code do?

  • const { first_name, ... } = req.body destructures the JSON the frontend sends in the request body.
  • The INSERT uses bind variables (:first_name, :salary, etc.) instead of string concatenation. This prevents SQL injection attacks.
  • autoCommit: true tells oracledb to commit the INSERT immediately so the new row is visible to subsequent queries.

Why bind variables instead of template strings?

If you built the SQL string by pasting user input directly (e.g. `INSERT INTO employees VALUES('${first_name}'...)`), a malicious user could type SQL commands into the form fields. Bind variables keep data and SQL completely separate, which is the standard pattern in Oracle development.

  • Save routes/employees.js.
  • Stop your server with Ctrl+C in the terminal, then restart it by running:
node server.js
  • Open http://localhost:3000 in your browser.
  • In the Add New Employee form, enter Jane for First Name.
  • Enter Smith for Last Name.
  • Enter Data Analyst for Job Title.
  • Enter 72000 for Salary.
  • Select Engineering from the department dropdown.
  • Click Add Employee.

Before you look at the table, do you think the new employee will appear automatically, or will you need to refresh the page?

The table updates instantly. You should see Jane Smith appear as a new row at the bottom with no page refresh needed. The frontend calls loadEmployees() after the POST completes, pulling fresh data from Oracle.

Form submits but no new row appears?

  • Open your browser DevTools (F12) and check the Console tab. A 404 error means the route path doesn't match /api/employees.
  • A 500 error likely means a typo in the SQL or bind variable names. Check that the column names in your INSERT match your table exactly (first_name, last_name, etc.).
  • Make sure you restarted the server after saving the file.

Help me debug why my POST route isn't creating employees.

Add the DELETE endpoint for removing employees

Each row in the employee table already has a red Delete button. Clicking it sends a DELETE request to /api/employees/:id, but the backend doesn't handle that yet.

  • In routes/employees.js, add the following DELETE route below the POST route you just added (still above module.exports = router;):
// DELETE employee
router.delete('/:id', async (req, res) => {
  const empId = parseInt(req.params.id);
  try {
    const result = await run(
      `DELETE FROM employees WHERE emp_id = :emp_id`,
      { emp_id: empId },
      { autoCommit: true }
    );
    res.json({ message: 'Employee deleted', rowsAffected: result.rowsAffected });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: err.message });
  }
});

What does this code do?

  • router.delete('/:id', ...) handles DELETE requests where the employee ID is part of the URL path (e.g. /api/employees/3).
  • parseInt(req.params.id) converts the URL parameter from a string to a number so it matches the emp_id NUMBER column in Oracle.
  • The DELETE statement uses a bind variable (:emp_id) for the same SQL injection protection as the INSERT.
  • Save routes/employees.js.
  • Restart your server (Ctrl+C, then node server.js).
  • Refresh http://localhost:3000 in your browser.
  • Click the red Delete button on the Jane Smith row you created earlier.

The row should disappear from the table immediately. The frontend calls loadEmployees() after the DELETE completes, so the table re-renders without the removed employee.

Delete button doesn't remove the row?

  • Check the Console tab in DevTools. A 404 means the route path doesn't match. Make sure your route uses /:id (with the colon).
  • If you see a 500 error, check the terminal for the Oracle error message. A common cause is using id instead of emp_id in the WHERE clause.

Help me figure out why my DELETE route isn't working.

Verify the full create-read-delete loop

You now have a complete CRUD application (Create, Read, Delete) with data flowing from browser to API to Oracle and back.

  • Fill in the form with another test employee: Tom / Lee / Designer / 65000 / Marketing.
  • Click Add Employee and confirm Tom Lee appears in the table.
  • Click Delete on Tom Lee's row and confirm he disappears.

Do you think the deleted employee is gone from the database permanently, or just hidden from the UI?

It is gone permanently. The DELETE SQL removes the row from Oracle. If you open SQL Developer and run SELECT * FROM employees;, Tom Lee will not be there.

✔️ Awesome, I've got everything!

Great. Double check you have saved routes/employees.js and that both the POST and DELETE routes are working in the browser.

ⓧ I'd like to double check the full code

Your routes/employees.js should now contain these sections in order:

  • The require imports at the top (express, router, run, oracledb).
  • GET / route (all employees with JOIN).
  • GET /report route (department summary).
  • POST / route (insert new employee).
  • DELETE /:id route (remove employee by ID).
  • module.exports = router; at the very bottom.

Scroll up and confirm each section is present. No changes are needed to server.js, public/index.html, or public/app.js for this step.

Your app now handles real writes. Browser clicks translate into INSERT and DELETE statements hitting Oracle, proving you can wire a full-stack loop from UI to database. Next up, you will tackle the Secret Mission and push your Oracle skills further with a stored procedure.

Secret mission

Add an Update Salary Stored Procedure

Your app can add and remove employees, but what about updating existing records? In this mission, you'll write a PL/SQL stored procedure that lives inside Oracle and call it from your Node.js API. This is the pattern enterprise Oracle teams use to keep business logic in the database layer.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally, so there are no ongoing cloud costs.

Resources you used:

  • The employee-mgmt project folder on your Desktop (Node.js files, node_modules, and dependencies).
  • Oracle database objects in XEPDB1: the emp_admin user, departments table, employees table, and update_salary stored procedure.

Keep everything running

No action needed. Choose this if you're still actively building or want to extend the project further.

  • Your employee-mgmt folder stays on your Desktop.
  • Your Oracle database objects remain in XEPDB1 under the emp_admin user.
  • To restart the server later, open a terminal in the employee-mgmt folder and run node server.js.

Pause - I'll come back to this later

Shut down running processes to free up memory. Your files and database objects stay intact for when you return.

  • If your Node.js server is still running, press Ctrl+C in its terminal window to stop it.
  • To free more memory, you can stop the Oracle XE Windows service. Open services.msc, find OracleServiceXE, right-click it, and select Stop.
  • When you come back, start the OracleServiceXE service again and run node server.js from your employee-mgmt folder.

Delete - I don't want to use this again

Remove all project resources and start fresh. This permanently deletes your project files and database objects.

  • If your Node.js server is still running, press Ctrl+C in its terminal window to stop it.
  • Delete the project folder. On macOS/Linux, run:
cd ~/Desktop
rm -rf employee-mgmt

On Windows, run:

cd ~/Desktop
rmdir /s /q employee-mgmt
  • Drop the Oracle database objects. Open SQL Developer, connect to localhost:1521/XEPDB1 as a user with DBA privileges (such as SYSTEM), and run:
DROP USER emp_admin CASCADE;

What does CASCADE do?

The CASCADE keyword automatically drops all objects owned by the user. This includes your departments table, employees table, and the update_salary stored procedure in one command.

Nice Work!

Nice Work!

You just built a full-stack Employee Management System where browser clicks translate into real SQL hitting a live Oracle database. That is end-to-end software engineering in action.

You've learned how to:

  • Built a Node.js Express REST API that performs full CRUD operations against Oracle XE 21c using the oracledb Thin mode driver.
  • Designed a relational schema with foreign keys linking departments and employees tables, then wrote JOIN and GROUP BY queries to produce meaningful reports from related data.
  • Created a browser frontend that uses the Fetch API to perform real-time creates, reads, and deletes against the Oracle database without page refreshes.
  • Secret Mission: Wrote a PL/SQL stored procedure and called it from Node.js, implementing database-side business logic for salary updates.

Ready to quiz yourself?