Section (2.2) - Node.js MySQL CRUD Operations

This tutorial will walk you through the basic CRUD (Create, Read, Update, and Delete) operations using Node.js and MySQL. We will use the mysql package for working with MySQL databases.

Prerequisites

Before we begin, ensure that you have the following set up:

1. Node.js installed on your machine
2. MySQL server installed and running
3. An existing MySQL database and table

Installing the MySQL Package

First, let's install the mysql package using npm:

npm install mysql

Connecting to the MySQL Database

Create a new file called database.js and add the following code to establish a connection to your MySQL database:

const mysql = require('mysql');

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

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

module.exports = connection;

Creating Records

To insert a new record into a table, we can use the INSERT INTO SQL statement. Create a new file called insert.js and add the following code:

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

const record = {
  name: 'John Doe',
  age: 28,
  city: 'New York'
};

const sql = 'INSERT INTO users SET ?';
connection.query(sql, record, (err, result) => {
  if (err) throw err;
  console.log('Record inserted:', result.insertId);
});

connection.end();

This code inserts a new record into the users table. Update the record object and table name to match your database schema.

Reading Records

To retrieve records from a table, we can use the SELECT SQL statement. Create a new file called read.js and add the following code:

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

const sql = 'SELECT * FROM users';
connection.query(sql, (err, results) => {
  if (err) throw err;
  console.log('Records retrieved:', results);
});

connection.end();

This code retrieves all records from the users table.

Updating Records

To update records in a table, we can use the UPDATE SQL statement. Create a new file called update.js and add the following code:

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

const id = 1;
const newData = {
  name: 'Jane Doe',
  age: 32,
  city: 'Los Angeles'
};

const sql = 'UPDATE users SET ? WHERE id = ?';
connection.query(sql, [newData, id], (err, result) => {
  if (err) throw err;
  console.log('Record updated:', result.affectedRows);
});

connection.end();

This code updates the record with an id of 1 in the users table. Update the id, newData object, and table name to match your database schema.

Deleting Records

To delete records from a table, we can use the DELETE SQL statement. Create a new file called delete.js and add the following code:

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

const id = 1;

const sql = 'DELETE FROM users WHERE id = ?';
connection.query(sql, id, (err, result) => {
  if (err) throw err;
  console.log('Record deleted:', result.affectedRows);
});

connection.end();

This code deletes the record with an id of 1 from the `users' table. Update the 'id' and table name to match your database schema.

Frequently Asked Questions

Q: How can I perform CRUD operations on multiple records at once?

A: You can use the INSERT INTO, UPDATE, and DELETE SQL statements with multiple values or conditions to perform CRUD operations on multiple records at once. For example, to insert multiple records, you can use the following code:

const records = [
  ['Alice', 30, 'London'],
  ['Bob', 25, 'Paris'],
  ['Charlie', 35, 'Berlin']
];

const sql = 'INSERT INTO users (name, age, city) VALUES ?';
connection.query(sql, [records], (err, result) => {
  if (err) throw err;
  console.log('Records inserted:', result.affectedRows);
});

Q: How can I handle transactions in Node.js with MySQL?

A: You can use the beginTransaction, commit, and rollback methods provided by the mysql package to handle transactions. Here's an example of a simple transaction:

connection.beginTransaction(err => {
  if (err) throw err;

  const sql = 'UPDATE users SET balance = balance - ? WHERE id = ?';
  connection.query(sql, [100, 1], (err, result) => {
    if (err) {
      return connection.rollback(() => {
        throw err;
      });
    }

    connection.query(sql, [100, 2], (err, result) => {
      if (err) {
        return connection.rollback(() => {
          throw err;
        });
      }

      connection.commit(err => {
        if (err) {
          return connection.rollback(() => {
            throw err;
          });
        }
        console.log('Transaction completed successfully.');
      });
    });
  });
});

Q: What is the difference between mysql and mysql2 packages?

A: Both mysql and mysql2 packages provide a way to connect and work with MySQL databases in Node.js. The mysql2 package is a newer, more actively maintained, and improved version of the mysql package. It offers better performance, support for prepared statements, and compatibility with MySQL, MariaDB, and Amazon RDS. You can choose either package depending on your requirements, but mysql2 is generally recommended for new projects.