Select All Records with Sequelize ORM when having One-To-Many relationship-CodePudding

I have two tables Users and Shops, User can have multiple shops, and shop can be owned by one user.

I'm using Node.js & Sequelize ORM (postgres)

The Problem

When fetching data from Shop Model, Sequelize add extra field UserId in select query like:

SELECT "id", "userId", "createdAt", "updatedAt", "UserId" FROM "Shops" AS "Shop";

This Query Excecuted when I do:

const shops = await Shop.findAll();

This Line Throws Exception says `error: column "UserId" does not exist'.

StackTrace:

Error
    at Query.run (F:\Work\Coding\stack\node_modules\sequelize\lib\dialects\postgres\query.js:50:25)
    at F:\Work\Coding\stack\node_modules\sequelize\lib\sequelize.js:313:28
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async PostgresQueryInterface.select (F:\Work\Coding\stack\node_modules\sequelize\lib\dialects\abstract\query-interface.js:396:12)
    at async Function.findAll (F:\Work\Coding\stack\node_modules\sequelize\lib\model.js:1119:21)
    at async F:\Work\Coding\stack\app.js:47:19 {
  name: 'SequelizeDatabaseError',
  parent: error: column "UserId" does not exist
      at Parser.parseErrorMessage (F:\Work\Coding\stack\node_modules\pg-protocol\dist\parser.js:287:98)
      at Parser.handlePacket (F:\Work\Coding\stack\node_modules\pg-protocol\dist\parser.js:126:29)
      at Parser.parse (F:\Work\Coding\stack\node_modules\pg-protocol\dist\parser.js:39:38)
      at Socket.<anonymous> (F:\Work\Coding\stack\node_modules\pg-protocol\dist\index.js:11:42)
      at Socket.emit (node:events:390:28)
      at addChunk (node:internal/streams/readable:315:12)
      at readableAddChunk (node:internal/streams/readable:289:9)
      at Socket.Readable.push (node:internal/streams/readable:228:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:199:23) {
    length: 164,
    severity: 'ERROR',
    code: '42703',
    detail: undefined,
    hint: 'Perhaps you meant to reference the column "Shop.userId".',
    position: '50',
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'parse_relation.c',
    line: '3599',
    routine: 'errorMissingColumn',
    sql: 'SELECT "id", "userId", "createdAt", "updatedAt", "UserId" FROM "Shops" AS "Shop";',
    parameters: undefined
  },
  original: error: column "UserId" does not exist
      at Parser.parseErrorMessage (F:\Work\Coding\stack\node_modules\pg-protocol\dist\parser.js:287:98)
      at Parser.handlePacket (F:\Work\Coding\stack\node_modules\pg-protocol\dist\parser.js:126:29)
      at Parser.parse (F:\Work\Coding\stack\node_modules\pg-protocol\dist\parser.js:39:38)
      at Socket.<anonymous> (F:\Work\Coding\stack\node_modules\pg-protocol\dist\index.js:11:42)
      at Socket.emit (node:events:390:28)
      at addChunk (node:internal/streams/readable:315:12)
      at readableAddChunk (node:internal/streams/readable:289:9)
      at Socket.Readable.push (node:internal/streams/readable:228:10)
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'parse_relation.c',
    line: '3599',
    routine: 'errorMissingColumn',
    sql: 'SELECT "id", "userId", "createdAt", "updatedAt", "UserId" FROM "Shops" AS "Shop";',
    parameters: undefined
  },
  sql: 'SELECT "id", "userId", "createdAt", "updatedAt", "UserId" FROM "Shops" AS "Shop";',
  parameters: {}
}

JavaScript Code

const { Sequelize, Model, DataTypes } = require('sequelize');

const connectToDB = async () => {
    const sequelize = new Sequelize("postgres://postgres:root@localhost:5432/testDB", {
        dialect: 'postgres'
    });
    await sequelize.authenticate();
    console.log('Database connected...');
    return sequelize;
}

connectToDB().then(async (sequelize) => {
    // --- Craete User Model ---
    class User extends Model { }
    await User.init({
        id: {
            primaryKey: true,
            type: DataTypes.UUID,
            defaultValue: Sequelize.UUIDV4,
        }
    }, { sequelize, modelName: 'User' })
    await User.sync();

    // --- Craete Shop Model ---
    class Shop extends Model { }
    await Shop.init({
        id: {
            primaryKey: true,
            type: DataTypes.UUID,
            defaultValue: Sequelize.UUIDV4,
        },
        userId: {
            type: DataTypes.UUID,
            allowNull: false
        }
    }, { sequelize, modelName: 'Shop' })
    await Shop.sync();

    /* RELATION BETWEEN User AND Shop (one-to-many) */
    await User.hasMany(Shop, {
        foreignKey: 'userId',
        onDelete: "CASCADE",
    })
    await Shop.belongsTo(User)

    // --- Find All Shops --
    const shops = await Shop.findAll();
    console.log(shops);

}).catch((err) => console.error(err));

The creation of the tables is Ok as printed in console:

Executing (default): SELECT 1 1 AS result
Executing (default): CREATE TABLE IF NOT EXISTS "Users" ("id" UUID , "createdAt"TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, 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 = 'Users' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): CREATE TABLE IF NOT EXISTS "Shops" ("id" UUID , "userId" UUID NOT NULL, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, 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 = 'Shops' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;

CodePudding user response：

You need to indicate the same foreignKey option value in both relationships:

/* RELATION BETWEEN User AND Shop (one-to-many) */
    User.hasMany(Shop, {
        foreignKey: 'userId',
        onDelete: "CASCADE",
    })
    Shop.belongsTo(User, {
        foreignKey: 'userId',
    })

P.S. there is no need to indicate await because these are sync functions.