expressjs-mysql-server.md

Creating an Express.js instance to pull data from a MySQL database

Table of Contents

1. The Server Code
2. The Explanation

Server

import express from 'express';
import mysql from 'mysql';

const app = express();
const port = 8900;
const db = mysql.createConnection({
	host: 'localhost',
	user: 'root',
	password: 'yourmysqlpassword',
	database: 'students',
});

app.use(express.json());

app.get('/', (req, res) => {
	res.json('Welcome to the backend!');
});

// tanscripts
app.get('/transcripts', (req, res) => {
	const q = 'SELECT * FROM transcripts';
	db.query(q, (err, data) => {
		if (err) return res.json(err);
		return res.json(data);
	});
});

app.post('/transcripts', (req, res) => {
	const q =
		'INSERT INTO transcripts (`studentid`, `yearid`, `semesterid`, `progid`) VALUES (?)';
	const values = [
		req.body.studentid,
		req.body.yearid,
		req.body.semesterid,
		req.body.progid,
	];

	db.query(q, [values], (err, data) => {
		if (err) return res.json(err);
		return res.json('Transcript added successfully');
	});
});
// End tanscripts

app.listen(port, (req, res) => {
	console.log(`Server connected on http://localhost:${port}`);
});

Explanation

• create a backend folder and in it initialise your nodejs application

npm init -y

• add a file to hold your server code, say index.js or server.js and add express, nodemon and mysql to your setup

npm install express nodemon mysql

• in the package.json file let "type" be "module" if you want to use ES6 and then create in there a script to run nodemon whenever you make any changes

"type": "module",
"scripts": {
	"start": "nodemon index.js"
},

• import express and mysql into your server file and create the server

import express from 'express';
import mysql from 'mysql';

const app = express();
const port = 8900;

app.listen(port, (req, res) => {
	console.log(`Server connected on http://localhost:${port}`);
});

• create your database either manually or with MySQL workbench and add a table or tables if you dont have one already and create a database connection

const db = mysql.createConnection({
	host: 'localhost',
	user: 'root',
	password: 'yourmysqlpassword',
	database: 'students',
});

• create a root route and pull back a response from the server to know that your backend is working fine

app.get('/', (req, res) => {
	res.json('Welcome to the backend!');
});

• create a route to pull your data from the database

app.get('/transcripts', (req, res) => {
	const q = 'SELECT * FROM transcripts';
	db.query(q, (err, data) => {
		if (err) return res.json(err);
		return res.json(data);
	});
});

• create another route to add data to your body and send it to the database.

app.post('/transcripts', (req, res) => {
	const q =
		'INSERT INTO transcripts (`studentid`, `yearid`, `semesterid`, `progid`) VALUES (?)';
	const values = [
		req.body.studentid,
		req.body.yearid,
		req.body.semesterid,
		req.body.progid,
	];

	db.query(q, [values], (err, data) => {
		if (err) return res.json(err);
		return res.json('Transcript added successfully');
	});
});

• Remember to add a middleware to allow express to take direct json data from the client.

app.use(express.json());

Good luck ;)