1

There are many similar questions, but I already tried most of suggested solutions without luck.

I already have database(mysql) so I skipped model definition and used sequelize-auto (programmatic). The first issue came with deprecated dependencies (sequelize-auto use sequelize v3).

Model generated from existing db via sequelize-auto (sequelize v3):

{
   "id":{
      "type":"INT(10) UNSIGNED",
      "allowNull":false,
      "defaultValue":null,
      "primaryKey":true,
      "foreignKey":{
         "source_table":"db_name",
         "source_schema":"db_name",
         "target_schema":null,
         "constraint_name":"PRIMARY",
         "source_column":"id",
         "target_table":null,
         "target_column":null,
         "extra":"",
         "column_key":"PRI",
         "isPrimaryKey":true
      }
   },
   "name":{
      "type":"VARCHAR(255)",
      "allowNull":false,
      "defaultValue":null,
      "primaryKey":false
   },
   "start":{
      "type":"DATE",
      "allowNull":false,
      "defaultValue":null,
      "primaryKey":false
   },
   "end":{
      "type":"DATE",
      "allowNull":false,
      "defaultValue":null,
      "primaryKey":false
   },
   "active":{
      "type":"TINYINT(1)",
      "allowNull":false,
      "defaultValue":"0",
      "primaryKey":false
   },
   "updated_at":{
      "type":"TIMESTAMP",
      "allowNull":false,
      "defaultValue":"CURRENT_TIMESTAMP",
      "primaryKey":false
   }
}

Column updated_at definition in create.sql:

updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

So generated model is completely missing ON UPDATE CURRENT_TIMESTAMP.

Once models are generated, I use them for sequelize v4 together with express.

Fortunatelly, it is possible to set timestamps: false in model definition, but it does not work. I also tried updated_at: false (together with underscored: true) without luck (it had no effect at all - still same error).

The only thing that worked was complete removing updated_at key from model before define. After that all worked like charm, but I need filter this column so it is not solution.

model.js:

const SequelizeAuto = require("sequelize-auto");
const Sequelize = require("sequelize");
const config = require("../conf/config.json");


const Op = Sequelize.Op; // fixed sequelize v3 bug
var options = {
    database: config.db.name,
    username: config.db.username,
    password: config.db.password,
    host: config.db.host,
    dialect: config.db.dialect,
    operatorsAliases: Op,
    pool: {
        max: 5,
        min: 0,
        idle: 10000
    },
    directory: false, // prevents the program from writing to disk
    //port: 'port',
    //tables: [],
    additional: {
        underscored: true,
        timestamps: false
    }
};

const modelOptions = {
    bb_campaign: {
        alias: "campaign"
    },
    bb_campaign_stats: {
        alias: "stat"
    }
};

function getTableOptions(tableName) {
    return {
        freezeTableName: true,
        underscored: true,
        timestamps: false,
        updated_at: false,
        tableName: tableName
    }
}

// SequelizeAuto uses old sequelize v3 + mysql (not mysql2)
var db_map = new SequelizeAuto(config.db.name, config.db.username, config.db.password, options);

module.exports = new Promise((resolve, reject) => db_map.run((err) => {
    if (err) {
        reject(err);
        return;
    }

    // atm session is closed, we need to create new
    // https://github.com/sequelize/sequelize-auto/issues/243
    let db = {};
    db.sequelize = new Sequelize(options);
    for (tableName in db_map.tables) {
        let alias = tableName in modelOptions ? 
            modelOptions[tableName].alias : tableName;

        db[alias] = db.sequelize.define(alias, db_map.tables[tableName], getTableOptions(tableName));
        console.log('Registered model for table %s as %s.', tableName, alias);

        // asociace ted nepotrebujeme
        //if (tableName in db_map.foreignKeys) {}
    }
    resolve(db);
}));

Insert:

Executing (default): INSERT INTO `bb_campaign` (`id`,`name`,`start`,`end`, `active`,`updated_at`) VALUES ('216450','item name','2018-12-02','2018-12-08',1,'CURRENT_TIMESTAMP') ON DUPLICATE KEY UPDATE `id`=VALUES(`id`), `name`=VALUES(`name`), `start`=VALUES(`start`), `end`=VALUES(`end`), `active`=VALUES(`active`);

DB error:

Unhandled rejection SequelizeDatabaseError: Incorrect datetime value: 'CURRENT_TIMESTAMP' for column 'updated_at' at row 1

Any suggestions?

bigless
  • 2,849
  • 19
  • 31

1 Answers1

0

Your date is quite wrong. You declared as timestamp in mysql table. But you inserted as CURRENT_TIMESTAMP as String.

Executing (default): INSERT INTO `bb_campaign` .... ,'**CURRENT_TIMESTAMP**') ON ....;

It should be NOW() without single quote instead.

   "updated_at":{
      "type":"TIMESTAMP",
      "allowNull":false,
      "defaultValue":**"CURRENT_TIMESTAMP"**,
      "primaryKey":false
   }

Above one, it's string again. It should be NOW() without double quote.

var user = sequelize.define('mytable', {  }, {
  ....
  timestamps: false,
  ....
});
PPShein
  • 13,309
  • 42
  • 142
  • 227
  • Thanks for pointing this, but it doesnt solve my issue. I want to exclude timestamp column updated_at from INSERT and UPDATE queries. – bigless Dec 09 '18 at 05:59
  • @bigless it's kinda of easy. Could you check it out my updated answer? – PPShein Dec 09 '18 at 06:27
  • I mentioned this solution explicitly in my question and it did not work. Did you read all my description? – bigless Dec 09 '18 at 07:12
  • Better provide your code then? Because I think `current_timestamp` value is included in your code. – PPShein Dec 09 '18 at 07:38
  • Model is generated. I dont manipulate with fields. Question updated. – bigless Dec 09 '18 at 14:09
  • the only thing I could suggest is `bb_campaign.update({ foo: 'bar' }, { silent: true });` Could you try using `silent` mode at your model while updating it? – PPShein Dec 09 '18 at 14:43
  • Do you have the same issue with updated `sequelize-auto` (version 0.7.5) ? – Steve Schmitt Dec 05 '20 at 19:16