Node.

js | SQLite | MongoDB — Lecture Notes

[Link]
Full-Stack Database Lecture Notes
SQLite (Relational) · MongoDB (Non-Relational)

Covering: Core Concepts · Setup · CRUD · Queries · Relationships · Best Practices

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

PART 1 — [Link] Fundamentals

1. Introduction to [Link]
[Link] is an open-source, cross-platform JavaScript runtime environment built on Chrome's V8
engine. It enables JavaScript to run on the server side, making it possible to build fast, scalable
network applications.

1.1 What is [Link]?

• Runtime environment — NOT a programming language or framework
• Uses Google's V8 JavaScript engine (same as Chrome)
• Non-blocking, event-driven I/O model
• Single-threaded with an event loop
• Ideal for I/O-intensive applications (web servers, APIs, real-time apps)

📌 Key Concept
[Link] executes JavaScript outside the browser. This means you can use the same language on
both front-end and back-end, reducing context-switching for developers.

1.2 How [Link] Works — The Event Loop

The Event Loop is the heart of [Link]. It allows Node to perform non-blocking I/O operations
despite JavaScript being single-threaded.

Component Description
Call Stack Executes synchronous code one function at a time
[Link] APIs Handles async operations (fs, http, timers, etc.)
Callback Queue Holds callbacks ready to be executed
Event Loop Moves callbacks from queue to call stack when stack is empty
Microtask Queue Handles Promises (.then) — higher priority than callback queue

1.3 Installing [Link]

Download the LTS version from [Link] or use a version manager:
# Install nvm (Node Version Manager) — recommended
curl -o- [Link] | bash

# Then install and use [Link]

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

nvm install --lts

nvm use --lts

# Verify installation
node --version # e.g., v20.11.0
npm --version # e.g., 10.2.4

1.4 [Link] Module System

[Link] uses CommonJS (CJS) by default. ES Modules (ESM) are also supported via .mjs or
[Link] type:"module".
// CommonJS (default)
const fs = require('fs');
[Link] = { myFunction };

// ES Modules (.mjs or type:"module" in [Link])

import fs from 'fs';
export const myFunction = () => {};

// Built-in modules (no install needed)

const path = require('path');
const http = require('http');
const os = require('os');
const events = require('events');

1.5 npm — Node Package Manager

npm is the default package manager for [Link]. It manages dependencies for your project.
npm init -y # Initialize project (creates [Link])
npm install express # Install a package (added to dependencies)
npm install --save-dev nodemon # Dev-only dependency
npm install -g nodemon # Install globally
npm uninstall express # Remove a package
npm update # Update all packages
npm run start # Run a script defined in [Link]
npm list # List installed packages

1.6 Your First [Link] Server

// [Link] — Basic HTTP Server
const http = require('http');

const PORT = 3000;

const server = [Link]((req, res) => {

[Link](200, { 'Content-Type': 'text/plain' });
[Link]('Hello from [Link]!');
});

[Link](PORT, () => {
[Link](`Server running at [Link]
});

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

1.7 [Link] — The Web Framework

[Link] is the most popular [Link] framework, providing a minimal and flexible layer for routing,
middleware, and request handling.
npm install express
// [Link] — Express Server
const express = require('express');
const app = express();

// Middleware
[Link]([Link]()); // Parse JSON request bodies
[Link]([Link]({ extended: true })); // Parse URL-encoded forms

// Routes
[Link]('/', (req, res) => {
[Link]({ message: 'Welcome to the API' });
});

[Link]('/users/:id', (req, res) => {

const { id } = [Link];
[Link]({ userId: id });
});

[Link]('/users', (req, res) => {

const { name, email } = [Link];
[Link](201).json({ name, email });
});

[Link](3000, () => [Link]('Express running on port 3000'));

1.8 Async Patterns in [Link]

[Link] has evolved through three patterns for handling asynchronous operations:
// 1. Callbacks (legacy)
[Link]('[Link]', 'utf8', (err, data) => {
if (err) return [Link](err);
[Link](data);
});

// 2. Promises
[Link]('[Link]', 'utf8')
.then(data => [Link](data))
.catch(err => [Link](err));

// 3. Async/Await (recommended — most readable)

async function readMyFile() {
try {
const data = await [Link]('[Link]', 'utf8');
[Link](data);
} catch (err) {
[Link](err);
}
}
readMyFile();

⚠️Warning: Callback Hell

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

Deeply nested callbacks make code hard to read and maintain. Always prefer Promises or
async/await in modern [Link] code.

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

PART 2 — SQLite (Relational Database)

2. SQLite with [Link]

SQLite is a lightweight, serverless, self-contained relational database engine. It stores data in a
single file on disk — perfect for development, prototyping, embedded systems, and small-to-
medium applications.

2.1 What is a Relational Database (RDS)?

A relational database organizes data into tables (relations) with rows and columns. Tables relate to
each other via foreign keys, and data is queried using Structured Query Language (SQL).

Term Definition
Table A collection of rows and columns (like a spreadsheet)
Row / Record A single data entry in a table
Column / Field An attribute/property of the data
Primary Key Unique identifier for each row
Foreign Key A column that references the primary key of another table
Index Data structure for fast lookups on a column
Schema The structure/blueprint of the database
Query A request to retrieve or manipulate data (SQL statement)

2.2 SQLite vs. Other Databases

Feature Detail
Serverless No separate server process needed — embedded in the app
Zero-config No installation or admin required; just a file
Single file Entire database stored in one .db file
ACID compliant Supports Atomicity, Consistency, Isolation, Durability
Limitation Not suitable for high-concurrency multi-writer production workloads
Best use Development, testing, mobile apps, small web apps, IoT

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

2.3 Setting Up SQLite in [Link]

We will use the better-sqlite3 package, which provides a synchronous, high-performance API —
perfect for learning and most use cases.
npm install better-sqlite3
// [Link] — Database connection
const Database = require('better-sqlite3');

// Opens (or creates) [Link] in the current directory

const db = new Database('[Link]', { verbose: [Link] });

[Link]('Connected to SQLite database');

[Link] = db;

📌 Alternative: sqlite3 (async)

The 'sqlite3' npm package is callback-based. Combine it with 'sqlite' for Promise support. better-
sqlite3 is synchronous and generally preferred for simplicity.

2.4 SQL Fundamentals

DDL — Data Definition Language
DDL statements define and modify the structure of database objects.
-- Create a table
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
age INTEGER,
created_at TEXT DEFAULT (datetime('now'))
);

-- Create a related table

CREATE TABLE IF NOT EXISTS posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
title TEXT NOT NULL,
body TEXT,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Alter a table (add column)

ALTER TABLE users ADD COLUMN phone TEXT;

-- Drop a table
DROP TABLE IF EXISTS posts;

DML — Data Manipulation Language

DML statements insert, update, delete, and query data.
-- INSERT
INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@[Link]', 28);
INSERT INTO users (name, email, age) VALUES ('Bob', 'bob@[Link]', 34);

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

-- SELECT
SELECT * FROM users;
SELECT id, name, email FROM users WHERE age > 25;
SELECT * FROM users ORDER BY name ASC LIMIT 10;

-- UPDATE
UPDATE users SET age = 29 WHERE email = 'alice@[Link]';

-- DELETE
DELETE FROM users WHERE id = 2;

2.5 CRUD Operations in [Link] with SQLite

Setting Up Tables
// [Link] — Run once to create tables
const db = require('./db');

[Link](`
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
age INTEGER,
created_at TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS posts (

id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
title TEXT NOT NULL,
body TEXT,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
`);

[Link]('Tables created successfully!');

CREATE — Insert Data

// Insert a single user
const insertUser = [Link](
'INSERT INTO users (name, email, age) VALUES (?, ?, ?)'
);

const result = [Link]('Alice', 'alice@[Link]', 28);

[Link]('Inserted user ID:', [Link]);

// Insert multiple users (using a transaction for performance)

const insertMany = [Link]((users) => {
const stmt = [Link]('INSERT INTO users (name, email, age) VALUES (?, ?, ?)');
for (const user of users) {
[Link]([Link], [Link], [Link]);
}
});

insertMany([
{ name: 'Bob', email: 'bob@[Link]', age: 34 },

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

{ name: 'Carol', email: 'carol@[Link]', age: 22 },

]);

READ — Query Data

// Get all users
const getAllUsers = [Link]('SELECT * FROM users');
const users = [Link]();
[Link](users);

// Get a single user by ID

const getUserById = [Link]('SELECT * FROM users WHERE id = ?');
const user = [Link](1);
[Link](user);

// Get users with filter

const getAdults = [Link]('SELECT * FROM users WHERE age >= ? ORDER BY name');
const adults = [Link](18);

// Count users
const countUsers = [Link]('SELECT COUNT(*) as total FROM users');
const { total } = [Link]();
[Link]('Total users:', total);

UPDATE — Modify Data

// Update a single field
const updateAge = [Link]('UPDATE users SET age = ? WHERE id = ?');
const changes = [Link](30, 1);
[Link]('Rows updated:', [Link]);

// Update multiple fields

const updateUser = [Link](
'UPDATE users SET name = ?, email = ? WHERE id = ?'
);
[Link]('Alice Smith', 'alicesmith@[Link]', 1);

DELETE — Remove Data

// Delete by ID
const deleteUser = [Link]('DELETE FROM users WHERE id = ?');
const result = [Link](1);
[Link]('Rows deleted:', [Link]);

// Delete with condition

const deleteOldUsers = [Link]('DELETE FROM users WHERE age > ?');
[Link](60);

2.6 Advanced SQL Queries

JOINs — Combining Tables
-- INNER JOIN: Only rows that match in BOTH tables
SELECT [Link], [Link], [Link]
FROM users u
INNER JOIN posts p ON [Link] = p.user_id;

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

-- LEFT JOIN: All users, even those with no posts

SELECT [Link], COUNT([Link]) AS post_count
FROM users u
LEFT JOIN posts p ON [Link] = p.user_id
GROUP BY [Link]
ORDER BY post_count DESC;
// In [Link]
const getUsersWithPosts = [Link](`
SELECT [Link], [Link], [Link], COUNT([Link]) AS post_count
FROM users u
LEFT JOIN posts p ON [Link] = p.user_id
GROUP BY [Link]
`);
const result = [Link]();

Aggregate Functions
SELECT COUNT(*) AS total_users FROM users;
SELECT AVG(age) AS average_age FROM users;
SELECT MAX(age) AS oldest FROM users;
SELECT MIN(age) AS youngest FROM users;
SELECT SUM(age) AS age_sum FROM users;

-- GROUP BY with HAVING

SELECT age, COUNT(*) as count
FROM users
GROUP BY age
HAVING count > 1;

Transactions
Transactions ensure a group of operations either all succeed or all fail — critical for data integrity.
// Wrapping operations in a transaction
const transfer = [Link]((fromId, toId, amount) => {
const debit = [Link]('UPDATE accounts SET balance = balance - ? WHERE id
= ?');
const credit = [Link]('UPDATE accounts SET balance = balance + ? WHERE id
= ?');

[Link](amount, fromId);
[Link](amount, toId);
// If either fails, both are rolled back automatically
});

try {
transfer(1, 2, 100);
[Link]('Transfer successful');
} catch (err) {
[Link]('Transfer failed, rolled back:', [Link]);
}

2.7 Building a REST API with Express + SQLite

// routes/[Link] — Full CRUD REST API
const express = require('express');
const router = [Link]();

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

const db = require('../db');

// GET /users — List all users

[Link]('/', (req, res) => {
const users = [Link]('SELECT * FROM users').all();
[Link](users);
});

// GET /users/:id — Get one user

[Link]('/:id', (req, res) => {
const user = [Link]('SELECT * FROM users WHERE id = ?').get([Link]);
if (!user) return [Link](404).json({ error: 'User not found' });
[Link](user);
});

// POST /users — Create user

[Link]('/', (req, res) => {
const { name, email, age } = [Link];
if (!name || !email) return [Link](400).json({ error: 'Name and email
required' });
const stmt = [Link]('INSERT INTO users (name, email, age) VALUES
(?, ?, ?)');
const result = [Link](name, email, age);
[Link](201).json({ id: [Link], name, email, age });
});

// PUT /users/:id — Update user

[Link]('/:id', (req, res) => {
const { name, email, age } = [Link];
const stmt = [Link]('UPDATE users SET name=?, email=?, age=? WHERE id=?');
const result = [Link](name, email, age, [Link]);
if ([Link] === 0) return [Link](404).json({ error: 'User not
found' });
[Link]({ message: 'User updated successfully' });
});

// DELETE /users/:id — Delete user

[Link]('/:id', (req, res) => {
const result = [Link]('DELETE FROM users WHERE id = ?').run([Link]);
if ([Link] === 0) return [Link](404).json({ error: 'User not
found' });
[Link]({ message: 'User deleted' });
});

[Link] = router;

✅ Best Practice
Always use prepared statements ([Link]) with placeholders (?) instead of string concatenation.
This prevents SQL Injection attacks, which are the #1 web security vulnerability.

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

PART 3 — MongoDB (Non-Relational Database)

3. MongoDB with [Link]

MongoDB is a document-oriented NoSQL database. Instead of storing data in tables with rows and
columns, MongoDB stores data as flexible JSON-like documents in collections — making it ideal for
hierarchical, dynamic, or rapidly evolving data.

3.1 What is a Non-Relational (NoSQL) Database?

NoSQL Type Description
Document Store Data stored as JSON-like documents (MongoDB, CouchDB)
Key-Value Store Simple key → value pairs (Redis, DynamoDB)
Column-Family Column-oriented, optimized for wide data (Cassandra)
Graph DB Nodes and edges for relationship-heavy data (Neo4j)

3.2 MongoDB Core Concepts

Term Definition
Database Container for collections (like an SQL database)
Collection Group of documents (like an SQL table)
Document A single JSON-like record (like an SQL row)
Field A key-value pair in a document (like an SQL column)
_id Auto-generated unique identifier for each document (ObjectId)
Embedded Doc A nested document inside another document
Reference Storing another document's _id to link documents
Index Improves query speed (like SQL indexes)
Aggregation Multi-stage pipeline for complex data processing

3.3 SQL vs. MongoDB Comparison

SQL MongoDB
Database Database
Table Collection

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

Row Document
Column Field
Primary Key _id (ObjectId)
JOIN Embedding or $lookup aggregation
SELECT find() / findOne()
INSERT insertOne() / insertMany()
UPDATE updateOne() / updateMany()
DELETE deleteOne() / deleteMany()
WHERE Query filter object { field: value }
GROUP BY $group in aggregation pipeline

3.4 Installing & Connecting MongoDB

Option A: MongoDB Atlas (Cloud — Recommended for Learning)
1. Go to [Link] and create a free account
2. Create a free cluster (M0 tier)
3. Add your IP address to the network access list
4. Create a database user with a password
5. Get the connection string (starts with mongodb+srv://)

Option B: Local MongoDB Installation

# macOS (Homebrew)
brew tap mongodb/brew
brew install mongodb-community
brew services start mongodb-community

# Ubuntu/Debian
sudo apt-get install -y mongodb
sudo systemctl start mongod

# Windows: Download installer from [Link]

Installing Mongoose ([Link] ODM)

npm install mongoose
// [Link] — MongoDB connection with Mongoose
const mongoose = require('mongoose');

const MONGO_URI = [Link].MONGO_URI || 'mongodb://localhost:27017/myapp';

async function connectDB() {

try {
await [Link](MONGO_URI, {
useNewUrlParser: true,
useUnifiedTopology: true,

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

});
[Link]('MongoDB connected successfully');
} catch (err) {
[Link]('MongoDB connection error:', [Link]);
[Link](1); // Exit process if DB fails to connect
}
}

// Listen for connection events

[Link]('disconnected', () => {
[Link]('MongoDB disconnected');
});

[Link] = connectDB;

3.5 Schemas and Models

Mongoose introduces Schemas to define the structure of your documents, and Models to interact
with the collection.
// models/[Link]
const mongoose = require('mongoose');

const userSchema = new [Link]({

name: {
type: String,
required: [true, 'Name is required'],
trim: true,
minlength: 2,
maxlength: 50,
},
email: {
type: String,
required: [true, 'Email is required'],
unique: true,
lowercase: true,
match: [/\S+@\S+\.\S+/, 'Invalid email format'],
},
age: {
type: Number,
min: 0,
max: 120,
},
role: {
type: String,
enum: ['user', 'admin', 'moderator'],
default: 'user',
},
isActive: { type: Boolean, default: true },
tags: [String], // Array of strings
}, {
timestamps: true, // Auto-adds createdAt and updatedAt
});

// Virtual field (not stored in DB)

[Link]('displayName').get(function () {
return `${[Link]} <${[Link]}>`;
});

// Instance method
[Link] = function () {

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

return `Hello, I'm ${[Link]}`;

};

[Link] = [Link]('User', userSchema);

3.6 CRUD Operations with Mongoose

CREATE — Insert Documents
const User = require('./models/User');

// Method 1: new + save()

async function createUser() {
const user = new User({
name: 'Alice',
email: 'alice@[Link]',
age: 28,
tags: ['developer', 'nodejs'],
});
const saved = await [Link]();
[Link]('Created:', saved._id);
}

// Method 2: [Link]() — shorthand

async function createUserFast() {
const user = await [Link]({
name: 'Bob', email: 'bob@[Link]', age: 34
});
[Link]('Created:', user._id);
}

// Method 3: insertMany() — bulk insert

async function createManyUsers() {
const users = await [Link]([
{ name: 'Carol', email: 'carol@[Link]', age: 22 },
{ name: 'Dave', email: 'dave@[Link]', age: 45 },
]);
[Link](`Inserted ${[Link]} users`);
}

READ — Query Documents

// Find ALL users
const users = await [Link]();

// Find with filter

const adults = await [Link]({ age: { $gte: 18 } });

// Find with projection (only return specific fields)

const names = await [Link]({}, 'name email -_id');

// Find ONE document

const user = await [Link]({ email: 'alice@[Link]' });

// Find by ID
const userById = await [Link]('64a1b2c3d4e5f6789012345');

// Sort, limit, skip (pagination)

const page1 = await [Link]()

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

.sort({ createdAt: -1 }) // newest first

.limit(10)
.skip(0);

// Count
const count = await [Link]({ isActive: true });

MongoDB Query Operators

Operator Usage
$eq Equal to: { age: { $eq: 28 } }
$ne Not equal: { age: { $ne: 28 } }
$gt Greater than: { age: { $gt: 18 } }
$gte Greater or equal: { age: { $gte: 18 } }
$lt Less than: { age: { $lt: 65 } }
$lte Less or equal: { age: { $lte: 65 } }
$in In array: { role: { $in: ['admin','mod'] } }
$nin Not in array: { role: { $nin: ['banned'] } }
$and All conditions true: { $and: [{age:{$gt:18}}, {isActive:true}] }
$or Any condition true: { $or: [{age:{$lt:18}}, {role:'admin'}] }
$regex Pattern match: { name: { $regex: /alice/i } }
$exists Field exists: { phone: { $exists: true } }

UPDATE — Modify Documents

// Update one document
await [Link](
{ email: 'alice@[Link]' }, // filter
{ $set: { age: 29, role: 'admin' } } // update
);

// Update many documents

await [Link](
{ isActive: false },
{ $set: { isActive: true } }
);

// Find and update (returns the updated document)

const updated = await [Link](
'64a1b2c3d4e5f6789012345',
{ $set: { name: 'Alice Smith' }, $push: { tags: 'expert' } },
{ new: true, runValidators: true } // new:true = return updated doc
);

// Common update operators

// $set — Set field value
// $unset — Remove a field
// $inc — Increment a number
// $push — Add item to array

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

// $pull — Remove item from array

// $addToSet — Add to array (no duplicates)

DELETE — Remove Documents

// Delete one
await [Link]({ email: 'alice@[Link]' });

// Delete many
await [Link]({ isActive: false });

// Find and delete (returns the deleted document)

const deleted = await [Link]('64a1b2c3...');
[Link]('Deleted:', [Link]);

3.7 Embedded Documents vs. References

Embedded Documents (Denormalization)
Store related data directly inside the parent document. Good for data that is always queried
together and doesn't change independently.
const postSchema = new [Link]({
title: String,
body: String,
// Embedded address (part of the same document)
author: {
name: String,
email: String,
},
comments: [
{
user: String,
text: String,
date: { type: Date, default: [Link] }
}
]
});

Document References (Normalization)

Store a reference (_id) to another document. Good for data shared across many documents or
frequently updated independently.
const postSchema = new [Link]({
title: String,
body: String,
author: {
type: [Link],
ref: 'User', // References the User model
required: true,
},
});

// Populate references when querying

const posts = await [Link]()
.populate('author', 'name email'); // Replace ObjectId with actual user data

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

3.8 Aggregation Pipeline

MongoDB's aggregation pipeline processes documents through a series of stages to compute
results, transform data, and perform complex analytics.
// Example: Get user stats by role
const stats = await [Link]([
{ $match: { isActive: true } }, // Stage 1: Filter
{ $group: { // Stage 2: Group
_id: '$role',
count: { $sum: 1 },
avgAge: { $avg: '$age' },
names: { $push: '$name' }
}},
{ $sort: { count: -1 } }, // Stage 3: Sort
{ $project: { role: '$_id', count: 1, avgAge: { $round: ['$avgAge', 1] } } }
]);

// Join collections with $lookup (like SQL JOIN)

const usersWithPosts = await [Link]([
{ $lookup: {
from: 'posts', // The other collection
localField: '_id',
foreignField: 'author',
as: 'posts' // Output array field
}},
{ $addFields: { postCount: { $size: '$posts' } } },
{ $sort: { postCount: -1 } }
]);

3.9 Building a REST API with Express + MongoDB

// routes/[Link] — Full Mongoose REST API
const express = require('express');
const router = [Link]();
const User = require('../models/User');

// GET /users
[Link]('/', async (req, res) => {
try {
const { page = 1, limit = 10, sort = '-createdAt' } = [Link];
const users = await [Link]()
.sort(sort)
.skip((page - 1) * limit)
.limit(Number(limit));
const total = await [Link]();
[Link]({ users, total, page: Number(page), pages: [Link](total /
limit) });
} catch (err) {
[Link](500).json({ error: [Link] });
}
});

// GET /users/:id
[Link]('/:id', async (req, res) => {
try {
const user = await [Link]([Link]);
if (!user) return [Link](404).json({ error: 'User not found' });

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

[Link](user);
} catch (err) {
[Link](400).json({ error: 'Invalid ID format' });
}
});

// POST /users
[Link]('/', async (req, res) => {
try {
const user = await [Link]([Link]);
[Link](201).json(user);
} catch (err) {
if ([Link] === 11000) {
return [Link](409).json({ error: 'Email already exists' });
}
[Link](400).json({ error: [Link] });
}
});

// PUT /users/:id
[Link]('/:id', async (req, res) => {
try {
const user = await [Link](
[Link], { $set: [Link] }, { new: true, runValidators: true }
);
if (!user) return [Link](404).json({ error: 'User not found' });
[Link](user);
} catch (err) {
[Link](400).json({ error: [Link] });
}
});

// DELETE /users/:id
[Link]('/:id', async (req, res) => {
try {
const user = await [Link]([Link]);
if (!user) return [Link](404).json({ error: 'User not found' });
[Link]({ message: 'User deleted', id: [Link] });
} catch (err) {
[Link](500).json({ error: [Link] });
}
});

[Link] = router;

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

PART 4 — Comparison, Architecture & Best Practices

4. SQLite vs. MongoDB — When to Use What

4.1 Side-by-Side Comparison
Feature SQLite → MongoDB
Type Relational (SQL) | Non-Relational (NoSQL/Document)
Data Format Tables, rows, columns | JSON-like documents
Schema Fixed schema (defined upfront) | Flexible schema (dynamic)
Relationships JOINs with foreign keys | Embedding or $lookup
Query Language SQL (standard) | MongoDB Query Language (MQL)
Scalability Vertical (scale up) | Horizontal (scale out)
ACID Full ACID compliance | ACID with transactions (v4.0+)
Best For Structured, relational data | Unstructured, hierarchical data
Use Cases Finance, inventory, user accounts | CMS, catalogs, real-time apps

4.2 Recommended Project Structure

project/
├── node_modules/
├── src/
│ ├── config/
│ │ └── [Link] # Database connection
│ ├── models/
│ │ └── [Link] # Mongoose schema / SQLite table defs
│ ├── routes/
│ │ └── [Link] # Route handlers
│ ├── controllers/
│ │ └── [Link] # Business logic (optional for larger apps)
│ ├── middleware/
│ │ ├── [Link] # Authentication middleware
│ │ └── [Link]
│ └── [Link] # Express app setup
├── .env # Environment variables (NEVER commit this!)
├── .gitignore
├── [Link]
└── [Link] # Entry point (starts the server)

4.3 Environment Variables (.env)

npm install dotenv

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

# .env file — NEVER commit to git!

PORT=3000
MONGO_URI=mongodb+srv://user:password@[Link]/myapp
JWT_SECRET=your_super_secret_key_here
NODE_ENV=development
// [Link] — Load env vars first
require('dotenv').config();
const app = require('./src/app');

const PORT = [Link] || 3000;

[Link](PORT, () => [Link](`Server running on port ${PORT}`));

4.4 Error Handling Middleware

// middleware/[Link]
const errorHandler = (err, req, res, next) => {
[Link]([Link]);

// Mongoose validation error

if ([Link] === 'ValidationError') {
const errors = [Link]([Link]).map(e => [Link]);
return [Link](400).json({ errors });
}

// Mongoose duplicate key

if ([Link] === 11000) {
return [Link](409).json({ error: 'Duplicate value' });
}

// Generic server error

[Link]([Link] || 500).json({
error: [Link] || 'Internal Server Error'
});
};

[Link] = errorHandler;

// [Link] — Register LAST (after all routes)

[Link](errorHandler);

4.5 Security Best Practices

• Never store passwords in plain text — use bcrypt to hash passwords
• Always use prepared statements (SQLite) or parameterized queries to prevent injection
• Store secrets in .env files and add .env to .gitignore
• Validate and sanitize all user inputs before processing
• Use HTTPS in production and set security headers ([Link])
• Implement rate limiting to prevent brute-force attacks
• Use JWT or session-based authentication — never roll your own crypto
• Regularly update dependencies (npm audit) to patch vulnerabilities

// Password hashing with bcrypt

const bcrypt = require('bcryptjs');

[Link] Full-Stack Database Lecture Notes

 [Link] | SQLite | MongoDB — Lecture Notes

// Hash password before saving

const hashedPassword = await [Link](plainPassword, 12);

// Compare password on login

const isMatch = await [Link](inputPassword, hashedPassword);

4.6 Quick Reference Card

Task Code / Command
Start Node server node [Link] OR nodemon [Link]
Create [Link] npm init -y
Install dependencies npm install express mongoose better-sqlite3
SQLite: connect const db = new Database('db.sqlite3')
SQLite: run statement [Link]('SQL').run(params)
SQLite: get one row [Link]('SELECT...').get(param)
SQLite: get all rows [Link]('SELECT...').all()
Mongoose: connect await [Link](MONGO_URI)
Mongoose: create await [Link]({ ... })
Mongoose: find all await [Link]()
Mongoose: find one await [Link]({ field: value })
Mongoose: find by ID await [Link](id)
Mongoose: update await [Link](id, {$set:{}},
{new:true})
Mongoose: delete await [Link](id)
Express: GET route [Link]('/path', (req, res) => [Link](data))
Express: POST route [Link]('/path', (req, res) => { const body =
[Link] })
Express: route params [Link], [Link], [Link]
HTTP status codes 200 OK, 201 Created, 400 Bad Request, 404 Not
Found, 500 Error

🎓 Summary
[Link] provides the runtime and Express provides the framework. SQLite is perfect for structured
relational data with fixed schemas. MongoDB shines for flexible, document-based data. Both
integrate seamlessly with [Link] — choose based on your data model and scalability requirements.

[Link] Full-Stack Database Lecture Notes