How to manually set updatedAt / createdAt

[stephenjwatkins]

I'm working on some tests and I need to manually set the updatedAt attribute. However, it always gets updated to the current date. For example, the following code does not override the updatedAt attribute, as I expected it to:

instance.updateAttributes({
  updatedAt: moment.utc().subtract(31, 'minutes').toDate()
}, {
  silent: true
});

Am I missing something related to the proper use of silent?

I'm running on v2.1.3.

[janmeier]

Yeah, this usecase is not really supported I guess - save will always set updatedAt to the current timestamp, unless you set silent to true, in which case it will ignore it completely.

I guess it actually makes to only set updatedAt if changed returns false

[mickhansen]

@stephenjwatkins as a work around you could use sinon clock for testing.

[stephenjwatkins]

@mickhansen I appreciate the suggestion. I will indeed use sinon clock for testing to work around this.

[ssteffl]

i wish there was a "force" flag for .save() on an instance, that just set the updatedAt flag even if nothing was changed. Im trying to use updatedAt field as a timestamp of the last time a record was known to be valid, since i'm syncing stuff in my db with a 3rd party's db. That way I can purge items from my db that are stale.

i dont want to create a new column for this if i could just get the same functionality out of the updatedAt field

[janmeier]

@ssteffl you can do instance.changed('updatedAt', true)

[ssteffl]

ok cool. that seems to work. im just doing:

existingRecord.changed('updatedAt', true);
return existingRecord.update(data, {transaction: txn});

and it seems to be setting the updatedAt correctly. Thanks!

[nathanmarks]

@mickhansen Any idea if it's possible to do this without using raw sql? (ie using the sequelize instance API).

My use case is that I am creating/updating entities in an offline application (uuid primary key) and setting updatedAt in that application. The application syncs with an online database via a node API using sequelize. The "updated" updatedAt after syncing with the server is causing some edge case issues.

Accepting PRs for this?

[nathanmarks]

Additionally, if this is something that you would be open to having implemented, do you have any opinions on what the user should pass in terms of options to the set+save/update/etc functions? I haven't contributed before here and not sure what the most idiomatic option is.

[mickhansen]

A PR adding a force option to save could do the trick, check it here: https://github.com/sequelize/sequelize/blob/master/lib/instance.js#L616

[nathanmarks]

Hey Mick,

I implemented this last night. It required modifying the set function too. Otherwise it is caught on the read only check.

I can provide more details when I'm on my computer. I have this running, with a test, albeit with a different option name. (I used manualUpdate). I'll change it to force, however because of raw I think forceTimestamps would be significantly more expressive and easy to understand.

[mickhansen]

Should be no need to modify the set function.
The save() function should set the updatedAt value with raw: true if a save is called.
I like force because that's what happens, a forced save.

[nathanmarks]

force sounds good.

Regarding your other point - this would mean that the user would have to call set twice if they don't want to set all the data using raw: true if they have virtual setters for their data.

At the moment, if you call set with updatedAt, the save function won't even see your updatedAt as the set function throws it out without options.raw = true.

I can't speak for others -- but my use case would still benefit from proper validation + invoking of virtual setters for the other columns. The goal of this in my eyes is to be able to set the timestamp manually without compromising the other columns.

However, this isn't my library -- if you want to restrict this functionality to updating/setting using raw: true that's fine by me obviously.

One more thought about force -- don't you think that implies that the other updated columns skip validation? Could be a source of confusion for some.

[mickhansen]

@nathanmarks Hm, i might have missed what you're intending to do here, are you overwriting updatedAt manually or are you trying to have save update it regardless?

Valid comment about force and validations.

[nathanmarks]

@mickhansen

Basically, I have entities being created + updated in an offline context.

When the application syncs with the online API, assuming everything is a-okay, I want to retain the updatedAt time that was set on the client.

For syncing offline, cross-device, it's important that the updatedAt times are representative of the last time the core data of that entity was changed (or created for the first time). Unless something happens in validation, I want to pass my updatedAt attribute and set that with the rest of the data.

Here's a contrived example:

const twoHoursAgo = new Date(Date.now() - 7200000);

const newData = {
  id: 'abcd-1234-5678-0123',
  name: 'Foo',
  updatedAt: twoHoursAgo
};

Model.findById(newData.id).then(instance => {

  // if null, create
  // or if found..... then update

  return instance.update(newData, {
    forceTimestamps: true
  });
}).then(instance => {
  // updatedAt should be my provided date (2 hours ago), not Date.now()
});

[mickhansen]

Hmm okay, not sure how we solve that best actually.

[nathanmarks]

@mickhansen I have an implementation running but it isn't necessarily 10000% ideal as it requires the option is passed to both set and save if you call them separately. (If using update, obviously you just pass it the once).

It also allows you to pass in the option to Model.create. Without it, you end up with the createdAt sent to the sequelize being honoured but the updatedAt is set to whenever the record made it to through sequelize.

[mickhansen]

Does set with raw: true and save with silent: true and fields: ['updatedAt'] work?

[nathanmarks]

@mickhansen Not working for me.

Something interesting to note though, consider the following test:

it('updates timestamps when passing forceTimestamps=true', function() {
  var self = this;

  return this.User.create({ username: 'user' }).then(function(user) {
    var updatedAt = new Date(Date.now() - 7200000);

    user.set({
      updatedAt: updatedAt
    }, { raw: true });

    return user.save({
      silent: true,
      fields: ['updatedAt']
    }).then(function(user1) {
      expect(user1.updatedAt).to.equalTime(updatedAt); // THIS PASSES
      return self.User.findById(user1.id);
    }).then(function(user2) {
      expect(user2.updatedAt).to.equalTime(updatedAt); // THIS FAILS
    });
  });
});

Instance.save() trips up here but returns the instance with the set attribute: https://github.com/sequelize/sequelize/blob/master/lib/instance.js#L637

I know it adds an extra option -- but do consider that the implementation I've already outlined is only about 12 LoC excluding tests and docs with a much cleaner usage example. EDIT and it also retains virtual setter etc... functionality for other columns and does not skip checking for other read-only columns, allowing you to call Instance.set() just the one time or use Instance.update() and it also works with Model.create().

[mickhansen]

Hmm, that line might be redundant since we do changed/fields checks earlier in the process.
Can you show your implementation? Love to give it a look.

[nathanmarks]

@mickhansen Ignore the name of the option as it's currently manualUpdate (from before we spoke in this thread) master...nathanmarks:manual-updatedat

As a side node, in the test I embedded inline above, is the first expectation passing by design? It seems weird that I can get a successful promise fulfillment from save containing an attribute that was not committed to the database.

[mickhansen]

@nathanmarks Sequelize calls always return promises, regardless of whether or not it was a no-op internally or not.

I'm trying to figure out the best way to solve this.
So in your case you're doing a type of ETL, so a verbatim option might make sense here.
In terms of general setting of updatedAt raw + silent should really work.

[nathanmarks]

@mickhansen The issue isn't a promise being returned (most of my JS apps involve promise consumption, usually with a heavier sprinkling of rejection than sequelize though) -- it just seems counterintuitive that the Instance.save() promise fulfillment arg (what I assumed to be the saved instance) passed to the Promise.then success handler is not true to the result of the save operation.

This resulted in a test I wrote showing a false positive because I was checking the updatedAt property of the instance passed in the save() promise fulfillment. In order to make the test work as intended -- I had to request the row again. The instance returned from that had the 'correct' (what actually made it to the DB) properties.

I modified my test case to use raw + silent (as shown above) and it failed. Are you saying you'd prefer to see the functionality implemented in a way that works using those options?

Given the current description of options.silent, that could be considered a breaking API change:

If true, the updatedAt timestamp will not be updated.

The other main issue with that is that it would prevent using Instance.update() and Model.create() shortcut methods unless you want to skip virtual setters and read only configs for every property you are updating/creating. My use case calls for retaining that functionality (so not passing raw) while updating the updatedAt timestamp.

I haven't tested this yet (about to) but i'm also going to be pushing a client generated deletedAt timestamp for records.

Just to summarize, these are the needs:

1. Update/create rows
2. Manually set timestamps
3. Retain virtual setter functionality for non-timestamp columns, ideally in a manner that is compatible with the shortcut methods like update().

[mickhansen]

@nathanmarks save should be resolved itself (it's instance).

It was a workaround suggestion, not the way i'd like it to work moving forward.
forceTimestamps/manualTimestamps just seems a tad specific to me, it's about solving a ETL/Sync issue (just that timestamps right now is the manifestation)

[nathanmarks]

@mickhansen want a PR with a failing test when checking the attributes of an instance returned from save then?

Right now for a workaround I'm going to rebase my branch as the proposed method doesn't seem to work.

I totally agree about the terminology, however things like verbatim could just as easily refer to the functionality that raw provides. While the option seems specific, it's also targeting a very specific problem (which is timestamps not being settable basically).

An alternative option is to open up the raw option and alternatively allow the user to pass an array of fields to process as raw -- and then make the necessary changes for this to work (assuming it is possible in a clean enough manner).

[nathanmarks]

@mickhansen Let me know if you want to see a proof of concept for the raw array option, I think it's an interesting way around your concern of adding an option that's so specific to the goal here.

[mickhansen]

@nathanmarks A test would be great.

I'm not at all sure how to solve this best, i'd love to see a proposal in a PR.

[nathanmarks]

@mickhansen I'll submit a failing test for that separate issue and put together a PR for the timestamp conundrum!

Totally understand if you're on the fence and not sure -- but if you could let me know whether you prefer an additional option or potentially leveraging the raw option and expanding the possible use of it, that would help me shape the proposal.

[tremby]

Poking my head in to say a force option for save would be very handy. Even a standalone touch method. All I want to do in a particular use case right now is update the timestamps.

I tried instance.changed('updatedAt', true) and that does do the trick, but it's not very intuitive, and I doubt I'll remember it next time I need to do this.

[ccckblaze]

instance.changed("updatedAt", true);
await instance.save();

updatedAt updated to Date.now()

works like charm!
cheers

[rsshilli]

For the next poor soul, when upgrading to Sequelize 5 beware that if you set updatedAt in Sequelize 3, paranoid gets set to false automatically. That is not the case in Sequelize 5.

[papb]

Hi @rsshilli can you please create an SSCCE to show what you mean?

[rsshilli]

Actually, I think the Sequelize 5 code is the right way to do it. There's nothing to fix. Setting updatedAt shouldn't modify the paranoid value.

[EndangeredMassa]

I also needed to be able to force a value for timestamps in a test context.

I managed to force a value for timestamps by running a separate query that converts an instance to a simple SQL statement. Your backend DB may require different syntax, but the premise here should get you where you want to go.

Note that I'm using Sequelize 3.

/**
 * @param {Sequelize.Model} instance
 * @param {string} somebody -  is an object where the keys can be anything
 *   but the values must be objects with a `toISOString` method, such as
 *   `Date` or `moment`.
 */
async function forceTimestamps(instance, timestamps) {
    let tableName = instance.$Model.tableName;
    let idField = instance.$Model.primaryKeyField;
    let idAttr = instance.$Model.primaryKeyAttribute;
    let id = instance[idAttr];

    let updates = '';
    Object.keys(timestamps).forEach((key) => {
        let value = timestamps[key];

        if (value.toISOString) {
            value = value.toISOString();
        }

        if (updates.length > 0) {
            updates += ',';
        }

        updates += `\n"${key}"='${value}'`;
    });

    let query = `
        UPDATE ${tableName}
        SET ${updates}
        WHERE "${idField}"='${id}'
    `;
    return sequelize.query(query, { type: sequelize.QueryTypes.UPDATE });
}

[rimiti]

@ccckblaze

await instance.changed("updatedAt", true);
await instance.save();

updatedAt updated to Date.now()

works like charm!
cheers

As mentioned into the documentation changed() doesn't return a promise. So, to properly update the updatedAt field, you just need to do that:

instance.changed("updatedAt", true);
await instance.save();

[ccckblaze]

@ccckblaze

await instance.changed("updatedAt", true);
await instance.save();
updatedAt updated to Date.now()
works like charm!
cheers

As mentioned into the documentation changed() doesn't return a promise. So, to properly update the updatedAt field, you just need to do that:

instance.changed("updatedAt", true);
await instance.save();

you're right, thanks for the correction

[mariusa]

How can we set updatedAt to the database current timestamp? In postgres: 'updated_at = now()'

We don't want to set the date from nodejs (slightly different), also don't want to use db triggers.

Answer:
item.updated_at = sql.literal('CURRENT_TIMESTAMP')

[adonespitogo]

I encountered this problem while trying to implement database-agnostic DB backup of json files. My solution was to set model definination timestamps to false, set the defaultValue of the attribute to Sequelize.NOW and manually providing the updatedAt field upon update.

[gorbak25]

Any progress here?

[rsshilli]

Why is this issue still open? There's nothing to fix.

[gorbak25]

@rsshilli Using the latest release of Sequelize I'm unable to set createdAt/updatedAt to a specific date using any listed workaround in this thread. Even a raw SQL query using sqlite as the backend doesn't work :(

[arnaudambro]

I've tried a lot of stuff, but this helped me

https://stackoverflow.com/a/52415630/5225096

await MyModel.update({ updatedAt }, { where: { id: instance.id }, silent: true });

[AnechaS]

try manual update

sequelize.getQueryInterface().queryGenerator.updateQuery(
      'YOU_TABLE',
      { updated_at: sequelize.literal('CURRENT_TIMESTAMP') },
      { id: 1 },
      { returning: false },
)

sequelize.query(query)

[alisherafat01]

I want to set updatedAt manually while creating object
Any solution?

[faisal-nstu]

I've tried a lot of stuff, but this helped me

https://stackoverflow.com/a/52415630/5225096

await MyModel.update({ updatedAt }, { where: { id: instance.id }, silent: true });

This worked for me. Thanks @arnaudambro

[ch3ll0v3k]

Found on StackOverflow. works for me...

https://stackoverflow.com/questions/64250923/change-value-of-createdat-in-sequelize

const yesterday = ( d => new Date(d.setDate(d.getDate()-1)) )(new Date);

hang.changed('createdAt', true);
hang.set('createdAt', yesterday,{raw: true});
await hang.save({ silent: true, fields: ['createdAt']  });    

//  "sequelize": "^6.29.3",
//  "sequelize-cli": "^5.5.1",

[Feliperojas2601]

Is any TS solution?, I got It is not possible to assign an argument of type ""updatedAt"" to the parameter of type "keyof Model<any, any>". when using changed()

[sham-beckett]

Struggling with the same TS issue