DataGrip 2017.2 Released!

DataGrip 2017.2 Released!

Hello!

Please give a warm welcome to our new release, DataGrip 2017.2.

DataGrip_20172_splash

We thank all of the EAPers who have helped us by testing all the new features we added in this release. Let’s have a look at them.

– Amazon Redshift and Microsoft Azure support
– Several databases in one PostgreSQL data source
– Transaction Control
– Evaluate expression
– Table DDL
– Integration with restore tools for PostgreSQL and MySQL
– Executing queries
– Coding assistance
– Other

Amazon Redshift and Microsoft Azure support

Database сloud services are growing more popular with every day. We were asked to add support for these two, especially Redshift, and here they are.

NewSupportedDatabases

Microsoft Azure is similar to SQL Server, so we just added a dedicated driver, UI for adding the data source and some enhancements in the introspection.

But there are many things we did for Amazon Redshift.  The introspection is now incremental. This means that only modified objects will be refreshed in a database tree after any operation instead of all objects.

Also, we started to support specific cases in SQL grammar which are different from PostgreSQL. For example, UNLOAD statement is not only highlighted correctly, but has an SQL-injection inside.

Unload

Another example: support for embedded Redshift functions that are absent in PostgreSQL.

RedshiftFunctions

Generation of the DDL scripts for tables and functions has been enhanced.

Now DDL for tables contains the following attribute:

column_attributes:

and table_attributes:

The source code of a function is now generated with the following section:

In general, our support means, that if a part of your Redshift code is highlighted as an error but in fact it isn’t, we consider this a bug. Please report such cases to our issue tracker.

If you’re already using Azure and Redshift via SQL Server and PostgreSQL drivers, please re-select the driver in the data source properties window:

ChangeDriver

Several databases in one PostgreSQL data source

This was a really long-awaited feature, but the wait is finally over. Thanks for bearing with us! We refactored a lot of code to implement this functionality, so some things might still be missing. Please give us your feedback on your experience using data sources with multiple databases.

SeveralDatabasesForPostgres

Data sources with several databases are also supported for Amazon Redshift, Microsoft Azure, and any unsupported databases whose drivers report several databases.

Transaction Control

Transaction Control feature replaced the Auto-commit option.

First, define what kind of transaction control you want to use when working with a particular data source. This option is available in data source properties. In the Auto mode you don’t need to commit any transactions executing the commit statement., while in the Manual mode, you, obviously need to do this.

DataSourceProperties

This also can be defined for each query console along with the isolation level.

IsolationLevel

The data editor in the Manual mode also has Commit and Rollback buttons on toolbar. All actions are also available in the context menu.

CommitInDataEditor

So, now it works like this:

Auto and Manual mode

Submit button or Ctrl/Cmd+Enter → submits your data, which means that your local changes(they are highlighted) are submitted to the database. If you are in the Manual mode this transaction is not committed.

Revert Selected from the context menu or Ctrl/Cmd+Alt+Z (it used to be Ctrl+Z, but Revert isn’t Undo, right?) on selected rows → reverts unsubmitted local changes of the selected rows.

Only in Manual mode

Commit button or Shift+Ctrl+Alt+Enter → commits the transaction. If you have unsubmitted local changes (again, they are highlighted) they will be automatically submitted before the commit.

Rollback button → rolls back transaction if it’s uncommitted.

Evaluate expression

This will help you see data without writing a query to the console.

In other IntelliJ-based IDEs, Ctrl+Alt+F8 on an object gives you quick evaluation. In DataGrip, invoke it on a table in a query to see the data of that table.

QuickEvaluateTable

Ctrl+Alt+F8 on a column name will show the values of that column in the expected result-set.

QuickEvaluatecolumn

If you invoke the same quick evaluate on the keyword of a query (or subquery), the pop-up will show you the result. Note that Alt+Click also works for this.

EvaluateAltGif

Press Alt+F8 to open an Evaluate pop-up, where any query can be run. If invoked on a table, it will show the data.

EvaluateTable

You can evaluate expressions here as well.

Table DDL

We have divided the Data and DDL tabs when viewing a table. Actually, there are no more tabs. Now, when you open a table with a double-click, you just see data. Where is the DDL? It can be opened by Edit Source on the toolbar or by Ctrl/Cmd+B.

Edit-source

The same DDL editor will open when pressing Ctrl/Cmd+B on the table name in the SQL script. As you may remember, Ctrl/Cmd+click does the same. In the previous versions this action  brought you to the database tree. If you still need this, press Alt+F1 on any object and choose Database view.

It became easier to open Data editor for a table. Just click F4 on the table name, either in the database tree or in SQL.

CtrlBF4

Integration with restore tools for PostgreSQL and MySQL

In 2016.3 we integrated DataGrip with mysqldump and pg_dump, so it’s logical to add integration with the restore tools of these databases as well. Now they can be accessed from the context menu. If there’s only one tool available, this option will look like ‘Restore with pg_restore’, etc.

RestoreMenu

In the case of PostgreSQL, the restore operation can be done with pg_dump or psql: there is a chooser in the Restore dialog:

pg_restore

Psql

And it looks like this in MySQL:

mysqlrestore

Executing queries

There is a new action for any file called Attach console. You can find via Ctrl+Shift+A or by opening the context menu for a file. After performing this action, this file can be run against the console. Switching consoles will be useful if you want to run the same script in several data sources.

AttachConsole

Called-for feature: a notification is now displayed when a large query is finished. To disable it, in Settings look for the “Database queries that took much time” notification. It appears if it took more than 20 seconds to execute a statement.

Notification

Many of you have asked for result-sets to be switched along with the corresponding consoles. Done!

SwtichResult-sets_preview

The same works in the opposite direction.

ScrollFromResult-set

These behaviors are on by default, look for the “Autoscroll ..” toggles in the gear-icon menu of the Database Console tool window.

AutoSCrollOff

And another small enhancement: when you execute a query with subqueries, the outer statement is selected in the drop-down by default. Well, nested statements often just cannot be executed at all.

Subqueries

Schema switcher works in MySQL even with a read-only connection.

MySQLSwitcher

Coding assistance

Completion now works for columns in table-valued functions.

TableValued

NEW and OLD references are resolved in PostgreSQL triggers.

NEWOLD

We’ve added support for MERGE statements for all databases where they are available.

Merge

Objects are resolved by their OID values in PostgreSQL.

OIDs

Sequences are resolved in scripts as well.

Sequence-1

Other

New features:

— Database objects can be added to Bookmarks.
— You can now pause file indexing when you need to free the CPU for other tasks, and then resume it when appropriate.
— We added per-monitor DPI support for Windows. Font settings are automatically adjusted based on the display resolution.
— Lines with two identical results are merged in Find in Path.
— Support for non-int-literal expressions in TABLESAMPLE and ALTER FOREIGN TABLE in Postgres.
— Support for ‘json_table’ function in Oracle.

Important fixes:

DBE-4600 with renaming schemas.
DBE-1288 with renaming sequences in PostgreSQL.
DBE-4507 in composite primary key editing.
DBE-4637 about a wrong error message in queries with GROUP BY.
— Faster data editor scrolling

Get your 30-day trial of DataGrip here.

That’s it! Don’t forget about twitterforum, and the issue tracker.

Your DataGrip Team
_
JetBrains
The Drive to Develop

This entry was posted in Uncategorized. Bookmark the permalink.

30 Responses to DataGrip 2017.2 Released!

  1. Adam L says:

    Loving all the goodies in native Redshift support — thank you so much! Resolves several pesky small issues I had with DG 2017.1 using Amazon’s JDBC 4.2 Redshift drivers manually — very helpful stuff!

  2. Xander says:

    All I want is some sort of color highlighting for database data…

    Heidi and everything else has this, why not Datagrip?

    I want null to be a color, int’s to be a color, etc. Instead the whole thing is black and white or gray and white or whatever.

  3. davis233 says:

    well, when i add a postgres datasource, i can not find the multi database, what is wrong with my DG?

  4. Samuel.Kein says:

    Where is… DDL Tab :(
    i need DDL tab~!! plz

    • Maksim Sobolevskiy says:

      Now it’s Edit Source on the toolbar (as mentioned in the post).
      But in 2017.2.1 we’ll improve it, sorry for the inconvenience.

  5. Brenden says:

    Any update on these issues?

    – DBE-2100
    – DBE-3241

    • Maksim Sobolevskiy says:

      I’m afraid, but they are rather complicated and not easy to fix. So: not in the next release, Brenden.

  6. mucio says:

    Where can I find a roadmap for the future features in DataGrip?

    I’m looking for native support for EXAXOL (unfortunately a jdbc connection is not enough, specific SQL syntax, procedures are called script and the programming language used is LUA) or, at least, the possibility to interate different SQL dialects.

    Also support for Presto will be very appreciated.

    • Maksim Sobolevskiy says:

      We are thinking about EXASOL now, perhaps it will appear in the next release.
      If it happens, it will give us an experience and some workflow in the aspect of adding new dialects.
      Stay tuned!

  7. Rodrigo says:

    One of the best features of the datagrip was exactly the two tabs of “Data” and “DDL”. Now, the new version dont exicts abandon this resource for me is a downgrade.

  8. Gene says:

    Right-clicking an object and selecting “Go to” -> “Declaration” no longer navigates to the object in the tree, it just opens the DDL. This was a huge feature when dealing with databases with a large number of objects (especially ones that are stored proc heavy). Is there a way I can switch that back?

    I wish you wouldn’t change existing functionality to something completely different. Instead, provide a SETTING to enable the NEW functionality, if wanted.

    • Maksim Sobolevskiy says:

      A new action is introduced called Select in the database view. Now there is no UI shortcut, but you can invoke it by Ctrl+Shift+A or define your own shortcut. Remember, that the same can be done in a more general way: press Alt+F1 on any object and choose Database view.

  9. PlanB says:

    There is an option to drag and drop some db object name (table, schema etc.) from navigator to console.

    I’d be very good to have an ability to drag and drop table name already with a schema name attached (%schema_name%.%db_object_name%), e.g. this can be done by drag and drop during holding a Ctrl button.

    Same applicable to column names in data grid – a drag and drop option for them from there to console window will be veeery useful.

    Thanks.

    • Maksim Sobolevskiy says:

      Workaround: Alt+Enter on object -> Qualify object

      About columns, I just cannot get the use-case :)

      • PlanB says:

        Didn’t get you about workaround.
        https://postimg.org/image/95edaowibf/
        Alt+Enter on table (names removed), there is nothing like “Qualify object”
        Same for sprocs there but ofc without “Truncate” option.
        If try Alt+Enter on table name which was drag’n’dropped from navigator to console – just nothing happens.

        As regarding use case for drag’n’drop column names from data grid, well, imagine..
        – you have a horizontally looong datagrid on the bottom,
        and you need to construct e.g. some subquery with more than X fields with SOME names that can be derived from that datagrid,
        so you start your input like “… (select super_important_column1, another_important_column2, … critical_column100500 … from ..)”
        and getting bored fast because column names suggestion popup window doesn’t work in the beginning of your input.

        – or you need to construct some DML/DDL real quick which part based on some set of column names that can be derived from the datagrid
        The workaround for this particular case is only copy-paste selection from a grid with specific “Data Extractor” setting that was set before.

        Btw such feature is implemented in Dell’s Toad for SQL Server.

        • Maksim Sobolevskiy says:

          1. It should work in console after darg’n’drop. If doesn’t please provide a screenshot. Mine is: https://pp.userapi.com/c639126/v639126404/4fb99/HYiXS4u2hAQ.jpg

          2. Why completion does not help? Just type sel, press tab. It will be SELECT * FROM table. Then use code completion to put column names. It will be muuuch faster than drag’n’dropping. Where is your result received from? Do these names are table columns or aliases? The same is about this DDL use-case.

          I don’t want to have this feature implemented =) I want to understand if our conceptions cover the needs of users.

        • Maksim Sobolevskiy says:

          1. Ok, let’s try this: http://img.pixady.com/2017/10/258255_qualifyidentfier_460x195.jpg
          You should drag it into the query console of this data source.

          2. BTW, from 2017.3 you’ll be able to drag-n-drop several columns at once from the table in the database tree.

          • PlanB says:

            1. Finally got it.
            To make “Qualify identifier” work, query console must be opened not only on data source level but on even schema level. Otherwise drag’n’dropped object names from other schemes will not yield “Qualify identifier” popup.
            On your screenshot you point on query console opened on default sql server “dbo” scheme. Just try to drag’n’drop to the same console (as on screenshot) object from some other schema of same data source, and magic will disappear :)
            As for me – I miss that possibility to drag’n’drop object from any schema and qualify it then if you have console opened on some default schema level.
            Anyway, thanks for your efforts.

            2. That sounds pretty good. Yep, now only one can be drag’n’dropped from multiple selection.

  10. PlanB says:

    Subj – PostgreSQL data source.
    After stored procedure script execution:
    “create or replace function myschema.my_sproc …” it appears in database objects navigator on the left side. If then open it in console window mentioned sproc signature is displayed changed – without schema name and “or replace” expression – like just “create function my_sproc …”. I believe metadata for sproc stored in DB in a way as it was present in sproc script that was initially executed there.
    Omitting part of full “create or ..” expression and schema name bring inconvenience.
    If it can be fixed in next releases it’d be much appreciated.

  11. DBA says:

    Cherishing every one of the treats in local Redshift bolster — thank you to such an extent! Resolves a few bothersome little issues I had with DG 2017.1 utilizing Amazon’s JDBC 4.2 Redshift drivers physically — exceptionally accommodating stuff!

Leave a Reply

Your email address will not be published. Required fields are marked *