SQL Schemas from the Terminal with SchemaSpy

I’m building an app backed by MySQL and I need to easily see what its columns are and how they relate to each other so I can write good SQL queries against it. Fortunately, the database geniuses invented the schema diagram for this. I found three or four tools that let me generate a schema diagram from a running database, but the only one I found that could generate diagrams from the terminal is SchemaSpy. I’m super glad I found it because it also includes other goodies like checking for anomalies - and I can generate this on command by writing a script - no opening an app and typing in login information and clicking apply… just write the script once, run it whenever, see my database in all it’s glory.

Running SchemaSpy

Unfortunately, I had a lot of trouble connecting it to my MySQL database in Azure. Here’s how I ended up doing it:

  • Install Java 8 - I installed it with brew install openjdk@8
  • Download the schemaspy jar file
  • Download the platform independent MySQL driver and unzip it
  • Create a properties file with the contents serverTimezone=UTC (otherwise I get timezone errors)
  • Run the command
java \
    -jar ~/Downloads/schemaspy-6.1.0.jar \
    -t mysql \
    -dp ~/Downloads/mysql-connector-java-8.0.22/mysql-connector-java-8.0.22.jar \
    -connprops tmp.properties \
    -host concert-mysqlsrv-dev-weus2.mysql.database.azure.com \
    -port 3306 \
    -s concert_mysql_dev_weus2 \
    -db concert_mysql_dev_weus2 \
    -u concert-user@concert-mysqlsrv-dev-weus2 \
    -p "$TF_VAR_mysql_user_password" \
    -o ./tmpschemaspy/ \
    -vizjs \

Notes

You’re supposed to be able to pass -connprops options as part of the flag, but I couldn’t make that work for me. The -s flag refers to schema and for MySQL, that’s just the database. The -vizjs flag means I don’t have to install graphviz.