Database Support Plugin in JetBrains IDE
Even among those who have been using IntelliJ IDEA for a long time, there are many people who do not know anything about the plugin that allows you to work with databases directly from the IDE. Meanwhile, for me personally, this is one of the most useful features of IDEA, because I can write code in vi. It is important how convenient it is to debug and improve it later. The question is, what does Database support have to do with it? Actually, when debugging applications, I use it.
The story about the Database Support plugin will be completely unofficial, I myself used it only in web development and in Android development, but the hardcore developers of hard enterprise will also be interesting, I hope.
Once, it occurred to me to write a web application. There was a chance that the load on it would be quite high and uneven, so I decided to use PostgreSQL as the DBMS in the hope that it would work faster than the usual MySQL. No sooner said than done. The prototype is written, the base is created, we begin to fill out the tables with all sorts of test muck.
After some time, it becomes clear that the structure of the tables needs to be slightly corrected, and the test records in them have bred - it is necessary to clean. And then it turns out that the standard manual tool for working with PostgreSQL - pgAdmin - is completely different from phpMyAdmin, and in general it is a standalone desktop application.
The question is, is there any universal thing that can work as a convenient interface to the database, moreover, an interface whose appearance does not depend on the database with which it works?
JetBrains has come up with this kind of thing for a long time, it's the Database Support plugin that works with IntelliJ IDEA Ultimate, RubyMine, PyCharm Professional Edition and PhpStorm. It is rather complicated in development and support, so it is not included in free IDEs.
Let's see how he can help us. To get started, configure it to work with PostgreSQL. If we do not yet have the JDBC driver for PostgreSQL on the computer, the IDE will offer us to download and install it. In my case, the DBMS is running on the same computer as the IDE:
See the Database tab on the right? So when you click on it, the Database Support plug-in window opens (you can open it both through View | Tool Windows | Database, and through the list of Tool Windows windows):
Now let's see what we can do with it. First, you can look at the database structure in the plugin window, and if you need to display a UML diagram, then this is also possible (press <Ctrl + Alt + U>). Secondly, we can view and edit database tables. We press F4 on the required table and here it is, in front of us:
We can edit any cell by pressing F2, the “Auto-commit” mark means that after the changes are transferred to the database, commit will be automatically done. If you uncheck, you can do commit and rollback manually.
When sorting a table (click on the column name), the database does not change, because the table is just the result of SELECT *, and if you are viewing it, the database is changed by someone else, you will not see this.
Sorting can be done according to different columns - first one by one, then by another, and the Reset columns order and visibility button
will appear if the window is wide enough, or you hover over the double angle brackets to the left of the Auto-commit setting.
Press <Ctrl + F12> or use the Structure Toolwindow (<Alt + 7>), and we can choose which columns we want to display and which ones not.
The same selection window displays in which order the columns are sorted (A - ascending, ascending, D - descending, descending). If the font of your look and feel allows, then instead of A and D you will see beautiful up and down arrows. You can select columns for viewing by a space .
Sorting can be done both in the table on the IDE side, and in advance - on the base side. The latter is done with the ORDER BY button, and with the help of View Query you can see which IDEA query is sent to the database:
Of the non-obvious features, it is worth noting recently - in EAP version 13 - the transposition of rows appeared (in IDEA 12 this was not): if there are no columns so much that the line does not fit on the screen, then double <Ctrl + Q>it can be obtained in the form of a column, and the arrows still run up and down the table with long rows. Of course, nothing happens with the contents of the database, it remains the same.
So far, we have been working with the Table View database. However, the console mode is also supported (we click on the console icon above the tree view in the Databases window):
Now we can communicate with the database directly by issuing SQL commands to it, and the familiar autocompletion by <Ctrl + space> is fully supported in SQL.
In console mode, there is also an “Auto-commit” setting. As before, it means that after the changes are transferred to the database, the transaction will be confirmed automatically. If you uncheck the box, the buttons for manual commit and rollback are activated to the right of it.
The query results, if any are expected (for example, with SELECTs) will be shown in a separate table at the bottom of the screen, and they can be viewed as well as the table view of the database called by F4.
Naturally, with this plugin you can change not only the values of the fields in the tables, but also the structure of the tables, for this it is easy to use the context menus in the database tree on the right.
If the matter was limited to supporting traditional databases, then the article on Habr might not have got into it: there are many amenities in working with a database with JDBC, but the final chord was not enough for the harastrasti. The chord happened in the fall of 2013, when I came to my colleague who writes all Android support in JetBrains.
An Android developer will easily recognize his torment in the command line screenshot below. In order to work directly with the SQLite database on the device or emulator, without an application, you have to call adb shell, then call sqlite3, and ask him to open the database at the address that includes the full name of the application package. And the name can be long. And autocompletion is not attached to adb shell.
Therefore, my visit was a gesture of despair: even to say DROP DATABASE, when enough unnecessary test data was accumulated in the tables, I had to spend time on a routine set of long lines.
The developer’s favorite thing at JetBrains is the fight against routine, so my ardent requests to do something about it fit well with the good intentions already laid out by fifty votes for this feature in our tracker. Despite a host of other tasks, SQLite support for Android was completed in two weeks. The solution turned out to be elegant: the SQLite database is pulled to the computer from the device or the emulator transparent to the user, then you can work with the local copy as with a regular database, and then you can upload it back to the device by calling Upload from the context menu.
To make sure that you are working with the current copy of the database, you can synchronize with it on the device by pressing the Syncronize button or <Ctrl + Alt + Y>:
Otherwise, working with the database when developing for Android is similar to working with a regular SQL server.
In conclusion, it remains to say that the Database Support plugin supports all DBMSs for which there are JDBC drivers (which are almost all popular DBMSs on the market), and understands 12 dialects of SQL. Which dialect you want to use (and have the appropriate auto-completion and other chips to it) can be specified in the IDE settings: Settings | SQL Dialects.
The story about the Database Support plugin will be completely unofficial, I myself used it only in web development and in Android development, but the hardcore developers of hard enterprise will also be interesting, I hope.
Web Developer History
Once, it occurred to me to write a web application. There was a chance that the load on it would be quite high and uneven, so I decided to use PostgreSQL as the DBMS in the hope that it would work faster than the usual MySQL. No sooner said than done. The prototype is written, the base is created, we begin to fill out the tables with all sorts of test muck.
After some time, it becomes clear that the structure of the tables needs to be slightly corrected, and the test records in them have bred - it is necessary to clean. And then it turns out that the standard manual tool for working with PostgreSQL - pgAdmin - is completely different from phpMyAdmin, and in general it is a standalone desktop application.
The question is, is there any universal thing that can work as a convenient interface to the database, moreover, an interface whose appearance does not depend on the database with which it works?
JetBrains has come up with this kind of thing for a long time, it's the Database Support plugin that works with IntelliJ IDEA Ultimate, RubyMine, PyCharm Professional Edition and PhpStorm. It is rather complicated in development and support, so it is not included in free IDEs.
Let's see how he can help us. To get started, configure it to work with PostgreSQL. If we do not yet have the JDBC driver for PostgreSQL on the computer, the IDE will offer us to download and install it. In my case, the DBMS is running on the same computer as the IDE:
See the Database tab on the right? So when you click on it, the Database Support plug-in window opens (you can open it both through View | Tool Windows | Database, and through the list of Tool Windows windows):
Now let's see what we can do with it. First, you can look at the database structure in the plugin window, and if you need to display a UML diagram, then this is also possible (press <Ctrl + Alt + U>). Secondly, we can view and edit database tables. We press F4 on the required table and here it is, in front of us:
We can edit any cell by pressing F2, the “Auto-commit” mark means that after the changes are transferred to the database, commit will be automatically done. If you uncheck, you can do commit and rollback manually.
under the spoiler - a collage, at the same time it is impossible to see Commit and Rollback prompts, do not try!
When sorting a table (click on the column name), the database does not change, because the table is just the result of SELECT *, and if you are viewing it, the database is changed by someone else, you will not see this.
Sorting can be done according to different columns - first one by one, then by another, and the Reset columns order and visibility button
will appear if the window is wide enough, or you hover over the double angle brackets to the left of the Auto-commit setting. Press <Ctrl + F12> or use the Structure Toolwindow (<Alt + 7>), and we can choose which columns we want to display and which ones not.
The same selection window displays in which order the columns are sorted (A - ascending, ascending, D - descending, descending). If the font of your look and feel allows, then instead of A and D you will see beautiful up and down arrows. You can select columns for viewing by a space .
Sorting can be done both in the table on the IDE side, and in advance - on the base side. The latter is done with the ORDER BY button, and with the help of View Query you can see which IDEA query is sent to the database:
Of the non-obvious features, it is worth noting recently - in EAP version 13 - the transposition of rows appeared (in IDEA 12 this was not): if there are no columns so much that the line does not fit on the screen, then double <Ctrl + Q>it can be obtained in the form of a column, and the arrows still run up and down the table with long rows. Of course, nothing happens with the contents of the database, it remains the same.
So far, we have been working with the Table View database. However, the console mode is also supported (we click on the console icon above the tree view in the Databases window):
Now we can communicate with the database directly by issuing SQL commands to it, and the familiar autocompletion by <Ctrl + space> is fully supported in SQL.
In console mode, there is also an “Auto-commit” setting. As before, it means that after the changes are transferred to the database, the transaction will be confirmed automatically. If you uncheck the box, the buttons for manual commit and rollback are activated to the right of it.
The query results, if any are expected (for example, with SELECTs) will be shown in a separate table at the bottom of the screen, and they can be viewed as well as the table view of the database called by F4.
Naturally, with this plugin you can change not only the values of the fields in the tables, but also the structure of the tables, for this it is easy to use the context menus in the database tree on the right.
Android developer story
If the matter was limited to supporting traditional databases, then the article on Habr might not have got into it: there are many amenities in working with a database with JDBC, but the final chord was not enough for the harastrasti. The chord happened in the fall of 2013, when I came to my colleague who writes all Android support in JetBrains.
An Android developer will easily recognize his torment in the command line screenshot below. In order to work directly with the SQLite database on the device or emulator, without an application, you have to call adb shell, then call sqlite3, and ask him to open the database at the address that includes the full name of the application package. And the name can be long. And autocompletion is not attached to adb shell.
Therefore, my visit was a gesture of despair: even to say DROP DATABASE, when enough unnecessary test data was accumulated in the tables, I had to spend time on a routine set of long lines.
The developer’s favorite thing at JetBrains is the fight against routine, so my ardent requests to do something about it fit well with the good intentions already laid out by fifty votes for this feature in our tracker. Despite a host of other tasks, SQLite support for Android was completed in two weeks. The solution turned out to be elegant: the SQLite database is pulled to the computer from the device or the emulator transparent to the user, then you can work with the local copy as with a regular database, and then you can upload it back to the device by calling Upload from the context menu.
To make sure that you are working with the current copy of the database, you can synchronize with it on the device by pressing the Syncronize button or <Ctrl + Alt + Y>:
Otherwise, working with the database when developing for Android is similar to working with a regular SQL server.
In conclusion, it remains to say that the Database Support plugin supports all DBMSs for which there are JDBC drivers (which are almost all popular DBMSs on the market), and understands 12 dialects of SQL. Which dialect you want to use (and have the appropriate auto-completion and other chips to it) can be specified in the IDE settings: Settings | SQL Dialects.