Visualize the development of the PostgreSQL database

for anybody not a secret that the design of the database structure is a major and sometimes very labor-intensive task in developing any software working with the data. We all somehow projected database, trying to imagine the schema of the table relationships, and often draw, or visualize the structure of the database before you move it to the DBMS. For modeling databases MySQL is MySQL Workbench, supplied by the developer for MS SQL has Database Diagrams; I until recently used Dia, and someone may be uses for this purpose MS Visio. But for PostgreSQL, I have not seen any adequate solution that would allow as simple as possible and accurately transfer the outline of the database structure in code create it in the database.

don't know how could it happen, but there is no abruscato about the product that I want to tell you.



So... (text, lots of pictures)


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

The author formally declares that the product (approximate translation):

Easy to use
Easily create and edit database models with simple and intuitive interface;

Supports different versions of PostgreSQL
Simulate the database once, and then simply export to your DBMS version. Part pgModeler includes methods of code generation that allow Your models to be swapped out for different versions of PostgreSQL;



cross platform
Written using Qt, pgModeler can be compiled for Windows, Linux and MacOSX. Build scripts easy configurable that helps to resolve the specific dependencies in each system;



Can be functionally extended using plugins
If You need some custom functionality, You will easily be able to implement it as a plugin for pgModeler. A template plugin and documentation kindly included in the Assembly.

Is open
Link to source code, located on github, is visible on the official website to the naked eye.

You all (except the most curious, which already went to the link on the official page), probably, ask what is unique about this product?

Let's take a look (pictures are clickable).











Well, all look very tasty, but is it really? I suggest to create a test database.

First we need to register a new connection to database in the settings (Menu->Edit->Configurations->Connections):


Add a new role to our test database. To do this, in the tree view on the right, you must call the context menu from the database:


Database automatically create will not:

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

Then add a new scheme:



Let's obosnovalsya that in the database we will store information about products in warehouses. Without moving, without residue, simply implement a membership product to a specific warehouse.
Create a new table. This can be done, for example, from context menu invoked from the workspace (checkered box):


In the constructor create a table we can just specify all the required columns, constraints, triggers, rules and indexes:


After taking all parameters from the table, it will appear in the workspace:


Create a table of products:

It is not necessary to create an additional column to link the two tables. The designer will do this for us.

To link two tables have several types of relations:


Create relationship 1-to-Many:


In the diagram it will look like this:


Now we can upload our schema in a file-script file-image or directly in the DBMS:


and watch (without ability to modify) the script to create the database for any of the supported versions of the database in a native format and in XML format (in the context menu from the database in the tree structure on the right):


Honestly, the source code can be seen for each of the database entity, whether the role, schema, database, or a simple table.

conclusion

Well, the author claimed the possibility of this quite 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 there may be some bugs and flaws, which, however, I have not met.

Think pgModeler will appeal to all those who choose for their projects PostgreSQL as the primary database, it will help you in modeling and visualization of your databases.

---

PS: I am not the author of pgModeler.
PPS: c the author you can connect for yourself.

Article based on information from habrahabr.ru