Databases

Overview

In this vitamin, you'll get hands-on experience with two different types of databases and connect both to an Express.js backend server.

Relational Databases vs Document Databases - What's the difference?

Relational databases (like MySQL) - Store data in tables with rows and columns. Best for structured data with clear relationships (e.g., users, orders, products). You query them using SQL (Structured Query Language).
Document databases (like MongoDB) - Store data as JSON-like documents. Best for flexible data or when your schema might change frequently. These are often called NoSQL databases because they don't use SQL.

Learning Objectives

Install and set up MySQL and MongoDB locally
Create databases, tables/collections, and insert data
Write basic queries to retrieve data
Use WHERE clauses to filter SQL queries
Connect both databases to an Express.js backend
Create GET, POST, and DELETE API endpoints for each database

Part 1: MySQL (Relational Database)

Step 1: Install MySQL

Download and install MySQL Community Server
Download and install MySQL Workbench (the GUI application)

Important

When installing MySQL Community Server, you'll be asked to create a root password. Remember this password! You'll need it to connect to your database.

Step 2: Connect to MySQL

Open MySQL Workbench
Create a new connection and enter your password
Test the connection - you should see:

Success

Successfully made the MySQL connection

Host: localhost
Port: 3306
User: root
SSL: enabled

Step 3: Create Database and Table

Create a new database called company_db:

CREATE DATABASE company_db;
USE company_db;

Create a table called employees with the following columns:
- id - AUTO INCREMENT PRIMARY KEY
  - AUTO INCREMENT means the database automatically assigns a unique number (1, 2, 3...) to each new row
  - PRIMARY KEY means this column uniquely identifies each row (no duplicates allowed)
- name - VARCHAR(255), NOT NULL
  - VARCHAR(255) means a variable-length string that can hold up to 255 characters
  - NOT NULL means this field is required (can't be empty)
- position - VARCHAR(255), NOT NULL
- salary - DECIMAL(10,2), NOT NULL
  - DECIMAL(10,2) means a number with up to 10 total digits and 2 decimal places (e.g., 75000.00)
Insert these three employees:
- Alice, Developer, salary 75000
- Bob, Designer, salary 65000
- Charlie, Manager, salary 85000

Step 4: Query the Data

Run this query to verify your data:

SELECT * FROM employees;

Expected output:

+----+---------+-----------+----------+
| id | name    | position  | salary   |
+----+---------+-----------+----------+
|  1 | Alice   | Developer | 75000.00 |
|  2 | Bob     | Designer  | 65000.00 |
|  3 | Charlie | Manager   | 85000.00 |
+----+---------+-----------+----------+

Step 5: Filter with WHERE Clause

The WHERE clause lets you filter results based on conditions. Try these queries:

Find employees with salary greater than 70000:

SELECT * FROM employees WHERE salary > 70000;

Expected output:

+----+---------+-----------+----------+
| id | name    | position  | salary   |
+----+---------+-----------+----------+
|  1 | Alice   | Developer | 75000.00 |
|  3 | Charlie | Manager   | 85000.00 |
+----+---------+-----------+----------+

Find all Developers:

SELECT * FROM employees WHERE position = 'Developer';

Expected output:

+----+-------+-----------+----------+
| id | name  | position  | salary   |
+----+-------+-----------+----------+
|  1 | Alice | Developer | 75000.00 |
+----+-------+-----------+----------+

Part 2: MongoDB (Document Database)

Step 1: Install MongoDB

Download and install MongoDB Community Server
Download and install MongoDB Compass (the GUI application - makes it much easier!)

Step 2: Connect to MongoDB

Open MongoDB Compass
Connect to your local MongoDB server: mongodb://localhost:27017

Step 3: Create Database and Collection

Create a new database called companyDB
Create a collection called projects

Step 4: Insert Documents

Add the following documents to the projects collection:

{ "name": "Project A", "budget": 50000 }
{ "name": "Project B", "budget": 30000 }
{ "name": "Project C", "budget": 20000 }

Step 5: Query the Data

In MongoDB Compass, click on the projects collection
Click the Find button (or you'll see the documents listed automatically)

You should see your documents:

[
  { "_id": ObjectId("..."), "name": "Project A", "budget": 50000 },
  { "_id": ObjectId("..."), "name": "Project B", "budget": 30000 },
  { "_id": ObjectId("..."), "name": "Project C", "budget": 20000 }
]

Step 6: Filter the Data

In MongoDB, you filter using JSON query objects instead of SQL's WHERE clause.

In the filter bar at the top of MongoDB Compass, try these queries:

Find projects with budget greater than 25000:

{ "budget": { "$gt": 25000 } }

Expected output:

[
  { "_id": ObjectId("..."), "name": "Project A", "budget": 50000 },
  { "_id": ObjectId("..."), "name": "Project B", "budget": 30000 }
]

Find project with name "Project A":

{ "name": "Project A" }

Expected output:

[
  { "_id": ObjectId("..."), "name": "Project A", "budget": 50000 }
]

Alternative: Using MongoDB Shell (mongosh)

You can also do everything above using the command line with mongosh. Open your terminal and run:

mongosh

Connect to your database:

use companyDB

Insert documents:

db.projects.insertMany([
  { name: "Project A", budget: 50000 },
  { name: "Project B", budget: 30000 },
  { name: "Project C", budget: 20000 }
])

Query all documents:

db.projects.find()

Filter - budget greater than 25000:

db.projects.find({ budget: { $gt: 25000 } })

Filter - find by name:

db.projects.find({ name: "Project A" })

Exit mongosh:

exit

Part 3: Connect with Express.js Backend

Now let's create a backend server that can fetch data from both databases!

Step 1: Set Up Your Project

Create a new folder for this project and open it in VS Code
Initialize the project and install dependencies:

npm init -y
npm install express mysql2 mongodb mongoose

What are these packages?

express - Web framework for creating our server
mysql2 - MySQL driver for Node.js
mongodb - MongoDB driver for Node.js
mongoose - ODM (Object Data Modeling) library for MongoDB that makes it easier to work with

Step 2: Create the Backend Server

Create a file called server.js and copy the code below. Fill in the TODOs!

// LIBRARIES
const express = require('express');
const app = express();
const mysql = require('mysql2');
const mongoose = require('mongoose');

// ============ SQL CONNECTION ============
// TODO: Create MySQL connection using mysql.createConnection()
// You'll need: host, user, password, and database
const connection = // YOUR CODE HERE

// MySQL Connection Verification
function verifyMySQLConnection() {
    connection.connect(function(err) {
        if (err) {
            console.error('Error connecting to MySQL: ' + err.stack);
            return;
        }
        console.log('MySQL connected as id ' + connection.threadId);
    });
}

// ============ MONGODB CONNECTION ============
// TODO: Connect to MongoDB using mongoose.connect()
// Your connection string should point to localhost and the companyDB database


// TODO: Create a Mongoose Schema for projects with name (String) and budget (Number)
const ProjectSchema = // YOUR CODE HERE

// TODO: Create a Mongoose Model called 'Project' using the schema
const ProjectModel = // YOUR CODE HERE

// MongoDB Connection Verification
mongoose.connection.on('connected', () => {
    console.log('MongoDB connected successfully');
});

mongoose.connection.on('error', (err) => {
    console.error('MongoDB connection error:', err);
});

// ============ ENDPOINTS ============
// Middleware to parse JSON request bodies
app.use(express.json());

// ------ MONGODB ENDPOINTS ------

// TODO: GET all projects from MongoDB
// Use ProjectModel.find({}) and return as JSON
app.get('/projects', async (req, res) => {
    // YOUR CODE HERE
});

// TODO: POST a new project to MongoDB
// Create a new ProjectModel with req.body, save it, and return with status 201
app.post('/projects', async (req, res) => {
    // YOUR CODE HERE
});

// TODO: DELETE a project from MongoDB by id
// Use req.params.id and ProjectModel.findByIdAndDelete()
// Return status 200 on success
app.delete('/projects/:id', async (req, res) => {
    // YOUR CODE HERE
});

// ------ MYSQL ENDPOINTS ------
// IMPORTANT: Use ? placeholders for user input instead of string concatenation!
// Example: connection.query('SELECT * FROM users WHERE id = ?', [userId], callback)
// This prevents SQL injection attacks where malicious users could insert
// harmful SQL code through input fields and damage your database.

// TODO: GET all employees from MySQL
// Use connection.query() with SELECT * FROM employees
// Return the results as JSON
app.get('/employees', function (req, res) {
    // YOUR CODE HERE
});

// TODO: POST a new employee to MySQL
// Use connection.query() with INSERT INTO
// Return the new employee's id with status 201
app.post('/employees', function (req, res) {
    // YOUR CODE HERE
});

// TODO: DELETE an employee from MySQL by id
// Use req.params.id and DELETE FROM with a WHERE clause
// Return status 200 on success
app.delete('/employees/:id', function (req, res) {
    // YOUR CODE HERE
});

// ============ START SERVER ============
app.listen(3000, function () {
    console.log('Server is running on http://localhost:3000');
    verifyMySQLConnection();
});

Step 3: Run the Server

node server.js

You should see:

Server is running on http://localhost:3000
MySQL connected as id X
MongoDB connected successfully

Step 4: Test the Endpoints

Use your browser for GET requests, and Postman for POST/DELETE requests.

GET endpoints (browser or Postman):

GET /employees - Expected: list of employees from MySQL
GET /projects - Expected: list of projects from MongoDB

POST endpoints (Postman only):

POST /projects - Send a JSON body with name and budget. Should return status 201.
POST /employees - Send a JSON body with name, position, and salary. Should return status 201.

DELETE endpoints (Postman only):

DELETE /projects/:id - Replace :id with an actual project _id from MongoDB. Should return status 200.
DELETE /employees/:id - Replace :id with an employee id (e.g., 1). Should return status 200.

Verify your endpoints work: Check the GET endpoints in your browser to confirm items were added or removed!

Why Databases?

Notice that when you restart your server, your data is still there! Unlike storing data in a variable (which disappears when the server restarts), databases persist your data permanently. This is why real applications use databases - your users' data needs to survive server restarts and updates.

Troubleshooting

MySQL connection error? Double-check your password in the connection config
MongoDB connection error? Make sure MongoDB is running (mongod in terminal or check Services)
Port 3000 already in use? Kill the process or change the port number

Submission

Zip your project folder and submit the zip file to Gradescope.

Your zip file should include:

server.js (with TODOs completed)
package.json
package-lock.json

Do not include node_modules/.

tip

We can regenerate node_modules/ by running npm install using your package.json.

Overview​

Learning Objectives​

Part 1: MySQL (Relational Database)​

Step 1: Install MySQL​

Step 2: Connect to MySQL​

Step 3: Create Database and Table​

Step 4: Query the Data​

Step 5: Filter with WHERE Clause​

Part 2: MongoDB (Document Database)​

Step 1: Install MongoDB​

Step 2: Connect to MongoDB​

Step 3: Create Database and Collection​

Step 4: Insert Documents​

Step 5: Query the Data​

Step 6: Filter the Data​

Alternative: Using MongoDB Shell (mongosh)​

Part 3: Connect with Express.js Backend​

Step 1: Set Up Your Project​

Step 2: Create the Backend Server​

Step 3: Run the Server​

Step 4: Test the Endpoints​

Submission​

Overview

Learning Objectives

Part 1: MySQL (Relational Database)

Step 1: Install MySQL

Step 2: Connect to MySQL

Step 3: Create Database and Table

Step 4: Query the Data

Step 5: Filter with WHERE Clause

Part 2: MongoDB (Document Database)

Step 1: Install MongoDB

Step 2: Connect to MongoDB

Step 3: Create Database and Collection

Step 4: Insert Documents

Step 5: Query the Data

Step 6: Filter the Data

Alternative: Using MongoDB Shell (mongosh)

Part 3: Connect with Express.js Backend

Step 1: Set Up Your Project

Step 2: Create the Backend Server

Step 3: Run the Server

Step 4: Test the Endpoints

Submission