MySQL Hack@Brown Starter Pack

Prerequisites

Node.js installed (v18 or higher)
MySQL database (choose one):
- Cloud (recommended): PlanetScale/Railway account (free tier available)
- Local: Docker installed OR MySQL installed locally

Project Structure

mysql/
├── .env                     # Your config (create this!)
├── .env.example             # Environment template
├── .gitignore               # Git ignore file
├── package.json             # Dependencies
├── server.js                # Express server setup
├── database/
│   ├── schema.sql          # Database schema
│   └── seed.sql            # Sample data
├── config/
│   └── database.js         # MySQL connection
├── models/
│   ├── userModel.js        # User operations
│   └── projectModel.js     # Project operations
├── routes/
│   ├── users.js            # User API routes
│   └── projects.js         # Project API routes
├── controllers/
│   ├── userController.js   # User business logic
│   └── projectController.js # Project business logic
├── middleware/
│   └── errorHandler.js     # Error handling
└── scripts/
    └── testConnection.js   # Connection test

Quick Start

Step 1: Set Up Project Structure

Option A: Use this starter pack directly

# Navigate to the mysql folder
cd mysql/

# The project structure is already set up!
# All files are in their correct locations

Option B: Copy to your own project

# Navigate to where you want your project
cd ~/your-workspace

# Create project directory
mkdir my-hackathon-project
cd my-hackathon-project

# Copy all files from the mysql/ starter folder
cp -r path/to/database-starter/mysql/* .

Step 2: Install Dependencies

npm install

Step 3: Configure Database

Cloud Setup (Recommended - No installation needed):

Choose one provider:

PlanetScale: https://planetscale.com (Serverless MySQL, free tier)
Railway: https://railway.app (Easy MySQL hosting)

After creating your database:

# Copy the example environment file
cp .env.example .env

# Edit .env and add your connection string
# DATABASE_URL=mysql://user:password@host:3306/database

Local Setup (Docker):

# Start MySQL with Docker
docker run -d \
  --name mysql-Hack@Brown \
  -e MYSQL_ROOT_PASSWORD=Hack@Brown123 \
  -e MYSQL_DATABASE=Hack@Brown \
  -e MYSQL_USER=Hack@Brown \
  -e MYSQL_PASSWORD=Hack@Brown123 \
  -p 3306:3306 \
  mysql:8.0

# Create .env file
echo "DATABASE_URL=mysql://Hack@Brown:Hack@Brown123@localhost:3306/Hack@Brown" > .env
echo "PORT=3000" >> .env

Step 4: Test Connection

npm run test-connection

You should see:

 MySQL Connected Successfully!
 Database: Hack@Brown
 Server Time: [current time]
 Version: 8.0

Step 5: Initialize Database

npm run db:setup

This creates:

3 tables (users, projects, project_collaborators)
Indexes for performance
Triggers for automatic timestamps
5 sample users
8 sample projects

Step 6: Start Server

npm start

Server runs at: http://localhost:3000

Step 7: Test the API

Open your browser to:

http://localhost:3000 - API info
http://localhost:3000/api/users - See all users
http://localhost:3000/api/projects - See all projects

API Endpoints

Users

Method	Endpoint	Description
GET	`/api/users`	Get all users
GET	`/api/users/:id`	Get user + their projects
POST	`/api/users`	Create new user
PUT	`/api/users/:id`	Update user
DELETE	`/api/users/:id`	Delete user
GET	`/api/users/search/:query`	Search users by name/email

Projects

Method	Endpoint	Description
GET	`/api/projects`	Get all projects
GET	`/api/projects?status=completed`	Filter by status
GET	`/api/projects?technology=react`	Filter by technology
GET	`/api/projects/:id`	Get specific project
GET	`/api/projects/user/:userId`	Get user's projects
GET	`/api/projects/trending`	Get trending projects
GET	`/api/projects/search/:query`	Full-text search
POST	`/api/projects`	Create new project
PUT	`/api/projects/:id`	Update project
DELETE	`/api/projects/:id`	Delete project
POST	`/api/projects/:id/like`	Like a project

Test Your API

Browser Testing

http://localhost:3000 - API info
http://localhost:3000/health - Health check
http://localhost:3000/api/users - All users
http://localhost:3000/api/projects - All projects

curl Examples

# Get all users
curl http://localhost:3000/api/users

# Create a user
curl -X POST http://localhost:3000/api/users \
  -H "Content-Type: application/json" \
  -d '{
    "username": "hacker2024",
    "email": "hack@2024.com",
    "full_name": "Hack@Brown Hacker",
    "skills": ["JavaScript", "MySQL"]
  }'

# Create a project
curl -X POST http://localhost:3000/api/projects \
  -H "Content-Type: application/json" \
  -d '{
    "title": "My Awesome Project",
    "description": "Building something cool",
    "user_id": 1,
    "technologies": ["mysql", "express", "react"],
    "status": "in-progress"
  }'

# Full-text search for projects
curl http://localhost:3000/api/projects/search/chatbot

# Like a project
curl -X POST http://localhost:3000/api/projects/1/like

# Get user with all their projects
curl http://localhost:3000/api/users/1

Database Schema

Users Table

id (INT AUTO_INCREMENT PRIMARY KEY)
username (VARCHAR, UNIQUE, NOT NULL)
email (VARCHAR, UNIQUE, NOT NULL)
full_name (VARCHAR)
bio (TEXT)
avatar_url (VARCHAR)
skills (JSON array) - e.g., ["JavaScript", "React"]
social_links (JSON object) - e.g., {"github": "url"}
is_active (BOOLEAN, default TRUE)
created_at, updated_at (TIMESTAMP)

Projects Table

id (INT AUTO_INCREMENT PRIMARY KEY)
title (VARCHAR, NOT NULL)
description (TEXT, NOT NULL)
user_id (INT, FOREIGN KEY → users)
technologies (JSON array)
tags (JSON array)
repository_url, demo_url (VARCHAR)
status (ENUM: planning, in-progress, completed, on-hold, archived)
likes, views (INT)
is_public (BOOLEAN)
created_at, updated_at, completed_at (TIMESTAMP)

Project Collaborators Table

project_id (FOREIGN KEY → projects)
user_id (FOREIGN KEY → users)
role (VARCHAR, default 'developer')
joined_at (TIMESTAMP)
PRIMARY KEY: (project_id, user_id)

Customization

Add a New Table

Edit database/schema.sql:

CREATE TABLE teams (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Create models/teamModel.js with CRUD operations
Create controller and routes
Run: npm run db:setup

Add Authentication

npm install jsonwebtoken bcrypt

# Then add to your User schema:
# - password_hash field (VARCHAR 255)
# - bcrypt hashing on user creation
# - login endpoint that returns JWT
# - auth middleware to protect routes

Troubleshooting

Connection Failed

# Test your connection
npm run test-connection

# Check your .env file
cat .env

# Common issues:
# - Wrong DATABASE_URL format
# - MySQL not running (if local)
# - Firewall blocking connection
# - Wrong credentials
# - PlanetScale: ensure SSL is configured

Port Already in Use

# In .env file, change:
PORT=3001

Reset Database

# This will drop and recreate tables (removes all data!)
npm run db:setup

mysql Command Not Found

If npm run db:setup fails:

# Install MySQL client tools:
# macOS: brew install mysql-client
# Ubuntu: sudo apt-get install mysql-client
# Windows: Install from mysql.com

# Or use Node.js to run the SQL:
node -e "require('dotenv').config(); const mysql = require('mysql2'); const fs = require('fs'); const conn = mysql.createConnection(process.env.DATABASE_URL); conn.query(fs.readFileSync('database/schema.sql', 'utf8'), console.log); conn.end();"

MySQL Advantages

What makes MySQL special:

Extremely popular - Largest community, most tutorials
Simple to use - Great for beginners
Fast reads - Optimized for read-heavy workloads
JSON support - Store flexible data (MySQL 5.7+)
Reliable - Battle-tested in production
Wide hosting - Available on every platform
LAMP/LEMP stack - Perfect for web applications

Example: Query JSON data

-- Find users with a specific skill
SELECT * FROM users
WHERE JSON_CONTAINS(skills, '"JavaScript"');

-- Get all React projects
SELECT * FROM projects
WHERE JSON_CONTAINS(technologies, '"react"');

-- Extract JSON field
SELECT username, JSON_EXTRACT(social_links, '$.github') as github
FROM users;

Sample Data

After running npm run db:setup, you'll have:

5 Users:

johndoe, janesmith, alexchen, sarahwilson, mikebrown

8 Projects:

E-Commerce Platform (completed)
Task Management Dashboard (completed)
AI Chatbot Assistant (in-progress)
Microservices Blog Platform (in-progress)
Fitness Tracker Mobile App (completed)
Weather Forecast Dashboard (completed)
Portfolio Template Generator (completed)
Code Snippet Manager (planning)

Best Practices

Security

Never commit .env (already in .gitignore)
Use parameterized queries (implemented with ?)
Hash passwords with bcrypt before storing
Use SSL for production databases
Validate all user input

Performance

Indexes on foreign keys (already created)
Full-text search indexes (FULLTEXT indexes created)
Connection pooling (configured in database.js)
Use JSON columns for flexible data
EXPLAIN your slow queries

Query Optimization Example:

-- Test query performance
EXPLAIN SELECT * FROM projects WHERE user_id = 1;

-- Add custom indexes as needed
CREATE INDEX idx_custom ON table_name(column_name);

Deployment

Heroku

# Install Heroku CLI, then:
heroku create your-app-name
heroku addons:create jawsdb

# Get database URL
heroku config:get JAWSDB_URL

git push heroku main

# Run setup
heroku run npm run db:setup

Railway

Push code to GitHub
Connect repo to Railway
Add MySQL service
Set environment variables
Deploy automatically

PlanetScale

Create database on PlanetScale
Get connection string
Deploy your API to Vercel/Railway/Heroku
Set DATABASE_URL environment variable

Resources

Advanced MySQL Features

Full-Text Search

-- Full-text search (already indexed)
SELECT * FROM projects
WHERE MATCH(title, description) AGAINST('chatbot' IN NATURAL LANGUAGE MODE);

-- Boolean search
SELECT * FROM projects
WHERE MATCH(title, description) AGAINST('+machine +learning' IN BOOLEAN MODE);

JSON Functions

-- Extract JSON array length
SELECT username, JSON_LENGTH(skills) as skill_count
FROM users;

-- Search within JSON array
SELECT * FROM projects
WHERE JSON_SEARCH(technologies, 'one', 'react') IS NOT NULL;

Triggers (Already Created)

-- Auto-set completed_at when status changes to 'completed'
-- This is already in schema.sql!
DELIMITER //
CREATE TRIGGER set_completed_at_trigger
BEFORE UPDATE ON projects
FOR EACH ROW
BEGIN
    IF NEW.status = 'completed' AND OLD.status != 'completed' THEN
        SET NEW.completed_at = CURRENT_TIMESTAMP;
    END IF;
END//
DELIMITER ;

FilesExpand file tree

README.md

Latest commit

History