- 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
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=seriussHow 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 paperSee the database name there (after opening workbench)
MYSQL_DATABASE=dbHow 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 poolImport 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', (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”.
localhost:8000/api
Output:
