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"] }
);
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");