Node.js 主線任務八：用 Migration 建資料表與 Seeding 實戰 (LiveFit & School)

🛑 防暴雷警示：以下筆記包含主線任務的完整解答與實作細節，強烈建議先親手透過 EntitySchema 劃定資料表藍圖並跑過一次 Migration，卡關再來對照參考喔！

本次任務重點為從零開始打造資料庫底層架構。我們將分別在 第一關：LiveFit 健身房 (3 張表) 與 第二關：學校成績 (4 張表) 中，運用 TypeORM 的 EntitySchema 宣告 Table 欄位與外來鍵關聯，透過 Migration 生成資料庫變更檔案並發動建表，最後透過 Seeder 清空與寫入合乎規範的初始測試資料。

---

第一關：LiveFit 健身房 (3 張資料表實作)

1. 定義 Entity (entities/*.js)

教練實體 (entities/USER.js)

💻 點擊展開程式碼解答 (livefit/entities/USER.js)

livefit/entities/USER.js

const { EntitySchema } = require("typeorm");

module.exports = new EntitySchema({
  name: "USER",
  tableName: "USER",
  columns: {
    id: { primary: true, type: "uuid", generated: "uuid", nullable: false },
    name: { type: "varchar", length: 50, nullable: false },
    email: { type: "varchar", length: 320, nullable: false, unique: true },
    role: { type: "varchar", length: 20, nullable: false },
    created_at: { type: "timestamp", createDate: true, nullable: false },
    updated_at: { type: "timestamp", updateDate: true, nullable: false },
  },
});

技能實體 (entities/SKILL.js)

💻 點擊展開程式碼解答 (livefit/entities/SKILL.js)

livefit/entities/SKILL.js

const { EntitySchema } = require("typeorm");

module.exports = new EntitySchema({
  name: "SKILL",
  tableName: "SKILL",
  columns: {
    id: { primary: true, type: "uuid", generated: "uuid", nullable: false },
    name: { type: "varchar", length: 50, nullable: false, unique: true },
  },
});

課程實體與雙重關聯 (entities/COURSE.js)

💻 點擊展開程式碼解答 (livefit/entities/COURSE.js)

livefit/entities/COURSE.js

const { EntitySchema } = require("typeorm");

module.exports = new EntitySchema({
  name: "COURSE",
  tableName: "COURSE",
  columns: {
    id: { primary: true, type: "uuid", generated: "uuid", nullable: false },
    name: { type: "varchar", length: 100, nullable: false },
    description: { type: "text", nullable: false },
    start_at: { type: "timestamp", nullable: false },
    end_at: { type: "timestamp", nullable: false },
    max_participants: { type: "integer", nullable: false },
    created_at: { type: "timestamp", createDate: true, nullable: false },
    updated_at: { type: "timestamp", updateDate: true, nullable: false },
  },
  relations: {
    user: {
      target: "USER",
      type: "many-to-one",
      joinColumn: { name: "user_id" },
    },
    skill: {
      target: "SKILL",
      type: "many-to-one",
      joinColumn: { name: "skill_id" },
    },
  },
});

---

2. 註冊 Entity (db/data-source.js)

💻 點擊展開程式碼解答 (livefit/db/data-source.js)

livefit/db/data-source.js

require("dotenv").config();
const { DataSource } = require("typeorm");
const User = require("../entities/USER");
const Skill = require("../entities/SKILL");
const Course = require("../entities/COURSE");

const dataSource = new DataSource({
  type: "postgres",
  host: process.env.DB_HOST || "localhost",
  port: Number(process.env.DB_PORT || 5432),
  username: process.env.DB_USERNAME || "student",
  password: process.env.DB_PASSWORD || "student666",
  database: process.env.DB_DATABASE || "livefit",

  synchronize: false, // ⚠️ 鐵律：synchronize 固定為 false

  entities: [User, Skill, Course],
  migrations: ["db/migrations/*.js"],
});

module.exports = { dataSource };

---

3. 生成與執行 Migration

cd livefit
npm run migration:generate -- db/migrations/Init
npm run migration:run

---

4. Seeder 寫入關聯資料 (db/seed.js)

💡 關鍵點：關聯寫入技巧

在寫入 COURSE 時，無需手動取出 ID，直接將已 save 的教練與技能 Entity 物件作為 user 與 skill 屬性傳入，TypeORM 會自動提煉外來鍵寫入 DB。

💻 點擊展開程式碼解答 (livefit/db/seed.js)

livefit/db/seed.js

const { dataSource } = require("./data-source");

async function clearAll() {
  // 被 FK 參考的表最後刪除 (先刪 COURSE，再刪 USER 與 SKILL)
  for (const name of ["COURSE", "USER", "SKILL"]) {
    if (dataSource.hasMetadata(name)) {
      await dataSource.createQueryBuilder().delete().from(name).execute();
    }
  }
}

async function main() {
  await dataSource.initialize();
  await clearAll();

  const skillRepo = dataSource.getRepository("SKILL");
  const userRepo = dataSource.getRepository("USER");
  const courseRepo = dataSource.getRepository("COURSE");

  // 1. 先建立基礎資料表 (SKILL, USER)
  const [weightTraining, yoga, spinning] = await skillRepo.save([
    { name: "重訓" },
    { name: "瑜珈" },
    { name: "飛輪" },
  ]);

  const [haige, xiaomei] = await userRepo.save([
    { name: "海格教練", email: "[email protected]", role: "COACH" },
    { name: "小美教練", email: "[email protected]", role: "COACH" },
  ]);

  // 2. 建立帶有外來鍵關聯的 COURSE 資料
  await courseRepo.save([
    {
      name: "肌力入門班",
      description: "從基本動作開始建立肌力",
      start_at: "2026-08-03 19:00:00",
      end_at: "2026-08-03 20:00:00",
      max_participants: 16,
      user: haige,
      skill: weightTraining,
    },
    {
      name: "週末飛輪",
      description: "燃脂飆汗飛輪課",
      start_at: "2026-08-08 10:00:00",
      end_at: "2026-08-08 11:00:00",
      max_participants: 12,
      user: xiaomei,
      skill: spinning,
    },
    {
      name: "晨間瑜珈",
      description: "喚醒身體的早晨瑜珈",
      start_at: "2026-08-05 07:00:00",
      end_at: "2026-08-05 08:00:00",
      max_participants: 8,
      user: xiaomei,
      skill: yoga,
    },
    {
      name: "核心特訓",
      description: "強化核心肌群穩定度",
      start_at: "2026-08-06 19:00:00",
      end_at: "2026-08-06 20:00:00",
      max_participants: 10,
      user: haige,
      skill: weightTraining,
    },
  ]);

  console.log("🌱 LiveFit seed 完成！");
  await dataSource.destroy();
}

main().catch((e) => {
  console.error("seed 失敗：", e.message);
  process.exit(1);
});

---

第二關：學校成績 (4 張資料表實作)

第二關共有 4 張資料表：CLASS (班級)、SUBJECT (科目)、STUDENT (學生) 與 GRADE (成績)。

1. 定義 Entity (school/entities/*.js)

班級實體 (school/entities/CLASS.js)

💻 點擊展開程式碼解答 (school/entities/CLASS.js)

school/entities/CLASS.js

const { EntitySchema } = require("typeorm");

module.exports = new EntitySchema({
  name: "Class",
  tableName: "CLASS",
  columns: {
    id: { primary: true, type: "uuid", generated: "uuid", nullable: false },
    name: { type: "varchar", length: 50, nullable: false },
  },
});

科目實體 (school/entities/SUBJECT.js)

💻 點擊展開程式碼解答 (school/entities/SUBJECT.js)

school/entities/SUBJECT.js

const { EntitySchema } = require("typeorm");

module.exports = new EntitySchema({
  name: "Subject",
  tableName: "SUBJECT",
  columns: {
    id: { primary: true, type: "uuid", generated: "uuid", nullable: false },
    name: { type: "varchar", length: 50, nullable: false },
  },
});

學生實體與班級關聯 (school/entities/STUDENT.js)

💻 點擊展開程式碼解答 (school/entities/STUDENT.js)

school/entities/STUDENT.js

const { EntitySchema } = require("typeorm");

module.exports = new EntitySchema({
  name: "Student",
  tableName: "STUDENT",
  columns: {
    id: { primary: true, type: "uuid", generated: "uuid", nullable: false },
    name: { type: "varchar", length: 50, nullable: false },
  },
  relations: {
    class: {
      target: "Class",
      type: "many-to-one",
      joinColumn: { name: "class_id" },
      nullable: false,
    },
  },
});

成績實體與雙重關聯 (school/entities/GRADE.js)

💻 點擊展開程式碼解答 (school/entities/GRADE.js)

school/entities/GRADE.js

const { EntitySchema } = require("typeorm");

module.exports = new EntitySchema({
  name: "Grade",
  tableName: "GRADE",
  columns: {
    id: { primary: true, type: "uuid", generated: "uuid", nullable: false },
    score: { type: "integer", nullable: false },
  },
  relations: {
    student: {
      target: "Student",
      type: "many-to-one",
      joinColumn: { name: "student_id" },
      nullable: false,
    },
    subject: {
      target: "Subject",
      type: "many-to-one",
      joinColumn: { name: "subject_id" },
      nullable: false,
    },
  },
});

---

2. 註冊 Entity 與 Migration

把四個 Entity 匯入並註冊至 school/db/data-source.js 的 entities 陣列後，在 school/ 目錄執行 Migration 指令：

cd school
npm run migration:generate -- db/migrations/Init
npm run migration:run

---

3. Seeder 清空與播種實作 (school/db/seed.js)

⚠️ 避坑預警：Seeder 清空與寫入順序

• 寫入順序：先寫入獨立表 (Class, Subject)，再寫入 Student，最後才寫入包含雙外來鍵的 Grade。
• 清空順序：必須先刪除帶有外來鍵的 Grade，最後才刪除被參考的 Class 與 Subject，否則會觸發外來鍵約束報錯！

💻 點擊展開程式碼解答 (school/db/seed.js)

school/db/seed.js

const { dataSource } = require('./data-source')

/** 清空：被 FK 指著的表最後刪（GRADE 先刪，CLASS / SUBJECT 最後刪）。 */
async function clearAll() {
  const ORDER = [
    'Grade',
    'Student',
    'Class',
    'Subject',
  ]
  for (const name of ORDER) {
    if (dataSource.hasMetadata(name)) {
      await dataSource.createQueryBuilder().delete().from(name).execute()
    }
  }
}

async function main() {
  await dataSource.initialize()
  await clearAll()

  const classRepo = dataSource.getRepository('Class')
  const subjectRepo = dataSource.getRepository('Subject')
  const studentRepo = dataSource.getRepository('Student')
  const gradeRepo = dataSource.getRepository('Grade')

  // 1. 建立班級與科目
  const [class1, class2] = await classRepo.save([
    { name: '一年一班' },
    { name: '一年二班' },
  ])

  const [chinese, math] = await subjectRepo.save([
    { name: '國文' },
    { name: '數學' },
  ])

  // 2. 建立學生 (接上對應班級)
  const [ming, hua] = await studentRepo.save([
    { name: '王小明', class: class1 },
    { name: '李小華', class: class2 },
  ])

  // 3. 建立成績 (接上學生與科目)
  await gradeRepo.save([
    { score: 95, student: ming, subject: chinese },
    { score: 88, student: hua, subject: math },
  ])

  console.log('🌱 School seed 完成！')
  await dataSource.destroy()
}

main().catch((e) => {
  console.error('seed 失敗：', e.message)
  process.exit(1)
})

---

本地單元測試驗收

分別在 livefit/ 與 school/ 資料夾下執行驗收：

# 驗收第一關 LiveFit
cd livefit && npm test

# 驗收第二關 School
cd ../school && npm test

• LiveFit 關卡通過標籤：Tests: 13 passed, 13 total
• School 關卡通過標籤：Tests: 11 passed, 11 total