How to make Backend ready for GET?

Fetching data from MySQL and show on website

How to connect MySQL with Express, Create route for GET/POST Request and Query to store/retrieve data?

setup a basic express app —–> connect mysql with express ——> create a route “/anything” for GET Request ——-> write the MySQL query for retrieving data from MySQL

BACKEND CREATION

create mysql connection (Make sure u have MySQL table to store data)
then create a route for recieving data
then start the server

STORING DATA INSIDE BACKEND

now put the Axios POST request to retrieve data

Contents

Directory Structure
Setup an Express App
Create MySQL CONNECTION POOL ( //pool.js )
- OR DO IT TRADITIONALLY
Create a Route (GET – for Retreiving data)
Now Make a GET Request
- Note: Add console logging to log the incoming request, the SQL query, values, and the result or error from the database interaction.
Send GET Request for a Single Product

Directory Structure

Setup an Express App

Install everything

Create a Backend Folder in VS Code

Then open Backend Folder with VS Code

If using Main Folder, then do ‘cd BACKEND’

Now

npm i express mysql2 dotenv nodemon

Create ENV FILE

create a “.env” file (npm i dotenv if you haven’t)

type these:

MYSQL_HOST=localhost

MYSQL_USER=heyy

MYSQL_PASSWORD=okayy

MYSQL_DATABASE=seriuss

How to find these, right?

Open MySQL WorkBench, open your connection saying root and localhost:3306

So now, you already know:

MYSQL_HOST=localhost

MYSQL_USER=root

MYSQL_PASSWORD= password of MySQL Workbench you written in diary/any paper

See the database name there (after opening workbench)

MYSQL_DATABASE=db

How to use them?

process.env.MYSQL_HOST

process.env.MYSQL_USER

process.env.MYSQL_PASSWORD

process.env.MYSQL_DATABASE

console.log(process.env.MYSQL_HOST)

console.log( ` Your value for Host is ${process.env.MYSQL_HOST} ` )

When writing inside commands: DONT WRITE ” “

for ex:

host: process.env.MYSQL_HOST, // No quotes here

Very important thing (import it at the top):

require(“dotenv”).config()

Now Create An Express App

first create this file called “index.js”

// index.js

require("dotenv").config()
const express=require('express')
const app =express()

app.get('/', (req, res)=>{
    res.send('Backend successfully runned')
})

const PORT = 8000

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

START THE SERVER

Run using nodemon: npx nodemon index.js
or simply: node index.js

output 1: in VS Code Terminal

output 2: inside postman

Now Open localhost:8000 in browser

output:

# Create MySQL Table

open mysql workbench

Create a new schema
Create a table
Create fields ………… (fields, datatype, nn, pk, ai) (nn-not null, ai-auto increase)
Right click the table and select all rows
Fill the data

Create MySQL CONNECTION POOL ( //pool.js )

https://expressjs.com/en/guide/database-integration.html#mysql

// pool.js

const mysql = require('mysql2');

const pool = mysql.createPool({
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DATABASE,
  waitForConnections: true,
  connectionLimit: 10, // Maximum number of connections in the pool
  queueLimit: 0,       // 0 means no limit for the queue
});

console.log('MySQL Pool created successfully.'); // Check if the pool is created successfully


// Test a connection from the pool
pool.getConnection((err, connection) => {
  if (err) {
    console.error('Testing: Error connecting to MySQL Pool:', err);
    return;
  }
  console.log('Testing: Successfully connected to MySQL Pool');

  // Release the connection back to the pool
  connection.release();
});

module.exports = pool; // Export the pool

Import the Pool

// index.js

const pool = require('./pool.js')

Output:

OR DO IT TRADITIONALLY

const mysql=require('mysql2')

const db = mysql.createConnection({

  host: 'hostname',

  user: 'user_name',

  password: 'password',

  database: 'database_name'

})

db.connect((err) => {

 if (err) {

   console.error('Error connecting to MySQL: ' + err.stack);

   return;

 }

 console.log('Connected to MySQL as ID ' + db.threadId);

});

db.end()

Use Pool in index.js (Main File)

You probably don’t need it

In your index.js (or app.js), also import the pool from db.js if you need it in the main file. If you don’t need to use the pool in index.js, you don’t need to import it there.

Create a Route (GET – for Retreiving data)

create a file in

├── /routes

│ └── router.js

How to use Pool in any Router? Just import it : )

const pool = require('./../pool.js')

use as middleware in index.js

app.use(express.json())

// router.js

const express = require('express');
const router = express.Router(); // Use express.Router() to create a new router
const pool = require('./../pool.js'); // Import the pool to use it

// Make a GET request to fetch data from the product table
router.get('/api/all-products', (req, res) => {
    console.log('Received a GET request on /api route'); // Log request

    const q = 'SELECT * FROM table_name';
    
    // Log query being executed
    console.log('Executing query:', q);

    pool.query(q, (err, data) => {
        if (err) {
            console.error('Error occurred while querying the database:', err); // Log the error with details
            return res.status(500).json({ message: 'Database query failed', error: err });
        }

        console.log('Query executed successfully. Data:', data); // Log the fetched data
        return res.json(data); // Return the fetched data
    });
});

module.exports = router; // Export the router for use in your main app

Import the route in index.js

// index.js

const router=require('./routes/router.js')

app.use(router)

Now Make a GET Request

Open Postman and then make a get request on the URL of “/api/all-products”.

localhost:8000/api/all-products

Output:

Note: Add console logging to log the incoming request, the SQL query, values, and the result or error from the database interaction.

Send GET Request for a Single Product

const express = require('express');
const router = express.Router(); // Use express.Router() to create a new router
const pool = require('./../pool.js'); // Import the pool to use it


// Make a GET request to fetch data from the product table
router.get('/api/product/:id', (req, res) => {
    
    const productId = req.params.id; // Get the ID from the request URL
    const q = 'SELECT * FROM table_name WHERE id = ?'; // Use a placeholder for dynamic ID

    // Execute the query with the dynamic ID
    console.log('Request on /api/product/:id was sent.');

    pool.query(q, [productId], (err, data) => {
        if (err) {
            console.error('Error occurred while querying the database:', err); // Log the error with details
            return res.status(500).json({ message: 'Database query failed', error: err });
        }

        console.log('Query executed successfully. Data:', data); // Log the fetched data
        return res.json(data); // Return the fetched data
    });
});


module.exports = router; // Export the router for use in your main app