CODEWITHSUNDEEP
javascriptnode.jssequelize.js
Julian Fock
Julian Fock

Reputation: 108

Sequelize Associations: How to update parent model when creating child?

It seems i have misunderstood sequelize .hasMany() and .belongsTo() associations and how to use them in service. I have two models:

const User = db.sequelize.define("user", {
  uid: { /*...*/  },
  createdQuestions: {
    type: db.DataTypes.ARRAY(db.DataTypes.UUID),
    unique: true,
    allowNull: true,
  },
});
const Question = db.sequelize.define("question", {
  qid: { /*...*/  },
  uid: {
    type: db.DataTypes.TEXT,
  },
});

Given that one user can have many questions and each question belongs to only one user I have the following associatons:

User.hasMany(Question, {
    sourceKey: "createdQuestions", 
    foreignKey: "uid",
    constraints: false,
});
Question.belongsTo(User, { 
    foreignKey: "uid", 
    targetKey: "createdQuestions",
    constraints: false,
});

What I want to achieve is this: After creation of a question object, the qid should reside in the user object under "createdQuestions" - just as the uid resides in the question object under uid. What I thought sequelize associations would do for me is to save individual calling and updating the user object. Is there a corresponding method? What I have so far is:

const create_question = async (question_data) => {
  const question = { /*... question body containing uid and so forth*/ };

  return new Promise((resolve, rejected) => {
    Question.sync({ alter: true }).then(
      async () =>
        await db.sequelize
          .transaction(async (t) => {
            const created_question = await Question.create(question, {
              transaction: t,
            });
          })
          .then(() => resolve())
          .catch((e) => rejected(e))
    );
  });
};

This however only creates a question object but does not update the user. What am I missing here?

Upvotes: 3

Views: 1299

Answers (2)

Ikdemm
Ikdemm

Reputation: 2353

Modelling a One-to-many relationship in SQL

SQL vs NoSQL

In SQL, contrary to how it is in NoSQL, every attribute has a fixed data type with a fixed limit of bits. That's manifested by the SQL command when creating a new table:

CREATE TABLE teachers (
   name VARCHAR(32),
   department VARCHAR(64),
   age INTEGER
);

The reason behind this is to allow us to easily access any attribute from the database by knowing the length of each row. In our case, each row will need the space needed to store:

32 bytes (name) + 64 bytes (department) + 4 bytes (age) = 100 byes

This is a very powerful feature in Relation Databases as it minimizes the time needed to retrieve data to Constant time since we knew where each piece of data is located in the memory.

One-to-Many Relationship: Case Study

Now, let's consider we have these 3 tables

enter image description here

Let's say we want to create a one-to-many relation between classes and teachers where a Teacher can give many classes.

enter image description here

We can think of it this way. But, this model is not possible for 2 main reasons:

  • It will make us lose our constant-time retrieval since we don't know the size of the list anymore
  • We fear that the amount of space given to the list attribute won't be enough for future data. Let's say we allocate space needed for 10 classes and we end up with a teacher giving 11 classes. This will push us to recreate our database to increase the column size.

Another way would be this:

enter image description here

While this approach will fix the limited column size problem, we no longer have a single source of truth. The same data is duplicated and stored multiple times.

That's why for this one-to-many relationship, we'll need to store the Id of the teacher inside this class table.

This way, we still can find all the classes a teacher can teach by running

SELECT *
FROM classes
WHERE teacherID = teacher_id

And we'll avoid all the problems discussed earlier.

Upvotes: 2

Ikdemm
Ikdemm

Reputation: 2353

Your relation is a oneToMany relation. One User can have multiple Questions. In SQL, this kind of relation is modelled by adding an attribute to Question called userId or Uid as you did. In Sequelize, this would be achieved through a hasMany or BelongsTo like this:

User.hasMany(Question)
Question.belongsTo(User, {
   foreignKey: 'userId',
   constraints: false
})

In other words, I don't think you need the CreatedQuestions attribute under User. Only one foreign key is needed to model the oneToMany relation.

Now, when creating a new question, you just need to add the userId this way

createNewQuestion = async (userId, title, body) => {
  const question = await Question.create({
    userId: userId,  // or just userId
    title: title,  // or just title
    body: body  // or just body
  })
  return question
}

Remember, we do not store arrays in SQL. Even if we can find a way to do it, it is not what we need. There must be always a better way.

Upvotes: 0

Related Questions