An Overview of pgModeler for PostgreSQL

When a project is being designed, the first thing to think about is what its purpose will be… what is the best solution, and what are the alternatives.  In software engineering, everything is done to serve data, whether it’s a graphical interface or business logic, so it’s no wonder the best starting point might be database planning.

The official documentation of a database can be very complicated, whatever technology it may be. Using the best concepts for a specific situation is not an easy task.

pgModeler is the program you can use to increase your productivity with PostgreSQL. It’s free, works on Windows, Mac, or Linux and provides a way to work with DDL commands through a rich interface built on top of SVG.

Installation

Installation is very simple, just download it from the site, and run the file. Some operating systems already have pgModeler included in their repositories, which is an alternative to downloading it.

pgModeler is an open source solution, and you can find it on GitHub, where new releases are published. 

It has a  paid version option, where you can support the project and use the latest features, for example, compatibility with the latest versions of PostgreSQL.

If you need a desktop entry, check it out in the following. This file can be named pgmodeler.desktop, and you can place it on /usr/share/applications/, but don’t forget to copy the logo presented in this blog, saving it on /etc/pgmodeler/pgmodeler_logo.png.

[Desktop Entry]
Name=pgModeler
GenericName=PostgreSQL Database Modeler
Comment=Program with nice Qt interface for visual modeling PostgreSQL on Entity Relationship Diagram
Exec=pgmodeler
Icon=/etc/pgmodeler/pgmodeler_logo.png
Terminal=false
Type=Application
Categories=Qt;Database;Development;

Graphical Interface

The curriculum of information technology courses, including colleges, contains data modeling disciplines, with UML as the standard for project design and documentation.

The graphical interface of pgModeler allows working with a kind of diagram specific for databases, the Entity Relationship Diagram (ERD), reproducing what you’ve built inside of your PostgreSQL cluster seamlessly.

Several languages are available:

• English (en_US);
• Spanish (es_ES);
• French (fr_FR);
• Dutch (nl_NL);
• Portuguese (pt_BR); and
• Chinese (zh_CN).

Printing what you’ve built is also available, and customizations are possible in the appearance, changing the font and colors of schemas, tables, relationships, etc.

Features

The features of pgModeler are simply tools to help you navigate between logical and physical models.

A logical model, is the diagram. You can use it to transform the idea of your customer, into a well documented project that other person can understand in the future, and make modifications on it.

The physical model is the script, the SQL code. PostgreSQL understands it, and so pgModeler too.

Through its reverse engineering algorithm, you can connect into your PostgreSQL cluster, and look at your existing domain model with a different perspective, or build it first, and then create the domain model executing the script, generated by what you’ve built in the diagram.

Entity Relationship Diagram

Once you understand its purpose, let’s see how a diagram looks like for a very simple project where you can visualize the relationship between the tables customer and film, named rental.

Note the lines between the tables, they are easy to see, and most importantly, understand. Primary and foreign keys are the starting points to visualize the relationships, and at their edges, the cardinality is being shown.

The constraints representing the keys can be seen, as pk, fk, and even the NOT NULL, as nn, in green at the right of each table. The schema is named store, and the picture above has been generated by the program itself.

Earlier we saw that diagrams are the logical model, which can be applied into a PostgreSQL cluster. In order to apply it, a connection must be established, for this example, I created a cluster running inside a Docker container.

Now with the database connection configured and tested, exporting is easy. Security concerns must be considered at this point, like establishing SSL with your cluster.

In the following, pgModeler creates the store schema, inside of a completely new database named blog_db, as I wanted to, not forgetting to mention the new role, with login permission.

Exporting process successfully ended! – Ok, there is a mistake, but it has been successfully ended, for sure.

postgres@716f75fdea56:~$ psql -U thiago -w -d blog_db;
psql (10.10 (Debian 10.10-1.pgdg90+1))
Type "help" for help.
blog_db=> set search_path to store;
SET
blog_db=> dt
        List of relations
Schema |   Name | Type  | Owner
--------+----------+-------+--------
store  | customer | table | thiago
store  | film   | table | thiago
store  | rental   | table | thiago
(3 rows)
blog_db=> du
                                  List of roles
Role name |                         Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
thiago    |                                                   | {}

Conclusion

Domain models are also known as mini worlds, and rarely you’ll see the same being applied on different projects. pgModeler can help you focus on what is really important, avoiding waste of time concerning the SQL syntax.