ER_BAD_FIELD_ERROR when using Waterline ORM in Sails.js

Question

I'm getting the following error when trying to call .create() on a model from a Sails.js controller.

enter image description here

Here is my model, TemperatureReading.js:

module.exports = {
  connection: 'mainDatabase',
  tableName: 'TemperatureReading',

  attributes: {

      id: {
        columnName: 'id',
        type: 'integer',
        minLength: 1,
        primaryKey: true,
        unique: true
      },

      deviceId: {
        columnName: 'deviceId',
        type: 'integer',
        minLength: 1,
      },

      temperatureReading: {
        columnName: 'temperatureReading',
        type: 'string',
        max: 150,
        required: true
      },

      dateRecorded: {
        columnName: 'dateRecorded',
        type: 'string',
      },
  }
};

routes.js:

module.exports.routes = {
    'get /enterSensorReading': 'MainController.getSensorReading'
};

MainController.getSensorReading:

getSensorReading: function (request, response) {

    var temperatureReading = request.param('temperatureReading');
    var date =  new Date(); 
    var dateRecorded = date.getDate() + "/"
                     + (date.getMonth()+1)  + "/" 
                     + date.getFullYear() + " " 
                     + date.getHours() + ":"  
                     + date.getMinutes() + ":" 
                     + date.getSeconds();

    console.log(temperatureReading);

    TemperatureReading.create({temperatureReading: temperatureReading, dateRecorded: dateRecorded}).exec(function(err, createdReading) {
        if(err) {
            response.send(err);
        } else {
            console.log('Created reading');
        }
    });

}

connections.js

module.exports.connections = {
      mainDatabase: {
      adapter: 'sails-mysql',
      host: '127.0.0.1',
      port: 3306,
      user: 'myUser',
      password: 'myPW',
      database: 'MAIN'
   }
};

And finally, my database structure:

TemperatureReading
------------------
id int(5) PK 
deviceId int(5) 
temperatureReading varchar(255) 
date varchar(255)

Any ideas as to what is going wrong?

can u pls also log the error in console. Sometimes, more information is provided by console log as compared to the error object sent in response — Mandeep Singh, Dec 02 '14 at 09:57

Mandeep Singh · Accepted Answer · 2014-12-02T20:19:03.243

The database structure should be something like this:

mysql> describe TemperatureReading;
+--------------------+--------------+------+-----+---------+----------------+

    | Field              | Type         | Null | Key | Default | Extra          |
    +--------------------+--------------+------+-----+---------+----------------+
    | id                 | int(11)      | NO   | PRI | NULL    | auto_increment |
    | deviceId           | int(11)      | YES  |     | NULL    |                |
    | temperatureReading | varchar(255) | YES  |     | NULL    |                |
    | dateRecorded       | varchar(255) | YES  |     | NULL    |                |
    | createdAt          | datetime     | YES  |     | NULL    |                |
    | updatedAt          | datetime     | YES  |     | NULL    |                |
    +--------------------+--------------+------+-----+---------+----------------+
    6 rows in set (0.00 sec)

It should be dateRecorded and not just date

If its a new app and you are creating the table from scratch, use add "migrate": "drop" in your model. This will automatically create the table for you. Make sure to remove this line next time you lift the server. If this does not solve the problem, then I don't see any issue with the code. I tested and it worked perfectly on my system. It might be possible that something messed up with your sails-mysql adapter. If fixing your database structure does not solve the problem, try this

npm uninstall sails-mysql
npm cache clear
npm install sails-mysql

Even more rigorous way would be:

rm -Rf node_modules
npm cache clear
npm install

Let me know if this solves the problem you are facing

ER_BAD_FIELD_ERROR when using Waterline ORM in Sails.js

1 Answers1