Building a MySQL-Powered Express Application: A Step-by-Step Guide to Integrating MySQL Workbench with Node.js

Hello friend, I trust you’re having a fantastic time, and it’s evident that you’re not just navigating but excelling through your endeavors. Your presence here reinforces that you’re making the most of your journey and enjoying every moment of it

So, I started learning about databases, and at first, it was a bit confusing with all these rows and columns. But as I dug into it, things started to make more sense. In this post, I’ll guide you through setting up, configuring, and managing databases with a Node.js-Express application. I encourage you to read until the end for a comprehensive understanding.

So this tutorial is based on my experience and learnings from my mistakes. So you can say it is based on a true life story 😀

Without wasting much of your time, lets get our hands dirty.

PREQUISITE
i. Have node installed in device.
ii. Have a working MySQL workbench.

1. Create a project: Open your command prompt or Git Bash and paste this command

mkdir SQL_Tut

You can name your project any name of your choice.

2. Next, you’ll need to open the project in your favorite code editor, preferable VSCode.

3. Next, we’ll initialize node in our project. Open your terminal and paste the command

npm init -y

Congratulations you have node running in your project 🎉

4. Next, let’s install the necessary dependencies. Run the following command in the root of your project folder:

npm install express mysql2 nodemon --save

5. You’ll need to create a main entry point file for your application, you can call it ‘index.js’, and populates it with the following code:

const express = require('express')
const app = express()

const port = 5000;



// INSERT INTO THE DATABASE ROUTE HERE




// GET DATA FROM THE DATABASE ROUTE HERE 

  
  // Start the Express server
  app.listen(port, () => {
    console.log(`Server is running at http://localhost:${port}`);
  });

6. Additionally, you’ll create a separate file for MySQL configuration. This file will handle the configuration of the MySQL connection pool.

db.js

// Mysql connection configuration

7. Next, we will be creating our database on MySql workbench, I assume you have MySql workbench installed in your device. If not you can watch this video for how to install workbench.

8. Now that you have workbench running, you can setup a new connection

workbench screen

9. After successfully creating a connection, next you’ll need to create a database, use this SQL query to create a database:

CREATE DATABASE tech_department

This will create a database with name “tech_department”

10. We need to create a column in the database, so paste this SQL query

USE software_company;

CREATE TABLE tech_department (
    Id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(266),
    role VARCHAR(266),
    date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This would create columns inside the database

Now your database is set to store data.

11. Next we’ll need to add our database configuration to the ‘db.js’ file

// db.js file

// Mysql connection configuration

const mysql = require('mysql2/promise');

// create the connection to database
const pool = mysql.createPool({
    host: "localhost",
    user: "root",
    password: "root",
    database: "software_company",
    multipleStatements: true,
    charset: "utf8mb4",
    decimalNumbers: true,
});

pool.getConnection()
  .then(conn => {
    const res = conn.query('SELECT 1');
    conn.release();
    return res;
  }).then(results => {
    console.log('Connected to MySQL DB');
  }).catch(err => {
    console.log(err); 
  });

  
  module.exports = pool

Basically we import the mysql2 module then use it to create a connection to the database, then check if it’s connected, if connected it returns a Promise.

12. Next, we will create routes to store and retrieve data from the database using SQL query. Paste this code inside the “index.js” file

// index.js file 

// INSERT INTO THE DATABASE ROUTE HERE

app.post('/add', async(req, res) => {
  // The data to be stored in the database
    name = "Pflow"
    role = "UI/UX, Product Guy"
  
  try {
    await pool.query(
      'INSERT INTO tech_department (name, role) VALUES (?, ?)',
      [name, role]
    );

    res.status(200).json("user has been added");
    
  } catch (error) {
    console.log('failed')
    res.status(400).json(error.message)
  }
})

If you have successfully added this to your ‘index.js’ file you can run your application with this command on the terminal

nodemon start or node index

14. Since this is a post route, we will be using postman to test it. Open your POSTMAN and hit the POST endpoint:

POST: http://localhost:5000/add

If the operation is successful, your data will be present in the database.

All inserted data

15. Now that we have data, lets perform an SQL operation to get those data. So paste this GET endpoint in the “index.js” file

// index.js

// GET DATA FROM THE DATABASE ROUTE HERE


app.get('/get', async (req, res) => {
  try {
    const [result] = await pool.query(`SELECT * FROM tech_department`)

    res.status(200).json(result);
  } catch (error) {
    console.log('failed')
    res.status(400).json(error.message)    
  }
});

By doing this and hitting the GET endpoint on POSTMAN, you should be able to retrieve all the inserted data from the database.

GET all the data from the database

By performing this basic SQL operations you have successfully inserted data and retrieved data from the database.

It’s worth emphasizing that the provided code demonstrates a basic usage of INSERT and SELECT SQL queries. However, for larger projects, there are more robust SQL commands available. If you’re interested in exploring a wider range of SQL commands for free, you can refer to this link.

If you follow these steps carefully, you should successfully connect an Express application and interact with a MySQL database. For more content on backend development and similar topics, feel free to follow my account for regular updates and valuable insights.

RESOURCES:

For a visual understanding, watch this YouTube video: https://youtu.be/Hej48pi_lOc?si=xJaBG6Wjt5jggPif

Learn, how to connect a Nodejs express app to MongoDB database:
https://medium.com/@chiboy96/84939067a914

Show me some love by ❤💛🧡💙❤
✅liking
💌subscribing
🚶‍♂️following

Thank you 🎉

ASSIGNMENT:
Learn about POSTMAN
https://youtu.be/MFxk5BZulVU?si=7ptdmQljWHiXeqNT