# Generating cool SQLite database diagrams with schemacrawler on Mac OS

*This was tested on MacOS 10.13*

[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))

> Note: The attached 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.


## General usage

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


### Bootload a database

Just in case you don't have a database to playround with

```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
```


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



## graphviz properties

You can edit `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
```