How to execute a JavaScript Stored Function in MySQL 9.0 using NodeJS and Postman?

Question

I recently came across the new features in MySQL 9.0 and found out about JavaScript Stored Programs in this link and in the MySQL docs. I also read this part about the new feature here.

So, I decided to test it using NodeJS and Postman.

Let's say I executed the below code in MySQL:

CREATE FUNCTION MySum(arg1 INT, arg2 INT) 
RETURNS INT LANGUAGE JAVASCRIPT AS 
$$
  return arg1 + arg2
$$;

I have the main NodeJS file (index.js) below:

const express = require('express');
const cors = require('cors');
const connection = require('./db/connection');
const my_app = require('./utilities/app');

//Creating an Express application
const app = express();

//Use CORS
app.use(cors());

//Routes
app.use('', try);

// Initializing server
app.listen(3000, "localhost", () => {
    console.log("Server is running on port 3000");
});

Below I have connection.js for connecting the server to the DB.

"use strict"
require('dotenv').config();
const mysql = require('mysql2');

//MySQL connection
const connection = mysql.createConnection({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASS,
    database: process.env.DB_NAME
});

connection.connect(err => {
    if (err) throw err;
    console.log('Connected to the MySQL server.');
});

module.exports = connection;

And here, I've got my try.js for executing the MySQL statements:

const express = require('express');
const router = express.Router();
const connection = require('./db/connection');

router.post('/sum', (req, res) => {
    const {num1, num2} = req.body;
    if(!num1 || !num2){
        return res.status(400).json({ error: 'The numbers are required' });
    } else {
        const sql = "SELECT sum(?, ?)";
        const values = [num1, num2];
        connection.execute(sql, values, (err, results) => {
            if (err) {
                res.status(500).json({ error: 'Error while executing the function' });
            } else {
                res.status(200).json({ message: `The sum is ${results[0]['sum(?, ?)']}` });
            }
        });
    }
});

module.exports = router;

However, I’m not sure how to display the sum of the 2 values. Is this the correct way to do this? Or is it different? Any help would be appreciated. Note: I’m using the Community Edition, not the Enterprise Edition.

Any help would be appreciated. Thanks!

Answer 1

I realized that JavaScript stored functions require the Multilingual Engine (MLE) component, which might not be available in the Community Edition. I tried to achieve thos new feature using free online DBs and platforms but it didn't worked. Instead, I used a SQL stored function to achieve the same functionality:

DELIMITER //

CREATE FUNCTION MySum(arg1 INT, arg2 INT) 
RETURNS INT
BEGIN
    RETURN arg1 + arg2;
END //

DELIMITER ;

I updated my NodeJS code to call the SQL stored function:

const express = require('express');
const router = express.Router();
const connection = require('./db/connection');

router.post('/sum', (req, res) => {
    const {num1, num2} = req.body;
    if(!num1 || !num2){
        return res.status(400).json({ error: 'The numbers are required' });
    } else {
        const sql = "SELECT MySum(?, ?) AS result";
        const values = [num1, num2];
        connection.execute(sql, values, (err, results) => {
            if (err) {
                res.status(500).json({ error: 'Error while executing the function' });
            } else {
                res.status(200).json({ message: `The sum is ${results[0].result}` });
            }
        });
    }
});

module.exports = router;

This solution worked for me. If anyone has further suggestions or improvements, please let me know!