# Installing and using schemacrawler for MacOS ## A recipe for generating cool SQLite database diagrams with schemacrawler on MacOS *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](http://www.schemacrawler.com/). It can be invoked from the command-line to produce, using [GraphViz](http://www.graphviz.org/), images/pdfs from a SQLite (or other database type) file. It can be used from the command-line to generate schema diagrams like these: ![image](https://user-images.githubusercontent.com/121520/41448959-de545a74-7012-11e8-99f9-520d76a683b8.png) 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](https://brew.sh/) package manager: ```sh 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](https://github.com/schemacrawler/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](#file-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: ```sh $ 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](#file-OLDscript-schemacrawler-on-macos-sh)) #### A couple of notes about The script [script-schemacrawler-on-macos.sh](#file-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](https://github.com/schemacrawler/SchemaCrawler/blob/a3fea8be74ae28d6e8318c14f2c3f4be314efe2a/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: ```sh 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` ```sh 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 \ -database /tmp/tmpdb.sqlite \ -user -password \ -infolevel standard \ -command schema \ -outputformat png \ -outputfile /tmp/mytmpdb.png ``` The output of that Bash command will be a file `/tmp/tmpdb.sqlite`, which looks like this: ![/tmp/tmpdb.sqlite](https://user-images.githubusercontent.com/121520/61403484-ede86e80-a8c4-11e9-8bda-f9ceb63b6101.png) ## GraphViz visual 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: ![image](https://user-images.githubusercontent.com/121520/61417204-f00ef500-a8e5-11e9-9e97-e690b23e31aa.png) More info about GraphViz in this StackOverflow Q: [How to influence layout of graph items?](https://stackoverflow.com/questions/11588667/how-to-influence-layout-of-graph-items)