Setup Doctrine Migrations to update MySQL timestamp on update

One project I’m working on uses MySQL exclusively and is also using Doctrine Migrations.

I wanted to set up a column called updated that was automatically set to the timestamp of the last time the row was changed.

This is done in SQL like this:

CREATE TABLE foo (
    id INT AUTO_INCREMENT NOT NULL,
    bar VARCHAR(100) NOT NULL,
    updated timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY(id)
);

It’s not quite obvious how to do this in Migrations as it is designed to be portable across database engines and clearly this is a MySQL-ism.

To do it, you use the columnDefinition option to addColumn() within your up() method, like this:

public function up(Schema $schema)
{
  $myTable = $schema->createTable('foo');
  $myTable->addColumn('id', 'integer', ['autoincrement'=>true]);
  $myTable->addColumn('bar', 'string', ['length' => 100]);
  $myTable->addColumn(
    'updated',
    'datetime',
    ['columnDefinition' => 'timestamp default current_timestamp on update current_timestamp']
  );

  $myTable->setPrimaryKey(['id']);
}

The columnDefinition option replaces the type (which you must still set it to a valid portable option) and so you can easily put in database-specific definitions when you need to.