Database Enhancements - aneeshp4/epl-database GitHub Wiki
The goal of our enhancement was to build a frontend for the user to interact with the database. We broke this goal down into 3 main components:
- AWS: We wanted to use AWS to host an EC2 instance (for our Rest API) and an RDS instance (for our database).
- Rest API/Backend: The purpose of the backend was so abstract away any SQL from the frontend, so that no end user (person using the frontend) could see how we interact with the database, but are still able to get data from the database.
- Frontend: Have the user actually be able to interact with the data in our database.
The main features we wanted in our end product were to give users the ability to filter matches played by a team, a specific month, or both, and to be able to look at detailed match statistics and odds of a chosen match.
Using the AWS Learner Lab for AWS, we first set up an EC2 instance:
- The EC2 instance we used was a t2.micro running a RedHat Linux OS.
- We created an Elastic IP that we associated with this EC2 instance so that we didn't have to connect to a different IP address every time we started up the EC2 instance.
- We added an inbound rule in the launch-wizard security group to allow connections from all IPs using the port 3000, as we chose to run our backend on port 3000.
We then set up our RDS instance:
- Our RDS instance was a db.t3.micro instance with the MySQL community engine.
- When creating this instance, we made sure to connect it to our EC2 instance, which automatically set up security inbound rules on the RDS instance and outbound rules on the EC2 instance.
After setting up our RDS instance, we ported over our database, which was stored locally on our laptops, into RDS:
- First we created a dump file for the database using MySQL Workbench.
- Then we connected MySQL Workbench to the RDS instance.
- Finally we opened the dump file (stored locally) on workbench and ran it on the RDS instance to build the database there.
To test if we could access the database from the EC2 instance, we did the following in the terminal:
- SSH into the EC2 instance.
- We installed mysql-server and started mysqld.
- Finally we connected to mySQL on the RDS instance using
mysql -h <rds-endpoint> -u <username> -p - After connecting to the RDS instance, we used a couple SELECT statements to make sure that we were interacting with our database on the RDS instance.
We spent about 4 hours on Saturday, August 19 setting both of these up.
Our API is run, and was developed, on the EC2 instance.
In order to build the Rest API, we had to do a lot of googling and learning to understand how a Rest API works, as neither of us had experience with building one before. We found out we needed split up the server into models, controllers, and routes. Our documentation of the backend and each of its parts can be found here.
We spent about 2 hours on Sunday, August 20 on actually learning/understanding the different parts of the server and then implementing and testing db.js, which interacts with the RDS instance via a SQL query to get data from our database.
We then spent another 3 hours on Thursday, August 24 building the models, controllers, and routes. We tested our API after completing it using Postman.
The frontend was built using React, and was developed locally on our laptops.
We had a little bit of frontend experience, and more specifically React experience, before this project so the learning parts of this were less time consuming. That being said, we still did have to do a good amount of googling when it came to all of Reacts quirks with state and useEffect and how it renders components. The documentation of the frontend and each of its parts can be found here.
We spent about 6 to 7 hours on Sunday, August 27 building all of the React components and styling the project with CSS.
- Backend:
- AWS:
- Frontend: