Visualize the development of a PostgreSQL database.

It's no secret that designing a database structure is one of the main and sometimes very time-consuming tasks in developing any software that works with data. All of us, in one way or another, design the database, trying to imagine a table interconnection scheme, and often draw, visualize the database structure before transferring it to the DBMS. To simulate MySQL databases there is MySQL Workbench, supplied by the developer, for MS SQL there are Database Diagrams; Until recently, I used Dia, and someone, maybe, uses MS Visio for these purposes. But for PostgreSQL, I have not met a single adequate solution that would allow the most simple and accurate transfer of the outline of the database structure to the code for its creation in the DBMS itself.



I do not know how it could have happened, but there is not a single word about the product that I want to tell you about.





')

So ... (text, many pictures)





I want to introduce you to 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
   
   Easily create and edit database models using a simple and intuitive interface;
2. Supports multiple versions of PostgreSQL
   
   Model the database once and then just export it to your version of the DBMS. The pgModeler includes code generation methods that allow your models to be unloaded for different 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 on each system;
   
   
   
   
4. Can be functionally extended with plugins.
   
   If you need any additional functionality, you can easily implement it as a plug-in to pgModeler. The plug-in template and documentation are kindly included in the build;
5. Is open source
   
   The link to the source code located on the githaba is visible on the official website with the naked eye.

All of you (except for the most curious, who have already gone through the link to the official page), probably ask what is unique about this product?



Let's see (the pictures are clickable).























Well, it looks very tasty, but is it really so? I propose to create a test database.



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 base:



Yes, the ID for the role will have to be added manually.



Next, add a new scheme:







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



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





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





After accepting all the parameters of the table, it will appear in the workspace:





Create a table of products:



It is not necessary to create a column for the connection of two tables. The designer will do it for us.



For the connection of two tables there are several types of relationships:





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 to see (without the possibility to change) the database creation script for any of the supported DBMS versions in the native format and in XML format (in the context menu from the database in the tree structure to the right):





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

As a conclusion

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



This project is currently 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 database 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.