db.js

js3 kB66 linesIn assignment_1_solution.zip

assignment_1_solution.zipbackendsrcdb.js

JavaScript
import pg from "pg";

// One connection pool for the whole process. DATABASE_URL looks like
// postgres://user:password@host:5432/dbname
// - locally:        host = localhost (Postgres published by `docker compose up db`)
// - inside compose: host = db        (the service name, resolved by Docker's DNS)
export const pool = new pg.Pool({
  connectionString:
    process.env.DATABASE_URL ?? "postgres://app:app@localhost:5432/campus_events",
});

// Create tables on startup so the app works on a fresh database without a
// separate migration step. IF NOT EXISTS makes this safe to run every time.
const SCHEMA = `
CREATE TABLE IF NOT EXISTS users (
  id            SERIAL PRIMARY KEY,
  name          TEXT        NOT NULL,
  email         TEXT        NOT NULL UNIQUE,
  password_hash TEXT        NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS events (
  id          SERIAL PRIMARY KEY,
  title       TEXT        NOT NULL,
  description TEXT        NOT NULL,
  category    TEXT        NOT NULL,
  location    TEXT        NOT NULL,
  starts_at   TIMESTAMPTZ NOT NULL,
  capacity    INTEGER     NOT NULL CHECK (capacity > 0),
  created_by  INTEGER     REFERENCES users(id) ON DELETE SET NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS rsvps (
  user_id    INTEGER NOT NULL REFERENCES users(id)  ON DELETE CASCADE,
  event_id   INTEGER NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, event_id)
);
`;

const SEED_EVENTS = [
  ["Intro to Docker Workshop", "Hands-on session: images, containers, volumes and networks. Bring your laptop with Docker Desktop installed.", "Workshop", "Lab 3, Faculty of Sciences", 7, 30],
  ["AWS Cloud Practitioner Study Group", "Weekly study group covering IAM, EC2, S3 and billing. Free tier tips included.", "Study Group", "Library, Room 204", 10, 20],
  ["AI & Data Engineering Career Talk", "Alumni share how they moved from university projects to industry roles.", "Talk", "Main Amphitheater", 14, 150],
  ["Hackathon: Build a Containerized App", "24-hour hackathon. Teams of 3 ship a three-tier app with Docker Compose.", "Hackathon", "Innovation Hub", 21, 60],
  ["Linux Command Line Basics", "Navigate the shell, manage files and permissions, and write your first script.", "Workshop", "Lab 1, Faculty of Sciences", 28, 25],
];

export async function migrate() {
  await pool.query(SCHEMA);

  const { rows } = await pool.query("SELECT count(*)::int AS n FROM events");
  if (rows[0].n > 0) return;

  // Seed a few events (dates relative to today) so the UI is not empty on first run.
  for (const [title, description, category, location, inDays, capacity] of SEED_EVENTS) {
    await pool.query(
      `INSERT INTO events (title, description, category, location, starts_at, capacity)
       VALUES ($1, $2, $3, $4, now() + ($5 || ' days')::interval, $6)`,
      [title, description, category, location, inDays, capacity],
    );
  }
  console.log(`Seeded ${SEED_EVENTS.length} events`);
}

In src/