How to make Backend ready for POST ?

Storing data to MySQL from form

Setup a basic express app —–> connect mysql with express (Make sure u have MySQL table to store data) ——> create a route “/anything” for POST Request ——-> write the MySQL query for storing data inside MySQL

Contents

Directory Structure
Setup an Express App
# Create a MySQL Table
Create MySQL DB POOL ( //pool.js )
Create a Route (POST – for Submitting data)
Testing in Postman
- How to create db pool?
- how to write db pool query?

Directory Structure

Create a structure like this

Setup an Express App

Install everything

Create a Backend Folder. Then open the folder with VS Code. (Note: If using Main Folder, then do ‘cd BACKEND’)

run this:

npm i express mysql2 dotenv nodemon

Create ENV FILE

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

type these:

MYSQL_HOST= check your connection in workbench, it will say 'localhost'

MYSQL_USER= check the same connection, it will say 'root'

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

MYSQL_DATABASE= After opening workbench, See the database name there 'db'

How to use these env, read it there. [link of that article]

Now Create An Express App with index.js

first create “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

Now Open localhost:8000 in browser

# Create a 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 DB POOL ( //pool.js )

// 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')

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.

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

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

Output:

Create a Route (POST – for Submitting data)

create a file in

├── /routes

│ └── routerProduct.js

// routerProduct.js

const express = require('express')
const router = express.Router()
const pool = require('./../pool.js') // Import the pool

// POST route to insert product data
router.post('/api/new-product', (req, res) => {
  console.log('Received a POST request on /api/new-product') // Log request

  const {
    displayImage, firstImage, secondImage, thirdImage, fourthImage,
    fifthImage, sixthImage, seventhImage, eighthImage, ninthImage, tenthImage,
    slug, cardTitle, category, brand, soldNumber, actualPrice,
    markedPrice, rating, freeShipping, stockStatus, title,
    description, bigDescription, specification,
  } = req.body // Destructure request body

  console.log('Data received:', req.body) // Log the incoming data

  const query = `
    INSERT INTO table_name (
      displayImage, firstImage, secondImage, thirdImage, fourthImage,
      fifthImage, sixthImage, seventhImage, eighthImage, ninthImage, tenthImage,
      slug, cardTitle, category, brand, soldNumber, actualPrice,
      markedPrice, rating, freeShipping, stockStatus, title,
      description, bigDescription, specification
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `;

  const values = [
    displayImage, firstImage, secondImage, thirdImage, fourthImage,
    fifthImage, sixthImage, seventhImage, eighthImage, ninthImage, tenthImage,
    slug, cardTitle, category, brand, soldNumber, actualPrice,
    markedPrice, rating, freeShipping, stockStatus, title,
    description, bigDescription, specification,
  ];

  pool.query(query, values, (err, results) => {
    if (err) {
      console.error('Error occurred while inserting data:', err); // Log error details
      return res.status(500).json({ message: 'Failed to insert product data', error: err });
    }

    console.log('Product inserted successfully:', results); // Log success message and results
    res.status(201).json({ message: 'Product added successfully', productId: results.insertId });
  });
});

module.exports = router;

// index.js

// Add routes
const routerProduct = require('./routes/routerProduct')
app.use(routerProduct)

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

Why parse JSON through express.json() middleware?

beacuse when axios will send data; it sends data in JSON format

{
  "cardTitle": "Hey what the heck",
  "firstImage": "image2.jpg",
}

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

Testing in Postman

localhost:8000/api/new-product

How to create db pool?

router.post(‘/api/new-product’, (req, res) => {
console.log(‘Received a POST request on /api/new-product’) // Log request

all data are stored in req.body

to get email: req.body.email

res.status(400) //to show status

res.json({ “error” : “email is required…” })

so when there is error, it will show like this:

SpaceUpper