Hack@Brown Database Starter Kit

This document walks you through the steps to get your database running.

Part 1: Before You Begin

What You'll Need

Check if you have Node.js
```
node --version
```
- If you see v18.0.0 or higher: Great!
- If you see "command not found": Install from https://nodejs.org
Choose your database
- MongoDB - Best for flexible schemas and rapid prototyping (document-based, NoSQL)
- PostgreSQL - Recommended for most projects (advanced features, JSONB support)
- MySQL - Good for traditional web apps (simple, widely supported)
Not sure? Use PostgreSQL.
Choose cloud or local
- Cloud (Recommended): No installation, works anywhere, free tier available
- Local: Runs on your computer, requires Docker

Part 2: PostgreSQL Setup (10 minutes)

Step 1: Create Your Project Folder

Open your terminal and type:

# Go to your Documents folder (or wherever you want your project)
cd ~/Documents

# Create a folder for your project
mkdir my-hack-brown-project

# Go into that folder
cd my-hack-brown-project

# Verify you're in the right place
pwd

You should see something like: /Users/yourname/Documents/my-hack-brown-project

Step 2: Create the Folder Structure

# Create all the folders you'll need
mkdir -p database config models routes controllers middleware scripts

# Verify the folders were created
ls -la

You should see: database, config, models, routes, controllers, middleware, scripts

Step 3: Copy Files from the Starter Kit

IMPORTANT: You need to copy files from this starter kit to your project.

From the database-starter/postgresql/ folder, copy these files:

Copy to your project root:
- package.json → my-hack-brown-project/package.json
- .env.example → my-hack-brown-project/.env.example
- .gitignore → my-hack-brown-project/.gitignore
Copy to config/ folder:
- database.js → my-hack-brown-project/config/database.js
Copy to models/ folder:
- userModel.js → my-hack-brown-project/models/userModel.js
- projectModel.js → my-hack-brown-project/models/projectModel.js
Copy to database/ folder:
- schema.sql → my-hack-brown-project/database/schema.sql
- seed.sql → my-hack-brown-project/database/seed.sql
Copy to scripts/ folder:
- testConnection.js → my-hack-brown-project/scripts/testConnection.js

Verify you copied everything:

ls -la config/
ls -la models/
ls -la database/
ls -la scripts/
ls -la

You should see the files listed above.

Step 4: Install Dependencies

npm install

What you'll see:

Lots of text scrolling by
"added X packages" at the end
A new node_modules/ folder appears

If you see an error:

Make sure you copied package.json to your project root
Make sure you're in the project folder (pwd to check)

Step 5: Set Up Your Database

OPTION A: Cloud Database (Recommended)

Go to Supabase
- Open https://supabase.com in your browser
- Click "Start your project"
- Sign up with GitHub or email
Create a Project
- Click "New project"
- Choose a name: hackatbrown-yourname
- Choose a password
- Choose a region (closest to you)
- Click "Create new project"
- This takes 1-2 minutes. You'll see "Setting up project..."
Get Your Connection String
- Once ready, click "Connect" (top right)
- Click "App Frameworks"
- Under "Node.js", copy the connection string
- It looks like: postgresql://postgres:[YOUR-PASSWORD]@db.abc123.supabase.co:5432/postgres

Save It to Your Project

# Create your .env file
cp .env.example .env

# Open .env in your text editor (VS Code, Sublime, nano, etc.)
nano .env

Replace the DATABASE_URL line with YOUR connection string:

DATABASE_URL=postgresql://postgres:[YOUR-PASSWORD]@db.abc123.supabase.co:5432/postgres
PORT=3000

Save and close (in nano: Ctrl+X, then Y, then Enter)

OPTION B: Local Database with Docker

Make sure Docker is running
```
docker --version
```
If you see an error, install Docker Desktop from https://docker.com

Start PostgreSQL

docker run -d \
  --name postgres-hackatbrown \
  -e POSTGRES_PASSWORD=hackatbrown123 \
  -e POSTGRES_DB=hackatbrown \
  -e POSTGRES_USER=hackatbrown \
  -p 5432:5432 \
  postgres:15

Create .env file

cp .env.example .env
echo "DATABASE_URL=postgresql://hackatbrown:hackatbrown123@localhost:5432/hackatbrown" > .env
echo "PORT=3000" >> .env

Step 6: Test Your Connection

npm run test-connection

What you SHOULD see:

Testing PostgreSQL connection...

[+] PostgreSQL Connected Successfully!
Database: hackatbrown (or your database name)
Server Time: 2024-11-04 ...
Version: PostgreSQL 15

[+] Connection test passed!
You can now run: npm run db:setup

If you see "Connection test failed":

Check your .env file exists: cat .env
Check the DATABASE_URL is correct
For cloud: Wait 1 more minute (database might still be starting)
For local: Check Docker is running: docker ps

Step 7: Create Your Tables

npm run db:setup

What you SHOULD see:

NOTICE:  [+] Database schema created successfully!
NOTICE:  Tables: users, projects, project_collaborators
NOTICE:  Indexes created for optimal performance
NOTICE:  Triggers set up for automatic updates
...
NOTICE:  [+] Sample data inserted successfully!
NOTICE:  Users created: 5
NOTICE:  Projects created: 8

This command:

Creates 3 tables
Adds indexes for speed
Adds 5 sample users
Adds 8 sample projects

If you see an error:

Make sure npm run test-connection worked first
Check that schema.sql and seed.sql are in the database/ folder

Step 8: Create a Simple Server File

You need a server.js file to run the API. Create it:

nano server.js

Paste this code:

// Load environment variables from .env file
require('dotenv').config();

// Import dependencies
const express = require('express');
const cors = require('cors');
const { testConnection } = require('./config/database');

// Create Express app
const app = express();

// Middleware
app.use(cors());
app.use(express.json());

// Welcome route
app.get('/', (req, res) => {
  res.json({
    message: 'Hack@Brown Database API',
    endpoints: {
      users: '/api/users',
      projects: '/api/projects'
    }
  });
});

// Health check
app.get('/health', async (req, res) => {
  const dbConnected = await testConnection();
  res.json({
    status: 'ok',
    database: dbConnected ? 'connected' : 'disconnected'
  });
});

// Example user routes (you'll expand this)
app.get('/api/users', async (req, res) => {
  try {
    const userModel = require('./models/userModel');
    const users = await userModel.getAllUsers();
    res.json(users);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Example project routes (you'll expand this)
app.get('/api/projects', async (req, res) => {
  try {
    const projectModel = require('./models/projectModel');
    const projects = await projectModel.getAllProjects();
    res.json(projects);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Start server
const PORT = process.env.PORT || 3000;
app.listen(PORT, async () => {
  console.log(`Server running on port ${PORT}`);
  await testConnection();
});

Save and close (Ctrl+X, Y, Enter)

Step 9: Start Your Server

npm start

What you SHOULD see:

Server running on port 3000
[+] PostgreSQL Connected Successfully!
Database: hackatbrown

If port 3000 is in use:

Edit .env: PORT=3001
Run npm start again

Step 10: Test It Works!

Open your web browser and visit:

http://localhost:3000
- You should see: {"message":"Hack@Brown Database API",...}
http://localhost:3000/api/users
- You should see JSON with 5 users (johndoe, janesmith, etc.)
http://localhost:3000/api/projects
- You should see JSON with 8 projects

SUCCESS! Your database is working!

Part 3: Understanding What You Built

Your Project Structure

my-hack-at-brown-project/
├── .env                      # YOUR database password (never commit!)
├── .gitignore                # Tells git to ignore .env
├── package.json              # Lists dependencies
├── server.js                 # YOUR API server
├── node_modules/             # Installed packages (never edit)
│
├── database/
│   ├── schema.sql           # Creates tables (you ran this once)
│   └── seed.sql             # Sample data (you ran this once)
│
├── config/
│   └── database.js          # Database connection code
│
├── models/
│   ├── userModel.js         # Functions to work with users
│   └── projectModel.js      # Functions to work with projects
│
└── (you can add:)
    ├── routes/              # Organize your API endpoints
    ├── controllers/         # Business logic
    └── middleware/          # Error handling, auth, etc.

How It Works

server.js - Your main file
- Loads environment variables (.env)
- Sets up Express server
- Defines API endpoints
- Starts listening on port 3000
config/database.js - Database connection
- Connects to PostgreSQL
- Exports a query() function
- You use this to run SQL
models/userModel.js - User operations
- getAllUsers() - Get all users
- getUserById(id) - Get one user
- createUser(data) - Add a user
- updateUser(id, data) - Change a user
- deleteUser(id) - Remove a user
- Each function uses query() from database.js
models/projectModel.js - Project operations
- Similar to userModel but for projects

The Data Flow

Browser Request
    ↓
server.js (endpoint: /api/users)
    ↓
models/userModel.js (calls getAllUsers())
    ↓
config/database.js (runs SQL query)
    ↓
PostgreSQL Database
    ↓
Returns data back up the chain
    ↓
Browser sees JSON response

Part 4: Next Steps

Test Your API with curl

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

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

# Create a new user
curl -X POST http://localhost:3000/api/users \
  -H "Content-Type: application/json" \
  -d '{"username":"testuser","email":"test@test.com","full_name":"Test User"}'

Add More Endpoints

Edit server.js to add more routes:

// Get a single user by ID
app.get('/api/users/:id', async (req, res) => {
  try {
    const userModel = require('./models/userModel');
    const user = await userModel.getUserById(req.params.id);
    if (!user) {
      return res.status(404).json({ error: 'User not found' });
    }
    res.json(user);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Create a new user
app.post('/api/users', async (req, res) => {
  try {
    const userModel = require('./models/userModel');
    const newUser = await userModel.createUser(req.body);
    res.status(201).json(newUser);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

Organize Your Code

As your API grows, organize it better:

Create route files (routes/users.js, routes/projects.js)
Create controllers (controllers/userController.js)
Add middleware (middleware/auth.js for authentication)

Part 5: Common Issues & Solutions

Issue: "Cannot find module 'dotenv'"

Solution:

npm install

Make sure you ran this after copying package.json

Issue: "DATABASE_URL is not set"

Solution:

# Check if .env file exists
ls -la .env

# If not, create it
cp .env.example .env

# Edit it and add your DATABASE_URL
nano .env

Issue: "Port 3000 already in use"

Solution:

# Edit .env and change the port
echo "PORT=3001" >> .env

# Or stop the other process
lsof -ti:3000 | xargs kill

Issue: "relation 'users' does not exist"

Solution: You didn't run the database setup:

npm run db:setup

Issue: Database connection fails

For Cloud (Supabase):

Check your connection string in .env
Make sure you replaced [YOUR-PASSWORD] with your actual password
Wait 1-2 minutes (database might still be starting)

For Local (Docker):

Check Docker is running: docker ps
If you don't see postgres-hackatbrown, start it again
Check the connection string matches what you set

Part 6: You're Ready to Build!

What to build next:

Add authentication (JWT tokens, password hashing)
Add more endpoints (update, delete, search)
Build a frontend (React, Vue, etc.)
Deploy to production (Heroku, Vercel, Railway)

Resources:

PostgreSQL Guide: postgresql/PostgreSQL_Starter.md
Model Documentation: Check the comments in models/userModel.js
Troubleshooting: See Part 5 above

MySQL Quickstart (Alternative to PostgreSQL)

If you chose MySQL instead:

Use the mysql/ folder instead of postgresql/

Docker command is different:

docker run -d \
  --name mysql-hackatbrown \
  -e MYSQL_ROOT_PASSWORD=hackatbrown123 \
  -e MYSQL_DATABASE=hackatbrown \
  -e MYSQL_USER=hackatbrown \
  -e MYSQL_PASSWORD=hackatbrown123 \
  -p 3306:3306 \
  mysql:8.0

Connection string format:

DATABASE_URL=mysql://hackatbrown:hackatbrown123@localhost:3306/hackatbrown

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Hack@Brown Database Starter Kit

Part 1: Before You Begin

What You'll Need

Part 2: PostgreSQL Setup (10 minutes)

Step 1: Create Your Project Folder

Step 2: Create the Folder Structure

Step 3: Copy Files from the Starter Kit

Step 4: Install Dependencies

Step 5: Set Up Your Database

Step 6: Test Your Connection

Step 7: Create Your Tables

Step 8: Create a Simple Server File

Step 9: Start Your Server

Step 10: Test It Works!

Part 3: Understanding What You Built

Your Project Structure

How It Works

The Data Flow

Part 4: Next Steps

Test Your API with curl

Add More Endpoints

Organize Your Code

Part 5: Common Issues & Solutions

Issue: "Cannot find module 'dotenv'"

Issue: "DATABASE_URL is not set"

Issue: "Port 3000 already in use"

Issue: "relation 'users' does not exist"

Issue: Database connection fails

Part 6: You're Ready to Build!

MySQL Quickstart (Alternative to PostgreSQL)

FilesExpand file tree

walkthrough.md

Latest commit

History

walkthrough.md

File metadata and controls

Hack@Brown Database Starter Kit

Part 1: Before You Begin

What You'll Need

Part 2: PostgreSQL Setup (10 minutes)

Step 1: Create Your Project Folder

Step 2: Create the Folder Structure

Step 3: Copy Files from the Starter Kit

Step 4: Install Dependencies

Step 5: Set Up Your Database

Step 6: Test Your Connection

Step 7: Create Your Tables

Step 8: Create a Simple Server File

Step 9: Start Your Server

Step 10: Test It Works!

Part 3: Understanding What You Built

Your Project Structure

How It Works

The Data Flow

Part 4: Next Steps

Test Your API with curl

Add More Endpoints

Organize Your Code

Part 5: Common Issues & Solutions

Issue: "Cannot find module 'dotenv'"

Issue: "DATABASE_URL is not set"

Issue: "Port 3000 already in use"

Issue: "relation 'users' does not exist"

Issue: Database connection fails

Part 6: You're Ready to Build!

MySQL Quickstart (Alternative to PostgreSQL)