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.
We begin by setting up the foundational structure for our project.
First, open your terminal and create a new directory for your project, then navigate into it.
mkdir student-course-management
cd student-course-management
Initialize a new Node.js project to create a package.json file, which will manage our project's dependencies and scripts.
npm init -y
Now we'll configure the server and initialize our database.
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;
Let's implement user registration, login, and logout functionality.
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;
Now we'll implement core course management features.
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.");
};
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;
This section adds a vital search feature and redesigns the home page.
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));
},
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,
});
});
};
This gives instructors full CRUD (Create, Read, Update, Delete) control over their courses.
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));
}
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.
This section adds essential features for a secure and reliable production application.
npm install morgan express-validator
Add morgan as middleware in app.js to log all HTTP requests.
// In app.js
const morgan = require("morgan");
app.use(morgan("dev"));
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.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.