Using MySQL in Node.js

To use MySQL in a Node.js application, you'll need to install the mysql package (or mysql2 for improved performance and additional features). Below is a step-by-step guide on how to set up and interact with a MySQL database using Node.js.

Step 1: Install MySQL and Node.js MySQL Module

1. Ensure MySQL is installed and running on your machine or server.

2. Install the MySQL package for Node.js:

   npm install mysql
   

   Alternatively, you can install mysql2:

   npm install mysql2
   

Step 2: Create a MySQL Database and Table

Before interacting with the database, you need a database and a table. You can create them using MySQL Workbench, phpMyAdmin, or the MySQL command line.

CREATE DATABASE nodejs_db;

USE nodejs_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    age INT
);

Step 3: Connect to the MySQL Database from Node.js

Create a new JavaScript file (e.g., app.js) and set up the connection to the MySQL database.

File: app.js

const mysql = require('mysql');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root', // Your MySQL username
  password: 'password', // Your MySQL password
  database: 'nodejs_db' // Name of your database
});

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

Step 4: Perform CRUD Operations

4.1 Insert Data

File: app.js (continued)

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

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

4.2 Query Data

File: app.js (continued)

// Retrieve users from the database
connection.query('SELECT * FROM users', (err, results) => {
  if (err) {
    console.error('Error fetching data:', err.stack);
    return;
  }
  console.log('Users:', results);
});

4.3 Update Data

File: app.js (continued)

// Update a user's age
const newAge = 31;
const userId = 1;
connection.query('UPDATE users SET age = ? WHERE id = ?', [newAge, userId], (err, result) => {
  if (err) {
    console.error('Error updating data:', err.stack);
    return;
  }
  console.log('Rows affected:', result.affectedRows);
});

4.4 Delete Data

File: app.js (continued)

// Delete a user from the database
connection.query('DELETE FROM users WHERE id = ?', [userId], (err, result) => {
  if (err) {
    console.error('Error deleting data:', err.stack);
    return;
  }
  console.log('Rows deleted:', result.affectedRows);
});

Step 5: Close the Connection

After performing database operations, it's good practice to close the connection.

File: app.js (continued)

// Close the database connection
connection.end((err) => {
  if (err) {
    console.error('Error closing the connection:', err.stack);
    return;
  }
  console.log('Database connection closed.');
});

Step 6: Run the Application

To execute the script, run:

node app.js

Notes:

1. Error Handling: Ensure you handle errors properly in a production environment. The examples provided log errors to the console, but in real-world applications, you'd handle them more robustly.
2. Connection Pooling: For better performance in larger applications, consider using connection pooling rather than creating a new connection for each request.
3. Prepared Statements: For better security, especially to prevent SQL injection attacks, use prepared statements or parameterized queries as shown in the examples above (e.g., UPDATE and DELETE queries).
4. mysql2: If using mysql2, the syntax is almost identical, but it offers better performance and additional features, such as support for Promises and async/await.