PROJECT 2: GameShare™
Objective: Build a full-stack CRUD app.
Tools: PostgreSQL, Express, EJS, bcrypt
Loading GameShare™...
For this project, we had to make a CRUD app, which stands for CREATE-READ-UPDATE-DESTROY, referring to the actions you can take with a database. In short, this meant making a website that gave users the ability to do all four actions to the data in a connected database. Being a gamer, I decided to make something related to games. After some thinking, GameShare™ was brought into existence: a website where people could share what games they played and find other people who share their interests. Kinda like a gaming rip-off of Tinder®.
With my idea set in stone, I now had to make a database for all the information I needed to store. PostgreSQL was the tool that we were given for this purpose. It’s a relational database management system, which just means we just stuff all our data into tables with columns of attributes and rows and rows of data all following the same structure.
For my app, I decided that I needed 3 tables:
- users: for user information,
- games: for game information,
- and usergamelist: for each user's list of games
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username TEXT NOT NULL,
email TEXT NOT NULL,
password_hashed TEXT NOT NULL,
profile_image TEXT,
about_me TEXT
);
CREATE TABLE games (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
description TEXT,
image_url TEXT,
player_count INTEGER
);
CREATE TABLE usergamelist (
id SERIAL PRIMARY KEY,
user_id INTEGER,
game_id INTEGER,
remark TEXT,
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
FOREIGN KEY (game_id) REFERENCES games (id) ON DELETE CASCADE
);
Working with Databases
With the database ready, I now had to seed it with dummy data. Using JavaScript, I connected to the database and made a request to add new data to it. This is an example of the C(reating) part of CRUD.
const db = require('./index.js')
const bcrypt = require('bcrypt')
const saltRounds = 10
const username = 'CobaltDingus'
const dummyEmail = 'cobalt@gmail.com'
const plainTextPassword = 'gaming'
const sql = `
INSERT INTO users (username, email, password_hashed) VALUES ($1, $2, $3);
`
function createSingleUser() {
const name = username
const email = dummyEmail
const password = plainTextPassword
bcrypt.genSalt(saltRounds, (err, salt) => {
bcrypt.hash(password, salt, function(err, hash) {
db.query(sql, [name, email, hash], (err, result) => {
if (err) console.log(err);
})
});
});
}
createSingleUser()
|
-> Hey, it's your local Hackerman here to explain bcrypt. This is a module containing functions to secure user passwords and make my job harder >:(
-> The way it works is by first generating salt, a string of random numbers that will be attached to your password. The salted password is then run through a hashing algorithm to turn it into an abomination of random characters that I will have to spend years trying to reverse engineer.
-> This hashed password is what is sent to the database along with the user's name and email. The trick then is that a password passed through all the same bcrypt functions will always return the same hashed password. This is how your plain text password is checked upon logging into the website.
-> And that's it! Hackerman out! Just ignore the fact that I stole all your credit card info while you were reading this.
|
...alright, I'm back. My service provider said they had to cut me off because I didn't pay my bill or something? Anyway, back to coding. In the pile of code above, there were 3 steps to communicating with PostgreSQL:
const db = require('./index.js')
const sql = `INSERT INTO users (username, email, password_hashed) VALUES ($1, $2, $3);`
db.query(sql, [name, email, hash], (err, result) => {
if (err) console.log(err);
})
1. Connecting to the database. The index file contained all the necessary code to import the database as an object to execute functions.
2. Coming up with instructions. SQL statements are, by design, meant to be somewhat understandable to non-coders. This one basically says "I want to insert some data in the users table, under these three columns, using three specific values for each respectively (marked as placeholders for now)."
3. Making the actual query. Using the .query method
on the db variable, the sql statement is
sent the database, along with all the other info that's meant to
fill in the placeholders (i.e. the user's name, email, and hashed
password that was acquired earlier).
I then repeated this process with a loop to seed dummy data for 10 users to simulate people caring about and using my website. With all the backend set up, I was ready to start with the actual website.
Coding GameShare™
For the website’s features, I started with the essential before considering anything else. These would be the features that carried out the necessary CRUD actions as per our objective. To do anything with my database however, I needed 2 new tools we had picked up: Express and EJS (and Express-EJS-Layouts to integrate them).
router.get('/games/:id', (req, res) => {
const gameId = req.params.id
const sqlGameDetails = `SELECT * FROM games WHERE id = $1;`
db.query(sqlGameDetails, [gameId], (err, result) => {
if (err) console.log(err);
const game = result.rows[0]
const sqlGameUsers = `
SELECT *
FROM usergamelist
JOIN users
ON (usergamelist.user_id = users.id)
WHERE game_id = $1;
`
db.query(sqlGameUsers, [gameId], (err, result) => {
if (err) console.log(err);
const gameUsers = result.rows
res.render('show', { game: game, gameUsers: gameUsers })
})
})
})
Express handled all the server side stuff like routing.
Requests from the client would go through here if they visited any URL with a pattern like gameshare.com/games/:id.
The ":id" in this case could be any anything. Whatever it is, it would be taken and put into an SQL statement to see if a game of that ID existed already in the database and if so, retrieve whatever data is being asked for.
As you might see, I actually have two statements: one to get the game details and one to get a list of the users who play said game.
Only after all this data is retrieved does the router render a web page for the user and for that, we now turn to EJS.
EJS essentially allows you to run JavaScript inside a HTML file. That alone is nice but with Express-EJS-Layouts, you can make templates for web pages filled with dynamically generated content.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>GameShare</title>
<link rel="stylesheet" href="/style.css">
<body>
<header id="top">
<%- include('_header') %>
</header>
<div class="wrapper">
<main>
<%- body %>
</main>
</div>
</body>
</html>
This is the base layout for every page in GameShare™ meaning that it applies for every page no matter what content the page actually has. You can see this base layout also includes a header (which is a custom layout for just the header of the page) and a content body which depends on what page layout I want to render. This for example is all I need to write for each game page:
<h1 class="header"><%= game.title %></h1>
<img class="game-image" src="<%= game.image_url %>" alt="Image of game">
<div class="paragraph">
<p>Description: <%= game.description %></p>
</div>
<section>
<div class="user-games">
<h2 class="tagline">Users</h2>
<table>
<tr>
<th></th>
<th>Username</th>
<th>Remarks</th>
</tr>
<% for (let gameUser of gameUsers) {%>
<tr>
<td><img src="<%= gameUser.profile_image %>" alt="Profile picture"></td>
<td><a class="player" href="/users/<%= gameUser.user_id %>"><%= gameUser.username %></a></td>
<td><span class="remark"><%= gameUser.remark %></span></td>
</tr>
<% } %>
</table>
</div>
</section>
All the <% %>, or the 'crab hands' as we called them in class, signify embedded JavaScript (which is what EJS stands for).
The code creates and fills new HTML elements with data from JavaScript variables. If you need a reminder of where the data came from, it was through the Express router from earlier:
db.query(sqlGameUsers, [gameId], (err, result) => {
if (err) console.log(err);
const gameUsers = result.rows
res.render('show', { game: game, gameUsers: gameUsers })
})
With Express and EJS together, I could do all my CRUD functions:
- C(reating) was fulfilled through forms that allowed users to sign up and create an account. Additionally, users can also create entries in the database by adding new games to their list of games.
- R(eading) was achieved any time data was displayed on a page.
- U(pdating) was fulfilled by allowing users to update their remarks for games as well as their general profile information.
- D(estroying) was fulfilled by allowing users to remove entries from their game lists.
And that covers most of the important parts of the code for GameShare™... wait a sec I'm dropping out again...
|
-> Hey, it's your local Hackerman again. JF forgot to mention that he created some 'middlewares'. This is basically extra code that runs before you get to a route. In particular, JF has some middlewares to determine if a user is logged in and whether they are on their own profile to check whether they can edit things. This means I can't just go to a URL like "users/SomeOtherPersonsID/edit-profile" and vandalize their page because, even if I'm logged in, my user ID won't be the same as the one I'm trying to edit. Blast!
|
Enjoying GameShare™? Be sure to leave a rating!
In designing GameShare™, I tried to be practical when it came to deciding my features. There were several things I wanted to add: a way to display all games in a list, a way to search for games or users, and biggest of all, a way for users to add new games that weren’t in the database. None of these were necessary to meet the objective so I left them aside to work on if I had spare time. I did also plan a chat feature to actually connect with other users but I did not manage to get it working in time for my project submission. Bits and pieces of it are still in the code so if you’re interested in sifting around for it, be my guest.
As I am probably going to make abundantly clear throughout this website, visual design is not my strong suit. I did my best to try and emulate the styles and colors of gaming websites I used (e.g. Nexus Mods) and while I do think the site could be more polished in some areas (like the UIs), I am fairly happy with what I made. If I went back and did it again, I would definitely not even bother with the chat feature as it ultimately took up a lot of time for something that wasn’t needed at all.
That’s it for GameShare™! Feel free to move on to my other projects: Project 1: Tic-Tac-Toe, Project 3: Petrol Down Under, or Project 4