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,
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]);
['columnDefinition' => 'timestamp default current_timestamp on update current_timestamp']
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.