There often comes the time when you have an existing database and you need to build a nice diagram to analyse it quickly. Here is how to do it using open-source software. We are going to be working on a Mac running macOS 10.14 Mojave, as well as an SQLite database.

The tool that we are going to use is Schemacrawler. It can generate very nice diagrams as you can see at the software’s home page.

Credits go out to the user @dannguyen for his Github Gist that you can find here. Thanks!

## Installing GraphViz

First, we need to install GraphViz, an open-source visualization tool. We are going to install it using Homebrew, which is like apt-get for the Mac.

### Install Homebrew

If you don’t have it already, you can install Homebrew with the following command.

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"  ### Install GraphViz Install GraphViz with the following command: brew install graphviz  ### Install Schemacrawler as a command-line tool Heavily adapted from here, only updated to the latest version of SchemaCrawler. #!/bin/bash # set up some variables to reduce redundancy _SCH_VERSION='15.06.01' _SCH_BNAME="schemacrawler-${_SCH_VERSION}-distribution"
_SCH_TNAME="/tmp/${SCH_BNAME}.zip" _SCH_URL="https://github.com/schemacrawler/SchemaCrawler/releases/download/v${_SCH_VERSION}/${_SCH_BNAME}.zip" _SCH_DIR='/usr/local/opt/schemacrawler' _SCH_SH="${_SCH_DIR}/schemacrawler-macos-opt.sh"

INITIAL_DIR="$(pwd)" # Download and unzip into /tmp cd /tmp curl -Lo${_SCH_TNAME} ${_SCH_URL} unzip${_SCH_TNAME}

# Move subdir from release package into /usr/local/opt
mkdir -p ${_SCH_DIR} cp -r /tmp/${_SCH_BNAME}/_schemacrawler/ ${_SCH_DIR} # create the shell script manually echo '#!/usr/bin/env bash' >${_SCH_SH}
echo "SC_DIR=${_SCH_DIR}" >>${_SCH_SH}
echo "java -cp \$(echo \$SC_DIR/lib/*.jar | tr ' ' ':'):\$SC_DIR/config schemacrawler.Main \"\$@\"" \
>> ${_SCH_SH} # make it executable and symlink it chmod +x${_SCH_SH}
ln -sf ${_SCH_SH} /usr/local/bin/schemacrawler cd "$INITIAL_DIR"
echo "done"


The latest releases can be found on the Github page here.

## How to use

If you have an SQL script, you need to run it to create a database first:

rm -rf database.db
cat db.sql | sqlite3 database.db


Then, you run schemacrawler to generate a diagram of the database.

  schemacrawler -server sqlite \
-database database.db \
-infolevel standard \
-command schema \
-outputformat png \
-outputfile OUTPUT_IMAGE_FILE.png


## Changing how the diagrams look by editing the behaviour of SchemaCrawler

You can change the look of the diagrams generated by SchemaCrawler by editing its properties. Just edit /usr/local/opt/schemacrawler/config/schemacrawler.config.properties.

88667/how-to-influence-layout-of-graph-items

schemacrawler.format.no_schemacrawler_info=true
schemacrawler.format.show_database_info=true
schemacrawler.format.show_row_counts=true
schemacrawler.format.identifier_quoting_strategy=quote_if_special_characters

schemacrawler.graph.graphviz.nodes.ranksep=circo
schemacrawler.graph.graphviz.graph.layout=circo
schemacrawler.graph.graphviz.graph.splines=ortho

schemacrawler.graph.graphviz.node.shape=folder
schemacrawler.graph.graphviz.node.style=rounded,filled
schemacrawler.graph.graphviz.node.fillcolor=#fcfdfc
#schemacrawler.graph.graphviz.node.color=red

schemacrawler.graph.graphviz.graph.fontname=Helvetica Neue
schemacrawler.graph.graphviz.node.fontname=Consolas
schemacrawler.graph.graphviz.edge.fontname=Consolas
schemacrawler.graph.graphviz.edge.arrowsize=1.5