Intermediate Phase Six - Updating Foreign Key References in Seed Data

Understand the Problem

We want to seed the many-to-many table InsectTree with data connecting known Insects to known Trees. We must ensure our seeder scripts match the actual IDs in the database, or dynamically find them if they can change.

Devise a Plan

1. Create a new seeder for InsectTree.
2. Insert rows referencing correct insectId and treeId. Hardcode or dynamically find IDs.
3. Test by re-migrating, re-seeding, and verifying records in the database with the SQLite CLI or npm test.
4. Handle conflicts if pre-existing data or new data is in the database.

Carry Out the Plan (Solution)

File Name & Location: server/seeders/xxxx-starter-insect-tree.js

// Example of static approach, partial pseudocode:
// up: (queryInterface, Sequelize) => {
//   return queryInterface.bulkInsert('InsectTrees', [
//     { insectId: 1, treeId: 1 },
//     { insectId: 1, treeId: 2 },
//     { insectId: 1, treeId: 4 },
//     { insectId: 1, treeId: 5 },
//     { insectId: 2, treeId: 5 }
//   ]);
// }

// down: (queryInterface, Sequelize) => {
//   return queryInterface.bulkDelete('InsectTrees', null, {});
// }
    

Expected Input & Output: Seeding doesn’t involve direct user input. The result is the InsectTrees table populated with correct references.

Elementary (Basic) Approach

• Hardcode the insectId and treeId based on known initial IDs from other seeders.

Advanced (Dynamic) Approach

• Query the existing Insect or Tree by name, then use the found id for insertion, so it adapts to changes in ID ordering.

Step-by-Step Directions:

1. Create the seeder: npx sequelize-cli seed:generate --name starter-insect-tree.
2. In up, insert rows for InsectTrees referencing the correct IDs.
3. In down, remove those rows with bulkDelete.
4. Run npx sequelize-cli db:seed:all or db:seed:undo to test.
5. Check references in SQLite or via queries.

Explanations & Analogies: If your table references IDs, you must be sure those IDs match real entries. Otherwise, it’s like labeling a phone contact with a number that belongs to someone else!

Real World Example: Many-to-many references are used in bridging tables for courses & students, product tags, etc.

Look Back

Make sure the seeded data lines up with actual rows in Insect and Tree. Handle future changes in base seed data by either adjusting IDs or using dynamic queries.