CRUD Operations with MySQL

Overview
When building web applications with Node.js, interacting with a database is a crucial aspect. MySQL, one of the most popular relational databases, is commonly used with Node.js to store and retrieve structured data. In this post, we will guide you through performing CRUD (Create, Read, Update, and Delete) operations using MySQL in Node.js. We’ll begin by setting up a connection to the MySQL database, and then move on to performing each of the CRUD operations — inserting, retrieving, updating, and deleting records in the database. Along the way, we will include examples to help you understand how to interact with MySQL using Node.js.

---

1. Introduction to CRUD Operations

CRUD operations are fundamental operations that allow us to manage the data in a database. These operations are used to manipulate and manage data effectively:

• Create: Inserting new records into the database.
• Read: Retrieving or querying existing data from the database.
• Update: Modifying the existing records in the database.
• Delete: Removing records from the database.

These operations form the core of data management in any application, and understanding how to implement them in Node.js with MySQL is essential for building full-stack web applications.

---

2. Setting Up MySQL with Node.js

Before we can perform CRUD operations, we need to set up MySQL and connect it to our Node.js application. First, ensure you have MySQL installed on your system. You can download MySQL from the official website.

Installing MySQL in Node.js

We will use the mysql2 package, a popular MySQL client for Node.js that allows us to interact with MySQL databases. To install mysql2, open your terminal or command prompt and run the following command:

npm install mysql2

Once mysql2 is installed, you can create a connection to your MySQL database.

Creating a Connection to MySQL

Create a file named db.js to set up the connection. This will ensure that we can interact with the MySQL database throughout our application.

const mysql = require('mysql2');

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

// Connect to the database
connection.connect((err) => {
if (err) {
    console.error('Error connecting to MySQL: ' + err.stack);
    return;
}
console.log('Connected to MySQL with thread ID: ' + connection.threadId);
});

module.exports = connection;

In the code above:

• We use the mysql.createConnection method to create a connection to MySQL.
• We specify the host, user, password, and database name for the connection.
• We then call connection.connect() to establish the connection.

---

3. Performing CRUD Operations

Now that we have set up the MySQL connection, let’s perform CRUD operations. We will create functions for each operation — Create, Read, Update, and Delete — and use them to manipulate data in the database.

3.1 Create Operation (Insert Data)

The Create operation is used to insert new records into a MySQL database. Let’s say we have a table called users in our database with the following structure:

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

To insert data into this users table, we can create a function that takes user data and inserts it into the table.

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

// Function to create a new user
function createUser(name, email, age) {
const query = 'INSERT INTO users (name, email, age) VALUES (?, ?, ?)';
connection.query(query, [name, email, age], (err, results) => {
    if (err) {
        console.error('Error inserting user: ' + err.stack);
        return;
    }
    console.log('User inserted with ID: ' + results.insertId);
});
}

// Example usage
createUser('John Doe', '[email protected]', 30);

In the createUser function:

• We use the INSERT INTO SQL statement to insert data into the users table.
• The ? placeholders are used to safely insert values, which helps prevent SQL injection attacks.
• connection.query executes the query, and results.insertId provides the ID of the newly inserted record.

3.2 Read Operation (Retrieve Data)

The Read operation is used to fetch data from the database. We can retrieve all users or a specific user based on the id.

Fetching All Users:

// Function to fetch all users
function getAllUsers() {
const query = 'SELECT * FROM users';
connection.query(query, (err, results) => {
    if (err) {
        console.error('Error fetching users: ' + err.stack);
        return;
    }
    console.log('Users:', results);
});
}

// Example usage
getAllUsers();

In the getAllUsers function:

• We use the SELECT * FROM users query to fetch all records from the users table.
• results is an array of all the users retrieved from the database.

Fetching a User by ID:

// Function to fetch a user by ID
function getUserById(id) {
const query = 'SELECT * FROM users WHERE id = ?';
connection.query(query, [id], (err, results) => {
    if (err) {
        console.error('Error fetching user: ' + err.stack);
        return;
    }
    if (results.length > 0) {
        console.log('User:', results[0]);
    } else {
        console.log('User not found');
    }
});
}

// Example usage
getUserById(1);

In the getUserById function:

• We use the SELECT statement with a WHERE clause to fetch a specific user based on their id.
• If the user is found, we log their data; otherwise, we indicate that the user does not exist.

3.3 Update Operation (Modify Data)

The Update operation is used to modify existing records in the database. Let’s say we want to update the details of a user.

// Function to update a user's information
function updateUser(id, name, email, age) {
const query = 'UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?';
connection.query(query, [name, email, age, id], (err, results) => {
    if (err) {
        console.error('Error updating user: ' + err.stack);
        return;
    }
    if (results.affectedRows > 0) {
        console.log('User updated successfully');
    } else {
        console.log('User not found');
    }
});
}

// Example usage
updateUser(1, 'John Doe', '[email protected]', 31);

In the updateUser function:

• We use the UPDATE SQL statement to modify the user’s data.
• The WHERE id = ? clause ensures that only the user with the specified id is updated.
• results.affectedRows indicates how many rows were affected by the query.

3.4 Delete Operation (Remove Data)

The Delete operation is used to remove records from the database. To delete a user by their id, we can use the following function:

// Function to delete a user by ID
function deleteUser(id) {
const query = 'DELETE FROM users WHERE id = ?';
connection.query(query, [id], (err, results) => {
    if (err) {
        console.error('Error deleting user: ' + err.stack);
        return;
    }
    if (results.affectedRows > 0) {
        console.log('User deleted successfully');
    } else {
        console.log('User not found');
    }
});
}

// Example usage
deleteUser(1);

In the deleteUser function:

• We use the DELETE FROM SQL statement to remove the user with the specified id.
• results.affectedRows indicates how many rows were deleted. If no rows were deleted, it means the user with that id doesn’t exist.

---

4. Handling Errors and Closing the Connection

Error handling is an essential part of working with databases. Always check for errors after every query, as we have done in the previous examples.

Additionally, when you are done using the MySQL connection, it’s important to close it to free up resources:

connection.end((err) => {
if (err) {
    console.error('Error closing the connection: ' + err.stack);
    return;
}
console.log('Connection closed');
});

You should call connection.end() when your application is shutting down, or when you no longer need to interact with the database.