error in sequelize > "name": "SequelizeEagerLoadingError"

Question

I have two tables:

//User.js

module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define("User", {
    userId: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      primaryKey: true,
    },
    email: {
      type: DataTypes.STRING,
    },
    firstName: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    lastName: {
      type: DataTypes.STRING,
      defaultValue: "",
    },
    password: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    chapterId: {
      type: DataTypes.STRING,
    },
  });

  User.associate = (models) => {
    User.belongsTo(models.Chapter, {
      foreignKey: "chapterId",
      targetKey: "chapterId",
      as: "chapter",
    });
  };

  return User;
};

and

//chapter table

module.exports = (sequelize, DataTypes) => {
  const Chapter = sequelize.define("Chapter", {
    chapterId: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      primaryKey: true,
    },
    chapterName: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    isChapterLocal: {
      type: DataTypes.BOOLEAN,
      allowNull: false,
    },
  });

  Chapter.associate = (models) => {
  };

  return Chapter;
};

and i am trying to fetch users with chapters included into it.

let getAll = async (req, res) => {
  try {
    const userData = await db.User.findAll({
      include: [
        {
          model: Chapter,
          as: "chapter",
        },
      ],
    });
    res.send(userData);
  } catch (e) {
    res.send(e);
  }
};

how to include chapter id and chapter name from chapter table, as present in chapterId row for user table. I am new to sequelize and MySQL and am unsure if the relation i have defined in the user model is good. Do we need to define associations in both tables.

Most probably you have not defined associations properly. You can check whether foreign key constaints are applied properly by doing Sequelize.sync({ force: true }) — Soham Lawar
Do you want a user to have exactly one chapter, or more than one chapter? The way you have it modeled right now limits a user to exactly one chapter. — type
a user must be associated to one chapter only.. its like his profile page. — Shubham Shaw

slideshowp2 slideshowp2 · Accepted Answer · 2020-05-15T09:44:33

It should work as expected. E.g.

import { sequelize } from '../../db';
import { Model, DataTypes } from 'sequelize';

class User extends Model {}
User.init(
  {
    userId: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      primaryKey: true,
    },
    email: {
      type: DataTypes.STRING,
    },
    firstName: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    lastName: {
      type: DataTypes.STRING,
      defaultValue: '',
    },
    password: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    chapterId: {
      type: DataTypes.STRING,
    },
  },
  { sequelize, modelName: 'User' },
);

class Chapter extends Model {}
Chapter.init(
  {
    chapterId: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      primaryKey: true,
    },
    chapterName: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    isChapterLocal: {
      type: DataTypes.BOOLEAN,
      allowNull: false,
    },
  },
  { sequelize, modelName: 'Chapter' },
);

User.belongsTo(Chapter, { foreignKey: 'chapterId', targetKey: 'chapterId', as: 'chapter' });

(async function test() {
  try {
    await sequelize.sync({ force: true });
    // seed
    await User.create(
      {
        userId: '1',
        email: '[email protected]',
        firstName: 'Lin',
        lastName: 'Du',
        password: '123',
        chapter: {
          chapterId: '1',
          chapterName: 'ok',
          isChapterLocal: false,
        },
      },
      { include: [{ model: Chapter, as: 'chapter' }] },
    );
    // test
    const userData = await User.findAll({
      include: [{ model: Chapter, as: 'chapter' }],
      raw: true,
    });
    console.log('userData:', userData);
  } catch (error) {
    console.log(error);
  } finally {
    sequelize.close();
  }
})();

The execution results:

Executing (default): DROP TABLE IF EXISTS "User" CASCADE;
Executing (default): DROP TABLE IF EXISTS "Chapter" CASCADE;
Executing (default): DROP TABLE IF EXISTS "Chapter" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "Chapter" ("chapterId" VARCHAR(255) NOT NULL UNIQUE , "chapterName" VARCHAR(255) NOT NULL, "isChapterLocal" BOOLEAN NOT NULL, PRIMARY KEY ("chapterId"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'Chapter' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): DROP TABLE IF EXISTS "User" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "User" ("userId" VARCHAR(255) NOT NULL UNIQUE , "email" VARCHAR(255), "firstName" VARCHAR(255) NOT NULL, "lastName" VARCHAR(255) DEFAULT '', "password" VARCHAR(255) NOT NULL, "chapterId" VARCHAR(255) REFERENCES "Chapter" ("chapterId") ON DELETE NO ACTION ON UPDATE CASCADE, PRIMARY KEY ("userId"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'User' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "Chapter" ("chapterId","chapterName","isChapterLocal") VALUES ($1,$2,$3) RETURNING *;
Executing (default): INSERT INTO "User" ("userId","email","firstName","lastName","password","chapterId") VALUES ($1,$2,$3,$4,$5,$6) RETURNING *;
Executing (default): SELECT "User"."userId", "User"."email", "User"."firstName", "User"."lastName", "User"."password", "User"."chapterId", "chapter"."chapterId" AS "chapter.chapterId", "chapter"."chapterName" AS "chapter.chapterName", "chapter"."isChapterLocal" AS "chapter.isChapterLocal" FROM "User" AS "User" LEFT OUTER JOIN "Chapter" AS "chapter" ON "User"."chapterId" = "chapter"."chapterId";
userData: [ { userId: '1',
    email: '[email protected]',
    firstName: 'Lin',
    lastName: 'Du',
    password: '123',
    chapterId: '1',
    'chapter.chapterId': '1',
    'chapter.chapterName': 'ok',
    'chapter.isChapterLocal': false } ]

Check the database:

node-sequelize-examples=# select * from "User";
 userId |       email       | firstName | lastName | password | chapterId
--------+-------------------+-----------+----------+----------+-----------
 1      | [email protected] | Lin       | Du       | 123      | 1
(1 row)

node-sequelize-examples=# select * from "Chapter";
 chapterId | chapterName | isChapterLocal
-----------+-------------+----------------
 1         | ok          | f
(1 row)

error in sequelize > "name": "SequelizeEagerLoadingError"

1 Answers