Pragmatism in the real world

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.

2 thoughts on “Setup Doctrine Migrations to update MySQL timestamp on update

  1. Hi Rob!

    Thanks again for this quick tip, as always it's very useful.

    I was just wondering, because I am currently using mysql there is no problem with this solution, but what happens when using different DB? Do you see any downside on using the following expression:

    ['default' => $this->connection->getDatabasePlatform()->getCurrentTimestampSQL()]

    I believe this would solve the multi-db-type problem, but then I'm not good enough in Doctrine and other dbms to understand whether this could bring issues later on…

Comments are closed.