Writing SchemaSpy Metadata in YAML

I often use SchemaSpy to share database structure within a project, but without comments, it's not easy for new members or clients to understand it just by looking.

• SchemaSpy

On this point, SchemaSpy lets you add comments to tables and columns by writing an additional XML file, even if the database itself doesn't have that information directly.

• Additional data
• SchemaMeta

While this XML file itself isn't that complex, I found writing it a bit of a hassle, so I decided to write the equivalent information in YAML instead and generate the XML from that.

Setup

The following steps have been verified to work in a Rails project.

• ruby: 2.6.5
• Rails: 6.0.1

Adding a Rake task

Add a Rake task to the Rails project that parses YAML and generates XML.

$ bundle exec rails g task meta_xml

Edit the generated lib/tasks/meta_xml.rake as follows.

require 'nokogiri'
require 'yaml'

namespace :meta_xml do
  desc 'Generate meta.xml for schemaspy from meta.yml'
  task generate: :environment do
    meta_yml_path = Rails.root.join('db/meta.yml')
    meta_yml = YAML.load_file(meta_yml_path).with_indifferent_access
    builder = Nokogiri::XML::Builder.new(encoding: 'UTF-8') do |xml|
      xml.schemaMeta('xmlns:xsi': 'http://www.w3.org/2001/XMLSchema-instance',
                     'xsi:noNamespaceSchemaLocation': 'http://schemaspy.org/xsd/6/schemameta.xsd') do
        xml.comments meta_yml[:comments]
        xml.tables do
          meta_yml[:tables].each do |table, props|
            xml.table(name: table, comments: props[:comments]) do
              props[:columns]&.each do |column, comments|
                xml.column(name: column, comments: comments)
              end
            end
          end
        end
      end
    end
    meta_xml_path = Rails.root.join('db/meta.xml')
    IO.write(meta_xml_path, builder.to_xml(indent: 2))
  end
end

Writing the YAML

As an example, I wrote information for the following tables in db/meta.yml. It includes comments for the users table, the todos table, and each of their columns.

comments: サンプルプロジェクトのデータベース

tables:
  users:
    comments: ユーザーテーブル
    columns:
      id: ユーザーID
      email: メールアドレス
  todos:
    comments: TODOテーブル
    columns:
      id: TODO ID
      user_id: ユーザーID
      text: テキスト

Outputting the XML

Now, running bundle exec rake meta_xml:generate generates the following db/meta.xml based on the contents of db/meta.yml.

<?xml version="1.0" encoding="UTF-8"?>
<schemaMeta xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://schemaspy.org/xsd/6/schemameta.xsd">
  <comments>サンプルプロジェクトのデータベース</comments>
  <tables>
    <table name="users" comments="ユーザーテーブル">
      <column name="id" comments="ユーザーID"/>
      <column name="email" comments="メールアドレス"/>
    </table>
    <table name="todos" comments="TODOテーブル">
      <column name="id" comments="TODO ID"/>
      <column name="user_id" comments="ユーザーID"/>
      <column name="text" comments="テキスト"/>
    </table>
  </tables>
</schemaMeta>

All that's left is to have SchemaSpy read this in.