How to call a stored procedure using JavaScript

A stored procedure is a named set of SQL queries to make it easy to reuse them repeatedly and is shared by multiple programs.

In this Answer, we’re going to discuss how we’re going to call a stored procedure using JavaScript. First, we’ll set up the database. Then we’ll connect the database with node.js to call the stored procedure using JavaScript.

Setting up the database

Let’s use a simple database named Employees having four columns, employee_id, employee_name, dept, and date_of_joining.

\c organization;
--Creating Employees table
CREATE TABLE IF NOT EXISTS Employees (
  employee_id SERIAL PRIMARY KEY,
  employee_name TEXT,
  dept TEXT,
  date_of_joining DATE
);
--Creating the stored procedure
CREATE OR REPLACE FUNCTION get_employees_by_department(
  department TEXT
)
RETURNS SETOF employees
AS $$
BEGIN
  RETURN QUERY SELECT * FROM employees WHERE dept = department;
END;
$$ LANGUAGE plpgsql;
--Inserting data into Employees table
INSERT INTO Employees (employee_name, dept, date_of_joining)
VALUES
  ('Usama Khan', 'HR', '2022-01-01'),
  ('Fatima Ahmed', 'Sales', '2022-02-15'),
  ('Hassan Ali', 'IT', '2022-09-20'),
  ('Saira Khan', 'IT', '2022-06-15'),
  ('Ahmad Malik', 'HR', '2022-07-10'),
  ('Bilal Ahmed', 'HR', '2023-01-20'),
  ('Noor Fatima', 'Sales', '2023-02-15'),
  ('Zainab Khan', 'Sales', '2022-08-25'),
  ('Sana Khan', 'HR', '2022-10-15'),
  ('Aamir Malik', 'Sales', '2022-11-30'),
  ('Amna Raza', 'IT', '2022-12-25'),
  ('Ali Hassan', 'IT', '2022-03-10'),
  ('Ayesha Siddiqui', 'HR', '2022-04-05'),
  ('Usman Ahmed', 'Sales', '2022-05-20'),
  ('Imran Malik', 'IT', '2023-03-10');

Code explanation

  • Line 1: Connection to the “organization” database is established using the \c meta command.

  • Lines 3–9: A table named Employees is created with four columns, employee_id (serial, primary key), employee_name, dept, and date_of_joining.

  • Lines 11–20: A function named get_employees_by_department() is created and defined. It returns a set of employees. The function is implemented using PL/pgSQL language.

  • Lines 22–39: A few records are inserted into the Employees table using the INSERT INTO command.

Setting up the JavaScript code

This JavaScript code snippet demonstrates how to call a stored procedure to interact with a PostgreSQL database. The focus is on executing the stored procedure to retrieve employees’ records based on different departments like “HR” and “Sales.” By leveraging the pg package and a well-defined function, we achieve seamless communication with the database. Let’s have a look at the following code:

const { Pool } = require('pg');
// Configuring the connection to the PostgreSQL database
const pool = new Pool({
  user: 'user1',
  host: 'localhost',
  database: 'organization',
  password: 'p123',
  port: 5432,
});
//Writing the function to call the stored procedure
function getEmployeesByDepartment(department) {
  const query = `SELECT * FROM get_employees_by_department('${department}')`;
  pool.query(query, (err, result) => {
    if (err) {
      console.error('Error executing query:', err);
    } else {
      console.log('Employees:', result.rows);
    }
  });
}
// Call the function for the "HR" department
getEmployeesByDepartment('HR');
// Close the connection pool
pool.end();

Code explanation

  • Line 1: We specify the required package pg.

  • Lines 3–10: The code to configure the connection to the PostgreSQL database organization.

  • Lines 12–23: We write a function to call the stored procedure, considering the failed response. We’ll show the result in the output.

  • Line 26: We call the function by passing the parameter HR to get all the employees’ records of the HR department.

  • Line 29: We release all the connections and resources used by the pool using the function pool.end().

Try it yourself

Here, we present a practical example to showcase the significance of using stored procedures in PostgreSQL. We have defined a powerful stored procedure named get_employees_by_department(), which allows you to retrieve employees’ records based on their respective departments simply by passing the department’s name as an argument to the function getEmployeesByDepartment().

By executing this code, you’ll witness firsthand how stored procedures enhance database interactions and streamline data retrieval, offering a flexible and efficient way to access information for any department with ease. Feel free to experiment with different department names to observe the dynamic results!

Please press the Run button to start.

\c organization;

--Creating Employees table
CREATE TABLE IF NOT EXISTS Employees (
  employee_id SERIAL PRIMARY KEY,
  employee_name TEXT,
  dept TEXT,
  date_of_joining DATE
);

--Creating the stored procedure
CREATE OR REPLACE FUNCTION get_employees_by_department(
  department TEXT
)
RETURNS SETOF employees
AS $$
BEGIN
  RETURN QUERY SELECT * FROM employees WHERE dept = department;
END;
$$ LANGUAGE plpgsql;

--Inserting data into Employees table
INSERT INTO Employees (employee_name, dept, date_of_joining)
VALUES
  ('Usama Khan', 'HR', '2022-01-01'),
  ('Fatima Ahmed', 'Sales', '2022-02-15'),
  ('Hassan Ali', 'IT', '2022-09-20'),
  ('Saira Khan', 'IT', '2022-06-15'),
  ('Ahmad Malik', 'HR', '2022-07-10'),
  ('Bilal Ahmed', 'HR', '2023-01-20'),
  ('Noor Fatima', 'Sales', '2023-02-15'),
  ('Zainab Khan', 'Sales', '2022-08-25'),
  ('Sana Khan', 'HR', '2022-10-15'),
  ('Aamir Malik', 'Sales', '2022-11-30'),
  ('Amna Raza', 'IT', '2022-12-25'),
  ('Ali Hassan', 'IT', '2022-03-10'),
  ('Ayesha Siddiqui', 'HR', '2022-04-05'),
  ('Usman Ahmed', 'Sales', '2022-05-20'),
  ('Imran Malik', 'IT', '2023-03-10');
Calling a stored procedure more than once using Javascript

Free Resources

License: Creative Commons-Attribution NonCommercial-ShareAlike 4.0 (CC-BY-NC-SA 4.0)