DEV Community

Cover image for Tools for Automatically Generating DB Documents (ER Diagrams, etc.) - schemaspy, tbls
Kenta Takeuchi
Kenta Takeuchi

Posted on • Originally published at bmf-tech.com

Tools for Automatically Generating DB Documents (ER Diagrams, etc.) - schemaspy, tbls

#docker #er

This article was originally published on bmf-tech.com.

Overview

When it comes to tools for automatically generating DB documents, MySQL Workbench is probably the first that comes to mind. However, I explored other good open-source software and tried them out, so here are my thoughts.

Schemaspy

A Java-based tool that connects to a database and generates DB documents in HTML format.

There is an image available on Dockerhub, so you can easily try it out.

For MySQL 5.7 (probably 5.8 as well), you should be able to run it like this:
docker run -v "$PWD/schema:/output" --net="host" schemaspy/schemaspy:latest \
-t mysql -host {DBHOST}:{DBPORT} -db {DBNAME} -u {DBUSER} -p {DBPASSWORD}

In a MySQL 5.6 environment, you need to tweak the command a bit.
docker run -v "$PWD/schema:/output" --net="host" schemaspy/schemaspy:latest -t mysql -host {DBHOST}:{DBPORT} -db {DBNAME} -u {DBUSER} -p {DBPASSWORD} -connprops useSSL\=false -s {DBNAME}

Both can be tested with a one-liner, making it simple.

Of course, it's not limited to MySQL.

tbls

A CI-friendly DB documentation tool that generates documents in markdown.

It can be installed via dep, rpm, brew, go, or docker.

Usage is simple, so refer to the GitHub README.

I adopted it for documenting my personal applications because I want to manage all documents in markdown.

Impressions

  • When comparing only ER diagrams, MySQL Workbench might be the easiest to view. However, it depends on the number of tables. If there are few tables, any tool seems fine.
  • MySQL Workbench does not automatically exclude tables without relational connections when generating ER diagrams, but schemaspy seems to handle this properly.
  • With schemaspy, if there are many tables, the relationship lines can be hard to follow, so it would be nice if the UI allowed for some adjustments. MySQL Workbench allows manual adjustments, offering flexibility (though there is an auto-arrange feature, it seems limited...)
  • Regarding ER diagrams, there is a physical limit to outputting relationships for all tables, so users might need to be creative. I don't think there are many cases where you want to view relationships for all tables at once, so narrowing down the tables might be a good approach. I wonder if schemaspy allows narrowing down the tables to generate as ER diagrams... I haven't looked closely, but at a glance, it doesn't seem possible...
  • tbls is CI-friendly, making it easy to integrate into CI, but schemaspy also seems relatively easy to integrate.
  • rarejob-tech-dept.hatenablog.com - Integrating ER Diagram Auto-generation into CI

References

Top comments (0)