Heroku Postgres and ER diagrams

How to create ER diagram of PostgreSQL database running in Heroku? With Moon Modeler, the process is simple. Just create a database connection, retrieve the information from Heroku Postgres and create a project containing the ER diagram. But let’s start one step at a time.

What is Heroku and Heroku Postgres?

Heroku is a platform as a service (PaaS) that enables developers to build, run, and operate applications entirely in the cloud. And Heroku Postgres is a managed SQL database service provided directly by Heroku. For more information visit: Heroku Postgres

Sample database structure in Heroku Postgres

Let’s say we have a database structure created in Heroku Postgres.

For this article, we will be working with the Pagila sample database. You can add this database to your Heroku Postgres environment by running an SQL script from pgAdmin.

Heroku - pgAdmin

Creating a Heroku Postgres database connection

Heroku

To create a new database connection in Moon Modeler, you will need database credentials. What credentials will be needed and where to find them in Heroku? Take a look at the screenshot below:

Heroku Postgres - database credentials

This is exactly the information we will use.

Moon Modeler

To create a new connection, click on the Connections button and then click on Create new connection. Enter a name for the connection and enter the information from Heroku as shown in the image below.

Heroku Postgres connection - Moon Modeler

We’re almost done. Now it is important to enable SSL/TLS. So go to the SSL/TLS tab and just enable this option.

SSL enabled

That’s it!

Reverse engineering and creation of ER diagrams

The next step is to load the structure. Click on the Connect and load existing database structure link to start reverse engineering. Moon Modeler will load the information from the database and create the project including the diagram.

Reverse engineering

Example of the newly created project:

Heroku postgres entity relationship diagram

Exporting ER diagrams to HTML or PDF

Once you have successfully created the project and ER diagram, you can enrich it with additional elements. Add notes, set cardinality, estimated sizes, etc. Then you can simply generate an HTML report or export the diagram to PDF format. This output will help you to communicate with others and will also serve for a better and clearer description of the database structure, which is especially useful in communication with teammates.

Forward engineering and script generation

Another option you have is to generate SQL scripts for your Heroku Postgres database design. In the image below, you can see the script created for the project loaded from Heroku Postgres.

Generated SQL script

Get started today and try Moon Modeler

Download