CODEWITHSUNDEEP
postgresqlexpresssequelize.js
Rahul Dagli
Rahul Dagli

Reputation: 4502

SequelizeDatabaseError: column does not exist (Postgresql)

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
  }
};

Upvotes: 14

Views: 25172

Answers (3)

shobhit agarwal
shobhit agarwal

Reputation: 83

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

Upvotes: 5

Praveenkumar Kalidass
Praveenkumar Kalidass

Reputation: 429

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

Upvotes: 0

AbhinavD
AbhinavD

Reputation: 7282

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

Upvotes: 5

Related Questions