Symfony World blog is not maintained anymore. Check new sys.exit() programming blog.

documenting database with symfony doctrine

Symfony projects using Doctrine ORM have their schema defined in schema.yml files. There's a very nice & useful feature, setting comments to your table columns (which is probably not mentioned in the official docs).

It's very easy - just set a comment option to each of your columns, defining what this column holds, for example:
Customer:
  actAs:
    Timestampable: ~
    Signable: ~
  columns:
    firstname:
      type: string(32)
      notnull: true
      comment: "imię"
    lastname:
      type: string(64)
      notnull: true
      comment: "nazwisko"
    email:
      type: string(64)
      notnull: true
      comment: "adres E-mail"
The MySQL code generated for such schema is already with provided comments:
CREATE TABLE customer (
  id BIGINT AUTO_INCREMENT,
  firstname VARCHAR(32) NOT NULL COMMENT 'imię',
  lastname VARCHAR(64) NOT NULL COMMENT 'nazwisko',
  email VARCHAR(64) NOT NULL COMMENT 'adres E-mail',
  created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
  created_by BIGINT DEFAULT '1' NOT NULL,
  updated_by BIGINT DEFAULT '1' NOT NULL,
  INDEX created_by_idx (created_by),
  INDEX updated_by_idx (updated_by),
  PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE = INNODB;

This is how phpmyadmin makes use of column comments:



And below, you can see another example of comments usage - an important database table that many other tables rely on (click to enlarge):



Short and precise description makes a table structure easier to be understood by a programmer.

It takes very little time to define those database comments. But it can save you a lot of time: either if you are working on a big project that has to be well-documented and need to review the structure or if the project is going to be extended by someone else who doesn't know what is where, or you may find it useful in many different situations.

3 comments:

  1. Hello,

    Thanks a lot for your article !
    Did you know how to add comments on table itself ? And on table indexes ?

    ReplyDelete
  2. I tried the same trick with database tables, but it seems that symfony ignores them, unfortunately. The only way I know is to do it manually :(

    Anyway, documenting columns is really useful afterall and I wonder why no one wrote about that in the official docs.

    ReplyDelete
  3. Hi!

    Customer:
    options:
    comment: 'Table comment here :)'

    ReplyDelete