"Field 'createdAt' doesn't have a default value" after timestamps are added to SequelizeMeta

[mixecan]

trafficstars

What you are doing?

I'm trying to run the migration scripts after createdAtand updatedAt columns are added to the migration table.

sequelize db:migrate:status
sequelize db:migrate --to migration1.js
sequelize db:migrate:schema:timestamps:add
sequelize db:migrate --to migration2.js

What do you expect to happen?

To completed the migration.

What is actually happening?

createdAt and updatedAt columns haven't any values when the migration is added to SequelizeMeta.

ERROR: SequelizeDatabaseError: Field 'createdAt' doesn't have a default value
    at Query.formatError (~/project/node_modules/sequelize/lib/dialects/mysql/query.js:247:16)
    at Query.handler [as onResult] (~/project/node_modules/sequelize/lib/dialects/mysql/query.js:68:23)
    at Query.Command.execute (~/project/node_modules/mysql2/lib/commands/command.js:30:12)
    at Connection.handlePacket (~/project/node_modules/mysql2/lib/connection.js:502:28)
    at PacketParser.onPacket (~/project/node_modules/mysql2/lib/connection.js:81:16)
    at PacketParser.executeStart (~/project/node_modules/mysql2/lib/packet_parser.js:77:14)
    at Socket.<anonymous> (~/project/node_modules/mysql2/lib/connection.js:89:29)
    at emitOne (events.js:116:13)
    at Socket.emit (events.js:211:7)
    at addChunk (_stream_readable.js:263:12)
    at readableAddChunk (_stream_readable.js:250:11)
    at Socket.Readable.push (_stream_readable.js:208:10)
    at TCP.onread (net.js:594:20)

Dialect: mysql Database version: 5.7 Sequelize CLI version: 4.0.0 Sequelize version: 4.37.7

[ox]

I was experiencing this same issue and was unable to get migrations to log that they were run in the DB. We reverted the changes made by db:mgirate:schema:timestamps:add by running the following SQL:

begin;
alter table "SequelizeMeta" drop column if exists "createdAt";
alter table "SequelizeMeta" drop column if exists "updatedAt";
commit;

After this, migrations were able to be logged into the SequelizeMeta table again.

[mixecan]

You can keep both columns by specifying the default value for both.

ALTER TABLE SequelizeMeta CHANGE COLUMN createdAt createdAt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE SequelizeMeta CHANGE COLUMN updatedAt updatedAt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;

I think that tracking the date of a migration is important and thus I preferred the workaround above

[sanderhouttekier]

I don't understand how this is still an issue. Is this command rarely used? and never tested? It seems like it could easily be fixed with setting a default value on the command that adds the 2 columns itself.

[sanderhouttekier]

Ok, thought why not just add a PR myself but it won't be as easy as I thought. the db:migrate:schema:timestamps:add command just creates a new model with the timestamps: true option. That is problematic as I can see only 2 solutions

Make a PR to update the timestamps true you could impose a default value there, but that would be a breaking change. You could also add a second option defining the wish to add a default value (next to timestamps:true you could add a timestampsWithDefaults: true but that just looks silly.

Change the db:migrate:schema:timestamps:add command to alter the new sequelizeMeta table after it was just created.

[dongreenmedl]

@saelfaer Looks like this might be a feature that lives "under the radar". There isn't any documentation on the feature so the only way to discover it is to check the help docs from the command line (probably also why I am responding to a 3 month old comment lol).

I took a look at the method that adds the timestamps: true to the model, and it appears that it rebuilds the entire model. Is it possible that we would just need to add the createdAt and updatedAt definitions to the model, along side the name property, in this method to include the defaultValue property?

[mixecan]

There isn't any documentation on the feature so the only way to discover it is to check the help docs from the command line

It's also described here https://github.com/sequelize/cli/tree/master/docs#timestamps

The problem is that the cli updates the model but doesn't keep track of the added columns and thus umzug doesn't know of createdAt and updatedAt columns.

Umzug, in fact, supports the timestamps: true option when setting up the storage class SequelizeStorage https://github.com/sequelize/umzug/blob/ebb08d65d5365a77d54b68eb87e2cc1249cb727a/src/storages/SequelizeStorage.js#L40-L66 https://github.com/sequelize/umzug/blob/ebb08d65d5365a77d54b68eb87e2cc1249cb727a/src/storages/SequelizeStorage.js#L87

But that option is not passed by the cli when umzug is initialised https://github.com/sequelize/cli/blob/0828c1f5f9234f226d332a61c8fe34ea2e9e8f37/src/core/migrator.js#L45-L46 https://github.com/sequelize/cli/blob/2a17c7888e782514f51e64e1e786036e06fe7689/src/helpers/umzug-helper.js#L41-L54

[ShaharHD]

Still a thing ... +2 years later.

Created a PR #899 which should fix the issue.

The relevant fields are: seedersTimestamps and migrationTimestamps which can be set in the sequelize config.json

The code will also autodetect if the table already has the timestamps and will act upon it.

[HosMercury]

2022 and i have this with seeding

[reymillenium]

When seeding I'm facing the same issue.

[matusalaermiyas]

Use it like this it will fix the error

createdAt: { allowNull: false, type: Sequelize.DATE, defaultValue: Sequelize.fn("NOW"), }, updatedAt: { allowNull: false, type: Sequelize.DATE, defaultValue: Sequelize.fn("NOW"), },

[mareksuscak]

You can keep both columns by specifying the default value for both.

ALTER TABLE SequelizeMeta CHANGE COLUMN createdAt createdAt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE SequelizeMeta CHANGE COLUMN updatedAt updatedAt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP;

I think that tracking the date of a migration is important and thus I preferred the workaround above

Thanks! The SQL script adapted for Postgres users is below:

ALTER TABLE "SequelizeMeta" ALTER COLUMN "createdAt" SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE "SequelizeMeta" ALTER COLUMN "updatedAt" SET DEFAULT CURRENT_TIMESTAMP;

A full script that creates the new table and migrates the data is below:

ALTER TABLE "SequelizeMeta" RENAME TO "SequelizeMetaBackup";
CREATE TABLE IF NOT EXISTS "SequelizeMeta" ("name" VARCHAR(255) NOT NULL UNIQUE , "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("name"));
INSERT INTO "SequelizeMeta" ("name", "createdAt", "updatedAt") SELECT "name", NOW() AS "createdAt", NOW() AS "updatedAt" FROM "SequelizeMetaBackup";
ALTER TABLE "SequelizeMeta" ALTER COLUMN "createdAt" SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE "SequelizeMeta" ALTER COLUMN "updatedAt" SET DEFAULT CURRENT_TIMESTAMP;
DROP TABLE "SequelizeMetaBackup";