Skip to content

README.md

Latest commit

 

History

History
433 lines (352 loc) · 10.4 KB

README.md

File metadata and controls

433 lines (352 loc) · 10.4 KB

PostgreSQL Hack@Brown Starter Pack

Prerequisites

  • Node.js installed (v14 or higher)
  • PostgreSQL database (choose one):
    • Cloud (recommended): Supabase/Railway/Neon account (free tier available)
    • Local: Docker installed OR PostgreSQL installed locally

Project Structure

postgresql/
├── .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         # PostgreSQL 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 postgresql folder
cd postgresql/

# 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 postgresql/ starter folder
cp -r path/to/database-starter/postgresql/* .

Step 2: Install Dependencies

npm install

Step 3: Configure Database

Cloud Setup (Recommended - No installation needed):

Choose one provider:

After creating your database:

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

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

Local Setup (Docker):

# Start PostgreSQL with Docker
docker run -d \
  --name postgres-Hack@Brown \
  -e POSTGRES_PASSWORD=Hack@Brown123 \
  -e POSTGRES_DB=Hack@Brown \
  -e POSTGRES_USER=Hack@Brown \
  -p 5432:5432 \
  postgres:15

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

Step 4: Test Connection

npm run test-connection

You should see:

 PostgreSQL Connected Successfully!
 Database: Hack@Brown
 Server Time: [current time]
 Version: PostgreSQL 15

Step 5: Initialize Database

npm run db:setup

This creates:

  • 3 tables (users, projects, project_collaborators)
  • Indexes & constraints
  • Full-text search indexes
  • Automatic triggers for 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:

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
POST /api/projects/:id/view Increment views

Test Your API

Browser Testing

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", "PostgreSQL"]
  }'

# 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 with PostgreSQL",
    "user_id": 1,
    "technologies": ["postgresql", "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 (SERIAL PRIMARY KEY)
  • username (VARCHAR, UNIQUE, NOT NULL)
  • email (VARCHAR, UNIQUE, NOT NULL)
  • full_name (VARCHAR)
  • bio (TEXT)
  • avatar_url (VARCHAR)
  • skills (JSONB array) - e.g., ["JavaScript", "React"]
  • social_links (JSONB object) - e.g., {"github": "url"}
  • is_active (BOOLEAN, default TRUE)
  • created_at, updated_at (TIMESTAMP)

Projects Table

  • id (SERIAL PRIMARY KEY)
  • title (VARCHAR, NOT NULL)
  • description (TEXT, NOT NULL)
  • user_id (INTEGER, FOREIGN KEY → users)
  • technologies (JSONB array)
  • tags (JSONB array)
  • repository_url, demo_url (VARCHAR)
  • status (ENUM: planning, in-progress, completed, on-hold, archived)
  • likes, views (INTEGER)
  • 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

  1. Edit database/schema.sql:
CREATE TABLE teams (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
  1. Create models/teamModel.js with CRUD operations
  2. Create controller and routes
  3. Run: npm run db:reset then npm run db:setup

Add Authentication

npm install jsonwebtoken bcrypt

# Then add to your User schema:
# - password_hash field
# - 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
# - PostgreSQL not running (if local)
# - Firewall blocking connection
# - Wrong credentials

Port Already in Use

# In .env file, change:
PORT=3001

Reset Database

# Drop and recreate tables (removes all data)
npm run db:reset

# Recreate tables and add sample data
npm run db:setup

psql Command Not Found

If npm run db:setup fails:

# Install PostgreSQL client tools:
# macOS: brew install postgresql
# Ubuntu: sudo apt-get install postgresql-client
# Windows: Install from postgresql.org

# Or run SQL files manually:
psql $DATABASE_URL -f database/schema.sql
psql $DATABASE_URL -f database/seed.sql

PostgreSQL Advantages

What makes PostgreSQL special:

  • JSONB support - Flexible schema with SQL power
  • Full-text search - Built-in (no Elasticsearch needed)
  • Array types - Store arrays natively
  • Window functions - Advanced analytics
  • CTEs - Complex queries made simple
  • Strong ACID - Data integrity guaranteed
  • Advanced indexing - GiST, GIN, BRIN

Example: Query JSONB data

-- Find users with a specific skill
SELECT * FROM users
WHERE skills @> '["JavaScript"]';

-- Get all React projects
SELECT * FROM projects
WHERE technologies @> '["react"]';

-- Find users with GitHub links
SELECT * FROM users
WHERE social_links ? 'github';

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)

Resources

Advanced PostgreSQL Features

Window Functions

-- Rank projects by likes within each status category
SELECT
  title,
  status,
  likes,
  RANK() OVER (PARTITION BY status ORDER BY likes DESC) as rank_in_category
FROM projects;

Common Table Expressions (CTEs)

-- Get users with the most projects
WITH project_counts AS (
  SELECT user_id, COUNT(*) as project_count
  FROM projects
  GROUP BY user_id
)
SELECT u.username, pc.project_count
FROM users u
JOIN project_counts pc ON u.id = pc.user_id
ORDER BY pc.project_count DESC;

JSON Aggregation

-- Get user with all their projects as a JSON array
SELECT
  u.*,
  json_agg(json_build_object(
    'id', p.id,
    'title', p.title,
    'status', p.status
  )) as projects
FROM users u
LEFT JOIN projects p ON u.id = p.user_id
WHERE u.id = 1
GROUP BY u.id;