pgModeler : A great tool for Postgres data modeling

Written on July 13, 2019
Estimated reading time : 2 mins
Tags : | opensource | postgres | setup |

Story

“A picture is worth a thousand words”

I always believed in keeping documentation as visual as possible. It helps you to run through the notes fast while revising and helps in onboarding new developers swiftly.
An Entity Relationship Diagram (ERD) is a great way to represent a database without going through verbose artifacts like model files, sql scripts.

"ERD"

Popular open source tools like MySQL Workbench allow you to create ERDs from scratch, get ERDs created automatically from an existing MySQL database as well as create databases from the ERD. However its counterpart in Postgres, pgAdmin lacks this feature.
I wanted to create a ERD for a Postgres database. My apparent options were to create one manually or buy a license for commercial softwares out there. The open source ones out there weren’t as good.

pgModeler was one which stood out and came well recommended.
It was created by Raphael Araújo e Silva from Brazil using C++ and Qt. It has a paid version as well as a free version. You will need to compile it yourself to get the free version working and the instructions given might not work as well - especially if you use a Mac as your dev system.
I decided to blog on the setup in Mac which worked for me so as to help others. Most of the other

Setup on Mac

Get the source code. I have checked out the latest version even though its not the stable master branch since at this time, the mastwer branch had a bug which was resolved in the beta version.


git clone https://github.com/pgmodeler/pgmodeler.git
cd pgmodeler
git checkout tags/v0.9.2-beta

Install the dependencies


brew install qt    
brew link qt5 --force
brew install postgresql
brew install libxml2

Edit PGSQL_LIB, PGSQL_INC, XML_INC, XML_LIB in pgmodeler.pri 


PGSQL_LIB = /usr/local/opt/postgresql/lib/libpq.dylib
PGSQL_INC = /usr/local/opt/postgresql/include
XML_INC = /usr/local/opt/libxml2/include/libxml2
XML_LIB = /usr/local/opt/libxml2/lib/libxml2.dylib

Commands (note : The libssl.1. and libcrypto.1. are found in the openssl install, not in the pg install)

export QT_ROOT=/usr/local/opt/qt
export INSTALLATION_ROOT=/Applications/pgmodeler.app
export PGSQL_ROOT=/usr/local/opt/postgres
export OPENSSL_ROOT=/usr/local/opt/openssl

$QT_ROOT/bin/qmake -r CONFIG+=release pgmodeler.pro
make && make install
$QT_ROOT/bin/macdeployqt $INSTALLATION_ROOT \
                            $INSTALLATION_ROOT/Contents/MacOS/pgmodeler-ch \
                            $INSTALLATION_ROOT/Contents/MacOS/pgmodeler-cli

cp $PGSQL_ROOT/lib/libpq.5.dylib $OPENSSL_ROOT/lib/libssl.1.* \
      $OPENSSL_ROOT/lib/libcrypto.1.* $INSTALLATION_ROOT/Contents/Frameworks

install_name_tool -change "@loader_path/../lib/libcrypto.1.0.0.dylib" \
 "@loader_path/../Frameworks/libcrypto.1.0.0.dylib" \
  $INSTALLATION_ROOT/Contents/Frameworks/libssl.1.0.0.dylib

install_name_tool -change "@loader_path/../lib/libcrypto.1.0.0.dylib" \ 
 "@loader_path/../Frameworks/libcrypto.1.0.0.dylib" \
 $INSTALLATION_ROOT/Contents/Frameworks/libpq.5.dylib

install_name_tool -change "@loader_path/../lib/libssl.1.0.0.dylib" \
 "@loader_path/../Frameworks/libssl.1.0.0.dylib" \ 
 $INSTALLATION_ROOT/Contents/Frameworks/libpq.5.dylib

install_name_tool -change libpq.5.dylib "@loader_path/../Frameworks/libpq.5.dylib" \ 
 $INSTALLATION_ROOT/Contents/Frameworks/libpgconnector.dylib

You should now be able to access it via Mac’s spotlight !
One of my favourite features in this software is the option to layer different parts of the data model to hide or showcase them.

"ERD with pgModeler"

If you like the software, please don’t hesitate to donate to the creator via the official website.

References





Feel free to share this article :

submit to reddit

Add your thoughts, questions, doubts, suggestions as comments below :