Database objects
Cassandra database
First and most important, a new database has joined our family. We’re gradually finding our feet with NoSQL. After adding Clickhouse support in 2018.2.2, we have now added support for Cassandra.
Write queries, observe data, and generate scripts—do everything you like about DataGrip!
Generate SQL files for the selected objects
Now, if you use the SQL Generator (Ctrl+Alt+G) to get the DDL from objects, you can also generate the SQL files for these objects. To do this, click the Save button on the left pane. As you can see, we’ve created two layouts for the output. Please, if you have any ideas you would like implemented, share them with us. Tell us what kind of settings you would like to see here.
Anyway, now you can click the Edit button to the right of the Layout drop-down and edit any of them. Or create your own. These layouts are just groovy scripts.
PostgreSQL extensions
DataGrip now supports extensions in PostgreSQL.
Data source quick doc
The quick doc for the data source (Ctrl+Q) now displays some statistics including the number of different objects.
Code completion
Automatic aliases
A new setting is available to automatically add aliases when completing table names. If the pre-generated aliases don’t suit your needs, feel free to create your own custom ones for particular tables.
Here’s an alias in action:
Not aggregated fields in GROUP BY
When you use the GROUP BY statement, DataGrip offers you a list of non-aggregated fields.
All columns list in SELECT
A list of all columns is available in completion after the SELECT keyword. Also works for MERGE and INSERT INTO table variable.
Postfix completion
We’ve also introduced Postfix completion which may be familiar if you’ve used other IntelliJ-based IDEs.
Here is a movie of how all of them work:
Named parameters
Completion now works well for named parameters of stored procedures.
Context for ambiguous names
In the case of ambiguous names, DataGrip now shows you the context.
Invert order of operands in JOIN
A new setting is called Invert order of operands in auto-generated ON clause.
If it’s set to off, the FROM table will be the first in JOIN conditions.
If it’s on, vice versa:
Refactoring
Introduce alias
Introduce alias, one of the most important SQL refactorings, is finally here. Get your stop-watch ready and see how much time this saves you!
Extract subquery
– The name suggested for CTE doesn’t lead to conflicts: DBE-6496
– Context is properly defined if a statement is wrapped in another expression: DBE-6503, DBE-6517
– DataGrip won’t suggest extracting CTE when the column aliases in an as-expression for dialects
that don’t support it:
DBE-6490
– This refactoring is enabled for MySQL: CTEs are supported since MySQL 8
– This refactoring works OK with deep subqueries. DBE-7332,
DBE-7333
Code insight
Unsafe statements
DataGrip will now warn you if you use the DELETE or UPDATE statement without WHERE clause.
If you are running this, there will be a cautionary notice.
Unreachable code
Another small improvement is a new inspection called Unreachable code.
Unused subquery
We’ve added a new inspection, Unused subquery item. It will help make your code neater.
Code generation
Dialects for Live Templates
Now you can choose dialects for Live Templates. Once a dialect is selected for a live template, it will only work in the specified dialect.
This option lets you create different dialect-based implementations for the same abbreviation.
Say, we want to create a template for getting the first n rows. In SQL Server and PostgreSQL, this query would have different syntax. You can use the same abbreviations for different live templates if they are in different groups. Create two new groups: SQL Server and PostgreSQL. In each group, create a corresponding live template with the appropriate syntax.
Don’t forget to choose the right dialect for your templates. They will work only in the needed context.
CREATE TABLE definition for SELECT
It is now possible to add a CREATE TABLE definition from the SELECT query. Just write an INSERT statement before it, and then press Alt+Enter → Create table definition
Column names hint
Column names hint appears automatically when using INS live template
Connectivity
Single connection mode
Previously, each new query console meant a new connection. In the new version, it’s possible to work with just one connection for the data source and have all consoles use that same one connection. In fact, it allows you to see the temporary objects in the database tree, or use the same transaction in different consoles. This is our first step toward creating full connection management in DataGrip. To turn it on, go to Data source properties → Options → Single connection mode.
Auto-reconnect
Also, the long-awaited auto-reconnect feature has been added, so disconnections after timeouts are no more!
Search and navigation
New Search Everywhere
IntelliJ Platform introduces a new better way to navigate over the project and the IDE: the reworked Search Everywhere dialog. In fact, it incorporates all of the other navigation dialogs: Search Everywhere, Find Action, Go to table/view/procedure/, Go to file, and Go to symbol. Note that all these navigation dialogs are still accessible through their own keyboard shortcuts, as before. To switch between tabs, use Tab.
Multiline code search
Now it’s possible to perform search/replace for a multiline fragment in Find in Path / Replace Path dialogs. It’s easier now to find a query in your scripts or source codes.
Multiline TODO comments
The IDE now supports multiline TODO comments. Now the first and all subsequent TODO comment lines are highlighted in the editor and listed in the TODO Tool Window. Add an indent starting from the second line of your multiline TODO comment, and the IDE will differentiate it from an ordinary comment.
User interface
High-contrast color scheme
Across all of our IDEs, we’re rolling out the brand new High-contrast scheme.
To turn it on, press Ctrl+`go to Look and Feel, and then choose High Contrast.
Color settings
We’ve also added the ability to define colors for data sources to the Properties dialog.
Better UI for page size setting
We've also reworked the UI for the page size a little. Now there’s a simple way to see all the rows from a table or a result.
Phew! That’s all!
Well, not quite.
Minor stuff
- Code completion for:
- Window functions
- Numeric fields in SUM() and AVG()
- FILTER (WHERE…) clause
- Field types in SQLite
- Items in brackets
- Table value types
- Use drop cascade syntax option when dropping an object.
- Tons of fixes in SQL formatter. If your case isn’t covered yet, create an issue.
- Expression/declaration lists can be folded in the editor
- Ability to assign shortcuts for selecting tabs. IDEA-68324
- Support for stored procedures in PostgreSQL 11. DBE-6863
- Support for vector valued updates in SQLite. DBE-4449
- If JOIN has no condition, this error is highlighted. DBE-6759
- ‘@’symbol doesn’t break the spell checker and thus works in variable names. DBE-2250
- JSON groovy extractor works OK with big numbers. DBE-7019
- Row count works if columns are sorted. DBE-2444
- The correct search path is set in the reopened data editor. DBE-7044
- Query plan visualizer works in Redshift. DBE-7129
- Better alias suggestions for temp tables in SQL Server. DBE-5981
- Columns of returned table are resolved in the code. DBE-7176
- No more exception during synchronizing MySQL 5.7. DBE-7234
