table.timestamps() should set ON UPDATE CURRENT_TIMESTAMP for updated_ts

[jsnajdr]

Expected:
When creating the updated_ts column in table.timestamps(), its DDL should be:

timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Actual:
The ON UPDATE CURRENT_TIMESTAMP part is missing - the updated_ts column is never autoupdated.

The syntax I'm describing works for MySQL, I'm not sure how compatible across database engines it is.

[elhigu]

At least with postgres syntax is not exactly that nice:

http://stackoverflow.com/questions/1035980/update-timestamp-when-row-is-updated-in-postgresql

Though I would like to have .autoUpdate() builder method for timestamp column, then it would be trivial to enable that for .timestamps() helper too.

Looks like SQLite3 also has triggers which could be used to implement this. Oracle and mssql might have also some nicer syntax for it or at least it could be done with triggers.

[gaastonsr]

This would be so nice! currently we need to depend on bookshelf updating our timestamps right?

[codeclown]

Instead of relying on bookshelf you can create the datetime-fields manually (mysql):

table.dateTime('created_at').notNullable().defaultTo(knex.raw('CURRENT_TIMESTAMP'))
table.dateTime('updated_at').defaultTo(knex.raw('NULL ON UPDATE CURRENT_TIMESTAMP'))

Or if you prefer updated_at to default to creation time as well:

table.dateTime('created_at').notNullable().defaultTo(knex.raw('CURRENT_TIMESTAMP'))
table.dateTime('updated_at').notNullable().defaultTo(knex.raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))

[Ice32]

Instead of relying on bookshelf you can create the datetime-fields manually (mysql):

table.dateTime('created_at').notNullable().defaultTo(knex.raw('CURRENT_TIMESTAMP'))
table.dateTime('updated_at').defaultTo(knex.raw('NULL ON UPDATE CURRENT_TIMESTAMP'))

Using the code you posted for updated_at with null default still inserts current timestamp for updated_at.

const UserModel = bookshelf.Model.extend({
  tableName: 'users',
  hasTimestamps: ['createdAt', 'updatedAt'],
});

This test fails:

it('doesntStoreUpdatedAtForCreation', async () => {
    // arrange && act
    const inserted = await new UserModel().save();

    // assert
    const retrievedModel = await UserModel.where({ id: inserted.id }).fetch();
    assert.notExists(retrievedModel.get('updatedAt'));
  });

Edit:
Nevermind, seems that completely removing hasTimestamps from model definition fixes this.

[ricardograca]

We're mixing Knex and Bookshelf issues here, but @Ice32 if you don't want to use the automatic timestamp feature of Bookshelf, you have to remove hasTimestamps: ['createdAt', 'updatedAt'] from your model definition. The code that @codeclown posted above will then work as expected.

[DealerUp]

My Solution:

const Database = use("Database");
/*......*/
table
        .timestamp("created_at")
        .notNullable()
        .defaultTo(Database.raw("CURRENT_TIMESTAMP"));

table
        .timestamp("updated_at")
        .notNullable()
        .defaultTo(
            Database.raw("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")
        );

[ericbiewener]

I think timestamps() needs to either provide this auto-update functionality or the docs need to explicitly state that it does not. I simply assumed that functionality was a critical reason for why the timestamps() method exists at all.

[pikonha]

In case you're looking for a way to create this columns in an automated way, I've got this solution from the docs:

table.timestamps(true, true);

[lualfer99]

En caso de que esté buscando una forma de crear estas columnas de forma automatizada, tengo esta solución de los documentos:

mesa . marcas de tiempo ( verdadero ,  verdadero ) ;

why two true's ??

[pikonha]

@lualfer99 documentation

[ImranHaider313]

```js
table.dateTime('created_at').notNullable().defaultTo(knex.raw('CURRENT_TIMESTAMP'))
table.dateTime('updated_at').notNullable().defaultTo(knex.raw('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))

It only auto insert values at first time, while on update it throws NULL error for updated_at. @codeclown answer seems like a better option