SequelizeDatabaseError: column does not exist (Postgresql)

Question

I'm using Sequelize with Postgresql DB and ExpressJS.

There are 2 models Manufacturer and ManufacturerTab. These two models are associated with each other. Manufacturer hasMany ManufacturerTab and ManufacturerTab belongsTo Manufacturer. 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
  }
};

Can u explain me on how did you do association? If possible share the code. — Praveenkumar Kalidass
– Praveenkumar Kalidass, Commented Jun 27, 2018 at 9:53
@PraveenKumar Inside the model, I've defined the association: classMethods: { associate: function(models) { ... } } — Rahul Dagli
– Rahul Dagli, Commented Jun 27, 2018 at 9:55
Not this, you should have called this associate method somewhere. I need that code — Praveenkumar Kalidass
– Praveenkumar Kalidass, Commented Jun 27, 2018 at 9:56
@PraveenKumar, No I've not defined the association anywhere else. I think sequelize auto generates foreign keys while running the migration? — Rahul Dagli
– Rahul Dagli, Commented Jun 27, 2018 at 10:01
I am not telling about defining association and foreign key generation. I am asking about calling the associate methods. Can u share me the sequelize open connection code?? — Praveenkumar Kalidass
– Praveenkumar Kalidass, Commented Jun 27, 2018 at 10:59

AbhinavD · Accepted Answer · 2018-06-28 03:50:07Z

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

shobhit agarwal · Accepted Answer · 2021-07-29 08:40:34Z

5

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'})

answered Jul 29, 2021 at 8:40

shobhit agarwal

832 silver badges4 bronze badges

Comments

Praveenkumar Kalidass · Accepted Answer · 2018-06-29 01:39:15Z

0

Your code does not have anything related to creating connection and association. Include the following code as a separate file in the directory where you have models

fs.readdirSync(__dirname)
    .filter(function(file) {
        return (file.indexOf('.') !== 0) && (file !== basename) &&
            (file.slice(-3) === '.js');
    })
    .forEach(function(file) {
        var model = sequelize['import'](path.join(__dirname, file));
        db[model.name] = model;
    });

Object.keys(db).forEach(function(modelName) {
    if (db[modelName].associate) {
        db[modelName].associate(db);
    }
});

edited Jun 29, 2018 at 1:39

answered Jun 27, 2018 at 14:55

Praveenkumar Kalidass

4295 silver badges12 bronze badges

Collectives™ on Stack Overflow

SequelizeDatabaseError: column does not exist (Postgresql)

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related