The following post is a copy of this Gist, an instruction recipe I wrote on how to get the superhandy schemacrawler program working on MacOS. It’s a command-line tool that allows you to easily generate SQL schemas as image files, like so:

This was tested on MacOS 10.14.5 on 2019-07-16

schemacrawler is a free and open-source database schema discovery and comprehension tool. It can be invoked from the command-line to produce, using GraphViz, images/pdfs from a SQLite (or other database type) file. It can be used from the command-line to generate schema diagrams like these:

To see more examples of commands and diagrams, visit scheacrawler’s docs: http://www.schemacrawler.com/diagramming.html

Install graphviz dependency

For schema drawing, schemacrawler uses graphviz, which can be installed via the Homebrew package manager:

brew install graphviz

Installing schemacrawler as a command-line tool

This section gives an example of how to install schemacrawler so that you can invoke it with your shell. There isn’t a Homebrew recipe, so the shell commands basically:

• Download a release zip from schemacrawler/releases
• Copies the relevant subdir from the release into a local directory, e.g. /usr/local/opt/schemacrawler
• Creates a simple shell script that saves you from having to run schemacrawler via the java executable
• symlinks this shell script into an executable path, e.g. /usr/local/bin

Downloading and installing schemacrawler

The latest releases can be found on the Github page:

https://github.com/schemacrawler/SchemaCrawler/releases/

Setting up schemacrawler to run on your system via $ schemacrawler

In this gist, I’ve attached a shell script script-schemacrawler-on-macos.sh that automates the downloading of the schemacrawler ZIP file from its Github repo, installs it, creates a helper script, and creates a symlink to that helper script so you can invoke it via:

$ schemacrawler ...

You can copy the script into a file and invoke it, or copy-paste it directly into Bash. Obviously, as with anything you copy-paste, read it for yourself to make sure I’m not attempting to do something malicious.

(An older version of this script can be found here)

A couple of notes about

The script script-schemacrawler-on-macos.sh has a few defaults – e.g. /usr/local/opt/ and /usr/local/bin/ – which are assumed to be writeable, but you can change those default vars for yourself.

One of the effects of is that it creates a Bash script named something like

Its contents are:

This script is a derivation of schemacrawler’s schemacrawler-distrib/src/assembly/schemacrawler.sh, the contents of which are:

General usage

Now that schemacrawler is installed as an executable shell command, here’s an example of how to invoke it – change DBNAME.sqlite and OUTPUT_IMAGE_FILE.png to something appropriate for your usecase:

  schemacrawler -server sqlite \
    -database DBNAME.sqlite \
    -user -password \
    -infolevel standard \
    -command schema \
    -outputformat png \
    -outputfile OUTPUT_IMAGE_FILE.png

Bootload a sample SQLite database and test out schemacrawler

Just in case you don’t have a database to play around with, you can copy paste this sequence of SQLite commands into your Bash shell, which will create the following empty database file at /tmp/tmpdb.sqlite

echo '''
DROP TABLE IF EXISTS business;
DROP TABLE IF EXISTS inspection;
DROP TABLE IF EXISTS violation;

CREATE TABLE business (
    business_id TEXT,
    name TEXT,
    address TEXT,
    city TEXT,
    postal_code TEXT,
    latitude DECIMAL,
    longitude DECIMAL,
    phone_number TEXT,
    application_date TEXT,
    owner_name TEXT
);

CREATE TABLE inspection (
    business_id TEXT,
    "Score" NUMERIC,
    date TEXT NOT NULL,
    type TEXT NOT NULL,
    FOREIGN KEY(business_id) REFERENCES business(business_id)
);

CREATE TABLE violation (
    business_id TEXT,
    date TEXT,
    "ViolationTypeID" TEXT,
    risk_category TEXT,
    description TEXT,
    FOREIGN KEY(business_id, date) REFERENCES inspection(business_id, date)
);''' \
  | sqlite3 /tmp/tmpdb.sqlite

Invoke schemacrawler like so:

schemacrawler -server sqlite \
  -user -password \
  -infolevel standard \
  -command schema \
  -outputformat png \
  -database /tmp/tmpdb.sqlite \
  -outputfile /tmp/mytmpdb.png

The output of that Bash command will be a file /tmp/tmpdb.sqlite, which looks like this:

graphviz properties

You can edit schemacrawler.config.properties, which is found wherever you installed the schemacrawler distribution – e.g. if you ran my installer script, it would be in /usr/local/opt/schemacrawler/config/schemacrawler.config.properties

Some example settings:

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

If you append the previous snippet to the default schemacrawler.config.properties, you’ll get output that looks like this:

More info about GraphViz in this StackOverflow Q:

How to influence layout of graph items?