Webinar Summary: Working with SQL and Databases in IntelliJ IDEA
On October 16, 2020, we hosted the live webinar ‘Working with SQL and Databases in IntelliJ IDEA’ by Maksim Sobolevskiy, Product Marketing Manager for Database Tools at JetBrains. He started with the basics of writing a simple query faster, and progressed to more complicated tasks like updating data, creating run configurations, and using language injections.
Session details
Maxim started his session by noting that DataGrip is a standalone JetBrains IDE for SQL and databases. IntelliJ IDEA Ultimate includes nearly all DataGrip functionality. In fact, DataGrip functionality is supported by nearly all JetBrains IDEs, including PhpStorm and RubyMine.
Getting started
Max started his session by assigning a shortcut to the Database Tool Window, since it doesn’t have one by default. He used an existing shortcut, which was being used by another window, but you can choose whatever works best for you.
He demonstrated that the Database Tool Window can be used to view existing connections, rename them, or create new ones. You can connect to many databases, like RedShift, Couchbase Query, H2, MondoDB, MySQL, Oracle, PostgreSQL, and many more.
IntelliJ IDEA can help you connect to a database, write queries, view data, manipulate it, and even export or import data. The database tool window has a database tree with many objects, and it also shows the active connections.
Max used PostgreSQL to demonstrate all the features of DataGrip, but he also noted that you would get the same functionality with the other databases.
Database connections
Max briefly explained various properties of a database connection – read only, transaction mode, run-keep alive query, auto-disconnect later, SSH and SSL, schemas to show in the database, advanced options on JDBC drivers.
Max demonstrated how to group database connections, which can be very handy when you have multiple connections and you want to keep them together because of where they are used, test and production databases, for example. He also used ‘Database Color Settings’ to change the background color of one of the databases to rose, making it easier to relate to.
To search for any database entity in the Database tool window, you can just start typing the name you are looking for, including using short forms, just as you can in IntelliJ IDEA. Clicking on an entity like a table name will display all the table data.
Query Console
To execute queries, you can create a new query console by right clicking in the database tool window. A console window is a scratch file attached to your data source. It is interesting to note that a lot of IntelliJ IDEA features, like Live Templates, are also available in the Database Tool Window. In the scratch file you just opened to write a query, you can use ‘sel’ and either press enter or tab to insert ‘SELECT * FROM ;’. Code completion will let you select the table, view, or other entity names from a list of options. Max mentioned that you can create your own live templates using project settings.
We have a screencast and a blog on the various live templates that are available in IntelliJ IDEA, if you are interested.
Services window
When you execute a query, you can view the services window, which shows the console attached to your query and the time taken by the query execution.
Moving beyond the basics
Moving beyond simple queries, Max showed how IntelliJ IDEA Ultimate understands the connections between your tables. So, when you try to use a JOIN clause in your query, IntelliJ IDEA will suggest the complete join clauses. You can use abbreviations when working with completion. So, for example, when searching for ‘actor_id’, you can type a shortened version of the name, such as simply ‘ai’.
Max showed how to write complex SQL queries very quickly in IntelliJ IDEA by using a combination of live templates, smart code completion, and abbreviation completion. Max also showed how to multiple queries in the query console window and choose whether to execute some or all of them.
IntelliJ IDEA and inspections for SQL Queries
IntelliJ IDEA has inspections for SQL queries as well. For example, it can highlight a subquery that needs an alias and is missing it. Of course, Alt+Enter can suggest and fix SQL statements. You can also format SQL queries using the regular IntelliJ IDEA ‘Reformat Code’ action.
In the preferences, you can modify the default behavior and specify whether you want IntelliJ IDEA to prompt you to select a query to run or run all queries, as well as other options. If you execute SQL queries often, this could be a good option to check out.
Adding comments to queries
To differentiate between the result windows of queries, you can add comments to your queries by preceding text using ‘–’ (without the quotes). This would name the result window with the comment. You can also compare the results of two query result set by using the ‘Compare with’ button.
Working with Query console window
The query console window also supports regular coding features available in IntelliJ IDEA, like, duplicating SQL lines, multiple carets, select all occurrences.
If your column names are ambiguous, IntelliJ IDEA will highlight them and suggest that you add qualifiers to them, including the relevant suggestions. You can also select multiple column names and add qualifiers to them by using Alt+Enter and using the suggestion ‘Qualify Identifier’.
Refactoring
You can also refactor queries, say, renaming a table alias using IntelliJ IDEA’s ‘Rename’ refactoring. This will change all occurrences of the table alias.
Navigation
You can navigate to the DDL of your database queries by using the ‘Go to Declaration or Usages’ action on the entity in the query console window. The ‘Quick definition’ action can also show the definition of database entities with its data. The ‘Jump to source’ action shows the data results for a query.
Just in case your query console window or the results window isn’t big enough to display everything that it has to show, you can use the ‘Hide All Windows’ action to view just your query console window or the results window.
Results window
In the results window, you can limit the number of rows to view for a query result in your data editor (not only can you view the data, but you can also modify it). You can also add or delete rows, or edit existing values, just like in an excel sheet, and you can copy-paste values from external sources like a scratch butter file (a text file). You can either revert the changes, by right clicking and choosing ‘revert’, or commit them to the data source. Before committing the changes, you can also view the DML queries that would execute against your database, by clicking on the ‘DML’ button in the toolbar.
Data extractors
Data extractors are an important feature since they let you view or export the results of tables, queries or other database objects in multiple formats. They are grouped as ‘Built-in’, ‘CSV’ or ‘Scripted’.
Let’s start with ‘SQL Inserts’ in ‘Built-in’ extractors. Every table, view, or resultset, as well as some other things, can be exported in multiple ways. So, for example, if you select ‘SQL Insert’ and select a few rows or all of them, copy them, and paste them to a scratch file, you would see all the data exported as a bunch of SQL insert statements. You can right click and select the language as ‘SQL’ or ‘PostgreSQL’, and the editor will use the relevant syntax highlighting for the SQL statements. You have a couple of options if you want to export all the data. You can select it all and export it manually. Or better still, you can use the ‘Export data’ option and then select from the extractor type and the name of the target file. The dialog box also shows a preview, so you know exactly what text will be exported. Similarly, you can select ‘SQL Update’, copy the result data, and paste it the scratch file to generate all the data as a set of SQL update statements.
The next group of data extractors is ‘CSV’. Here again you can export some or all of your data in multiple formats – like CSV and TSV – and you can configure these formats so that they work best for you.
The last group of data extractors is ‘Scripted’, which enables you to export your SQL data in multiple formats like HTML, JSON, Markdown, XML and many others. If you access the option ‘Go to Scripts Directory’, you can see that these are scripts written in the Groovy. You can edit them to customize your exported data. You can also add new extractors to this folder. The ‘Pretty’ extractor was recently added. It arranges and displays your data in a textual tabular form, which you can easily share as a text message.
Copy to Database
This feature works with both tables and result sets, and it can be particularly useful if you want to query the result of a query (which took a long time to complete). In this case, you can copy the result of a query as a table in a database.
Max shared some tips on how to view your data if you have a lot of columns in the returned data. You can use the ‘Transpose’ option to view the column names vertically rather than horizontally. You can also view the column list by using the action ‘Columns List’ (Ctrl+F12/ Command F12). You can search for a particular column and hide columns you don’t need to view by selecting them and pressing space.
Viewing your data in different formats
In addition to being able to extract data from your tables and query results in different formats (like CSV, JSON, XML and others), you can also view them in these formats. Select the ‘eye’ icon and choose from three options for viewing the data: table, tree, or text.
Settings – Sorting and Searching
Let’s look at some of the Settings options. When you deselect the ‘Sort via ORDER BY’ option, sorting will happen on the client, that is, on the IntelliJ IDEA side. Select the ‘View Query’ option to find out the query whose results are displayed. It is interesting to note that you can click on the name of a column, and your results will be sorted on it. Now, when you click on view query, you’ll see that an ORDER BY clause is added to the query.
There are multiple ways to filter your data. The first one is to specify a ‘WHERE’ clause in your SQL queries, and the second is to specify the condition in the ‘filter criteria’ text field used in the data query results window. Another great feature is the ability to use ‘Find’ to specify a value and check the ‘Filter rows’ option to display only the rows that contain the value specified in the find text field. This can help you to narrow your search without specifying the name of a particular column.
To search for values across tables, you can also use the ‘Full-Text Search’ feature in the Database Tool Window by right-clicking ‘tables’. On execution, the search result window shows the name of the table and the count of the matching rows found in it. You can click on the table names to view the matching results.
Double Shift to search for literally anything
Max demonstrated the use of double shift to find the view ‘sales_by_film_category’ by searching for the abbreviation ‘sbfc’ or even ‘sfc’.
For the symbols tab on Search Everywhere, you can view everything that IntelliJ IDEA has indexed. Each tab on the search every window, like Classes, Files, Symbols, and Actions, has its own individual shortcuts. Max emphasized that Find Action is your friend because it helps you find what you need even if you only vaguely remember the name of the action you are looking for.
Local History
IntelliJ IDEA saves versions of your files and folders irrespective of whether you are using a VCS. This is not, of course, to recommend against using a VCS. We have a blog and a screencast on it, after all.
Accessing Clipboard and Console History
The ‘Paste From History’ action gives you access to, and navigate through, all of the values you’ve copied while using IntelliJ IDEA, not just the last one. ‘Browse Console History’ allows you to browse the queries you run.
SQL Log
IntelliJ IDEA saves all the SQL queries that have been run on a file. You can access the file and inspect it for any database command or query as required.
Recent Files and Locations
You can navigate through recent database query files, consults, and result sets using the usual IntelliJ IDEA shortcuts for recent files and locations.
Find in Path
With Databases, you can use the ‘Find in Path’ action to look for text not only in any file in your project, but also in database objects, like functions.
Creating Java classes from tables
Sometimes you may need to create a POJO class that maps to a table. To do so, right click the table in the Database Tools Window and select ‘Generate POJOs.groovy’, followed by the directory where you want the class to be.
As the name suggests, the POJO is generated using a code file, which you can access and modify to suit your needs. To do so, access ‘Scripted Extensions’, and choose ‘Go To Scripts Directory’.
Code assistance in your SQL queries in Java classes
You can use Alt+Enter to inject language and references into your String values that store queries. With a connected PostgreSQL DB, you can get code assistance with SQL query, which offers syntax highlighting and query completion. For example, basic code completion would enable you to complete the table name. You can also use ‘Quick Documentation’ or ‘Go to Declaration’ on the table name to view the table DDL and initial results. You can also view the table data by using action ‘Jump to Source’.
Our next webinar
For all our IntelliJ IDEA users, we are hosting another webinar on ‘Bootiful Kotlin‘.
Join us Wednesday, November 11, 9:00 am – 10:00 am CET. Register now.
And be sure to Follow us on Twitter so you don’t miss the updates.
Happy developing!
Subscribe to IntelliJ IDEA Blog updates
