Column Comments in PostgreSQL and MySQL: How to Document Columns Without a Migration

In PostgreSQL, column documentation can be done using COMMENT ON COLUMN, while MySQL uses the COMMENT attribute. However, many databases lack meaningful comments for columns, not because nobody wants to write them, but because the process of adding or modifying a column comment is the same as changing the column's definition: requiring a migration file, code review, deploy, and other overhead.

Schemity, a desktop ERD tool, keeps field descriptions directly in the diagram, eliminating the need for migrations or review processes. This approach allows developers to document column meanings without affecting the database's schema, type, constraints, or indexes. Editing a description in the diagram is akin to editing a comment in code, requiring only a pull request and immediate effect without impacting production.

The reason this approach isn't more widely adopted is that column comments are often viewed as mere prose, not part of schema changes. PostgreSQL comes closest to addressing this with COMMENT ON COLUMN statements, which are relatively cheap and safe. MySQL, however, lacks an equivalent, and modifying a comment means altering the entire column definition with ALTER TABLE, which can drastically change data storage.

ORMs generally avoid supporting column comments, as they are seen as niche and not critical for schema management. The problem is that documentation is treated as a schema feature, which competes with other schema features in rankings and is frequently deprioritized. In reality, field descriptions are facts about the column's meaning, not changes to its storage or constraints.

Schemity addresses this by allowing descriptions to be attached to fields within the diagram's JSON file, separate from the database. This keeps descriptions out of the migration path, making them easy to add, modify, and document without the need for migrations or reviews. The absence of descriptions in the database means writing one produces no migration to review or run, resulting in a more streamlined and efficient documentation process.