How to Create and Use Functions in MySQL with NodeJS?

We will learn how to create and use functions in MySQL with Node.js. MySQL functions allow encapsulating complex calculations and business logic within the database, which can then be called from Node.js applications. This method is particularly useful for reusing SQL code and maintaining a clean application structure.

Steps to Create the Application

Step 1: Initialize the Node.js Application

First, create a new directory for your application and initialize a new Node.js project.

mkdir mysql-functions-app
cd mysql-functions-app
npm init -y

Step 2: Install Required Modules

Install the mysql package to interact with MySQL from Node.js.

npm install mysql

Project Structure:

Updated Dependencies:

{
  "name": "mysql-functions-app",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "mysql": "^2.18.1"
  }
}
 

Step 3: Create the MySQL Database and Function

• Open your MySQL client (e.g., MySQL Workbench, phpMyAdmin, or the command-line interface).
• Connect to your MySQL server.
• Select the database where you want to create the function.
• Create a MySQL Database (if not already done):

CREATE DATABASE my_database;
USE my_database;

• Run above command in your databse terminal i'm using phpmyadmin console.

• Create the Function in MySQL: Run the following SQL code in your MySQL client:

CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
BEGIN
   RETURN a + b;
END;

OR

DELIMITER //

CREATE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
BEGIN
   RETURN a + b;
END //

DELIMITER ;

Step 4: Set Up the Node.js Application

Create the necessary files in your Node.js project.

• Create db.js for MySQL Connection:

const mysql = require('mysql');

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'my_database'
});

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

module.exports = connection;

• Create index.js for Running the Function:

const connection = require('./db');

const query = 'SELECT add_numbers(5, 10) AS result;';
connection.query(query, (error, results) => {
    if (error) throw error;
    console.log('Result:', results[0].result);
});

connection.end();

Example: This example shows the use of function in MySQL with NodeJS.

//db.js
const mysql = require('mysql');

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'my_database'
});

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

module.exports = connection;

//index.js
const connection = require('./db');

const query = 'SELECT add_numbers(5, 10) AS result;';
connection.query(query, (error, results) => {
    if (error) throw error;
    console.log('Result:', results[0].result);
});

connection.end();

Output:

To see the output:

• Run the Node.js application by executing the following command in your terminal:

node index.js

Conclusion

By following this guide, you’ve successfully created a function in MySQL and integrated it with a Node.js application. This method allows you to encapsulate business logic directly within the database, making your application more modular and maintainable. Leveraging MySQL functions in your Node.js applications can significantly streamline your codebase, enabling easier management and better performance for database-related operations.

kartikjaat5765

Follow

Improve

Article Tags :

• Web Technologies
• Node.js