Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Setup Doctrine Migrations to Update MySQL Timestamp on Update

DZone's Guide to

Setup Doctrine Migrations to Update MySQL Timestamp on Update

· Java Zone
Free Resource

Try Okta to add social login, MFA, and OpenID Connect support to your Java app in minutes. Create a free developer account today and never build auth again.

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.

Build and launch faster with Okta’s user management API. Register today for the free forever developer edition!

Topics:

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

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}