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:WorkCodingstacknode_modulessequelizelibdialectspostgresquery.js:50:25)
at F:WorkCodingstacknode_modulessequelizelibsequelize.js:313:28
at processTicksAndRejections (node:internal/process/task_queues:96:5)
at async PostgresQueryInterface.select (F:WorkCodingstacknode_modulessequelizelibdialectsabstractquery-interface.js:396:12)
at async Function.findAll (F:WorkCodingstacknode_modulessequelizelibmodel.js:1119:21)
at async F:WorkCodingstackapp.js:47:19 {
name: 'SequelizeDatabaseError',
parent: error: column "UserId" does not exist
at Parser.parseErrorMessage (F:WorkCodingstacknode_modulespg-protocoldistparser.js:287:98)
at Parser.handlePacket (F:WorkCodingstacknode_modulespg-protocoldistparser.js:126:29)
at Parser.parse (F:WorkCodingstacknode_modulespg-protocoldistparser.js:39:38)
at Socket.<anonymous> (F:WorkCodingstacknode_modulespg-protocoldistindex.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:WorkCodingstacknode_modulespg-protocoldistparser.js:287:98)
at Parser.handlePacket (F:WorkCodingstacknode_modulespg-protocoldistparser.js:126:29)
at Parser.parse (F:WorkCodingstacknode_modulespg-protocoldistparser.js:39:38)
at Socket.<anonymous> (F:WorkCodingstacknode_modulespg-protocoldistindex.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;
Advertisement
Answer
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.