HackWashU Databases Workshop

Welcome to the HackWashU Databases Workshop! This hands-on project teaches you practical database concepts, API integration, and modern web development through two complete projects:

1. Python Music Library - Build a SQLite database from the iTunes API
2. Supabase Todo App - Create a full-stack web app with authentication and Row Level Security

👉 Start Here: Complete Step-by-Step Workshop Guide 👈

The workshop guide includes:

• Detailed instructions for both projects
• Code walkthroughs with explanations
• SQL query examples
• Troubleshooting tips
• Challenge exercises
• Discussion questions with comprehensive answers

# Install dependencies
python -m pip install requests

# Generate your music database
python database_generator.py

# Explore the results
python explore_db.py

# Create a .env file with your Supabase credentials
# (See WORKSHOP_GUIDE.md for detailed setup)

# Start local server
python -m http.server 8000

# Open http://localhost:8000/index.html

• Audience: Beginners to intermediate developers
• Duration: 2-3 hours
• Format: Hands-on, code-along with live demos
• Prerequisites: Python 3.x, web browser, text editor

A relational SQLite database populated from the iTunes Search API:

• Artists, Albums, Songs tables with foreign key relationships
• Data deduplication using dictionary caching
• CSV exports for analysis
• Practice SQL queries on real music data

A full-stack web application with user authentication:

• PostgreSQL database in the cloud (Supabase)
• User authentication (sign up, login, logout)
• Row Level Security - users only see their own todos
• Full CRUD operations - create, read, update, delete
• Modern UI with active/completed task separation

├── WORKSHOP_GUIDE.md          # 📚 Complete step-by-step instructions
├── database_generator.py      # Python script: builds music library from iTunes API
├── explore_db.py              # Python script: analyze the music database
├── index.html                 # Supabase Todo app (HTML structure)
├── app.js                     # Todo app JavaScript (7-step organization)
├── styles.css                 # Todo app styles
├── .env                       # (create this) Your Supabase credentials
├── exports/                   # Generated by database_generator.py
│   ├── music_library.db       # SQLite database
│   ├── artists.csv            # Artist export
│   ├── albums.csv             # Album export
│   └── songs.csv              # Song export
└── LICENSE                    # MIT License

📖 Detailed Instructions in Workshop Guide →

• SQLite schema design (artists, albums, songs)
• Foreign keys and relationships
• iTunes Search API integration
• Data deduplication using dictionaries
• CSV exports with Python's csv module

# Install dependencies
python -m pip install requests

# Customize artists in database_generator.py, then:
python database_generator.py

# Explore your database
python explore_db.py

• exports/music_library.db - SQLite database
• exports/artists.csv - All artists
• exports/albums.csv - All albums
• exports/songs.csv - All songs

artists (id, name)
   ↓
albums (id, title, artist_id, genre, release_year)
   ↓
songs (id, title, album_id)

📖 Detailed Instructions in Workshop Guide →

• Supabase setup (PostgreSQL + Authentication)
• Row Level Security (RLS) for data privacy
• Full CRUD operations (Create, Read, Update, Delete)
• User authentication (sign up, login, logout)
• Client-side JavaScript with async/await
• Environment variables for security

1. Create Supabase account at supabase.com
2. Create todos table with RLS policies (see workshop guide)
3. Create .env file in project root:

   SUPABASE_URL=https://your-project.supabase.co
   SUPABASE_ANON_KEY=your-anon-key-here
   

4. Run local server:

   python -m http.server 8000

5. Open http://localhost:8000/index.html

• Step 1: Load configuration from .env
• Step 2: Initialize Supabase client
• Step 3: Get HTML element references
• Step 4: Helper functions (UI generation)
• Step 5: CRUD operations
• Step 6: Event listeners
• Step 7: Session management

• Workshop Guide - Complete step-by-step instructions
• Discussion Q&A - Detailed answers to common questions
• Challenge Exercises - Extend your projects
• Troubleshooting - Common issues and solutions

• SQLite Tutorial
• Supabase Documentation
• iTunes Search API
• PostgreSQL Tutorial

See the comprehensive troubleshooting section in the workshop guide for detailed solutions.

Quick fixes:

• .env not loading? Ensure you're running a local server (python -m http.server), not opening HTML directly
• RLS errors? Check your Supabase policies include user_id = auth.uid()
• Python errors? Install requests with python -m pip install requests
• Empty database? Check internet connection and artist names in database_generator.py

• Start with the Workshop Guide - it's designed for independent work
• Encourage exploration and experimentation
• Use explore_db.py to demonstrate SQL queries
• Show database inspection in Supabase Table Editor
• Discuss real-world applications: e-commerce, social media, analytics

• Why use SQLite vs PostgreSQL?
• When to use Row Level Security?
• API rate limiting and error handling
• Database normalization and relationships
• Frontend vs backend security

• Add priority levels or due dates to todos
• Create a playlist feature for the music library
• Implement real-time updates with Supabase subscriptions
• Add search/filter functionality
• Deploy the todo app to production

This project is licensed under the MIT License.