Database Management

This page defines how databases should be used, maintained, and handed off between student cohorts.
Our goal is to ensure data security, predictable deployments, reliable backups, and clear documentation for every backend-driven project in the practicum.

---

Our Standard Database Technology

The practicum standardizes on PostgreSQL for backend applications.

Why PostgreSQL?	Benefit
Strong relational integrity	Perfect for multi-table application logic
Open source & enterprise-level	Used by major companies (Instagram, Discord, Reddit)
Strong SQL + JSON support	Flexible for modern data-heavy apps
Broad deployment support	Vercel, Neon, Supabase, Railway, Render, etc.
Works with common ORMs	Prisma, TypeORM, Sequelize

While other databases exist, this standard ensures consistency across teams, classrooms, and support resources.

---

When Students Work With Databases

Only certain roles work directly with a remote production database.

Role	Access
Professor / TPM	Full database admin
Lead Engineer	Temporary access for migrations & deployment
Backend Engineers	May propose migrations; do not hold creds
PM / Contributors	No database modification access

Local database access for development is encouraged for all students.

---

Required Documentation for Any Database

Every repository that uses a database must contain:

📌 A DATABASE.md file in the project root, containing:

• ERD or table documentation
• Migration history and current schema
• Reset/seed instructions (if any)
• Which host is used (if remote)
• Who owns/manage access (TPM/Professor)

📌 A .env.example file, including required DB variables (never credentials).

DATABASE_URL="postgresql://USER:PASSWORD@HOST:PORT/DB_NAME"

📌 Schema migrations documented via ORM
Prisma, TypeORM, Sequelize, Drizzle, etc.

Migration scripts must be committed to Git.

---

Database Persistence Between Cohorts

To protect past work and ensure future success:

🔐 Production databases should NOT reset each quarter

Instead:

• Data that identifies students or users must be anonymized or removed
• Testing data should be cleared before final deployment
• Schema must evolve without wiping existing data unless approved

🧼 Development environments CAN reset freely

Local development DBs should be disposable.

Production data must be preserved unless instructors approve a reset.

---

Schema Changes & Migrations

All schema changes must:

• Use a migration file (never manual DB edits)
• Be reviewed in a Pull Request
• Include SQL, ORM migration, or schema diff documentation
• Include safe defaults (no destructive changes without backup)

❌ Never Allowed

• Altering production tables directly through a DB GUI
• Deploying schema changes without a backup
• Dropping tables in production without explicit approval

✔ Safe Change Examples

ALTER TABLE "users" ADD COLUMN "email_verified" BOOLEAN DEFAULT false;

✔ Required Review Checklist

Requirement	Description
Migration script included	No manual DB changes
Backward compatible	Does not break existing data
README or DATABASE.md updated	Reflects schema change
Backup created (production)	If deployment planned

---

Backups & Recovery

Backups protect semester progress and user data.
They must be created before major deployments or schema changes.

Required Backup Policy

DB Type	Backup Expectation
Production	Backup before schema or deployment
Development	Optional; may reset any time

How to Backup PostgreSQL (Generic)

pg_dump -Fc --no-acl --no-owner -h HOST -U USER DB_NAME > backup.dump

How to Restore

pg_restore --verbose --clean --no-acl --no-owner -h HOST -U USER -d DB_NAME backup.dump

---

Future Learning: Other Database Options

Although the practicum uses PostgreSQL exclusively, you may encounter:

• MySQL / MariaDB — older industry systems, common in legacy hosting
• SQLite — lightweight local DB for small tools or mobile apps
• MongoDB — document database for flexible schemas
• Redis — in-memory caching or sessions
• Supabase / Neon / PlanetScale / Firebase — hosted DB platforms

Click to learn when these are useful ⭐

Technology	Best Use Case
MySQL	Legacy enterprise web apps
MongoDB	Semi-structured, rapidly changing schemas
SQLite	Offline or embedded apps
Redis	Session data, rate limiting, caching
Supabase / Neon	Cloud PostgreSQL for scalable student projects
PlanetScale (MySQL)	Branch-based versioning, schema workflows

💡 Knowing PostgreSQL builds a foundation transferable to all relational systems.

---