Mastering SQL: A Comprehensive Guide to SQL in NodeJS || Lesson - 3

Mayank AggarwalMayank Aggarwal
22 min read

Table of contents

As we embark on this journey, we will explore the intricacies of the MySQL package, delve into the power of SQL commands from the command line interface (CLI), and master the art of creating, manipulating, and querying databases. Additionally, we will unravel the mysteries of routing in web development, unveiling the key components that enable seamless communication between different parts of your application.

Chapter 1: MySQL Package

In this chapter, we'll kick off our journey by understanding the MySQL package, an essential tool for interacting with MySQL databases programmatically. We'll explore how to install the package, establish a connection to a MySQL server, and execute basic SQL queries.

Installing the MySQL Package

To get started, you'll need to install the MySQL package for your programming language of choice. Below, we'll showcase examples for both Python and Node.js.

Python:

pip install mysql-connector-python

Node.js:

npm install mysql

Establishing a Connection

Once the package is installed, the next step is to establish a connection to your MySQL server. Replace the placeholder values with your actual database credentials.

Python:

import mysql.connector

# Replace placeholders with your actual database credentials
config = {
    'user': 'your_username',
    'password': 'your_password',
    'host': 'your_host',
    'database': 'your_database',
    'raise_on_warnings': True
}

# Establish a connection
connection = mysql.connector.connect(**config)

Node.js:

const mysql = require('mysql');

// Replace placeholders with your actual database credentials
const connection = mysql.createConnection({
  host: 'your_host',
  user: 'your_username',
  password: 'your_password',
  database: 'your_database'
});

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

Executing Basic SQL Queries

With a successful connection, you can now execute basic SQL queries. Let's perform a simple SELECT query to fetch data from a hypothetical 'users' table.

Python:

# Create a cursor to execute SQL queries
cursor = connection.cursor()

# Example: SELECT query
cursor.execute("SELECT * FROM users")

# Fetch and print the results
for row in cursor.fetchall():
    print(row)

# Close the cursor and connection
cursor.close()
connection.close()

Node.js:

// Example: SELECT query
connection.query("SELECT * FROM users", (err, results) => {
  if (err) throw err;

  // Print the results
  console.log(results);

  // Close the connection
  connection.end();
});

This introductory chapter lays the foundation for our exploration of MySQL and sets the stage for more advanced interactions in the upcoming chapters. In the next chapter, we'll delve into using SQL commands from the command line interface (CLI).

Chapter 2: Using SQL from CLI

In this chapter, we'll delve into the basics of using SQL commands directly from the Command Line Interface (CLI). This hands-on approach provides a fundamental understanding of SQL syntax and serves as a precursor to interacting with databases programmatically in subsequent chapters.

Accessing the MySQL CLI

To start using SQL commands, open a terminal and access the MySQL CLI by entering the following command:

mysql -u your_username -p

Replace your_username with your actual MySQL username. You'll be prompted to enter your password.

Basic SQL Commands

  1. SELECT Statement:

    Retrieve data from a table using the SELECT statement.

     SELECT * FROM users;

    This query fetches all columns (*) from the 'users' table.

  2. INSERT Statement:

    Add new records to a table using the INSERT statement.

     INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');

    This query inserts a new user into the 'users' table with a specified username and email.

  3. UPDATE Statement:

    Modify existing records in a table using the UPDATE statement.

     UPDATE users SET email = 'john.doe@example.com' WHERE username = 'john_doe';

    This query updates the email address for the user with the username 'john_doe'.

  4. DELETE Statement:

    Remove records from a table using the DELETE statement.

     DELETE FROM users WHERE username = 'john_doe';

    This query deletes the user with the username 'john_doe' from the 'users' table.

Examples of SQL Commands

Let's put these SQL commands into action within the MySQL CLI.

-- SELECT
SELECT * FROM users;

-- INSERT
INSERT INTO users (username, email) VALUES ('alice_smith', 'alice@example.com');

-- UPDATE
UPDATE users SET email = 'alice.smith@example.com' WHERE username = 'alice_smith';

-- DELETE
DELETE FROM users WHERE username = 'john_doe';

Executing these commands demonstrates how SQL operates on the database directly. Understanding these basics is crucial as we progress into interacting with the database programmatically in later chapters.

This hands-on experience with SQL commands from the CLI lays the groundwork for creating and manipulating tables as we move forward. In the next chapter, we'll apply this knowledge to create a 'user' table for our web application.

Chapter 3: Create Table 'User'

Creating a table in MySQL is a foundational step in database design. In this chapter, we will delve into the process of creating a 'User' table using Node.js and Express with the help of the mysql package.

Setting up the Project

Before diving into creating the table, make sure you have Node.js and npm (Node Package Manager) installed. Create a new Node.js project and install the necessary packages:

mkdir node-mysql-example
cd node-mysql-example
npm init -y
npm install express mysql

Now, create an index.js file to start building our application:

// index.js

const express = require('express');
const mysql = require('mysql');
const app = express();
const port = 3000;

// MySQL Connection Configuration
const db = mysql.createConnection({
  host: 'localhost',
  user: 'your_username',
  password: 'your_password',
  database: 'your_database',
});

// Connect to MySQL
db.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err);
  } else {
    console.log('Connected to MySQL');
  }
});

// Create 'User' Table
const createUserTableQuery = `
  CREATE TABLE IF NOT EXISTS User (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
  )
`;

db.query(createUserTableQuery, (err, result) => {
  if (err) {
    console.error('Error creating User table:', err);
  } else {
    console.log('User table created successfully');
  }
});

app.listen(port, () => {
  console.log(`Server is running on port ${port}`);
});

Explanation

  1. MySQL Connection Configuration: Set up the connection details for your MySQL database. Replace 'your_username', 'your_password', and 'your_database' with your actual MySQL credentials.

  2. Connect to MySQL: Establish a connection to the MySQL database using the mysql package.

  3. Create 'User' Table Query: Define the SQL query to create the 'User' table. The table has columns for id (auto-incremented primary key), username, and email.

  4. Execute the Query: Use the query method to execute the SQL query. This query checks if the 'User' table already exists and creates it if not.

  5. Express Server Setup: Set up a basic Express server to listen on port 3000.

  6. Listen for Connections: Start the Express server and listen for incoming connections.

Running the Application

Save the changes to index.js and run the application:

node index.js

Visit http://localhost:3000 in your browser. The application will connect to the MySQL database and create the 'User' table if it doesn't exist.

This chapter lays the groundwork for our application by creating the necessary table to store user data. Subsequent chapters will build upon this foundation, incorporating CRUD operations and routing.

Chapter 4: Insert into 'User'

Now that we have our 'User' table set up, it's time to explore how to insert individual records into this table using Node.js and Express. In this chapter, we'll focus on the 'INSERT' statement to add a single user to the database.

Updating index.js

Let's continue building upon the existing project from Chapter 3. We'll enhance our Express application to handle the insertion of a user into the 'User' table.

// index.js

// ... (previous code)

// Express Middleware to Parse JSON
app.use(express.json());

// Route to Insert a User
app.post('/add-user', (req, res) => {
  const { username, email } = req.body;

  if (!username || !email) {
    return res.status(400).json({ error: 'Username and email are required' });
  }

  const insertUserQuery = 'INSERT INTO User (username, email) VALUES (?, ?)';

  db.query(insertUserQuery, [username, email], (err, result) => {
    if (err) {
      console.error('Error inserting user:', err);
      return res.status(500).json({ error: 'Internal Server Error' });
    }

    console.log('User inserted successfully');
    res.status(201).json({ message: 'User inserted successfully' });
  });
});

// ... (remaining code)

Explanation

  1. Express Middleware to Parse JSON: We use express.json() to parse incoming JSON requests, making it easier to handle data sent from the client.

  2. Route to Insert a User: We create a new route, /add-user, that listens for POST requests. This route expects a JSON payload containing username and email.

  3. Input Validation: Check if both username and email are provided. If not, respond with a 400 Bad Request status and an error message.

  4. Insert User Query: Define the SQL query for inserting a user into the 'User' table using the 'INSERT' statement. We use placeholders (?) for the values to prevent SQL injection.

  5. Execute the Query: Use the query method to execute the SQL query, passing the values for username and email as an array.

  6. Handle Errors and Respond: If there's an error during the insertion process, log the error and respond with a 500 Internal Server Error. Otherwise, log a success message and respond with a 201 Created status.

Testing the Endpoint

Now, you can test the /add-user endpoint using a tool like Postman or by sending a POST request using a tool like curl or from a web application.

Example using curl:

curl -X POST -H "Content-Type: application/json" -d '{"username": "john_doe", "email": "john.doe@example.com"}' http://localhost:3000/add-user

This chapter extends our application, allowing us to insert individual users into the 'User' table. In the subsequent chapters, we'll explore more advanced topics such as bulk insertion and routing for a complete web application.

Chapter 5: Insert in Bulk

Building upon the foundation laid in the previous chapters, we'll now explore how to insert multiple users into the 'User' table in bulk. This can significantly improve efficiency when dealing with larger datasets.

Extending index.js

Let's continue enhancing our Express application to handle bulk insertion of users.

// index.js

// ... (previous code)

// Route to Insert Users in Bulk
app.post('/add-users', (req, res) => {
  const users = req.body;

  if (!users || !Array.isArray(users) || users.length === 0) {
    return res.status(400).json({ error: 'Invalid or empty user data' });
  }

  const insertUsersQuery = 'INSERT INTO User (username, email) VALUES ?';

  // Extracting values for bulk insertion
  const values = users.map((user) => [user.username, user.email]);

  db.query(insertUsersQuery, [values], (err, result) => {
    if (err) {
      console.error('Error inserting users in bulk:', err);
      return res.status(500).json({ error: 'Internal Server Error' });
    }

    console.log(`${result.affectedRows} users inserted successfully`);
    res.status(201).json({ message: `${result.affectedRows} users inserted successfully` });
  });
});

// ... (remaining code)

Explanation

  1. Route to Insert Users in Bulk: We create a new route, /add-users, that listens for POST requests. This route expects a JSON array containing user objects with username and email properties.

  2. Input Validation: Check if the provided data is a non-empty array of users. If not, respond with a 400 Bad Request status and an error message.

  3. Insert Users Query: Define the SQL query for bulk insertion using the 'INSERT' statement with the VALUES keyword followed by a placeholder for the array of values.

  4. Prepare Values for Bulk Insertion: Extract the username and email values from each user object and create an array of arrays (values) to be used in the bulk insertion query.

  5. Execute the Query: Use the query method to execute the SQL query, passing the array of values for bulk insertion.

  6. Handle Errors and Respond: If there's an error during the bulk insertion process, log the error and respond with a 500 Internal Server Error. Otherwise, log the number of affected rows (number of users inserted) and respond with a 201 Created status.

Testing the Endpoint

You can test the /add-users endpoint similarly to the single-user insertion. Use a tool like Postman or send a POST request using curl or a web application.

Example using curl:

curl -X POST -H "Content-Type: application/json" -d '[{"username": "john_doe", "email": "john.doe@example.com"}, {"username": "jane_smith", "email": "jane.smith@example.com"}]' http://localhost:3000/add-users

This chapter expands our application's capabilities, allowing for the efficient insertion of multiple users into the 'User' table in a single query. The next chapters will focus on routing and building a complete web application.

Chapter 6: Routing

Routing plays a crucial role in web development, directing incoming requests to the appropriate parts of the application. In this chapter, we'll establish the groundwork for handling different routes in our Node.js and Express application.

Extending index.js

We'll enhance our Express application to include basic routing capabilities.

// index.js

// ... (previous code)

// Home Route
app.get('/', (req, res) => {
  res.send('Welcome to the Home Page!');
});

// About Route
app.get('/about', (req, res) => {
  res.send('This is the About Page.');
});

// Contact Route
app.get('/contact', (req, res) => {
  res.send('Contact us at contact@example.com');
});

// ... (remaining code)

Explanation

  1. Home Route: We define a route for the home page (/). When a user navigates to the root URL, they will see the message "Welcome to the Home Page!".

  2. About Route: Another route is created for the '/about' path. Visiting this route will display the message "This is the About Page.".

  3. Contact Route: Similarly, we create a route for the '/contact' path. Users accessing this route will see the message "Contact us at contact@example.com".

Testing the Routes

Run your application (node index.js) and navigate to the following URLs in your browser or use tools like curl or Postman:

Each route should display the respective message. This simple routing mechanism provides a foundation for building more complex applications where different routes lead to various functionalities.

Dynamic Routing

Express also allows for dynamic routing by defining routes with parameters. For example:

// Dynamic Route
app.get('/user/:userId', (req, res) => {
  const userId = req.params.userId;
  res.send(`User Profile Page for user with ID ${userId}`);
});

Here, the ':userId' part of the route is a parameter that can capture values from the URL. For instance, accessing http://localhost:3000/user/123 will display "User Profile Page for user with ID 123".

In the upcoming chapters, we'll integrate our MySQL database into these routes, allowing us to dynamically fetch and display user data based on the route parameters.

Chapter 7: Home Route

Now that we've laid the groundwork for routing in our Express application, let's dive into a more practical scenario. In this chapter, we'll integrate our MySQL database with the home route to dynamically retrieve and display user data.

Extending index.js

Let's enhance our home route to fetch user data from the 'User' table in the MySQL database.

// index.js

// ... (previous code)

// Home Route
app.get('/', (req, res) => {
  const getUsersQuery = 'SELECT * FROM User';

  db.query(getUsersQuery, (err, result) => {
    if (err) {
      console.error('Error fetching users:', err);
      return res.status(500).json({ error: 'Internal Server Error' });
    }

    // Render a simple HTML page with user data
    const usersHtml = result.map(user => `<li>${user.username} - ${user.email}</li>`).join('');
    const html = `<h1>User List</h1><ul>${usersHtml}</ul>`;

    res.send(html);
  });
});

// ... (remaining code)

Explanation

  1. SELECT Query: We define a SQL query (getUsersQuery) to select all columns from the 'User' table. This query retrieves all user data from the database.

  2. Execute the Query: Use the query method to execute the SELECT query. The callback function handles the result or any errors that may occur during the database operation.

  3. Render HTML: For simplicity, we create an HTML page in the response. The page includes an unordered list (<ul>) with list items (<li>) for each user, displaying their username and email.

  4. Handle Errors: If there's an error during the database operation, log the error and respond with a 500 Internal Server Error.

Testing the Home Route

Run your application (node index.js) and navigate to http://localhost:3000/ in your browser. The page should display a list of users retrieved from the 'User' table in your MySQL database.

Improving Rendering with Templates

In a real-world scenario, you'd likely use a template engine (such as EJS, Pug, or Handlebars) to render HTML pages more efficiently. This would separate the HTML structure from the code logic. For instance, using the EJS template engine:

  1. Install EJS: npm install ejs

  2. Set EJS as the view engine in index.js:

// index.js

// ... (previous code)

// Set EJS as the View Engine
app.set('view engine', 'ejs');

// ... (remaining code)
  1. Create a 'views' folder in your project directory and add a file named home.ejs with the following content:
<!-- views/home.ejs -->

<h1>User List</h1>
<ul>
  <% users.forEach(user => { %>
    <li><%= user.username %> - <%= user.email %></li>
  <% }); %>
</ul>
  1. Modify the home route to render the 'home.ejs' template:
// index.js

// ... (previous code)

// Home Route with EJS Rendering
app.get('/', (req, res) => {
  const getUsersQuery = 'SELECT * FROM User';

  db.query(getUsersQuery, (err, result) => {
    if (err) {
      console.error('Error fetching users:', err);
      return res.status(500).json({ error: 'Internal Server Error' });
    }

    // Render the 'home' template with user data
    res.render('home', { users: result });
  });
});

// ... (remaining code)

Now, your application uses the 'home.ejs' template to render the home route, providing a cleaner separation between logic and presentation.

Chapter 8: Add Template

In this chapter, we'll take a step further by adding a template for adding new users to our system. We'll create an HTML form that allows users to input their data, and upon submission, the data will be sent to the server for insertion into the MySQL database.

Extending index.js

Let's update our Express application to include a route for adding users and a corresponding template.

// index.js

// ... (previous code)

// Set EJS as the View Engine
app.set('view engine', 'ejs');

// Add User Form Route
app.get('/add-user-form', (req, res) => {
  res.render('add-user-form');
});

// ... (remaining code)

Creating the 'add-user-form.ejs' Template

  1. Create a new file named add-user-form.ejs in the 'views' folder:
<!-- views/add-user-form.ejs -->

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Add User Form</title>
</head>
<body>
  <h1>Add User</h1>
  <form action="/add-user" method="post">
    <label for="username">Username:</label>
    <input type="text" id="username" name="username" required>
    <br>
    <label for="email">Email:</label>
    <input type="email" id="email" name="email" required>
    <br>
    <button type="submit">Add User</button>
  </form>
</body>
</html>
  1. Update index.js to handle form submissions:
// index.js

// ... (previous code)

// Add User Form Route
app.get('/add-user-form', (req, res) => {
  res.render('add-user-form');
});

// Handle Form Submission
app.post('/add-user', (req, res) => {
  const { username, email } = req.body;

  if (!username || !email) {
    return res.status(400).json({ error: 'Username and email are required' });
  }

  const insertUserQuery = 'INSERT INTO User (username, email) VALUES (?, ?)';

  db.query(insertUserQuery, [username, email], (err, result) => {
    if (err) {
      console.error('Error inserting user:', err);
      return res.status(500).json({ error: 'Internal Server Error' });
    }

    console.log('User inserted successfully');
    res.status(201).json({ message: 'User inserted successfully' });
  });
});

// ... (remaining code)

Explanation

  1. Add User Form Route: We create a new route, /add-user-form, that renders the 'add-user-form' template. This route is responsible for displaying the form to add a new user.

  2. 'add-user-form.ejs' Template: This template includes a simple HTML form with fields for the username and email. The form uses the POST method to send data to the server at the /add-user route.

  3. Handle Form Submission: We extend the /add-user route to handle POST requests. It extracts the username and email from the form submission and inserts the user into the 'User' table in the MySQL database.

Testing the Add User Form

  1. Start your application: node index.js.

  2. Open your browser and navigate to http://localhost:3000/add-user-form.

  3. Fill out the form with a username and email and click the "Add User" button.

You can also test the form submission programmatically using curl or Postman. This chapter adds an interactive element to our application, allowing users to input data and have it dynamically inserted into the MySQL database.

Chapter 9: Show Route

In this chapter, we'll build upon the routing capabilities of our Node.js and Express application by creating a route to display individual user details. This "show" route will dynamically retrieve and present data for a specific user based on a given user ID.

Extending index.js

Let's enhance our Express application to include the "show" route.

// index.js

// ... (previous code)

// Show User Route
app.get('/user/:userId', (req, res) => {
  const userId = req.params.userId;

  if (!userId || isNaN(userId)) {
    return res.status(400).json({ error: 'Invalid user ID' });
  }

  const getUserQuery = 'SELECT * FROM User WHERE id = ?';

  db.query(getUserQuery, [userId], (err, result) => {
    if (err) {
      console.error('Error fetching user:', err);
      return res.status(500).json({ error: 'Internal Server Error' });
    }

    if (result.length === 0) {
      return res.status(404).json({ error: 'User not found' });
    }

    // Render a simple HTML page with user data
    const user = result[0];
    const html = `<h1>${user.username}</h1><p>Email: ${user.email}</p>`;

    res.send(html);
  });
});

// ... (remaining code)

Explanation

  1. Show User Route: We create a new route, /user/:userId, to handle requests for displaying individual user details. The :userId part of the route is a parameter that captures the user ID from the URL.

  2. User ID Validation: Check if the user ID is valid (a positive integer). If not, respond with a 400 Bad Request status and an error message.

  3. SELECT Query for User: Define a SQL query (getUserQuery) to select a user from the 'User' table based on the provided user ID.

  4. Execute the Query: Use the query method to execute the SELECT query, passing the user ID as a parameter.

  5. Handle Errors: If there's an error during the database operation, log the error and respond with a 500 Internal Server Error.

  6. User Not Found: If the query result is empty, respond with a 404 Not Found status and an error message indicating that the user was not found.

  7. Render HTML: If a user is found, render a simple HTML page with the user's username and email.

Testing the Show Route

  1. Start your application: node index.js.

  2. Open your browser and navigate to http://localhost:3000/user/1 (replace 1 with an existing user ID in your database).

  3. You should see a page displaying the username and email of the user with the specified ID.

This chapter adds a dynamic "show" route to our application, allowing us to retrieve and display individual user details based on the user ID provided in the URL.

Chapter 10: Edit Route

Building on the foundation of the "show" route, let's create an "edit" route that allows users to view and modify their information. In this chapter, we'll implement a form for editing user details, and upon submission, the updated information will be saved to the MySQL database.

Extending index.js

Let's enhance our Express application to include the "edit" route.

// index.js

// ... (previous code)

// Edit User Form Route
app.get('/edit-user/:userId', (req, res) => {
  const userId = req.params.userId;

  if (!userId || isNaN(userId)) {
    return res.status(400).json({ error: 'Invalid user ID' });
  }

  const getUserQuery = 'SELECT * FROM User WHERE id = ?';

  db.query(getUserQuery, [userId], (err, result) => {
    if (err) {
      console.error('Error fetching user:', err);
      return res.status(500).json({ error: 'Internal Server Error' });
    }

    if (result.length === 0) {
      return res.status(404).json({ error: 'User not found' });
    }

    const user = result[0];
    res.render('edit-user-form', { user });
  });
});

// ... (remaining code)

Creating the 'edit-user-form.ejs' Template

  1. Create a new file named edit-user-form.ejs in the 'views' folder:
<!-- views/edit-user-form.ejs -->

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Edit User</title>
</head>
<body>
  <h1>Edit User</h1>
  <form action="/update-user/<%= user.id %>" method="post">
    <label for="username">Username:</label>
    <input type="text" id="username" name="username" value="<%= user.username %>" required>
    <br>
    <label for="email">Email:</label>
    <input type="email" id="email" name="email" value="<%= user.email %>" required>
    <br>
    <button type="submit">Update User</button>
  </form>
</body>
</html>
  1. Update index.js to handle form submissions for updating user details:
// index.js

// ... (previous code)

// Edit User Form Route
app.get('/edit-user/:userId', (req, res) => {
  const userId = req.params.userId;

  if (!userId || isNaN(userId)) {
    return res.status(400).json({ error: 'Invalid user ID' });
  }

  const getUserQuery = 'SELECT * FROM User WHERE id = ?';

  db.query(getUserQuery, [userId], (err, result) => {
    if (err) {
      console.error('Error fetching user:', err);
      return res.status(500).json({ error: 'Internal Server Error' });
    }

    if (result.length === 0) {
      return res.status(404).json({ error: 'User not found' });
    }

    const user = result[0];
    res.render('edit-user-form', { user });
  });
});

// Update User Route
app.post('/update-user/:userId', (req, res) => {
  const userId = req.params.userId;
  const { username, email } = req.body;

  if (!userId || isNaN(userId) || !username || !email) {
    return res.status(400).json({ error: 'Invalid user ID, username, or email' });
  }

  const updateUserQuery = 'UPDATE User SET username = ?, email = ? WHERE id = ?';

  db.query(updateUserQuery, [username, email, userId], (err, result) => {
    if (err) {
      console.error('Error updating user:', err);
      return res.status(500).json({ error: 'Internal Server Error' });
    }

    console.log('User updated successfully');
    res.status(200).json({ message: 'User updated successfully' });
  });
});

// ... (remaining code)

Explanation

  1. Edit User Form Route: We create a new route, /edit-user/:userId, to handle requests for displaying the edit form. This route extracts the user ID from the URL, fetches the corresponding user from the database, and renders the 'edit-user-form' template with the user data.

  2. 'edit-user-form.ejs' Template: This template is similar to the 'add-user-form.ejs' template, but it includes the current user's data as default values in the form fields.

  3. Update User Route: We create a new route, /update-user/:userId, to handle form submissions for updating user details. This route extracts the user ID from the URL, validates the incoming data, and executes an SQL UPDATE query to update the user's information in the database.

Testing the Edit Route

  1. Start your application: node index.js.

  2. Open your browser and navigate to http://localhost:3000/edit-user/1 (replace 1 with an existing user ID in your database).

  3. Modify the user details in the form and click the "Update User" button.

You can also test the form submission programmatically using curl or Postman. This chapter adds an "edit" route to our application, allowing users to view and update their information through a user-friendly form.

Chapter 11: Update Route

In this chapter, we will focus on the "update" route, which handles the process of updating user details in the MySQL database based on the form submission from the "edit" route. This route completes the CRUD (Create, Read, Update, Delete) operations for user management in our Node.js and Express application.

Extending index.js

Let's enhance our Express application to include the "update" route.

// index.js

// ... (previous code)

// Update User Route
app.post('/update-user/:userId', (req, res) => {
  const userId = req.params.userId;
  const { username, email } = req.body;

  if (!userId || isNaN(userId) || !username || !email) {
    return res.status(400).json({ error: 'Invalid user ID, username, or email' });
  }

  const updateUserQuery = 'UPDATE User SET username = ?, email = ? WHERE id = ?';

  db.query(updateUserQuery, [username, email, userId], (err, result) => {
    if (err) {
      console.error('Error updating user:', err);
      return res.status(500).json({ error: 'Internal Server Error' });
    }

    console.log('User updated successfully');
    res.redirect(`/user/${userId}`);
  });
});

// ... (remaining code)

Explanation

  1. Update User Route: We extend the /update-user/:userId route to handle POST requests for updating user details. This route extracts the user ID from the URL and the updated username and email from the form submission.

  2. Data Validation: Validate the user ID, username, and email. If any of these values are invalid or missing, respond with a 400 Bad Request status and an error message.

  3. UPDATE Query: Define a SQL query (updateUserQuery) to update the user's information in the 'User' table based on the provided user ID.

  4. Execute the Query: Use the query method to execute the UPDATE query, passing the updated username, email, and user ID as parameters.

  5. Handle Errors: If there's an error during the database operation, log the error and respond with a 500 Internal Server Error.

  6. User Updated Successfully: If the update is successful, log a success message and redirect the user to the "show" route (/user/:userId) to display the updated user details.

Testing the Update Route

  1. Start your application: node index.js.

  2. Open your browser and navigate to http://localhost:3000/edit-user/1 (replace 1 with an existing user ID in your database).

  3. Modify the user details in the form and click the "Update User" button.

After submitting the form, you should be redirected to the user's show page (/user/:userId), where you can see the updated information.

This chapter completes the CRUD functionality for user management in our application, allowing users to update their details seamlessly. The interconnected nature of these routes provides a holistic approach to building a robust web application with Node.js and Express.

By interconnecting these chapters, you'll gain a comprehensive understanding of MySQL, SQL commands, routing in web development, and the integration of database operations into your applications. Happy coding!

Next Part will be uploaded soon. So make sure to follow me.

51
Subscribe to my newsletter

Read articles from Mayank Aggarwal directly inside your inbox. Subscribe to the newsletter, and don't miss out.

mayankmayankaggarwalWeMakeDevsHashnodeSQLMERN Stack

Written by

Mayank Aggarwal
Mayank Aggarwal

Hello! I'm Mayank Aggarwal a B.Tech Student. As a dedicated tech enthusiast and blogger, I'm excited to welcome you to my corner of the internet. I am A backend Developer. I am Working on My DSA Skills. Now preparing for Interviews.