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
usingexpress-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