SequelizeDatabaseError: column does not exist (Postgresql)
Solution 1
When we write Manufacturer.hasMany(models.ManufacturerTab)
and we query using include
, sequelize will assume that ManufacturerTab
contains a column called ManufacturerId
. This acts as a foreign key on which the join operation can happen.
In the model definition that you have provided
module.exports = (sequelize, DataTypes) => {
var ManufacturerTab = sequelize.define('ManufacturerTab', {
sequence: DataTypes.ARRAY(DataTypes.INTEGER),
tab_name: DataTypes.STRING
}
there is so such column and hence the error.
If you have created your db tables using migrations, you need to define a new column
ManufacturerId: {
type: Sequelize.INTEGER, // or choose what you want
allowNull: false,
references: {
model: 'Manufacturer',
key: 'id',
},
onDelete: 'CASCADE', // or choose what you want to do
Solution 2
You need to specify the foreign key to be used in association otherwise it will use default. Add foreignKey in both has belongsTo and HasMany association.
Manufacturer.hasMany(models.ManufacturerTab, {foreignKey: 'your-foreignKey'})
ManufacturerTab.belongsTo(models.Manufacturer, {foreignKey: 'your-foreignKey'})
Related videos on Youtube
Rahul Dagli
Updated on November 02, 2022Comments
-
Rahul Dagli over 1 year
I'm using Sequelize with Postgresql DB and ExpressJS.
There are 2 models
Manufacturer
andManufacturerTab
. These two models are associated with each other.Manufacturer
hasManyManufacturerTab
andManufacturerTab
belongsToManufacturer
. I'm getting an error while trying fetch the ManufacturerTabs list:{ "name": "SequelizeDatabaseError", "message": "column ManufacturerTabs.ManufacturerId does not exist", "parent": { "name": "error", "length": 130, "severity": "ERROR", "code": "42703", "position": "704", "file": "parse_relation.c", "line": "3293", "routine": "errorMissingColumn", "sql": "SELECT \"Manufacturer\".\"id\", \"Manufacturer\".\"manufacturer_name\", \"Manufacturer\".\"manufacturer_logo_url\", \"Manufacturer\".\"manufacturer_archived_status\", \"Manufacturer\".\"createdAt\", \"Manufacturer\".\"updatedAt\", \"ManufacturerTabs\".\"id\" AS \"ManufacturerTabs.id\", \"ManufacturerTabs\".\"sequence\" AS \"ManufacturerTabs.sequence\", \"ManufacturerTabs\".\"tab_name\" AS \"ManufacturerTabs.tab_name\", \"ManufacturerTabs\".\"createdAt\" AS \"ManufacturerTabs.createdAt\", \"ManufacturerTabs\".\"updatedAt\" AS \"ManufacturerTabs.updatedAt\", \"ManufacturerTabs\".\"ManufacturerId\" AS \"ManufacturerTabs.ManufacturerId\" FROM \"Manufacturers\" AS \"Manufacturer\" LEFT OUTER JOIN \"ManufacturerTabs\" AS \"ManufacturerTabs\" ON \"Manufacturer\".\"id\" = \"ManufacturerTabs\".\"ManufacturerId\";" }, "original": { "name": "error", "length": 130, "severity": "ERROR", "code": "42703", "position": "704", "file": "parse_relation.c", "line": "3293", "routine": "errorMissingColumn", "sql": "SELECT \"Manufacturer\".\"id\", \"Manufacturer\".\"manufacturer_name\", \"Manufacturer\".\"manufacturer_logo_url\", \"Manufacturer\".\"manufacturer_archived_status\", \"Manufacturer\".\"createdAt\", \"Manufacturer\".\"updatedAt\", \"ManufacturerTabs\".\"id\" AS \"ManufacturerTabs.id\", \"ManufacturerTabs\".\"sequence\" AS \"ManufacturerTabs.sequence\", \"ManufacturerTabs\".\"tab_name\" AS \"ManufacturerTabs.tab_name\", \"ManufacturerTabs\".\"createdAt\" AS \"ManufacturerTabs.createdAt\", \"ManufacturerTabs\".\"updatedAt\" AS \"ManufacturerTabs.updatedAt\", \"ManufacturerTabs\".\"ManufacturerId\" AS \"ManufacturerTabs.ManufacturerId\" FROM \"Manufacturers\" AS \"Manufacturer\" LEFT OUTER JOIN \"ManufacturerTabs\" AS \"ManufacturerTabs\" ON \"Manufacturer\".\"id\" = \"ManufacturerTabs\".\"ManufacturerId\";" }, "sql": "SELECT \"Manufacturer\".\"id\", \"Manufacturer\".\"manufacturer_name\", \"Manufacturer\".\"manufacturer_logo_url\", \"Manufacturer\".\"manufacturer_archived_status\", \"Manufacturer\".\"createdAt\", \"Manufacturer\".\"updatedAt\", \"ManufacturerTabs\".\"id\" AS \"ManufacturerTabs.id\", \"ManufacturerTabs\".\"sequence\" AS \"ManufacturerTabs.sequence\", \"ManufacturerTabs\".\"tab_name\" AS \"ManufacturerTabs.tab_name\", \"ManufacturerTabs\".\"createdAt\" AS \"ManufacturerTabs.createdAt\", \"ManufacturerTabs\".\"updatedAt\" AS \"ManufacturerTabs.updatedAt\", \"ManufacturerTabs\".\"ManufacturerId\" AS \"ManufacturerTabs.ManufacturerId\" FROM \"Manufacturers\" AS \"Manufacturer\" LEFT OUTER JOIN \"ManufacturerTabs\" AS \"ManufacturerTabs\" ON \"Manufacturer\".\"id\" = \"ManufacturerTabs\".\"ManufacturerId\";" }
Manufacturer Model:
'use strict'; module.exports = (sequelize, DataTypes) => { var Manufacturer = sequelize.define('Manufacturer', { manufacturer_name: DataTypes.STRING, manufacturer_logo_url: DataTypes.TEXT, manufacturer_archived_status: DataTypes.BOOLEAN }, { classMethods: { associate: function(models) { Manufacturer.hasMany(models.ManufacturerTab) } } }); return Manufacturer; };
ManufacturerTab Model:
'use strict'; module.exports = (sequelize, DataTypes) => { var ManufacturerTab = sequelize.define('ManufacturerTab', { sequence: DataTypes.ARRAY(DataTypes.INTEGER), tab_name: DataTypes.STRING }, { classMethods: { associate: function(models) { ManufacturerTab.belongsTo(models.Manufacturer) } } }); return ManufacturerTab; };
Manufacturer Tab routes:
var models = require('../models'); var express = require('express'); var router = express.Router({mergeParams: true}); /* Get Manufacturer Tabs List */ router.get('/', function(req, res) { var manufacturer_id = req.params.manufacturer_id; models.Manufacturer.findAll({ include: [models.ManufacturerTab] }). then(function(manufacturers) { res.status(200).json(manufacturers); }, function(error) { res.status(500).send(error); }); });
app.js
var express = require('express'); var path = require('path'); var favicon = require('serve-favicon'); var logger = require('morgan'); var cookieParser = require('cookie-parser'); var bodyParser = require('body-parser'); var cors = require('cors'); var routes = require('./routes/index'); var users = require('./routes/users'); var manufacturers = require('./routes/manufacturers'); var manufacturer_tabs = require('./routes/manufacturer_tabs'); var app = express(); // view engine setup app.set('views', path.join(__dirname, 'views')); app.set('view engine', 'pug'); // uncomment after placing your favicon in /public //app.use(favicon(__dirname + '/public/favicon.ico')); app.use(logger('dev')); app.use(bodyParser.json()); app.use(bodyParser.urlencoded({ extended: false })); app.use(cookieParser()); app.use(express.static(path.join(__dirname, 'public'))); app.use(cors({origin: 'http://localhost:4200'})); app.use('/', routes); app.use('/users', users); app.use('/manufacturers', manufacturers); app.use('/manufacturers/:manufacturer_id/manufacturer-tabs', manufacturer_tabs); // catch 404 and forward to error handler app.use(function(req, res, next) { var err = new Error('Not Found'); err.status = 404; next(err); }); // error handler // no stacktraces leaked to user unless in development environment app.use(function(err, req, res, next) { res.status(err.status || 500); res.render('error', { message: err.message, error: (app.get('env') === 'development') ? err : {} }); }); module.exports = app;
Config.js
module.exports = { development: { dialect: "postgres", username: process.env.DB_USERNAME, password: process.env.DB_PASSWORD, database: process.env.DB_NAME, host: process.env.DB_HOSTNAME }, staging: { dialect: "postgres", username: process.env.DB_USERNAME, password: process.env.DB_PASSWORD, database: process.env.DB_NAME, host: process.env.DB_HOSTNAME }, production: { dialect: 'postgres', username: process.env.DB_USERNAME, password: process.env.DB_PASSWORD, database: process.env.DB_NAME, host: process.env.DB_HOSTNAME } };