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.
Creating a Heroku Postgres database connection
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:
This is exactly the information we will use.
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.
We’re almost done. Now it is important to enable SSL/TLS. So go to the SSL/TLS tab and just enable this option.
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.
Example of the newly created project:
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.