MySQL Get Started

MySQL Get Started with Node.js

MySQL is a powerful, open-source relational database that is widely used for storing and managing data. When combined with Node.js, MySQL allows you to build robust, data-driven applications. This guide will show you how to integrate MySQL with Node.js step by step.

Why Use MySQL with Node.js?

• Scalability: MySQL can handle large datasets, making it suitable for real-world applications.
• Ease of Use: MySQL is beginner-friendly and works seamlessly with Node.js.
• Flexibility: Node.js provides non-blocking database queries, ensuring high performance.
• Popularity: MySQL is one of the most widely used databases, supported by a large community.

Prerequisites

Before getting started, ensure you have the following installed on your system:

1. Node.js: Download Node.js
2. MySQL: Download MySQL
3. NPM: Node.js includes NPM (Node Package Manager) by default.

Step 1: Install the MySQL Module

The MySQL module allows Node.js to connect to and interact with a MySQL database.

Install the MySQL Package

Run the following command in your project directory:

npm install mysql

Step 2: Create a Database in MySQL

Open the MySQL Command Line or any MySQL GUI (like phpMyAdmin).

Create a new database:

CREATE DATABASE nodejs_demo;

Switch to the database:

USE nodejs_demo;

Create a table:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    age INT
);

Step 3: Connect MySQL to Node.js

Basic Connection

Create a file named app.js and add the following code:

const mysql = require('mysql');

// Create a connection to the database
const connection = mysql.createConnection({
    host: 'localhost', // Your MySQL server hostname
    user: 'root', // Your MySQL username
    password: '', // Your MySQL password
    database: 'nodejs_demo', // Your database name
});

// Connect to the database
connection.connect((err) => {
    if (err) {
        console.error('Error connecting to MySQL:', err);
        return;
    }
    console.log('Connected to MySQL successfully!');
});

// Close the connection
connection.end();

Step 4: Performing CRUD Operations

CRUD stands for Create, Read, Update, Delete, and these are the basic operations you’ll perform on a database.

1. Create: Insert Data into a Table

const mysql = require('mysql');

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

// Insert a new user
const user = { name: 'John Doe', email: 'john@example.com', age: 30 };
const sql = 'INSERT INTO users SET ?';

connection.query(sql, user, (err, result) => {
    if (err) {
        console.error('Error inserting data:', err);
        return;
    }
    console.log('Data inserted successfully! ID:', result.insertId);
});

// Close the connection
connection.end();

2. Read: Fetch Data from a Table

const sql = 'SELECT * FROM users';

connection.query(sql, (err, results) => {
    if (err) {
        console.error('Error fetching data:', err);
        return;
    }
    console.log('Data fetched:', results);
});

// Close the connection
connection.end();

3. Update: Modify Existing Data

const sql = 'UPDATE users SET age = ? WHERE id = ?';
const data = [35, 1]; // Update age to 35 where id is 1

connection.query(sql, data, (err, result) => {
    if (err) {
        console.error('Error updating data:', err);
        return;
    }
    console.log('Data updated successfully! Rows affected:', result.affectedRows);
});

// Close the connection
connection.end();

4. Delete: Remove Data from a Table

const sql = 'DELETE FROM users WHERE id = ?';
const data = [1]; // Delete the user with id 1

connection.query(sql, data, (err, result) => {
    if (err) {
        console.error('Error deleting data:', err);
        return;
    }
    console.log('Data deleted successfully! Rows affected:', result.affectedRows);
});

// Close the connection
connection.end();

Step 5: Using Connection Pools

For better performance, use connection pools to manage multiple database connections efficiently.

const pool = mysql.createPool({
    connectionLimit: 10,
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs_demo',
});

pool.getConnection((err, connection) => {
    if (err) {
        console.error('Error getting a connection:', err);
        return;
    }

    connection.query('SELECT * FROM users', (err, results) => {
        connection.release(); // Release the connection back to the pool
        if (err) {
            console.error('Error fetching data:', err);
            return;
        }
        console.log('Data fetched from pool:', results);
    });
});

Handling Errors

Always handle potential errors while working with MySQL, such as:

1. Connection Errors: Check your credentials and database server status.
2. SQL Errors: Validate your queries to avoid syntax errors.
3. Connection Timeout: Use connection pools to manage multiple requests efficiently.