title: Sequelize - How to insert csv data into multiple tables in mysql using sequelize
body: <p>What I'm using</p>
  <pre><code>node.js, express, sequelize 6.3.3, fast-csv 4.3.1 and mysql database.
  </code></pre>
  <p>What I have</p>
  <p>I have a CSV file with following headers and data.</p>
  <pre><code>no, name, email, township, father_name, father_phone, mother_name, mother_phone, parent_township
  </code></pre>
  <p>In this CSV, I have two types of data: <code>students</code> and <code>parents</code>.</p>
  <p>In my <code>MySQL</code> database, I have three tables:</p>
  <pre><code>students, parents and townships 
  </code></pre>
  <p>Students table:</p>
  <pre><code>id, name, email, township_id
  </code></pre>
  <p>Parents table:</p>
  <pre><code>id, student_id, father_name, father_phone, mother_name, mother_phone, parent_township_id
  </code></pre>
  <p>Townships table:</p>
  <pre><code>id, name
  </code></pre>
  <p>What I've done</p>
  <p>I read the CSV with <code>fast-csv</code> npm package with the following code.</p>
  <pre class="lang-js prettyprint-override"><code>let csvData = [];
  
  fs.createReadStream(req.file.path)
      .pipe(
        csv.parse({
          ignoreEmpty: true,
          trim: true,
          skipLines: 6,
        })
      )
      .on(&quot;error&quot;, (error) =&gt; console.error(error))
      .on(&quot;data&quot;, (row) =&gt; {
        csvData.push(getHeaders(_.compact(row), csvStudentDataEntryFields));
      })
      .on(&quot;end&quot;, () =&gt; {
        fs.unlinkSync(req.file.path);
  
        const data = csvData.map((data) =&gt; {
          const student = _.pick(data, studentFields);
          const parent = _.pick(data, parentFields);
          return [student, parent];
        });
  
        return res.status(201).send({
          status: &quot;success&quot;,
          data,
        });
      });
  </code></pre>
  <p>What I got</p>
  <p>With the above code, I got <code>data</code> with following values.</p>
  <pre><code>[
      [ // row 1 from CSV
          {
              &quot;name&quot;: &quot;John&quot;,
              &quot;email&quot;: &quot;john@gmail.com&quot;,
              &quot;township&quot;: &quot;NYC&quot;,
          },
          {
              &quot;fatherName&quot;: &quot;Smith&quot;,
              &quot;fatherPhone&quot;: &quot;9111111111&quot;,
              &quot;motherName&quot;: &quot;Mary&quot;,
              &quot;motherPhone&quot;: &quot;9111111111&quot;,
              &quot;parentTownship&quot;: &quot;NYC&quot;
           }
      ],
      [ // row 2 from CSV
          {
              &quot;name&quot;: &quot;Cutter&quot;,
              &quot;email&quot;: &quot;cutter@gmail.com&quot;,
              &quot;township&quot;: &quot;NYC&quot;,
          },
          {
              &quot;fatherName&quot;: &quot;Laseli&quot;,
              &quot;fatherPhone&quot;: &quot;9111111111&quot;,
              &quot;motherName&quot;: &quot;Mary&quot;,
              &quot;motherPhone&quot;: &quot;9111111111&quot;,
              &quot;parentTownship&quot;: &quot;NYC&quot;
          }
      ]
  ]
  </code></pre>
  <p>What I want</p>
  <p>I want to store those <code>row 1</code> and <code>row 2</code> from <code>data</code> into respective tables in the database.</p>
  <p>The problems</p>
  <p>I think I need to replace those <code>township</code> text data <strong>with real id</strong> because I have foreign keys stated as above.</p>
  <p>How can I achieve it? I want to do it in database level. I don't want to look up the <code>id</code> of that township name in separate js module.</p>
  <p><strong>Updated</strong></p>
  <p>Student Model</p>
  <pre><code>class Student extends Model {
      static associate(models) {
        Student.belongsTo(models.Township, {
          foreignKey: &quot;townshipId&quot;,
          as: &quot;township&quot;,
          targetKey: &quot;townshipId&quot;,
        });
  
       Student.hasOne(models.Parent, {
          foreignKey: &quot;studentId&quot;,
          as: &quot;parent&quot;,
          sourceKey: &quot;studentId&quot;,
        });
      }
    }
  </code></pre>
  <p>Parent Model</p>
  <pre><code>class Parent extends Model {
      static associate(models) {
        Parent.belongsTo(models.Student, {
          foreignKey: &quot;studentId&quot;,
          as: &quot;student&quot;,
          targetKey: &quot;studentId&quot;,
        });
  
        Parent.belongsTo(models.Township, {
          foreignKey: &quot;parentTownshipId&quot;,
          as: &quot;township&quot;,
          targetKey: &quot;townshipId&quot;,
        });
      }
    }
  </code></pre>
  <p>Township Model</p>
  <pre><code>class Township extends Model {
      static associate(models) {
        Township.hasMany(models.Student, {
          foreignKey: &quot;townshipId&quot;,
          as: &quot;township&quot;,
          sourceKey: &quot;townshipId&quot;,
        });
  
        Township.hasMany(models.Parent, {
          foreignKey: &quot;townshipId&quot;,
          as: &quot;parentTownship&quot;,
          sourceKey: &quot;townshipId&quot;,
        });
      }
    }
  </code></pre>
  <p><strong>Updated:</strong></p>
  <p>in my controller,</p>
  <pre><code>let std = await models.Student.create(
      {
          nameEn: &quot;John&quot;,
          email: &quot;john@gmail.com&quot;,
          townshipId: 1,
          parent: [{ ...d[1], parentTownshipId: 1 }],
      },
      { include: [&quot;parent&quot;] }
  );
  </code></pre>