Cli: ERROR: null value in column "createdAt" violates not-null constraint

Created on 6 Aug 2018 · 14Comments · Source: sequelize/cli

What are you doing?

I'm following the tutorial here: http://docs.sequelizejs.com/manual/tutorial/migrations.html#creating-first-model-and-migration-

But when I do:

node_modules/.bin/sequelize db:seed:all

it gives to me:

ERROR: null value in column "createdAt" violates not-null constraint

I tested in a new npm init project with a new Postgres DB 10.4.

__Dialect:__ postgres
__Dialect version:__ "pg": "7.4.3",
__Database version:__ 10.4
__Sequelize version:__ "sequelize": "4.38.0"
__Tested with latest release:__ No

UPDATE:

Maybe this is a way to go? http://mmiyauchi.com/?p=2057

Source

frederikhors

👍1

Most helpful comment

@frederikhors As the error message suggests, createdAt (and also updatedAt) cannot be NULL. You need to add them with values in the bulk insert objects.

In the tutorial, it is likely that either the timestamps for the table did not have a NOT NULL constraint or they did not exist as columns on the table.

Simply add them with the current date (new Date()) as in the following:

'use strict';

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.bulkInsert('Users', [{
        firstName: 'John',
        lastName: 'Doe',
        email: '[email protected]',
        // add createdAt, updatedAt
        createdAt: new Date(),
        updatedAt: new Date()
      }], {});
  }
};

vapurrmaid on 6 Aug 2018

👍10

All 14 comments

@frederikhors As the error message suggests, createdAt (and also updatedAt) cannot be NULL. You need to add them with values in the bulk insert objects.

In the tutorial, it is likely that either the timestamps for the table did not have a NOT NULL constraint or they did not exist as columns on the table.

Simply add them with the current date (new Date()) as in the following:

'use strict';

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.bulkInsert('Users', [{
        firstName: 'John',
        lastName: 'Doe',
        email: '[email protected]',
        // add createdAt, updatedAt
        createdAt: new Date(),
        updatedAt: new Date()
      }], {});
  }
};

vapurrmaid on 6 Aug 2018

👍10

@vapurrmaid I was thinking this was something sequelize handle automgically, like Active Record from Rails or something...

frederikhors on 6 Aug 2018

I found this: http://mmiyauchi.com/?p=2057

frederikhors on 6 Aug 2018

@frederikhors I could be wrong, but I haven't seen anywhere demonstrating that sequelize handles it for you.

However I THINK (have not tested), you could add a new Date() as a default value to the Users schema.

In the migration:

createdAt: {
  allowNull: false,
  defaultValue: new Date(),
  type: Sequelize.DATE
},
updatedAt: {
  allowNull: false,
  defaultValue: new Date(),
  type: Sequelize.DATE
}

vapurrmaid on 6 Aug 2018

👍8

I found the answer; for some reason createdAt and updatedAt were added to the SequelizeMeta table -- but the migration was adding a record to the SequelizeMeta table without date values and it was THAT query that was breaking the migration.

bingomanatee on 7 Sep 2018

👍2

@bingomanatee how did you fixed it?

danielrvt on 10 Sep 2018

I deleted all the tables in my database (including sequelizeMeta) and rebuilt them by running my migrations.

The key here is that for some reason the timestamps were added to my SequelizeMeta table but Sequelize doesn't expect them to be there so the inserts have no timestamp (createdAt/updatedAt) data, and therefore, fail.

When they were rebuild the sequelizeMeta tables no longer have timestamps and the problem does not occur. I think in retrospect I could have just deleted the sequelizeMeta. Obviously in production you'll want to back up your data first.

Alternately create a fresh database, run your migrations there, and import your data from your old database, then point your system to the new database.

bingomanatee on 10 Sep 2018

👍1

Alternatively you can set up a default value for the columns of SequelizeMeta https://github.com/sequelize/cli/issues/652#issuecomment-394822748

mixecan on 15 Oct 2018

@frederikhors I could be wrong, but I haven't seen anywhere demonstrating that sequelize handles it for you.

However I THINK (have not tested), you could add a new Date() as a default value to the Users schema.

In the migration:
createdAt: {
  allowNull: false,
  defaultValue: new Date(),
  type: Sequelize.DATE
},
updatedAt: {
  allowNull: false,
  defaultValue: new Date(),
  type: Sequelize.DATE
}

thanks to @vapurrmaid it would be great if you could update your documentation (insreting default values) - migrations, so that not anyone would have to search for the solution.

mkosir on 8 Feb 2019

The timestamps in the metadata table are probably because of:
sequelize db:migrate:schema:timestamps:add

mhdtahawi on 10 Oct 2019

👍1

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.