Work done for Centrale Lyon, MOD 4.6 Database system
Airline Company Website is up and running at http://35.180.199.50/, if the app is not running, please contact me (via Messenger, mail, or pull request).
The project is build with the React library.
It is a simple-page application with state variable. That means that, once your on the page, you never reload, or load, another page on the same domain, everything is self contained.
When you click a link (or, more precisely, what looks like a link), you change the state of the app (hold in the appState variable).
src/App.js
// ...
<>
<Header
isConnected={user.isConnected}
isAdmin={user.isAdmin}
homeState={() => changeAppState('home')}
signInState={() => changeAppState('signin')}
profileState={() => changeAppState('profile')}
adminState={() => changeAppState('admin')}
/>
{appState === 'home' && <Home flightsState={() => changeAppState('flights')}/>}
{appState === 'flights' && <Flights checkoutState={() => changeAppState('checkout')}/>}
{appState === 'checkout' && <Checkout completionState={() => changeAppState('completion')}/>}
{appState === 'completion' && <Completion homeState={() => changeAppState('home')}/>}
{appState === 'signin' && <SignIn previousState={() => changeAppState(previousAppState)}/>}
{appState === 'profile' && <Profile previousState={() => changeAppState(previousAppState)} checkoutState={() => changeAppState('checkout')}/>}
{appState === 'admin' && <Admin/>}
<Footer />
</>
// ...The application use an external API to retrieve and edit information in the database.
The main advandage is that this API can be distributed on any platform, not related to the platform that distrubutes the application.
Here, for cost efficiency reason, both the API and the application are served by the same server, but this hasn't to be the case. Indeed, for a more secure and reliable application, it would have been more appropriate to distribute the application with Serverless (AWS Lambda) platform and to distribute the API with either a instance connected to a SQL Database or another Serverless environement connected to a SQL environement (AWS RDS).
One of the exemple of the numerous call made to the API is the call made to retrieve the available flights. This call is made in src/hoooks/useFlights.js.
// ...
const url = (fromAirport, toAirport, date) => (
`${apiendpoint}/flights/airports/${fromAirport}-${toAirport}/date/${date.valueOf()}`
);
const fetchAPI = async(fromAirport, toAirport, date) => {
const response = await fetch(url(fromAirport, toAirport, date));
return await response.json();
};
// ...
function loadFlights() {
// Fetch API
let isMounted = true;
fetchAPI(fromAirport, toAirport, date).then(responseJSON => {
if (!isMounted) {
return;
}
const data = {}
responseJSON.forEach(flight => {
data[flight.id] = {
id: flight.id,
from: flight.from,
to: flight.to,
departure: new Date(flight.departure),
arrival: new Date(flight.arrival),
price: flight.price,
};
});
setFlights(data);
});
return () => {
isMounted = false;
};
}
// ...Here, in development, API is reached on something like http://127.0.0.1/flights/airports/10-13 with 10 and 13 the id of the airports chosen by the user.
Have a look at src/hooks/useAirports.js to see how the airports are retrieved.
Authentification is handle in src/hooks/useUser.js signIn function.
async function signIn(email, password) {
// Hash password
const passwordHash = createHash('sha256').update(password).digest('hex');
// Ask for token
const responseJSON = await fetch(apiendpoint + '/signin', {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({
email: email,
passwordHash: passwordHash,
}),
}).then(response => response.json());
if (responseJSON && responseJSON.status === 'connected') {
user.isConnected = true;
user.token = responseJSON.token;
user.isAdmin = responseJSON.isAdmin;
user.email = email;
user.password = password;
setIsConnected(user.isConnected);
} else {
signOut()
}
}Frontend request signin to the backend by sending the email and the hash of the password to the dedicated endpoint.
The password is never sent over the network, this would be a security leak. A even more secure approach would have implied salt and pepper before hashing.
If backend recognizes the user, it send a status flag to connected. So, frontend can hold in a local variable the current status of the user, and most importantly, its JWT Token (user.token).
JWT Token is a key sent by the server to authenticate the user in subsequent request. Frontend will send this token along side other information to be able to perform operations related to a given account, or operations that needs special rights (admin status needed to update the database).
The admin panel let user authenticated as admin to create, update, and delete objects in the database. This implies that the user has the appropriate JWT Token to perform the action.
The code source for the admin panel is located at src/components/admin/.
There are three forms, one to create objects (AdminCreateForm at src/components/admin/admin-create-form.js), on to update existing objects (AdminUpdateForm at src/components/admin/admin-update-form.js), and one to delete objects AdminDeleteForm at src/components/admin/admin-delete-form.js.
These forms are created thanks to a JSON template databaseSchema at src/components/admin/database-schema.js.
{
...,
'name-of-an-object': {
'fields': [
...,
['name-of-a-field', 'type-of-this-field'],
['name-of-a-external key field', '$name-of-this-external-key-object$field-to-display-a.field-to-display-b.etc.']
...,
]
}
...,
}This allow you to update the UI, and the database schema, withtout having to change the code.
Backend is a simple Express server.
In development, the server is used purely as an API. In production, the root endpoint is reserved to serve the application, the rest remains the API.
MySQL Community Server must be up and running for the API to work.
Most of the code needed to run the server is at backend/mysql-server/app.js.
The folder backend/mysql-server/commands contains useful commands to create and delete the database. It should be use only at the beginning and at the end of the life of the project.
The package mysql is used to connect to the database running instance.
// Connection to SQL Database
const mysql = require('mysql');
const {connect, end, query} = require('./helpers/mysql-helpers');
const { env } = require('process');
const connection = mysql.createConnection({
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
});
connect(connection);Three helpers are defined in backend/mysql-server/helpers/mysql-helpers.js to transform callback functions into async functions.
These are useful to avoid sending result before getting result in asynchronous operations. Indeed,
expressusemethod need a return statement that cannot be completed with acallback(or with intrigated workaround).
The information to connect to the database is stored in backend/mysql-server/.env. This file is never sent to the GitHub Repository and need to be created when cloning the repository (it handles sensitive information).
All the endpoint paths that only involve to retrieve information used the GET method.
For exemple, the endpoint to retrieve the flights corresponding to a given date and a given correspondance is /flights/airports/:from-:to/date/:date (word starting with : are parameters, see Express documentation).
app.get('/flights/airports/:from-:to/date/:date', async (req, res) => {
// ...
// Read parameters and check that everything is correct
// ...
// Request to database
console.log(flight.from, flight.to)
const date = new Date(parseInt(dateInt));
const sqlQuery = `
SELECT departure.date, flight.departure_time, flight.arrival_time, a_departure.name as name_departure, a_arrival.name as name_arrival
FROM departure
LEFT JOIN flight ON departure.flight_id = flight.id
LEFT JOIN connection ON flight.connection_id = connection.id
LEFT JOIN airport a_departure ON connection.departure_airport_id = a_departure.id
LEFT JOIN airport a_arrival ON connection.arrival_airport_id = a_arrival.id
WHERE DATE(departure.date) = ? AND a_departure.id = ? AND a_arrival.id = ?
ORDER BY departure.date ASC
`;
const result = await query(connection, sqlQuery, [date.toJSON().slice(0, 10), flight.from, flight.to])
console.log('Result Search Flights');
console.log(result);
// Map the result to correct type
// ...
// DEV, for test only
// Add a fake response to always have something to click on
// ...
res.status(200);
res.json(data);
return;
});Each endpoint that modified the database is a POST method endpoint. Before processing the request, the server check the JWT Token send in the request. If the token doesn't have the admin flag, it rejects the request.
The JWT Token is generated by the server in the /signin endpoint, thanks to the jsonwebtoken package.
app.post('/signin', async (req, res) => {
const {email, passwordHash} = req.body;
// Check if the user exist
const sqlQuery = `
SELECT admin
FROM user
WHERE email = ? AND password_hash = ?
`;
const result = await query(connection, sqlQuery, [email, passwordHash]);
console.log('Result Search User');
// If it does, create a token
if (result.length > 0) {
const user = result[0];
const token = jwt.sign({ role: user.admin ? 'admin' : '' }, process.env.HASH_SECRET);
res.json({status:'connected', token: token, isAdmin: user.admin ? true : false});
} else { // If not, inform the client
res.json({status:'not connected'});
}
});process.env.HASH_SECRET is stored in backend/mysql-server/.env. This file is never sent to GitHub Repository because it handles sensitive data. Without this secret, the client cannot modify its token.
By default, jsonwebtoken use SHA256 encryption which is secure enough for our purpose (you won't succeed to modify your token with bruteforce only).
- Clone the Github repository and install dependencies
git clone https://github.com/AdrKacz/airline-company-app.git
cd airline-company-app-
Install MySQL
-
Follow the instruction to install MySQL, and start your database (it should already be done by default)
-
Install MySQL Workbench
-
Open MySQL Workbench and connect to your running database
-
Alter the root user to be able to connect to it in code
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password_here';
FLUSH PRIVILEGES;- Copy your username and password in a
backend/mysql-server/.envfile
DB_HOST=localhost
DB_USER=<your root user>
DB_PASSWORD=<your root password>
DB_NAME=airlineapp
HASH_SECRET=<your hash secret, choose anything you want>
MODE=DEV
- Go to MySQL Workbench and create your database
CREATE DATABASE airlineapp;- Go to your terminal and initiate your database
cd backend/mysql-server
npm install
node commands/initiate.js- Go to MySQL Workbench and create your first admin user
USE airlineapp;
INSERT INTO user (email, password_hash, admin)
VALUES (<admin email>, <admin hashed password>, true);To find your hashed password, go to SHA256 Online, enter your password in Input, and copy the result from Output.
- Update the API endpoint to your local one in
src/constants.js.
// Only choose one
// DEV
exports.apiendpoint = 'http://127.0.0.1:8080';
// PROD
// exports.apiendpoint = <production endpoint, don't care for now>;- Run both your MySQL API server and your React server
yarn install
yarn startAnd in another terminal window :
cd backend/mysql-server
npm install
npm start-
Go to http://localhost:3000, and edit your code in
src/(react, frontend) ormysql-server(express api, backend) to see live updates. -
On your browser, hit
Ctrl-Rto reload page and reset the state of your app. -
On your terminals, hit
Ctrl-Cto stop your local servers.
- Go to AWS EC2 and create a new instance.
- Select Ubuntu, last version
- Keep all other setting to default
- In the firewall section, update SSH to allow only My IP, and add HTTP and allow anywhere
- Review and launch
- Connect to your instance using the Connect button and its instructions
- Install MySQL on your instance
sudo apt update
sudo apt install mysql-server- Check the status of your running database
sudo systemctl status mysqlYou should see something like the following :
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since ... ....-..-.. ..:..:.. UTC; 1min 38s ago
Main PID: 2390 (mysqld)
Status: "Server is operational"
Tasks: 37 (limit: 1154)
Memory: 350.3M
CGroup: /system.slice/mysql.service
└─2390 /usr/sbin/mysqld
... .. ..:..:.. ip-...-...-...-... systemd[1]: Starting MySQL Community Server...
... .. ..:..:.. ip-...-...-...-... systemd[1]: Started MySQL Community Server.- Log in your database and alter the root user, choose your own password and remember it
sudo mysqlALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password_here';
FLUSH PRIVILEGES;
exit- Login with your updated credentials and create your database
sudo mysql -u root -pCREATE DATABASE airlineapp;
exit- Clone this Git Repository
git clone https://github.com/AdrKacz/airline-company-app.git
cd airline-company-app- Install NPM
sudo apt install npm
- Create
backend/mysql-server/.env
nano backend/mysql-server/.envDB_HOST=localhost
DB_USER=<your root user>
DB_PASSWORD=<your root password>
DB_NAME=airlineapp
HASH_SECRET=<your hash secret, choose anything you want>
MODE=PROD
Exemple
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=password
DB_NAME=airlineapp
HASH_SECRET=secret
- Initiate your database
cd backend/mysql-server
npm install
node commands/initiate.js- Create admin user
sudo mysql -u root -pUSE airlineapp;
INSERT INTO user (email, password_hash, admin)
VALUES (<admin email>, <admin hashed password>, true);Exemples
USE airlineapp;
INSERT INTO user (email, password_hash, admin)
VALUES ('admin@admin', '8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918', true);
exitTo find your hashed password, go to SHA256 Online, enter your password in Input, and copy the result from Output.
- Create your NGINX server and run it
sudo wget http://nginx.org/keys/nginx_signing.key
sudo apt-key add nginx_signing.key
cd /etc/apt
Add the following lines to the end of sources.list
deb http://nginx.org/packages/ubuntu focal nginx
deb-src http://nginx.org/packages/ubuntu focal nginx
sudo apt-get update
sudo apt-get install nginx
sudo systemctl start nginx.service
sudo systemctl status nginx.service
You should see something like:
● nginx.service - nginx - high performance web server
Loaded: loaded (/lib/systemd/system/nginx.service; enabled; vendor preset: enabled)
Active: active (running) since ... ....-..-.. ..:..:.. UTC; 5s ago
Docs: https://nginx.org/en/docs/
Process: 13785 ExecStart=/usr/sbin/nginx -c /etc/nginx/nginx.conf (code=exited, status=0/SUCCESS)
Main PID: 13786 (nginx)
Tasks: 2 (limit: 1154)
Memory: 1.7M
CGroup: /system.slice/nginx.service
├─13786 nginx: master process /usr/sbin/nginx -c /etc/nginx/nginx.conf
└─13787 nginx: worker process
... .. ..:..:.. ip-...-...-...-... systemd[1]: Starting nginx - high performance web server...
... .. ..:..:.. ip-...-...-...-... systemd[1]: Started nginx - high performance web server.
-
Go to AWS EC2, select your instance and copy its Public IP and in your browser go to
http://<your instance ip>/airline. You should see a webpage with Welcome to nginx! on it. -
Update
airline-company-app/src/constants.jsto update your instance ip
cd && nano airline-company-app/src/constants.js// Only choose one
// DEV
// exports.apiendpoint = 'http://127.0.0.1:8080';
// PROD
exports.apiendpoint = 'http://<your ip address>';
Now build the source to take into account your modification.
cd && cd airline-company-app
# npm install could take a long time if your instance doesn't have a lot of memory
# alternatively, you can execute yarn build-server on your dev environment on your machine
# and then scp (copy via ssh) the build repo in your cloud instance
npm install
npm run build-server- Create a
.conffile for NGINX
cd /etc/nginx/conf.d
sudo mv default.conf default.conf.bak
sudo nano airline.confAnd type the following
server {
location / {
proxy_pass http://localhost:8080;
}
}
sudo nginx -s reload- Install tmux and run your server
cd
sudo apt install tmuxInto tmux, use
Ctrl-b "to split window,Ctrl-b Arrowto change window, andCtrl-b dto detach your session. Then, typetmux attach-session -t 0to recover your session.
Open tmux and run the server.
cd
cd airline-company-app/
tmux # this will open tmux
cd backend/mysql-server
npm install
npm run prodHit Ctrl-b d to detach your session.
Your site is up and running at http://<your instance ip>/airline.
Exit your cloud instance.
exit # (or Ctrl-d)Explore the database schema in DrawSQL Airline Company App.
This project was bootstrapped with Create React App.
In the project directory, you can run:
Runs the app in the development mode.
Open http://localhost:3000 to view it in the browser.
The page will reload if you make edits.
You will also see any lint errors in the console.
Launches the test runner in the interactive watch mode.
See the section about running tests for more information.
Builds the app for production to the build folder.
It correctly bundles React in production mode and optimizes the build for the best performance.
The build is minified and the filenames include the hashes.
Your app is ready to be deployed!
See the section about deployment for more information.
Note: this is a one-way operation. Once you eject, you can’t go back!
If you aren’t satisfied with the build tool and configuration choices, you can eject at any time. This command will remove the single build dependency from your project.
Instead, it will copy all the configuration files and the transitive dependencies (webpack, Babel, ESLint, etc) right into your project so you have full control over them. All of the commands except eject will still work, but they will point to the copied scripts so you can tweak them. At this point you’re on your own.
You don’t have to ever use eject. The curated feature set is suitable for small and middle deployments, and you shouldn’t feel obligated to use this feature. However we understand that this tool wouldn’t be useful if you couldn’t customize it when you are ready for it.
You can learn more in the Create React App documentation.
To learn React, check out the React documentation.
This section has moved here: https://facebook.github.io/create-react-app/docs/code-splitting
This section has moved here: https://facebook.github.io/create-react-app/docs/analyzing-the-bundle-size
This section has moved here: https://facebook.github.io/create-react-app/docs/making-a-progressive-web-app
This section has moved here: https://facebook.github.io/create-react-app/docs/advanced-configuration
This section has moved here: https://facebook.github.io/create-react-app/docs/deployment
This section has moved here: https://facebook.github.io/create-react-app/docs/troubleshooting#npm-run-build-fails-to-minify