Sequelize - How to insert csv data into multiple tables in mysql using sequelize

Question

What I'm using

node.js, express, sequelize 6.3.3, fast-csv 4.3.1 and mysql database.

What I have

I have a CSV file with following headers and data.

no, name, email, township, father_name, father_phone, mother_name, mother_phone, parent_township

In this CSV, I have two types of data: students and parents.

In my MySQL database, I have three tables:

students, parents and townships

Students table:

id, name, email, township_id

Parents table:

id, student_id, father_name, father_phone, mother_name, mother_phone, parent_township_id

Townships table:

id, name

What I've done

I read the CSV with fast-csv npm package with the following code.

let csvData = [];

fs.createReadStream(req.file.path)
    .pipe(
      csv.parse({
        ignoreEmpty: true,
        trim: true,
        skipLines: 6,
      })
    )
    .on("error", (error) => console.error(error))
    .on("data", (row) => {
      csvData.push(getHeaders(_.compact(row), csvStudentDataEntryFields));
    })
    .on("end", () => {
      fs.unlinkSync(req.file.path);

      const data = csvData.map((data) => {
        const student = _.pick(data, studentFields);
        const parent = _.pick(data, parentFields);
        return [student, parent];
      });

      return res.status(201).send({
        status: "success",
        data,
      });
    });

What I got

With the above code, I got data with following values.

[
    [ // row 1 from CSV
        {
            "name": "John",
            "email": "[email protected]",
            "township": "NYC",
        },
        {
            "fatherName": "Smith",
            "fatherPhone": "9111111111",
            "motherName": "Mary",
            "motherPhone": "9111111111",
            "parentTownship": "NYC"
         }
    ],
    [ // row 2 from CSV
        {
            "name": "Cutter",
            "email": "[email protected]",
            "township": "NYC",
        },
        {
            "fatherName": "Laseli",
            "fatherPhone": "9111111111",
            "motherName": "Mary",
            "motherPhone": "9111111111",
            "parentTownship": "NYC"
        }
    ]
]

What I want

I want to store those row 1 and row 2 from data into respective tables in the database.

The problems

I think I need to replace those township text data with real id because I have foreign keys stated as above.

How can I achieve it? I want to do it in database level. I don't want to look up the id of that township name in separate js module.

Updated

Student Model

class Student extends Model {
    static associate(models) {
      Student.belongsTo(models.Township, {
        foreignKey: "townshipId",
        as: "township",
        targetKey: "townshipId",
      });

     Student.hasOne(models.Parent, {
        foreignKey: "studentId",
        as: "parent",
        sourceKey: "studentId",
      });
    }
  }

Parent Model

class Parent extends Model {
    static associate(models) {
      Parent.belongsTo(models.Student, {
        foreignKey: "studentId",
        as: "student",
        targetKey: "studentId",
      });

      Parent.belongsTo(models.Township, {
        foreignKey: "parentTownshipId",
        as: "township",
        targetKey: "townshipId",
      });
    }
  }

Township Model

class Township extends Model {
    static associate(models) {
      Township.hasMany(models.Student, {
        foreignKey: "townshipId",
        as: "township",
        sourceKey: "townshipId",
      });

      Township.hasMany(models.Parent, {
        foreignKey: "townshipId",
        as: "parentTownship",
        sourceKey: "townshipId",
      });
    }
  }

Updated:

in my controller,

let std = await models.Student.create(
    {
        nameEn: "John",
        email: "[email protected]",
        townshipId: 1,
        parent: [{ ...d[1], parentTownshipId: 1 }],
    },
    { include: ["parent"] }
);

AnonyMouze · Accepted Answer · 2020-07-31 14:53:31Z

1

I think you can insert all relationship together.

This is from sequelize document

const amidala = await User.create({
  username: 'p4dm3',
  points: 1000,
  profiles: [{
    name: 'Queen',
    User_Profile: {
      selfGranted: true
    }
  }]
}, {
  include: Profile
});

const result = await User.findOne({
  where: { username: 'p4dm3' },
  include: Profile
});

console.log(result);

set source key

 const Foo = sequelize.define('foo', {
  name: { type: DataTypes.TEXT, unique: true }
}, { timestamps: false });
const Bar = sequelize.define('bar', {
  title: { type: DataTypes.TEXT, unique: true }
}, { timestamps: false });
const Baz = sequelize.define('baz', { summary: DataTypes.TEXT }, { timestamps: false });
Foo.hasOne(Bar, { sourceKey: 'name', foreignKey: 'fooName' });
Bar.hasMany(Baz, { sourceKey: 'title', foreignKey: 'barTitle' });
// [...]
await Bar.setFoo("Foo's Name Here");
await Baz.addBar("Bar's Title Here");

ref https://sequelize.org/master/manual/advanced-many-to-many.html#using-one-to-many-relationships-instead

edited Jul 31, 2020 at 14:53

answered Jul 31, 2020 at 13:02

AnonyMouze

4262 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

khukho Over a year ago

Thank you so much for your answer. But I got the following error. SequelizeDatabaseError: Incorrect integer value: 'Falam' for column 'township_id'

AnonyMouze Over a year ago

this is probably association problems.

khukho Over a year ago

I've updated and added associations in my question. Please see this.

AnonyMouze Over a year ago

try add target key source.belongsTo(target, {foreignKey: 'fkname', targetKey: 'id'});

khukho Over a year ago

Thanks. I see clearly your first way to check first if the incoming NYC exists in townships table and if it is there, grab the id and replace it with NYC. I think upset will not be OK because it will insert if NYC is not there in townships. It could allow inserting false data. And sorry I don't understand your recommend way in last comment. (There will be routes to CRUD townships resource separately.)

|

Collectives™ on Stack Overflow

Sequelize - How to insert csv data into multiple tables in mysql using sequelize

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related