How to Perform Complex Queries in MySQL with Node.js?

MySQL is a colleague relational database management system that is used for complex queries to deal with intricate data manipulation works. This guide is dedicated to teaching the reader more about making elaborate queries with the help of Node. js and the mysql2 package Once you have installed js you then install the mysql2 package.

Prerequisites

• Node.js
• NPM
• MySQL

Steps to Perform Complex Queries with Node.js

Step 1: Set Up the Node.js Project

Start by creating a new Node.js project and installing the necessary dependencies

mkdir geeksforgeeks
cd geeksforgeeks
npm init -y
npm install mysql2 dotenv nodemon

Project structure:

Updated dependencies:

  "dependencies": {
    "dotenv": "^16.4.5",
    "mysql2": "^3.11.0",
    "nodemon": "^3.1.4"
  }

Step 2: Create and Configure the .env File

Create a .env file in the root directory of your project to store your MySQL database credentials..

DB_HOST="localhost"
DB_USER="root"
DB_PASSWORD=""
DB_DATABASE="geeksforgeeks"

Step 3: Create a Database

• Open MySQL Workbench and connect to your MySQL server.
• Create the Database:

CREATE DATABASE geeksforgeeks;

Select the newly created database from the schema list.

Step 4: Create Tables

• Execute the following SQL script to create tables:

USE geeksforgeeks;

-- Create customers table
CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

-- Create orders table
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT,
  total DECIMAL(10, 2),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Create products table
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category VARCHAR(50)
);

Step 5: Insert Sample Data

• Execute the following SQL script to insert sample data:

INSERT INTO customers (name) VALUES ('Geeksfor'), ('Geeks');
INSERT INTO orders (customer_id, total) VALUES (1, 100.00), (2, 150.00);
INSERT INTO products (category) VALUES ('Education'), ('Coding');

After insertion tables will be:

• customers table:

• orders table:

• products table:

Step 6: Set Up the Database Connection

Create a db.js file to establish the connection between phpmyadmin and MySQL database.

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

const connection = mysql.createConnection({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_DATABASE,
});

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

module.exports = connection;

Step 7: Perform Complex Queries

Create a queries.js file to perform complex queries, such as joins, subqueries, and aggregations.

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

// Example 1: Join Query
const joinQuery = `
  SELECT orders.id, customers.name, orders.total
  FROM orders
  JOIN customers ON orders.customer_id = customers.id
`;

db.query(joinQuery, (err, results) => {
    if (err) throw err;
    console.log('Join Query Results:', results);
});

// Example 2: Subquery
const subquery = `
  SELECT name, (SELECT COUNT(*) FROM orders WHERE
  customer_id = customers.id) AS order_count
  FROM customers
`;

db.query(subquery, (err, results) => {
    if (err) throw err;
    console.log('Subquery Results:', results);
});

// Example 3: Aggregation
const aggregationQuery = `
  SELECT category, COUNT(*) AS product_count
  FROM products
  GROUP BY category
`;

db.query(aggregationQuery, (err, results) => {
    if (err) throw err;
    console.log('Aggregation Query Results:', results);
});

Step 8: Execute the project

For executing the project run the queries.js file:

node queries.js

Output:

Conclusion

In this guide, we have shown how to use Node and MySQL for the performing of the complex queries.js. Establishing a project using the mysql2 package and making several types of operations through the utilizing of API, enables you to work with and sort out data. In particular, this approach contributes to creating a solid base for a set of activities to work with large and extensive datasets in your Node.js applications.