Master Node.js From Scratch

Clear, interactive, and structured Node.js lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to Node.js
Introduction to Node.js What Is Node.js? Node.js Architecture Features of Node.js Node.js vs Browser JavaScript Node.js Use Cases
2. Installation and Setup
Installation Introduction LTS and Current Versions npm Setup Environment Setup Verify Installation Node.js REPL
3. Node.js Fundamentals
Fundamentals Introduction Running JavaScript Global Objects Process Object Command-Line Arguments Environment Variables
4. JavaScript Basics
JavaScript Basics Introduction Variables: var, let and const JavaScript Data Types Comparison and Logical Operators If, Else If, Else and Switch Loops and Iteration Functions and Arrow Functions JavaScript Array Methods
5. Node.js Modules
Modules Introduction CommonJS Modules ES Modules Export and Import Built-in Modules Creating Custom Modules Path Module OS Module
6. Working with JSON
JSON Introduction JSON Syntax JSON.stringify() JSON.parse() JSON Array of Objects JSON User Database
7. Node Package Manager
npm Introduction package.json Installing Packages Dependencies and DevDependencies npm Scripts Package Versions and Semantic Versioning npm init and Project Setup
8. File System
File System Introduction Reading Files Writing Files Appending Files Deleting Files Working with Directories
9. CLI Applications
CLI Applications Introduction Command-Line Input Task Manager Project Structure Reading todos.json Adding, Updating and Deleting Tasks Saving Tasks to JSON
10. Events and EventEmitter
Events Introduction EventEmitter Registering Events Emitting Events Removing Event Listeners Custom Events
11. Asynchronous Programming
Async Programming Introduction Callbacks Promises async and await setTimeout() and setInterval() Event Loop and Non-Blocking I/O
12. Native HTTP Module
HTTP Module Introduction Creating an HTTP Server Handling Requests Sending Responses HTTP Request Methods Status Codes and Headers Handling POST Request Body Parsing Incoming JSON Products JSON API
13. Express.js
Express.js Introduction Installing Express Creating an Express Server Request and Response Objects Route Parameters and Query Strings Serving Static Files
14. Express Middleware
Middleware Introduction Built-in Middleware Application-Level Middleware Router-Level Middleware Custom Middleware Error-Handling Middleware
15. REST API Development
REST API Introduction GET Endpoints POST Endpoints PUT and PATCH Endpoints DELETE Endpoints Request Validation and API Responses
16. Database Integration
Database Introduction MySQL Integration MongoDB Integration Database CRUD Operations Database Queries and Filtering Connection Management
17. Authentication and Authorization
Authentication Introduction Password Hashing User Registration User Login JWT Authentication Authorization and Protected Routes
18. Advanced Node.js
Advanced Node.js Introduction Error Handling Debugging Node.js Applications Streams Buffers Performance and Security

MySQL Integration in Node.js

Learn how to connect a Node.js application to a MySQL database, execute SQL queries, and retrieve data using the mysql2 package.

MySQL is a relational database management system that stores data in tables containing rows and columns. A Node.js application can connect to MySQL to insert, retrieve, update, and delete records.

For example, an application can store product details in a MySQL table and retrieve those products whenever a client requests them.

Remember: Node.js runs the application logic, MySQL stores the data, and the mysql2 package helps Node.js communicate with the MySQL server.

1. What Is MySQL Integration?

MySQL integration means connecting a Node.js application to a MySQL database so that the application can execute SQL queries and work with stored data.

For example, a product table might contain the following records:

ProductId ProductName Price
1 Laptop 50000
2 Mobile 20000
3 Keyboard 1500

Node.js can send a SELECT query to MySQL and receive these records as the result of the query.

2. How Node.js Connects to MySQL

The application uses a database driver to establish a connection with MySQL. After connecting, it can execute SQL statements and process the returned results.

Client

Sends a request to the application

↓
Node.js Application

Processes the request and prepares an SQL query

↓
mysql2 Package

Communicates with the MySQL database server

↓
MySQL Database

Executes the SQL query and returns the result

↓
Node.js Response

Sends the result back to the client

3. Installing the Required Packages

First, create a Node.js project. Open a terminal in the project folder and initialize the project using npm.

Terminal
npm init -y

Next, install Express and mysql2. Express will handle HTTP requests, while mysql2 will connect the application to MySQL.

Terminal
npm install express mysql2

The project structure can look like this:

Text
mysql-node-app/
│
├── node_modules/
├── package.json
├── package-lock.json
└── app.js
Make sure a MySQL server is installed and running, and that you have a database user with permission to access your database. Installing mysql2 alone does not install the MySQL server.

4. Creating a MySQL Database and Table

Before connecting from Node.js, create a database and a table in MySQL. You can execute these statements in MySQL Workbench or another MySQL client.

SQL
CREATE DATABASE nodejsdb;

USE nodejsdb;

CREATE TABLE Products (
    ProductId INT PRIMARY KEY AUTO_INCREMENT,
    ProductName VARCHAR(100) NOT NULL,
    Price DECIMAL(10, 2) NOT NULL
);

INSERT INTO Products (ProductName, Price)
VALUES
    ('Laptop', 50000),
    ('Mobile', 20000),
    ('Keyboard', 1500);

SELECT * FROM Products;

After running the statements, the Products table contains sample data.

ProductId ProductName Price
1 Laptop 50000.00
2 Mobile 20000.00
3 Keyboard 1500.00

5. Connecting Node.js to MySQL

The mysql2 package provides a createConnection() method that establishes a connection to the MySQL server.

JavaScript
const mysql = require('mysql2');

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'your_mysql_user',
    password: 'your_mysql_password',
    database: 'nodejsdb'
});

connection.connect((error) => {
    if (error) {
        console.error('Database connection failed:', error.message);
        return;
    }

    console.log('Connected to MySQL successfully!');
});

Understanding the connection properties:

Property Purpose
host Specifies the MySQL server address.
user Specifies the MySQL username.
password Specifies the password for the database user.
database Specifies the database to use.
createConnection() Creates a database connection.
Replace the username and password with your actual MySQL credentials. Do not commit database passwords to source control. In a real application, load credentials from environment variables or a secret manager.

6. Retrieving Data Using SELECT

After connecting to MySQL, Node.js can execute a SELECT statement to retrieve product records from the database.

JavaScript
connection.query(
    'SELECT * FROM Products',
    (error, results) => {
        if (error) {
            console.error('Query failed:', error.message);
            return;
        }

        console.log(results);
    }
);

The query() method executes the SQL statement. If the query succeeds, the results argument contains the returned rows.

Example output:

Output
[
  { ProductId: 1, ProductName: 'Laptop', Price: '50000.00' },
  { ProductId: 2, ProductName: 'Mobile', Price: '20000.00' },
  { ProductId: 3, ProductName: 'Keyboard', Price: '1500.00' }
]

The exact output representation can vary by driver configuration. For example, DECIMAL values are commonly returned as strings by mysql2 unless numeric conversion is configured.

7. Using Parameterized Queries

When a query uses values supplied by a client, use placeholders instead of building SQL statements by joining strings. Parameterized queries help protect against SQL injection.

JavaScript
const productId = 1;

connection.execute(
    'SELECT * FROM Products WHERE ProductId = ?',
    [productId],
    (error, results) => {
        if (error) {
            console.error('Query failed:', error.message);
            return;
        }

        console.log(results);
    }
);

The question mark is a placeholder. The value in the array is supplied separately to the query.

Important: Never directly concatenate untrusted user input into SQL statements. Use parameterized queries for values supplied by clients.

8. Complete Example: Express with MySQL

The following example creates an Express API that retrieves all products from MySQL. It uses mysql2's promise-based connection pool so the route can use async/await.

JavaScript
const express = require('express');
const mysql = require('mysql2/promise');

const app = express();

app.use(express.json());

const pool = mysql.createPool({
    host: 'localhost',
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: 'nodejsdb',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

app.get('/api/products', async (req, res) => {
    try {
        const [rows] = await pool.execute(
            'SELECT * FROM Products'
        );

        res.status(200).json(rows);
    } catch (error) {
        console.error('Database query failed:', error.message);

        res.status(500).json({
            message: 'Unable to retrieve products'
        });
    }
});

async function startServer() {
    try {
        await pool.query('SELECT 1');

        app.listen(3000, () => {
            console.log('Server running at http://localhost:3000');
        });
    } catch (error) {
        console.error('Unable to connect to MySQL:', error.message);
        await pool.end();
        process.exit(1);
    }
}

startServer();

Set the DB_USER and DB_PASSWORD environment variables before running the application.

Start the application:

Terminal
node app.js

Open this URL in a browser or send a GET request from Postman:

HTTP
GET http://localhost:3000/api/products

If the database connection and query succeed, the API returns the products as a JSON array.

9. Common MySQL Connection Problems

Problem Possible Cause What to Check
Access denied Incorrect credentials or insufficient privileges Verify the username, password, and database permissions.
Connection refused MySQL server is stopped or host/port is incorrect Confirm the server is running and the connection settings are correct.
Unknown database The database does not exist or the name is incorrect Verify that nodejsdb has been created.
Table doesn't exist The expected table was not created in the selected database Confirm that the Products table exists in nodejsdb.
Module not found The package is missing from the project Run npm install mysql2 express.

Important Points to Remember

  • MySQL stores structured data in tables.
  • Install mysql2 to connect Node.js to MySQL.
  • Create the database and tables before querying them.
  • Use the correct database host, username, password, and database name.
  • Use SELECT queries to retrieve records.
  • Use parameterized queries for values supplied by clients.
  • Handle connection and query errors properly.
  • Use a connection pool for applications that serve multiple requests.
  • Keep database credentials out of source code.

Summary

MySQL integration allows a Node.js application to store and retrieve structured data. The mysql2 package provides the connection and query functionality needed to communicate with MySQL. Express can expose API endpoints that execute database queries and return results as JSON.

In the next lesson, we will learn how to connect Node.js applications to MongoDB.