How to Use MySQL Workbench with Node.js and Express.js

Posted on by Panther

MySQL Workbench is a powerful visual tool for database design, development, and administration. It provides an intuitive interface for creating, managing, and analyzing MySQL databases. In this tutorial, we will learn how to use MySQL Workbench with Node.js and Express.js to connect to a MySQL database, perform CRUD operations, and display the data in a web application.

Prerequisites

Before we begin, make sure you have the following installed on your machine:

  • MySQL Server: Download and install the MySQL Community Server from the official website (link).
  • MySQL Workbench: Download and install the MySQL Workbench from the official website (link).
  • Node.js: Download and install the latest stable version of Node.js from the official website (link).
  • Express.js: Install Express.js globally by running the following command:
npm install -g express
  • MySQL module for Node.js: Install the MySQL module for Node.js by running the following command in your project directory:
npm install mysql

Setting up the MySQL Database

  1. Open MySQL Workbench and connect to your local MySQL Server. If you haven’t set a password for the root user, you can use the default password “root”.
  2. Create a new database by clicking on the “Create a new schema in the connected server” button or by selecting “Database” -> “Create Schema…” from the menu.
  3. Enter a name for the database (e.g., “mydatabase”) and click “Apply”.
  4. In the Navigator panel, select the newly created database.
  5. Right-click on the database and select “Set as Default Schema”.

Creating a Node.js Project

  1. Create a new directory for your project. Open a terminal or command prompt and navigate to this directory.
  2. Initialize a new Node.js project by running the following command:
npm init
  1. Follow the prompts and enter the necessary information for your project.
  2. After the initialization is complete, a package.json file will be created in your project directory.

Creating the Express.js Server

  1. Create a new file called server.js in your project directory.
  2. Open server.js in a text editor and include the following code:
const express = require('express');
const app = express();
const port = 3000;

app.listen(port, () => {
  console.log(`Server running at http://localhost:${port}`);
});
  1. Save the file.
  2. Open a terminal or command prompt and navigate to your project directory.
  3. Start the Express.js server by running the following command:
node server.js
  1. You should see the following message in the terminal: “Server running at http://localhost:3000”.

Connecting to the MySQL Database

  1. Open server.js in a text editor.
  2. Include the following code at the top of the file to import the mysql module:
const mysql = require('mysql');
  1. Add the following code after the server initialization code to connect to the MySQL database:
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'root',
  database: 'mydatabase',
});

connection.connect((err) => {
  if (err) throw err;
  console.log('Connected to the MySQL database');
});
  1. Save the file.
  2. Restart the server by stopping it in the terminal (press Ctrl + C) and running the node server.js command again.
  3. You should see the following message in the terminal: “Connected to the MySQL database”.

Creating the Database Table

  1. Open MySQL Workbench and connect to your local MySQL Server.
  2. In the Navigator panel, select the database you created earlier.
  3. Right-click on the database and select “Create Table…”.
  4. Enter a name for the table (e.g., “users”) and click “Apply”.
  5. Click on the “+ Add” button to add columns to the table.
  6. Enter column names and select data types for each column.
  7. Click “Apply” to create the table.

Performing CRUD Operations

Next, we will show how to perform CRUD (Create, Read, Update, Delete) operations on the database using Node.js and Express.js.

Reading Data from the Database

  1. Open server.js in a text editor.
  2. Add the following code after the connection code to define a route for retrieving data from the database:
app.get('/users', (req, res) => {
  const sql = 'SELECT * FROM users';

  connection.query(sql, (err, results) => {
    if (err) throw err;
    res.send(results);
  });
});
  1. Save the file.
  2. Restart the server.
  3. Open a web browser and navigate to “http://localhost:3000/users”.
  4. You should see an array of records from the “users” table in the JSON format.

Creating Data in the Database

  1. Open server.js in a text editor.
  2. Add the following code after the reading data code to define a route for creating data in the database:
app.post('/users', (req, res) => {
  const { name, email } = req.body;
  const sql = `INSERT INTO users (name, email) VALUES ('${name}', '${email}')`;

  connection.query(sql, (err) => {
    if (err) throw err;
    res.send('User created successfully');
  });
});
  1. Save the file.
  2. Restart the server.
  3. Use a web API testing tool (e.g., Postman) to send a POST request to “http://localhost:3000/users” with a JSON payload containing “name” and “email” properties.
  4. You should receive a response with the message “User created successfully”.

Updating Data in the Database

  1. Open server.js in a text editor.
  2. Add the following code after the creating data code to define a route for updating data in the database:
app.put('/users/:id', (req, res) => {
  const { id } = req.params;
  const { name, email } = req.body;
  const sql = `UPDATE users SET name = '${name}', email = '${email}' WHERE id = ${id}`;

  connection.query(sql, (err) => {
    if (err) throw err;
    res.send('User updated successfully');
  });
});
  1. Save the file.
  2. Restart the server.
  3. Use a web API testing tool to send a PUT request to “http://localhost:3000/users/:id” (replace “:id” with the actual user ID) with a JSON payload containing “name” and “email” properties.
  4. You should receive a response with the message “User updated successfully”.

Deleting Data from the Database

  1. Open server.js in a text editor.
  2. Add the following code after the updating data code to define a route for deleting data from the database:
app.delete('/users/:id', (req, res) => {
  const { id } = req.params;
  const sql = `DELETE FROM users WHERE id = ${id}`;

  connection.query(sql, (err) => {
    if (err) throw err;
    res.send('User deleted successfully');
  });
});
  1. Save the file.
  2. Restart the server.
  3. Use a web API testing tool to send a DELETE request to “http://localhost:3000/users/:id” (replace “:id” with the actual user ID).
  4. You should receive a response with the message “User deleted successfully”.

Displaying Database Data in a Web Application

Now, let’s display the data from the MySQL database in a web application using Express.js and EJS (Embedded JavaScript).

  1. Install EJS by running the following command in your project directory:
npm install ejs
  1. Create a new directory called “views” in your project directory.
  2. Create a new file called “index.ejs” in the “views” directory.
  3. Open “index.ejs” in a text editor and add the following code:
<!DOCTYPE html>
<html>
<head>
  <title>Users</title>
</head>
<body>
  <h1>Users</h1>
  <ul>
    <% users.forEach(function(user) { %>
      <li><%= user.name %> (<%= user.email %>)</li>
    <% }); %>
  </ul>
</body>
</html>
  1. Save the file.
  2. Open server.js in a text editor.
  3. Replace the reading data code with the following code to render the “index.ejs” template with the data from the database:
app.get('/', (req, res) => {
  const sql = 'SELECT * FROM users';

  connection.query(sql, (err, results) => {
    if (err) throw err;
    res.render('index', { users: results });
  });
});
  1. Save the file.
  2. Restart the server.
  3. Open a web browser and navigate to “http://localhost:3000”.
  4. You should see a list of users with their names and email addresses.

Conclusion

In this tutorial, we learned how to use MySQL Workbench with Node.js and Express.js to connect to a MySQL database, perform CRUD operations, and display the data in a web application. Now, you can leverage the power of MySQL Workbench to design and manage your databases while using Node.js and Express.js to build web applications that interact with the database.

Related Post