Visualize PostgreSQL database development

It's no secret that designing a database structure is one of the main and sometimes very laborious tasks in developing any software that works with data. One way or another, we all design a database, trying to imagine a diagram of the relationships of tables, and often draw, visualize the structure of the database before transferring it to the DBMS. For modeling MySQL databases, there is MySQL Workbench supplied by the developer; for MS SQL there are Database Diagrams; Until recently, I used Dia, and someone, perhaps, uses MS Visio for these purposes. But for PostgreSQL, I have not seen a single adequate solution that would allow to transfer the outline of the database structure to the code of its creation in the DBMS itself as simply and accurately as possible.

I don’t know how this could happen, but there’s not a single habrast about the product that I want to tell you about.



So ... (text, many pictures)


I want to introduce you opensource product distributed under the GNU General Public License 3 , called pgModeler .

The author officially declares that his product (approximate translation) :
  1. Easy to use.
    Easy to create and edit database models using a simple and intuitive interface;
  2. Supports various versions of PostgreSQL
    Model the database once, and then simply export to your version of the DBMS. PgModeler includes code generation methods that enable your models to be uploaded for various versions of PostgreSQL;

  3. Cross-platform
    Written with Qt , pgModeler can be compiled for Windows, Linux, and MacOSX. Build scripts are easily configurable, which helps resolve specific dependencies in each system;

  4. It can be functionally expanded using plugins.
    If you need some additional functionality, then you can easily implement it as a plugin to pgModeler. The plugin template and documentation are kindly included in the assembly;
  5. It is open source software.
    A link to the source code located on the github is visible on the official website with the naked eye.


You all (except the most curious, who have already followed the link to the official page), probably ask, what is unique about this product?

Let's see (pictures are clickable). Well, everything looks very tasty, but is it really so? I propose to create a test base. First you need to register a new connection to the DBMS in the settings (Menu-> Edit-> Configurations-> Connections): Add a new role for our test database. To do this, in the tree view on the right, you need to call the context menu from the database itself: we will not automatically create the database : yes, the ID for the role will have to be added manually. Next, add a new scheme:



























Let's condition that in the database we will store information about goods in warehouses. Without movements, without residues, we simply realize that the goods belong to a certain warehouse.

Create a new table. This can be done, for example, from the context menu called from the workspace (checkered field):


In the table creation designer, you can immediately specify all the necessary columns, restrictions, triggers, rules and indexes:


After accepting all the table parameters, it will be displayed in the workspace:


Create product table:

You should not create a column for linking two tables. The constructor will do this for us.

Several types of relationships are provided for connecting two tables:


Create a 1-to-Many relationship:


On the diagram, it will look like this:


Now we can upload our schema to the creation script file, to the picture file or directly to the DBMS:


and also see (without the ability to change) the DB creation script for any of the supported DBMS versions in the native format and in XML format (in the context menu from database in the tree structure on the right):


Honestly, the source code can be seen for each database entity, whether it is a role, schema, database or just a table.

In conclusion

Well, the features declared by the author of this software are fully justified. Everything is very intuitive and simple.

This project is now in version v0.4.0-rc1 and is actively developing. The functionality is quite rich, but some bugs and flaws are possible, which, however, I have not yet met.

I think pgModeler will appeal to all those who choose PostgreSQL as their main DBMS for their projects, and it will help you in modeling and visualizing your databases.



PS: I am not the author of pgModeler.
PPS: you can contact the author yourself.

Also popular now: