Codd Schema Documentation
Codd is a utility for managing PostgreSQL migrations, written in Haskell. In Codd Experiment 3: Checkpoint Migrations, I experimented with using Codd to verify and manage checkpoint migrations. Codd can be used to verify schema documentation in the same way!
One drawback to maintaining a database schema as a sequence of
migrations is that it can be difficult to correctly understand the
current schema. Stepping through many migrations and building up a
representation of the current schema in your head is time consuming and
error prone. Using pg_dump
to take a snapshot of the current schema guarantees accuracy, but the
dump does not include valuable documentation. Codd snapshots similarly
provide accuracy but no documentation.
One solution is to (separately) maintain a documented schema that is continually updated to always represent the current schema. I maintain separate SQL files for each PostgreSQL schema. The documentation is written in Markdown in comments, and LiterateX can render the documentation as HTML and PDF. The rendered documentation can even include diagrams. I usually use Graphviz to render high-level diagrams and BurntSushi/erd to render entity-relationship diagrams.
For example, documentation for a table may include the following:
- General documentation that (briefly) describes the purpose of the table.
- Any information that is important to know but might be easy to miss, about the table, specific columns, constraints, indexes, etc.
- Relation documentation makes it easy for readers to understand relations.
- Mutation documentation makes it clear what data is immutable, and it can also provide key details about when certain data may be updated.
The documented schema must be kept in sync with migrations, and Codd provides a way to verify consistency. Applying the documented schema to an empty database should match the snapshot that Codd creates from the applied migrations. This can be automated, and a CI job can verify that developers update the documented schema whenever they add a migration.