1. Installing GraphViz
    1. Install Homebrew
    2. Install GraphViz
    3. Default location of GraphViz Executables
    4. Install Schemacrawler as a command-line tool
  2. How to use
  3. Changing how the diagrams look by editing the behaviour of SchemaCrawler
    1. Changing diagram settings for a single diagram
  4. BONUS! Example of a script for generating diagrams
    1. New version that runs queries and triggers also (Delivery 3)

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 libtool
brew link libtool
brew install graphviz
brew link --overwrite graphviz

Default location of GraphViz Executables

By default, the dot executable should be:

On Linux/Mac OS-X :

  • Firstly in: /usr/local/bin/dot
  • Then in: /usr/bin/dot

You can also specify the environment variable GRAPHVIZ_DOT to set the exact location of your GraphViz executable.

(from the GraphViz webpage)

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 \
  -user -password \
  -infolevel standard \
  -command schema \
  -outputformat png \
  -loglevel INFO \
  -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.


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

Changing diagram settings for a single diagram

You can place a schemacrawler.config.properties file in the folder where you run schemacrawler to override certain settings only. Here is an example I made:

schemacrawler.schema.retrieval.strategy.primarykeys=dictionary
schemacrawler.format.show_row_counts=true
schemacrawler.format.show_ordinal_numbers=true
schemacrawler.graph.graphviz.graph.fontname=Arial

BONUS! Example of a script for generating diagrams

This script was used by me for generating diagrams from the SQL scripts written by my students of Databases in the 2nd year of the Integrated Masters of Informatics Engineering at FEUP.

#!/usr/bin/env bash

function diagram
{
    local database="$(pwd)/$1"
    echo "database is $database"
    local path="$(pwd)/$2"
    echo "path is $path"
    /bin/cp "$database" "/tmp/database.db"
    /bin/ls -la "/tmp/database.db"
    /bin/bash -c '/usr/local/bin/schemacrawler -server sqlite -database /tmp/database.db -user -password -loglevel info -command schema -outputformat png -outputfile /tmp/diagram.png'
    /bin/cp "/tmp/diagram.png" "$path"
}

rm -rf database.db
cat criar.sql | sqlite3 database.db && \
	echo "Cria BD" && \
cat povoar.sql | sqlite3 database.db && \
	echo "Povoa BD"
read WAIT
diagram database.db diagram.png

New version that runs queries and triggers also (Delivery 3)

#!/usr/bin/env bash

function faz_diagrama
{
local database="$(pwd)/$1"
echo "database is $database"
local path="$(pwd)/$2"
echo "path is $path"
/bin/rm "/tmp/diagram.pdf"
/bin/cp "$database" "/tmp/database.db"
/bin/ls -la "/tmp/database.db"
/bin/bash -c '/usr/local/bin/schemacrawler -server sqlite -database /tmp/database.db -user -password -loglevel info -command schema -outputformat pdf -outputfile /tmp/diagram.pdf'
/bin/cp "/tmp/diagram.pdf" "$path"
}

function existe()
{
local ficheiro=$1
[ -f "$ficheiro" ] || (echo "Ficheiro $ficheiro não existe!" && read ERROR)
}

function corre_queries
{
for (( i = 1; i <= 10; i++ )); do
local file_path="./int${i}.sql"
printf "\n---------Ficheiro Query ${file_path}---------\n\n"
existe "$file_path" && \
cat "$file_path" | sqlite3 database.db || (echo "Erro a correr query ${i}" && read ERROR)
done
}

function testa_triggers
{
for (( i = 1; i <= 3; i++ )); do
local file_path="./gatilho${i}_XXXXXX.sql"
printf "\n---------Ficheiro Gatilho ${file_path}---------\n\n"

( existe "./gatilho${i}_adiciona.sql" || existe "./gatilho${i}_verifica.sql" ] || existe "./gatilho${i}_remove.sql" ) && \
cat "./gatilho${i}_adiciona.sql" | sqlite3 database.db && \
cat "./gatilho${i}_verifica.sql" | sqlite3 database.db && \
cat "./gatilho${i}_remove.sql" | sqlite3 database.db || (echo "Erro a correr trigger ${i}" && read ERROR)
done
}

rm -rf database.db
cat criar.sql | sqlite3 database.db && \
echo "Cria BD" && \
cat povoar.sql | sqlite3 database.db && \
echo "Povoa BD"
echo "Pressione qq tecla..."
read WAIT

corre_queries && \
echo "Corre Queries Corretamente"
read WAIT
echo "Pressione qq tecla..."

testa_triggers && \
echo "Corre Triggers Corretamente"
read ERROR

faz_diagrama database.db diagram.pdf