Calling MySQL Stored Procedures from Node.js

Summary: in this tutorial, you will learn how to call a stored procedure in MySQL from a Node.js application.

This tutorial picks up where the Deleting Data in MySQL from Node.js tutorial left off.

The steps for calling a stored procedure are similar to the steps for executing a query as follows:

1. Connect to the MySQL database server.
2. Call the stored procedure by executing the CALL spName statement. The spName is the name of the stored procedure.
3. Close the database connection.

Calling a MySQL stored procedure example

For the demonstration, we create a new stored procedure filterTodo to query rows from the todos table based on the value of the completed field.

DELIMITER $$

CREATE PROCEDURE filterTodo(IN done BOOLEAN)
BEGIN
    SELECT * FROM todos WHERE completed = done;
END$$

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

The stored procedure filterTodo retrieves rows in the todos table depending on the value of the done argument. When the done argument is true, it retrieves all completed todos; Otherwise, it returns the incomplete todos.

To call a stored procedure, you use the CALL statement. For example, to call the filterTodo stored procedure, you execute the following statement:

CALL filterTodo(false);Code language: SQL (Structured Query Language) (sql)

The statement returns the following result set:

+----+------------------------------------+-----------+
| id | title                              | completed |
+----+------------------------------------+-----------+
|  2 | Insert a new row with placeholders |         0 |
|  3 | Master Node.js MySQL               |         0 |
+----+------------------------------------+-----------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)Code language: JavaScript (javascript)

The following stored_procedure.js program calls the filterTodo stored procedure and returns the result set:

let mysql = require('mysql');

let connection = mysql.createConnection({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
});

connection.connect((err) => {
  if (err) return console.error(err.message);

  let sql = `CALL filterTodo(?)`;

  connection.query(sql, [false], (error, results, fields) => {
    if (error) return console.error(error.message);

    console.log(results);
  });

  // close the database connection
  connection.end();
});
Code language: JavaScript (javascript)

How it works.

First, import the mysql module:

let mysql = require('mysql');Code language: JavaScript (javascript)

Second, create a connection to the MySQL database using the createConnection method:

let connection = mysql.createConnection({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
});Code language: JavaScript (javascript)

Note that we retrieve the connection parameters (host, port, user, password, and database) from environment variables using .env.

Third, connect to the MySQL database:

connection.connect((err) => {
  if (err) return console.error(err.message);
});Code language: JavaScript (javascript)

This code checks if there is any error and displays it.

Fourth, define an SQL query that calls the filterTodo stored procedure:

let sql = `CALL filterTodo(?)`;Code language: JavaScript (javascript)

Fifth, specify the data used in the query:

let data = [false];Code language: JavaScript (javascript)

In this case, the placeholder ? in the SQL query will be replaced by false.

Sixth, execute the SQL query using the query method:

connection.query(sql, [false], (error, results, fields) => {
  if (error) return console.error(error.message);

  console.log(results);
});Code language: JavaScript (javascript)

This code executes the query and displays the number of rows affected.

Finally, close the database connection:

connection.end();Code language: CSS (css)

In this tutorial, you have learned how to call a stored procedure in MySQL from a Node.js program.