Generating ER Diagrams directly from *.sql files without even installing mysql

​DBeaver or other similar open source GUI programs for DBMS can draw the entity-relationship diagram for a live mysql database. Yet the DDL (e.g. create table statements) alone should give enough information for the ER diagram. Can I draw it without even installing mysql? I found at least two projects that come close to fulfilling my needs.

The first one is ehne’s erdot written in python. It accepts a .json file as input and produces a .dot file to be fed to graphviz to produce the diagram in various file graphic formats. Installation is a simple command:pip3 install ERDot . The executable is in ~/.local/bin/ . If echo $PATH does not show this directory, you may want to add export PATH=$PATH:$HOME/.local/bin at the end of ~/.bashrc and open a new terminal for it to take effect.

The second one is Andrew Gallant’s (BurntSushi’s) erd written in haskell. It takes a simple text file of the .er format and generates the ER diagram in one of many graphic file formats or as a .dot file. On my linux mint 20.2, this is how I install it:

sudo apt install -y haskell-stack
stack upgrade
wget https://github.com/BurntSushi/erd/archive/refs/heads/master.zip -O erd-master.zip
unzip erd-master.zip
cd erd-master/
stack install

Notes: (1) The installation of erd and its dependencies takes up (maybe much) more than 3.5G of space and filled up my virtual machine! I had to switch to my host OS to complete it. (2) stack upgrade may ask you for sudo permission to install the binary. Without this step, you would see some error messages about 404 not found.

So I wrote two small scripts pmysql.php and json2erd.py which together convert a .sql file into one of the two formats suitable for input to the above two programs. The first script is written in php only because I want to call Cal Henderson’s SQLParser . Otherwise I would have written everything in python. Download pmysql.php into an empty directory and type composer require iamcal/sql-parser in that directory to install the dependency. Download json2erd.py into ~/.local/bin/ and chmod a+x ~/.local/bin/json2erd.py .

workflow

Now download the sakila sample database and extract sakila-schema.sql then php pmysql.php /path-to/sakila-schema.sql | json2erd.py -t busu | erd -f svg > sakila-busu.svg to generate the diagram in svg format. As for ehne’s erdot, it does not work in a pipe, and therefore it takes 3 separate steps to generate the svg file:

php pmysql.php /path/to/sakila-schema.sql | json2erd.py -t ehne > sakila-ehne.json
erdot sakila-ehne.json
dot -Tsvg sakila-ehne.dot > sakila-ehne.svg

Another example is the school database. In the DDL the references to table names are prefixed by the database name, which SQLParser does not recognize. Therefore we have to remove all those prefixes before we can send it to pmysql.php:

wget https://raw.githubusercontent.com/travisrecupero/School-Database-in-mySQL/master/School%20Database.sql
perl -pe 's/`Final`\.//g' School\ Database.sql > ~/school.sql
php pmysql.php ~/school.sql | json2erd.py -t busu | erd -f svg > school-busu.svg

Here are the final svg diagrams.

https://ckhung.github.io/a/m/22/erd/sakila-busu.svg

https://ckhung.github.io/a/m/22/erd/sakila-ehne.svg

https://ckhung.github.io/a/m/22/erd/school-busu.svg

https://ckhung.github.io/a/m/22/erd/school-ehne.svg

本文有中文版。