First full stack project for your review

Backend Development
1

Hello guys. I did the full stack course during covid but only now got back to it. My way of learning is through doing so it took me many iterations of getting it wrong before I had something working.
This is a very simple microservice for online games, with user registration, and managing the scores and providing them with scores through the API.
I coded a game too (it was the easiest part!) but for simplicity I didn’t include it but only the most basic frontend page with a button to send the score manually instead of the game doing so.
It’s missing a few final touches like the registration also logging in.

The frontend:

<!DOCTYPE html>
<html>
<head>
  <title>Game Page</title>
  <style>
    .container {
      max-width: 400px;
      margin: 0 auto;
      padding: 20px;
      border: 1px solid #ccc;
      text-align: center;
    }
    h1 {
      font-size: 24px;
      margin-bottom: 20px;
    }
    table {
      width: 100%;
      margin-top: 20px;
    }
    th, td {
      padding: 8px;
      text-align: left;
    }
    th {
      background-color: #f2f2f2;
    }
    body {
        margin: 0;
        padding: 0;
        overflow: hidden;
      }

      #canvas {
        display: block;
      }
  </style>
</head>
<body>
  <canvas id="canvas"></canvas>
    <script src="game.js"></script>
    
  <div class="container">
    <h1>Under Construction</h1>
    <div>
      <label for="username">Username:</label>
      <input type="text" id="username" name="username" />
      <label for="password">Password:</label>
      <input type="password" id="password" name="password" />
      <label for="score">Score:</label>
      <input type="text" id="score" name="score" />
      <button id="loginBtn">Log In</button>
      <button id="registerBtn">Register</button>
      <button id="logoutBtn">Log Out</button>
      <button id="submitScoreBtn">Submit Score</button>
    </div>
    <h2>Top Scores</h2>
    <table id="topScoreTable">
  <thead>
    <tr>
      <th>Username</th>
      <th>Score</th>
    </tr>
  </thead>
  <tbody id="topScoreTbody"></tbody>
  </table>
      <h2>Latest Scores</h2>
    <table id="lastScoresTable">
  <thead>
    <tr>
      <th>Username</th>
      <th>Score</th>
    </tr>
  </thead>
  <tbody id="lastScoreTbody"></tbody>
</table>


  </div>

  <script>
    const loginBtn = document.getElementById('loginBtn');
    const registerBtn = document.getElementById('registerBtn');
    const logoutBtn = document.getElementById('logoutBtn');
    const usernameInput = document.getElementById('username');
    const passwordInput = document.getElementById('password');
    const sendscoreBtn = document.getElementById('submitScoreBtn');//tmp
    const scoreInput = document.getElementById('score');//tmp

    sendscoreBtn.addEventListener('click', () => {
      //test score submit
      submitScore(scoreInput.value);
    });

    loginBtn.addEventListener('click', () => {
      const username = usernameInput.value;
      const password = passwordInput.value;
      
      // Send POST request to login endpoint
      fetch('http://127.0.0.1:3000/login', {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json'
        },
        body: JSON.stringify({ username, password })
      })
      .then(response => response.json())
      .then(data => {
        // After receiving the response from the login request
        const { authToken } = data; // Assuming `data` is the response data
          // Store the token in localStorage
        localStorage.setItem('authToken', authToken);
        console.log(data);
      })
      .catch(error => {
        console.error(error);
      });
    });

    registerBtn.addEventListener('click', () => {
      const username = usernameInput.value;
      const password = passwordInput.value;
      
      // Send POST request to register endpoint
      fetch('http://127.0.0.1:3000/register', {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json'
        },
        body: JSON.stringify({ username, password })
      })
      .then(response => response.json())
      .then(data => {
        // After receiving the response from the login request
        const { authToken } = data; // Assuming `data` is the response data
        // Store the token in localStorage
        localStorage.setItem('authToken', authToken);
        console.log(data);
      })
      .catch(error => {
        console.error(error);
      });
    });

    logoutBtn.addEventListener('click', () => {
      const authToken = localStorage.getItem('authToken');

      // Send POST request to logout endpoint
      fetch('http://127.0.0.1:3000/logout', {
        method: 'POST',
            headers: {
      'Authorization': `${authToken}`}
      })
      .then(response => response.json())
      .then(data => {
        // Handle the response data
        console.log(data);
      })
      .catch(error => {
        console.error('error:' + error);
      });
    });

function PopulateTables(data){
 const topScoreTable = document.getElementById('topScoreTable');
      const topScoreTbody = document.getElementById('topScoreTbody');

      // Clear the table body
      topScoreTbody.innerHTML = '';

      // Populate the table with top scores
      data.topScores.forEach((user) => {
        const row = document.createElement('tr');
        const usernameCell = document.createElement('td');
        const scoreCell = document.createElement('td');

        usernameCell.textContent = user.username;
        scoreCell.textContent = user.score;

        row.appendChild(usernameCell);
        row.appendChild(scoreCell);
        topScoreTbody.appendChild(row);
      });

      const lastScoreTable = document.getElementById('lastScoreTable');
      const lastScoreTbody = document.getElementById('lastScoreTbody');

      lastScoreTbody.innerHTML = '';

      // Populate the table with latest scores
      data.latestScores.forEach((user) => {
        const row = document.createElement('tr');
        const usernameCell = document.createElement('td');
        const scoreCell = document.createElement('td');

        usernameCell.textContent = user.username;
        scoreCell.textContent = user.score;

        row.appendChild(usernameCell);
        row.appendChild(scoreCell);
        lastScoreTbody.appendChild(row);
      });
}

// Client-side code
function populateScores() {
  fetch('http://127.0.0.1:3000/scores')
    .then((response) => response.json())
    .then((data) => {
      PopulateTables(data);
    })
    .catch((error) => {
      console.error(error);
    });
}


// Call the function to populate scores on page load or whenever needed
populateScores();

function submitScore(score) {
  const data = { score };
  const authToken = localStorage.getItem('authToken');

  fetch('http://127.0.0.1:3000/submitscore', {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'Authorization': `${authToken}`
    },
    body: JSON.stringify(data),
  })
    .then(response => response.json())
    .then(data => {
      console.log(data);
      // Score submitted successfully, update the scores table
      //populateScores();
    })
    .catch(error => {
      console.error(error);
    });
}

// Client-side code
const sse = new EventSource('http://127.0.0.1:3000/updates');

sse.onmessage = function (event) {
  const eventData = JSON.parse(event.data);
console.log(eventData);
  PopulateTables(eventData);
};

    </script>
  </body>
</html>

The backend:

const express = require('express');
const app = express();
const bodyParser = require('body-parser');
const cors = require('cors');
const bcrypt = require('bcrypt');

// Import the database module
const db = require('./users/database');

// Middleware to parse JSON data
app.use(express.json());
// Middleware to parse JSON data
app.use(bodyParser.json());
// Enable CORS
app.use(cors());

const session = require('express-session');
const SQLiteStore = require('connect-sqlite3')(session);

// Set up session middleware
app.use(session({
    secret: 'your-secret-key', // Replace with a strong secret key
    resave: false,
    saveUninitialized: false,
    store: new SQLiteStore({
      db: './users/database.db', // Replace with the path to your SQLite database file
      table: 'sessions',
      ttl: 604800, // Session expiration time (in seconds), e.g., 7 days
    }),
  })
);

const jwt = require('jsonwebtoken');

function generateAuthToken(userId) {
  const secretKey = 'your-secret-key'; // Replace with your own secret key
  const token = jwt.sign({ id: userId }, secretKey);
  return token;
}

const verifyToken = (req, res, next) => {
  const token =
    req.body.token || req.query.token || req.headers['authorization'];

  if (!token) {
    return res.status(403).send("A token is required for authentication");
  }

  try {
    const decoded = jwt.verify(token, 'your-secret-key');
    req.user = decoded;

    db.getUserByUserId(decoded.id, (err, user) => {
      if (err) {
        console.error(err);
        return res.status(500).json({ error: 'Internal server error' });
      }
      if (!user) {
        return res.status(401).json({ error: 'User id not found' });
      }
      if (user.token !== token) {
        return res.status(401).json({ error: 'Token expired' });
      }
      req.user = user;
      next();
    });
  } catch (err) {
    return res.status(401).send("Invalid Token");
  }
};


app.use('/submitscore', verifyToken);
app.use('/logout', verifyToken);

app.post("/login", async (req, res) => {

  // Our login logic starts here
  try {
    // Get user input
    const { username, password } = req.body;

    // Validate user input
    if (!(username && password)) {
      res.status(400).send("All input is required");
    }
     db.getUserByUsername(username, (err, user) => {
        if (err) {
          console.error(err);
          return res.status(500).json({ error: 'Internal server error' });
        }

        if (!user) {
          return res.status(401).json({ error: 'Username not found' });
        }

        // Check if the password matches using bcrypt
        bcrypt.compare(password, user.password, (bcryptErr, result) => {
          if (bcryptErr) {
            console.error(bcryptErr);
            return res.status(500).json({ error: 'Internal server error' });
          }

          if (!result) {
            // Password does not match
            return res.status(401).json({ error: 'Incorrect password' });
          }

          const authToken = generateAuthToken(user.id); // Generate the authentication token
          user.token = authToken;

          db.updateUserToken(username, authToken, (err, user) => {
            if (err) {
              console.error(err);
              return res.status(500).json({ error: 'Internal server error' });
            }
          });

          return res.json({ message: 'Login successful', authToken });
        });
    })
  } catch (err) {
    console.log(err);
  }
});

// Define the registration route
app.post('/register', (req, res) => {
  const { username, password } = req.body;

  // Perform validation checks on the input data
  if (!username || !password) {
    return res.status(400).json({ error: 'Username and password are required' });
  }

  // Check if the username already exists in the database
  db.getUserByUsername(username, (err, user) => {
    if (err) {
      console.error(err);
      return res.status(500).json({ error: 'Internal server error' });
    }

    if (user) {
      return res.status(400).json({ error: 'Username already exists' });
    }

    // Encrypt the password
    bcrypt.hash(password, 10, (hashErr, hashedPassword) => {
      if (hashErr) {
        console.error(hashErr);
        return res.status(500).json({ error: 'Internal server error' });
      }

      // Register the user in the database with the hashed password
      db.registerUser(username, hashedPassword, 0, (registerErr) => {
        if (registerErr) {
          console.error(registerErr);
          return res.status(500).json({ error: 'Internal server error' });
        }

        return res.json({ message: 'User registered successfully' });
      });
    });
  });
});

// Server-side code
let topScores = []; // In-memory variable to store the top 10 scores
let latestScoresTable = [];  // In-memory variable to store the last 10 scores

// Function to update the topScores table
function updateTopScoresTable() {
  db.getTopScores(10, (err, scores) => {
    if (err) {
      console.error(err);
      return;
    }
    topScores = scores;
    //console.log('topScores: ' + JSON.stringify(topScores));
  });
}

function updateLastScoresTable() {
  db.getLastScores(10, (err, scores) => {
    if (err) {
      console.error(err);
      return;
    }
    latestScoresTable = scores;
    //console.log('latestScoresTable: ' + JSON.stringify(latestScoresTable));
  });
}

// Endpoint to retrieve both tables
app.get('/scores', (req, res) => {
    return res.json({ topScores: topScores, latestScores: latestScoresTable });
});

// Endpoint to handle score submission
app.post('/submitscore', (req, res) => {
  // Process the submitted score and store it in the database
  const { score } = req.body;
  const username = req.user.username;
//console.log(score +' ' + req.user.id);
  db.updateScore(req.user.id, score, (err) => {
    if (err) {
      console.error(err);
      return res.status(500).json({ error: 'Internal server error' });
    }

    // Update the topScores table
    updateTopScoresTable();

    updateLastScoresTable();

    // Send a Server-Sent Event to connected clients to update their tables
    const eventData = JSON.stringify({ latestScores: latestScoresTable, topScores: topScores });
    sendSSEToAllClients(eventData);

    return res.json({ message: 'Score submitted successfully' });
  });
});



app.post('/logout', (req, res) => {
          db.updateUserTokenById(req.user.id, '', (err, user) => {
            if (err) {
              console.error(err);
              return res.status(500).json({ error: 'Internal server error' });
            }
          });
  //console.log('req.session: ' + JSON.stringify(req.session));
  req.session.destroy((err) => {
    if (err) {
      console.error(err);
      return res.status(500).json({ error: 'Internal server error' });
    }
    return res.json({ message: 'Logged out successfully' });
  });
});

// SSE
const connectedClients = [];

// SSE endpoint
app.get('/updates', (req, res) => {
  res.setHeader('Content-Type', 'text/event-stream');
  res.setHeader('Cache-Control', 'no-cache');
  res.setHeader('Connection', 'keep-alive');
  res.setHeader('Access-Control-Allow-Origin', '*');
  res.flushHeaders();

  // Add the client to the connected clients list
  connectedClients.push(res);

  // Send the latest scores data as an initial message
  const initialEventData = JSON.stringify({ latestScores: latestScoresTable, topScores: topScores });
  res.write(`data: ${initialEventData}\n\n`);

  // Remove the client from the connected clients list when the connection is closed
  req.on('close', () => {
    const clientIndex = connectedClients.indexOf(res);
    connectedClients.splice(clientIndex, 1);
  });
});

// Function to send SSE to all connected clients
function sendSSEToAllClients(eventData) {
  connectedClients.forEach((client) => {
    client.write(`data: ${eventData}\n\n`);
  });
}

function init(){
    updateTopScoresTable();
    updateLastScoresTable();
}

init();

// Start the server
app.listen(3000, () => {
  console.log('Server is running on port 3000');
});

The DB module:

const sqlite3 = require('sqlite3').verbose();

// Connect to the database
const db = new sqlite3.Database('database.db');

db.serialize(() => {
  // Create a users table
  db.run(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      username TEXT UNIQUE,
      password TEXT,
      score INTEGER DEFAULT 0,
      token TEXT,
      datetime DATETIME DEFAULT CURRENT_TIMESTAMP
    )
  `);
});



  // Query the data
  db.all('SELECT * FROM users', (err, rows) => {
    if (err) {
      console.error(err);
    } else {
      console.log(rows);
    }
  });




module.exports = {
  registerUser: function (username, password, score, callback) {
    // Insert the user into the database
    db.run('INSERT INTO users (username, password, score) VALUES (?, ?, ?)', [username, password, score], callback);
  },
  getUserByUsername: function (username, callback) {
    // Retrieve the user from the database by username
    db.get('SELECT * FROM users WHERE username = ?', [username], callback);
  },
  getUserByUserId: function (userid, callback) {
    // Retrieve the user from the database by username
    db.get('SELECT * FROM users WHERE id = ?', [userid], callback);
  },
  updateScore: function (userid, score, callback) {
    // Update the user's score in the database
    db.run('UPDATE users SET score = ? WHERE id = ?', [score, userid], callback);
  },
  getTopScores: function (limit, callback) {
  db.all('SELECT username, score, datetime FROM users ORDER BY score DESC LIMIT ?', [limit], callback);
  },
  // Additional database functions...
  close: function(){
      // Close the database connection
    db.close();
  },
  updateUserToken: function (username, token, callback) {
    // Update the user's token in the database
    db.run('UPDATE users SET token = ? WHERE username = ?', [token, username], callback);
  },
    updateUserTokenById: function (id, token, callback) {
    // Update the user's token in the database
    db.run('UPDATE users SET token = ? WHERE id = ?', [token, id], callback);
  },
  getLastScores: function (limit, callback) {
  db.all('SELECT username, score, datetime FROM users ORDER BY datetime DESC LIMIT ?', [limit], callback);
},
};
2

Looks like there’s a serious bug I didn’t consider. The SQLite is asynchronous so I had to change the code:

database.js:

 updateScore2: function (userid, score) {
      return new Promise((resolve, reject) => {
        db.run('UPDATE users SET score = ? WHERE id = ?', [score, userid], function (err) {
          if (err) {
            reject(err);
          } else {
            resolve();
          }
        });
      });
  },
 getTopScores2: function(limit) {
      return new Promise((resolve, reject) => {
        db.all('SELECT username, score, datetime FROM users ORDER BY score DESC LIMIT ?', [limit], function (err, rows) {
          if (err) {
            reject(err);
          } else {
            resolve(rows);
          }
        });
      });
    }

server.js:

app.post('/submitscore', async (req, res) => {
  const { score } = req.body;
  const username = req.user.username;
console.log('/submitscore: ' + score +' ' + req.user.id);
  try {
    // Update the score
    await db.updateScore2(req.user.id, score);

    // Fetch the updated top scores
    const updatedScores = await db.getTopScores2(10);

    // Update the topScores variable
    topScores = updatedScores;
console.log('topScores: ' + JSON.stringify(topScores));
    sendEventsToAll();
    return res.json({ message: 'Score submitted successfully' });
  } catch (error) {
    console.error(error);
    return res.status(500).json({ error: 'Internal server error' });
  }
});