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
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.
- Creates a simple shell script that saves you from having to run
- symlinks this shell script into an executable path, e.g.
Downloading and installing schemacrawler
The latest releases can be found on the Github page:
Setting up schemacrawler to run on your system via
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/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:
schemacrawler is installed as an executable shell command, here’s an example of how to invoke it – change
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
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
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:
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
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: