Over a million developers have joined DZone.

Setup Doctrine Migrations to Update MySQL Timestamp on Update

· Java Zone

What every Java engineer should know about microservices: Reactive Microservices Architecture.  Brought to you in partnership with Lightbend.

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.

Microservices for Java, explained. Revitalize your legacy systems (and your career) with Reactive Microservices Architecture, a free O'Reilly book. Brought to you in partnership with Lightbend.

Topics:

Published at DZone with permission of Rob Allen, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}