Student Course Management System

A chronological guide to building a basic full stack web application from scratch using Express.js, EJS, and SQLite. It covers project setup, authentication, course management, search functionality, and production-readiness features.

Part 1: Project Setup & Scaffolding

We begin by setting up the foundational structure for our project.

1. Create the Project Directory

First, open your terminal and create a new directory for your project, then navigate into it.

mkdir student-course-management
cd student-course-management

2. Initialize the Node.js Project

Initialize a new Node.js project to create a package.json file, which will manage our project's dependencies and scripts.

npm init -y

Part 2: Express Server and Database Setup

Now we'll configure the server and initialize our database.

1. Create the Database Configuration

Create config/database.js. This script connects to our SQLite database and creates the necessary tables if they don't exist.

// config/database.js
const sqlite3 = require("sqlite3").verbose();

const DBSOURCE = "db.sqlite";

const db = new sqlite3.Database(DBSOURCE, (err) => {
  if (err) {
    console.error(err.message);
    throw err;
  } else {
    console.log("Connected to the SQLite database.");
    db.serialize(() => {
      db.run(`CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT,
                email TEXT UNIQUE,
                password TEXT,
                role TEXT NOT NULL CHECK(role IN ('student', 'instructor', 'admin')),
                CONSTRAINT email_unique UNIQUE (email)
            )`);
      db.run(`CREATE TABLE IF NOT EXISTS courses (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                description TEXT,
                instructor_id INTEGER,
                FOREIGN KEY (instructor_id) REFERENCES users(id)
            )`);
    });
  }
});

module.exports = db;

Part 3: User Authentication

Let's implement user registration, login, and logout functionality.

1. Create the User Model

Create models/user.js to handle user-related database operations, including password hashing with bcrypt.

// models/user.js
const db = require("../config/database");
const bcrypt = require("bcrypt");
const saltRounds = 10;

const User = {
  create: (name, email, password, role, callback) => {
    bcrypt.hash(password, saltRounds, (err, hash) => {
      if (err) return callback(err);
      const sql =
        "INSERT INTO users (name, email, password, role) VALUES (?, ?, ?, ?)";
      db.run(sql, [name, email, hash, role], function (err) {
        callback(err, { id: this.lastID });
      });
    });
  },
  findByEmail: (email, callback) => {
    db.get("SELECT * FROM users WHERE email = ?", [email], (err, row) =>
      callback(err, row)
    );
  },
  findById: (id, callback) => {
    db.get("SELECT * FROM users WHERE id = ?", [id], (err, row) =>
      callback(err, row)
    );
  },
  comparePassword: (password, hash, callback) => {
    bcrypt.compare(password, hash, (err, isMatch) => {
      if (err) return callback(err);
      callback(null, isMatch);
    });
  },
};

module.exports = User;

Part 4: Course Management & Role-Based Dashboards

Now we'll implement core course management features.

1. Create Authorization Middleware

Create middleware/authMiddleware.js to restrict access based on user roles.

// middleware/authMiddleware.js
exports.isAuthenticated = (req, res, next) => {
  if (req.session.userId) return next();
  res.redirect("/auth/login");
};

exports.isInstructor = (req, res, next) => {
  if (req.session.role === "instructor") return next();
  res.status(403).send("Access Denied: Instructors only.");
};

2. Create the Course Model

Create models/course.js to handle course data.

// models/course.js
const db = require("../config/database");

const Course = {
  create: (title, description, instructorId, callback) => {
    const sql =
      "INSERT INTO courses (title, description, instructor_id) VALUES (?, ?, ?)";
    db.run(sql, [title, description, instructorId], function (err) {
      callback(err, { id: this.lastID });
    });
  },
  findAll: (callback) => {
    const sql = `
            SELECT c.id, c.title, c.description, u.name as instructorName
            FROM courses c JOIN users u ON c.instructor_id = u.id
        `;
    db.all(sql, [], (err, rows) => callback(err, rows));
  },
  findById: (id, callback) => {
    const sql = `
            SELECT c.id, c.title, c.description, c.instructor_id, u.name as instructorName
            FROM courses c JOIN users u ON c.instructor_id = u.id WHERE c.id = ?
        `;
    db.get(sql, [id], (err, row) => callback(err, row));
  },
};

module.exports = Course;

Part 5: Feature: Course Search and Homepage Overhaul

This section adds a vital search feature and redesigns the home page.

1. Update Course Model for Search

Add a search method to models/course.js.

// In models/course.js, inside the Course object
search: (searchTerm, callback) => {
    const sql = `
        SELECT courses.id, courses.title, courses.description, users.name as instructorName
        FROM courses
        JOIN users ON courses.instructor_id = users.id
        WHERE courses.title LIKE ? OR users.name LIKE ?
    `;
    const searchQuery = `%${searchTerm}%`;
    db.all(sql, [searchQuery, searchQuery], (err, rows) => callback(err, rows));
},

2. Add Search to Course Controller

Add the searchCourses function to controllers/courseController.js.

// In controllers/courseController.js
exports.searchCourses = (req, res) => {
  const { q } = req.query;
  if (!q) return res.redirect("/courses");
  Course.search(q, (err, courses) => {
    if (err) return res.status(500).send("Error searching for courses.");
    res.render("courses/search-results", {
      title: `Search Results for "${q}"`,
      courses: courses,
      searchTerm: q,
    });
  });
};

Part 6: Instructor Course Management (Edit/Delete)

This gives instructors full CRUD (Create, Read, Update, Delete) control over their courses.

1. Update Course Model for Edit/Delete

Add update and delete methods to models/course.js.

// In models/course.js, inside the Course object
update: (id, title, description, callback) => {
    const sql = 'UPDATE courses SET title = ?, description = ? WHERE id = ?';
    db.run(sql, [title, description, id], (err) => callback(err));
},
delete: (id, callback) => {
    db.run('DELETE FROM courses WHERE id = ?', id, (err) => callback(err));
}

2. Update Course Controller and Routes

Add controller functions (getEditCourse, postUpdateCourse, postDeleteCourse) and corresponding routes (/:id/edit, /:id/update, /:id/delete) for handling course updates and deletions. These routes must be protected by the isAuthenticated and isInstructor middleware.

Part 7: Production Readiness Features

This section adds essential features for a secure and reliable production application.

1. Install New Dependencies

npm install morgan express-validator

2. Implement Request Logging

Add morgan as middleware in app.js to log all HTTP requests.

// In app.js
const morgan = require("morgan");
app.use(morgan("dev"));

3. Implement Input Sanitization & Form Validation

  • Create middleware/validationMiddleware.js using express-validator to build validation chains for user registration and course creation. Apply this middleware to the relevant POST routes to sanitize and validate user input, preventing common vulnerabilities.
  • Add JavaScript-based validation in the EJS templates to provide immediate feedback to users, improving the overall user experience and reducing invalid form submissions.

Part 8: Deploying to Render.com

The final step is to deploy it to a live production environment so it can be used from anywhere. We will use Render for this, as it offers a free tier for web services and makes deployment straightforward.

Student Course Management System Web Application

By Sujit Mohanty

Student Course Management System Web Application

  • 6