Top PostgreSQL Development Tools

The PG Day Russia conference is growing and scaling: this summer we are preparing reports and trainings for you on all the most popular databases, as well as on data administration and storage. in preparation, we launched a corporate blog, where we plan to share valuable information about what is happening in the world of databases. The first post is dedicated to development tools for PostgreSQL, its author varanio will be happy to answer your questions and comments!

I asked different people related to PostgreSQL what they use in real life to develop applications using PG.

This, of course, is not a strictly mathematical sampling, but nevertheless, there is a certain list of tools on the ear that are worthy of “touching” them, which I am going to do in this article.

If your tool is not listed, or you just have something to say, welcome to the comments.

So, here is an informal top and subjective description.

psql

In the first place is psql, and this is not surprising. Reliable as a Kalashnikov rifle, free, out of the box, what else is needed for happiness? To edit queries, use the editor specified in the EDITOR environment variable, usually put vim, nano or something like that. Well, in general, psql is a unix-way, i.e. you can run it with your editor, your pager to display the results, you can send a sql request to the input through the pipe, and send the output to the right place.

Of the minuses, weak autocomplete can be noted, as well as the fact that it is necessary to memorize non-intuitive commands from the \d \dt+ \sf series, etc. (however, all command descriptions are available via the \? command)

Well, work in the console and in the Vime - this does not suit everyone for some reason :)

In fact, sometimes you want to have a complete list of tables / views somewhere on the left and be able to click on the one you need to see what is there at all. Those. at least some kind of GUI. The work in psql, though effective, resembles work in a dark room with a small flashlight that illuminates only one object at a time.

datagrip

IDE for databases. Despite the fact that the product is relatively fresh, it is already used everywhere. Mainly due to the fact that it is immediately built into megapopular products from the company JetBrains: IntelliJ IDEA, PyCharm, PhpStorm, etc.

Actually, this build of it is at the same time the main killer feature of the product: you edit, for example, a php code that has a line with an sql query, and you suddenly realize that the IDE tells you (right in your code) the SQL syntax, the names tables and their fields, underlines reds, if something is written wrong, formats SQL and much, much more. Of course, in the same IDE, you can do what other GUIs for databases can do: view lists of tables and other entities, make separate requests, export tables to different formats and much more.

Of the features, I would point out the following things:

• You can select several insert'ov and click "Edit as table" (see picture). After that, edit it in a convenient tabular form instead of the sql syntax, and you can also add rows, columns, export them to csv, etc.
• You can compare the results of two queries. This is useful when you try to simplify a complex query without breaking anything.
• embeddedness in the code is not fully worked out. For example, when renaming a table column in any interface, the IDE does not find the necessary SQL lines in the code (the autocomplete worked in these lines), and vice versa, it finds some sort of nonsense.
• There is not a lot of visual development. Those. You can make a table, but the view can no longer. If the table contains some id with a foreign key (for example, a link to a certain dictionary), I would like to select values ​​from the dictionary when entering data into the table, and not drive in the id.
• If you look at the table in any of the schemes, then Datagrip sends a request to set search_path = schemas, which leads to bad consequences if you use pgbouncer (and it is used almost always in the case of php or when there are many servers), so for dev-development it is better to use different connections: for the code to work, via pgbouncer, for ide, directly to the database.

Datagrip is being actively developed, in particular, some annoying bugs with syntax highlighting have been fixed.

In general, a good modern tool, I recommend.

pgAdmin

Many use it, but rather out of habit. Or because it's free. pgAdmin4 is a weird product, while the description says that it is the best open source product for development and administration.

How to use it for administration is not very clear. pgAdmin can not be "zainit" new server, you can not correct pg_hba.conf or postgresql.conf. Apparently, this means scant schedules of requests per second, the output of server configuration details and statistics in tables. Not sure in general. How do you use pgAdmin for administration?

How to use it in terms of development is even less clear. Subjectively, the interface as a whole is not convenient for development. Despite the fact that the fourth version was rewritten in python + JS with jQuery, in fact, everything remained the same.

In order to clarify the situation a bit, in the developer’s head there is such a picture: there is a base on some server, there are diagrams in it, tables and views in the diagrams. Those. table - maximum, 3rd level. And if the base is one, then generally the second level. Poked on the table - I saw a few first lines.

In the head of the developer pgAdmin something like this: "Death Koshcheeva at the end of the needle, that needle in the egg, then the egg in the duck, the duck in the hare, the hare in the chest, and the chest stands on a high oak, and the Koschey tree as your own eye saves ", namely (see picture):

There is a group of servers, there is a server in it, there are bases on the server, roles, etc., you can select a specific database from the bases, you can see diagrams, languages ​​in it, God knows what else. In the diagrams, you can select the desired schema, in the schema 100500 in total, and somewhere at the end of the list of "tables". In the tables, you can select the desired table, click on it with the right mouse button, there you select "view data" in the large list, in this "view data" there is a "view first 100 rows" and there finally is death Koscheev few lines for your reference.

The killer feature of pgAdmin is the ability to debug the pl / pgsql stored procedures. I did not meet other free programs with this opportunity.

EMS Studio

EMS Studio seems to work only under Windows. This is its main drawback, because, as is well known, PostgreSQL is very rarely used under Windows.

I looked at this software only once under Wine, so I could be wrong, but in general I did not like it terribly. Mad piling up of incomprehensible icons, vague interface. By the way, under my Wine, I got pop-up hints, and I played “guess the functionality from the picture”. Very hard.

To a heap there for some reason made a visual query designer. Where instead of the text to write where id = 5 , one should press a few buttons with the mouse and select from the drop-down list. Those who know SQL do not need it, those who do not know it will not help.

Features that are called convenient: auto-complete with aliases, export the result of the query in SQL format (insert), a convenient GUI for exporting the database, the ability to perform only the selected part of SQL.

Able to debag pl / pgsql. In general, a lot of things can, but some outstanding features that would distinguish from others, I can not name.

NAVICAT

Navicat is probably the most feature-rich program. It can do everything that other GUIs can do for the database: object designer, table viewer, autocompet, database design tools, pl / pgsql debugging, import / export, and so on.

Truly comprehensive software that works on almost any operating system. Offhand, much more convenient EMS Studio.

Killer feature, in my opinion, is a comparison of bases. Those. You can take two bases, find out how they differ in structure and form requests for synchronization.

The price tag, however, as they say, "horse" - two times more expensive than EMS. But here, it seems, it is fully justified.

PGCLI

Those who try to work with psql immediately begin to dream of richer functionality, for example, autocompletion. To implement these women there is a pgcli.

pgcli can auto-complete keywords, functions, tables, columns, columns in alice. It knows how to highlight syntax, edit SQL in multiline mode without a separate editor, etc.

In short, pgcli is essentially psql on steroids.

phppgadmin

Many of those who switched from MySQL instinctively look for phpmyadmin counterparts, and stumble upon phppgadmin. Unfortunately, phppgadmin has not been developing for several years, so the dead are either good or nothing. In general, silent, perhaps.

Total

Not all of these tools I have experience using, so please comment in the comments. What do you use?

It should also be noted that the developers of popular tools will come to the pgday conference not only for postgres, but also for other databases, you can torment them with questions and make some suggestions on features. In any case, we invite everyone to visit this most useful event, which will be held in St. Petersburg on July 5-7!