Sequelize models associations not working in typescript

Question

I have 2 Models, Note and Category, and each Category has many notes while a Note belongs to one Category:

How could I retrieve all notes with their respective category color? So far I've tried what's in the picture bellow, but it is return a "{"error":"Category is not associated to Note!"}".

Answer 1

You have not established the correct relationship between note and category models. You are missing below association:

Note.belongsTo(Category, { foreignKey: 'categoryId', targetKey: 'id' });

The complete example:

Note.ts:

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

const config = {
  tableName: 'notes',
  sequelize: sequelizeInstance,
};

class Note extends Model {
  public id!: number;
  public title!: string;
  public content!: string;
  public categoryId!: number;
}
Note.init(
  {
    id: {
      primaryKey: true,
      autoIncrement: true,
      type: DataTypes.INTEGER,
      allowNull: false,
    },
    title: DataTypes.STRING,
    content: DataTypes.STRING,
  },
  config,
);

export default Note;

Category.ts:

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

const config = {
  tableName: 'categories',
  sequelize: sequelizeInstance,
};

class Category extends Model {
  public id!: number;
  public title!: string;
  public color!: number;
  public categoryId!: number;
}
Category.init(
  {
    id: {
      primaryKey: true,
      autoIncrement: true,
      type: DataTypes.INTEGER,
      allowNull: false,
    },
    title: DataTypes.STRING,
    color: DataTypes.INTEGER,
  },
  config,
);

export default Category;

To avoid circular references, we put all models into index.ts file and build relationships for them.

index.ts:

import { sequelize as sequelizeInstance } from '../../db';
import Note from './note';
import Category from './category';

Category.hasMany(Note, {
  sourceKey: 'id',
  foreignKey: 'categoryId',
  as: 'notes',
  onDelete: 'CASCADE',
  onUpdate: 'CASCADE',
});
Note.belongsTo(Category, { foreignKey: 'categoryId', targetKey: 'id' });

(async function test() {
  try {
    await sequelizeInstance.sync({ force: true });
    // seed
    await Category.bulkCreate(
      [
        {
          title: 'tech',
          color: 1,
          notes: [
            { title: 'go', content: 'golang' },
            { title: 'nodejs', content: 'nodejs is good' },
          ],
        },
        {
          title: 'food',
          color: 2,
          notes: [{ title: 'beef', content: 'I like beef' }],
        },
      ],
      { include: [{ model: Note, as: 'notes' }] },
    );

    // test
    const result = await Note.findAll({ include: [Category], raw: true });
    console.log(result);
  } catch (error) {
    console.log(error);
  } finally {
    await sequelizeInstance.close();
  }
})();

The execution results of the above test:

Executing (default): DROP TABLE IF EXISTS "notes" CASCADE;
Executing (default): DROP TABLE IF EXISTS "categories" CASCADE;
Executing (default): DROP TABLE IF EXISTS "categories" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "categories" ("id"   SERIAL , "title" VARCHAR(255), "color" INTEGER, PRIMARY KEY ("id"));
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 = 'categories' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): DROP TABLE IF EXISTS "notes" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "notes" ("id"   SERIAL , "title" VARCHAR(255), "content" VARCHAR(255), "categoryId" INTEGER REFERENCES "categories" ("id") ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY ("id"));
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 = 'notes' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "categories" ("id","title","color") VALUES (DEFAULT,'tech',1),(DEFAULT,'food',2) RETURNING *;
Executing (default): INSERT INTO "notes" ("id","title","content","categoryId") VALUES (DEFAULT,'go','golang',1),(DEFAULT,'nodejs','nodejs is good',1),(DEFAULT,'beef','I like beef',2) RETURNING *;
Executing (default): SELECT "Note"."id", "Note"."title", "Note"."content", "Note"."categoryId", "Category"."id" AS "Category.id", "Category"."title" AS "Category.title", "Category"."color" AS "Category.color" FROM "notes" AS "Note" LEFT OUTER JOIN "categories" AS "Category" ON "Note"."categoryId" = "Category"."id";
[ { id: 2,
    title: 'nodejs',
    content: 'nodejs is good',
    categoryId: 1,
    'Category.id': 1,
    'Category.title': 'tech',
    'Category.color': 1 },
  { id: 1,
    title: 'go',
    content: 'golang',
    categoryId: 1,
    'Category.id': 1,
    'Category.title': 'tech',
    'Category.color': 1 },
  { id: 3,
    title: 'beef',
    content: 'I like beef',
    categoryId: 2,
    'Category.id': 2,
    'Category.title': 'food',
    'Category.color': 2 } ]

Check the database:

node-sequelize-examples=# select * from "notes";
 id | title  |    content     | categoryId
----+--------+----------------+------------
  1 | go     | golang         |          1
  2 | nodejs | nodejs is good |          1
  3 | beef   | I like beef    |          2
(3 rows)

node-sequelize-examples=# select * from "categories";
 id | title | color
----+-------+-------
  1 | tech  |     1
  2 | food  |     2
(2 rows)

Dependencies versions: "sequelize": "^5.21.3", postgres:9.6

source code: https://github.com/mrdulin/node-sequelize-examples/tree/master/src/examples/stackoverflow/61166342