Documenting a database schema is often an afterthought, even though it is really important esp. for onboarding new developers into the project.

This post outlines a really straightforward approach I recently adopted for my projects using Liquibase, tbls & jOOQ.

About Liquibase

Liquibase is a great utility for managing database schema evolution. It has support for a great set of features like a YAML/XML DSL that takes care of automatic rollbacks, support for parameter interpolation, and many more which are quite handy in enterprise contexts.

Here is what the DSL looks like in YAML:

- changeSet:
    id: 1
    author: lorefnon
    changes:
    - createTable:
        tableName: approvals
        remarks: Tracks inventory order approvals
        columns:
        - column:
            name: id
            type: uuid
            constraints:
              primaryKey: true
              nullable: false
              primaryKeyName: pk_approvals
        - column:
            name: action
            type: varchar
            constraints: { nullable: false }
        - column:
            name: approver_user_id
            type: uuid
            constraints:
              foreignKeyName: fk_approvals__approver_user_id
              referencedColumnNames: id
              referencedTableName: users
              onDelete: cascade
            remarks: >-
              null value indicates that an approval was initiated but not approved yet

While liquibase is a JVM based utility, it works quite well in any web application. There is a nice CLI that you can integrate with any build pipeline without having to write Java code (though a programmatic API is also available for advanced integration).

Remarks in the Changelog DSL

While most of what is happening in the changelog fragment about is self-explanatory, the part we want to zoom into in this post are the remarks fields.

Many modern databases allow storing comments about different database level objects in the database itself. For Postgres, the manual discusses the COMMENT command in quite a bit of detail.

As you may have guessed, the remarks in our changelog YAML above, get propagated as comments stored in the db.

About tbls

tbls is a CLI friendly tool for documenting the database.

It can infer the table schema, relationships between tables through foreign keys and generate nice looking markdown documentation. There README has an example of a generated output.

Cool thing is along with the schema, it can also pick up the comments in the table, and they will get included in the generated doc as descriptions.

About jOOQ

jOOQ is a type safe query builder for JVM which I have blogged about before.

If your app is built upon java, kotlin or any other JVM language, you can use jOOQ to generate classes representing of your tables, enabling you to query the database without having to directly deal with JDBC/SQL. It is a nice sweet spot between raw SQL and full-fledged JPA-style ORMs.

Interesting thing is that if you use the jOOQ code generator, then your database level comments will be picked up by jOOQ and embedded as javadoc comments in the generated code.

/**
 * This class is generated by jOOQ.
 * Tracks inventory order approvals
 */
@Generated(
    value = {
        "https://www.jooq.org",
        "jOOQ version:3.15.1"
    }
)
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public class ApprovalsRecord extends UpdatableRecordImpl<ApprovalsRecord> implements Record4<UUID, String, UUID, UUID>, IApproval {
    
    // ...

    /**
     * Setter for <code>PUBLIC.APPROVALS.APPROVER_USER_ID</code>. null value
     * indicates that an approval was initiated but not approved yet
     */
    @Override
    public void setApproverUserId(@Nullable UUID value) {
        set(2, value);
    }

    /**
     * Getter for <code>PUBLIC.APPROVALS.APPROVER_USER_ID</code>. null value
     * indicates that an approval was initiated but not approved yet
     */
    @Nullable
    @Override
    public UUID getApproverUserId() {
        return (UUID) get(2);
    }
}

This is really convenient, because now we have easy access to our data model documentation within our IDE as well as within any database explorer. And, liquibase & jOOQ ensure that they never go out of sync.