CODEWITHSUNDEEP
node.jssequelize.js
Venkatesh
Venkatesh

Reputation: 43

"ER_BAD_FIELD_ERROR: Unknown column 'id' in 'field list'" sequelize with nodejs

when i try to get data from a certain route in NODEJS thru Sequelize i get the error of unknown column which is neither in model nor route below is my code.

my model 

      module.exports = function(sequelize, DataTypes) {
    return sequelize.define('ProspectType', {
      ProspectTypeID: {
        type: DataTypes.INTEGER(11),
        allowNull: false
      },
      ProspectTypeName: {
        type: DataTypes.STRING(50),
        allowNull: true
      }
    }, {
      tableName: 'ProspectType'
    });
  };

my route

        .get('/prospectType', function (req, res) {
        models
            .ProspectType
            .findAll()
            .then(function (data) {
                res
                    .json(data);
            })
            .catch(function (error) {
                res
                    .boom
                    .notAcceptable(error);
            });
    })

even though there is no column 'id' i get this error

SequelizeDatabaseError: ER_BAD_FIELD_ERROR: Unknown column 'id' in 'field 
 list'

Upvotes: 4

Views: 18110

Answers (3)

Erik
Erik

Reputation: 1

So I was having a similar issue. I fixed this error by changing the primary key to "id" and setting a field value to whatever you want to call your primary key. For this example it would be the equivalent of:

id: {
  type: Sequelize.STRING(50),
  allowNull: false,
  field: "ProspectTypeID",
  primaryKey: true,
},

I was running into this issue because I used Sequelize-auto to generate my models, but doing this to my models fixed the issue.

Upvotes: 0

elirandav
elirandav

Reputation: 2073

For those who are using the sequelize annotations, I resolved this error by just annotating the relevant field as @PrimaryKey:

@Table({
    tableName: 'users'
})
export default class User extends Model<User> {
    @PrimaryKey
    @Column({
        type: DataTypes.INTEGER,
        allowNull: false,
    })
    public userId!: number

    @Column({
        type: DataTypes.INTEGER,
        allowNull: false
    })
    public groupId!: number
}

Upvotes: 0

AbhinavD
AbhinavD

Reputation: 7292

I am assuming that you want ProspectTypeID to be a primary key. However you have not told the sequelize that this is your primary key. Therefore if is looking for a default primary key which is id.

Just declare it as a primary key in your model and you should be good

ProspectTypeID: {
    type: Sequelize.STRING(50),
    allowNull: false,
    primaryKey: true,
  },

Upvotes: 15

Related Questions