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.<\/p>\n
Before we begin, make sure you have the following installed on your machine:<\/p>\n
npm install -g express\n<\/code><\/pre>\n\n- MySQL module for Node.js: Install the MySQL module for Node.js by running the following command in your project directory:<\/li>\n<\/ul>\n
npm install mysql\n<\/code><\/pre>\nSetting up the MySQL Database<\/h2>\n\n- 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”.<\/li>\n
- 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.<\/li>\n
- Enter a name for the database (e.g., “mydatabase”) and click “Apply”.<\/li>\n
- In the Navigator panel, select the newly created database.<\/li>\n
- Right-click on the database and select “Set as Default Schema”.<\/li>\n<\/ol>\n
Creating a Node.js Project<\/h2>\n\n- Create a new directory for your project. Open a terminal or command prompt and navigate to this directory.<\/li>\n
- Initialize a new Node.js project by running the following command:<\/li>\n<\/ol>\n
npm init\n<\/code><\/pre>\n\n- Follow the prompts and enter the necessary information for your project.<\/li>\n
- After the initialization is complete, a
package.json<\/code> file will be created in your project directory.<\/li>\n<\/ol>\nCreating the Express.js Server<\/h2>\n\n- Create a new file called
server.js<\/code> in your project directory.<\/li>\n- Open
server.js<\/code> in a text editor and include the following code:<\/li>\n<\/ol>\nconst express = require('express');\nconst app = express();\nconst port = 3000;\n\napp.listen(port, () => {\n console.log(`Server running at http:\/\/localhost:${port}`);\n});\n<\/code><\/pre>\n\n- Save the file.<\/li>\n
- Open a terminal or command prompt and navigate to your project directory.<\/li>\n
- Start the Express.js server by running the following command:<\/li>\n<\/ol>\n
node server.js\n<\/code><\/pre>\n\n- You should see the following message in the terminal: “Server running at http:\/\/localhost:3000”.<\/li>\n<\/ol>\n
Connecting to the MySQL Database<\/h2>\n\n- Open
server.js<\/code> in a text editor.<\/li>\n- Include the following code at the top of the file to import the
mysql<\/code> module:<\/li>\n<\/ol>\nconst mysql = require('mysql');\n<\/code><\/pre>\n\n- Add the following code after the server initialization code to connect to the MySQL database:<\/li>\n<\/ol>\n
const connection = mysql.createConnection({\n host: 'localhost',\n user: 'root',\n password: 'root',\n database: 'mydatabase',\n});\n\nconnection.connect((err) => {\n if (err) throw err;\n console.log('Connected to the MySQL database');\n});\n<\/code><\/pre>\n\n- Save the file.<\/li>\n
- Restart the server by stopping it in the terminal (press
Ctrl + C<\/code>) and running the node server.js<\/code> command again.<\/li>\n- You should see the following message in the terminal: “Connected to the MySQL database”.<\/li>\n<\/ol>\n
Creating the Database Table<\/h2>\n\n- Open MySQL Workbench and connect to your local MySQL Server.<\/li>\n
- In the Navigator panel, select the database you created earlier.<\/li>\n
- Right-click on the database and select “Create Table…”.<\/li>\n
- Enter a name for the table (e.g., “users”) and click “Apply”.<\/li>\n
- Click on the “+ Add” button to add columns to the table.<\/li>\n
- Enter column names and select data types for each column.<\/li>\n
- Click “Apply” to create the table.<\/li>\n<\/ol>\n
Performing CRUD Operations<\/h2>\n
Next, we will show how to perform CRUD (Create, Read, Update, Delete) operations on the database using Node.js and Express.js.<\/p>\n
Reading Data from the Database<\/h3>\n\n- Open
server.js<\/code> in a text editor.<\/li>\n- Add the following code after the connection code to define a route for retrieving data from the database:<\/li>\n<\/ol>\n
app.get('\/users', (req, res) => {\n const sql = 'SELECT * FROM users';\n\n connection.query(sql, (err, results) => {\n if (err) throw err;\n res.send(results);\n });\n});\n<\/code><\/pre>\n\n- Save the file.<\/li>\n
- Restart the server.<\/li>\n
- Open a web browser and navigate to “http:\/\/localhost:3000\/users”.<\/li>\n
- You should see an array of records from the “users” table in the JSON format.<\/li>\n<\/ol>\n
Creating Data in the Database<\/h3>\n\n- Open
server.js<\/code> in a text editor.<\/li>\n- Add the following code after the reading data code to define a route for creating data in the database:<\/li>\n<\/ol>\n
app.post('\/users', (req, res) => {\n const { name, email } = req.body;\n const sql = `INSERT INTO users (name, email) VALUES ('${name}', '${email}')`;\n\n connection.query(sql, (err) => {\n if (err) throw err;\n res.send('User created successfully');\n });\n});\n<\/code><\/pre>\n\n- Save the file.<\/li>\n
- Restart the server.<\/li>\n
- 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.<\/li>\n
- You should receive a response with the message “User created successfully”.<\/li>\n<\/ol>\n
Updating Data in the Database<\/h3>\n\n- Open
server.js<\/code> in a text editor.<\/li>\n- Add the following code after the creating data code to define a route for updating data in the database:<\/li>\n<\/ol>\n
app.put('\/users\/:id', (req, res) => {\n const { id } = req.params;\n const { name, email } = req.body;\n const sql = `UPDATE users SET name = '${name}', email = '${email}' WHERE id = ${id}`;\n\n connection.query(sql, (err) => {\n if (err) throw err;\n res.send('User updated successfully');\n });\n});\n<\/code><\/pre>\n\n- Save the file.<\/li>\n
- Restart the server.<\/li>\n
- 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.<\/li>\n
- You should receive a response with the message “User updated successfully”.<\/li>\n<\/ol>\n
Deleting Data from the Database<\/h3>\n\n- Open
server.js<\/code> in a text editor.<\/li>\n- Add the following code after the updating data code to define a route for deleting data from the database:<\/li>\n<\/ol>\n
app.delete('\/users\/:id', (req, res) => {\n const { id } = req.params;\n const sql = `DELETE FROM users WHERE id = ${id}`;\n\n connection.query(sql, (err) => {\n if (err) throw err;\n res.send('User deleted successfully');\n });\n});\n<\/code><\/pre>\n\n- Save the file.<\/li>\n
- Restart the server.<\/li>\n
- Use a web API testing tool to send a DELETE request to “http:\/\/localhost:3000\/users\/:id” (replace “:id” with the actual user ID).<\/li>\n
- You should receive a response with the message “User deleted successfully”.<\/li>\n<\/ol>\n
Displaying Database Data in a Web Application<\/h2>\n
Now, let’s display the data from the MySQL database in a web application using Express.js and EJS (Embedded JavaScript).<\/p>\n
\n- Install EJS by running the following command in your project directory:<\/li>\n<\/ol>\n
npm install ejs\n<\/code><\/pre>\n\n- Create a new directory called “views” in your project directory.<\/li>\n
- Create a new file called “index.ejs” in the “views” directory.<\/li>\n
- Open “index.ejs” in a text editor and add the following code:<\/li>\n<\/ol>\n
<!DOCTYPE html>\n<html>\n<head>\n <title>Users<\/title>\n<\/head>\n<body>\n <h1>Users<\/h1>\n <ul>\n <% users.forEach(function(user) { %>\n <li><%= user.name %> (<%= user.email %>)<\/li>\n <% }); %>\n <\/ul>\n<\/body>\n<\/html>\n<\/code><\/pre>\n