DataGrip 2021.3.1

Data editor adds the ability to display an aggregate view for a range of cells.
November 26, 2021
New Version

Features

  • Data editor
    • Aggregates - Added the ability to display an Aggregate view for a range of cells. This will help you manage your data and spare you from having to write additional queries. Features include:
      • The Aggregate view shares the panel with Value view, each with its own tab now. You can move this panel to the bottom of the data editor.
      • You can use the gear icon to display or hide any aggregate from this view.
      • Like extractors, aggregates are scripts. You can create and share your own in addition to the nine scripts that are bundled by default.
      • Aggregate scripts and extractors are interchangeable. If you’ve previously used an extractor to get just one value, you can now copy it to the Aggregators folder and use it for aggregates. Like the Extractors folder, it is located in Scratches and consoles / Extensions / Database Tools and SQL.
        • One aggregate value will be displayed in the status bar, and you can choose which value (sum, mean, median, min, max, and so on) you’d like it to be.
    • Table view for tree nodes - Pressing F4 on any schema node now displays a table view of the node’s contents. You can use this view to hide/show columns, export the data to many formats, and use text search. More importantly, the following navigation actions work here, too:
      • Ctrl+B shows you the DDL.
      • F4 shows the data.
      • Alt+Shift+B highlights the object in the database tree.
    • Independent split - If you split the editor and open the same table again, the two data editor windows will now be completely independent. You can then set different filtering and ordering options for them to compare and work with the data. Previously, filtering and ordering were synchronized.
    • Custom font - You can now choose a dedicated font for displaying data.
    • Foreign key navigation by several values - In the data editor, you can now select several values and navigate to the related data.
    • Setting for default sorting - You can now define the default method for sorting tables via ORDER BY or client-side: the latter doesn’t run any new queries and sorts only the current page.
    • Display mode for binary data - 16-byte data is now displayed as UUID by default. You can also customize how binary data is displayed in the data editor column.
    • Completion for filter {} and sort {} (MongoDB) - Code completion is now available when you’re filtering data in MongoDB collections.
  • Keeping your database in the VCS
    • Mapping the DDL data source and the real one - The ability to generate a DDL data source based on a real one was introduced in the previous release. Now, this workflow is fully supported. You can:
      • Generate a DDL data source from a real one.
      • Use the DDL data source to map the real one.
      • Compare and synchronize them in both directions.
      • Just as a reminder, a DDL data source is a virtual data source whose schema is based on a set of SQL scripts. Storing these files in the Version Control System is a way to keep your database under the VCS.
      • There is also a new tab in the data configuration properties, DDL mappings, where you can define which real data source is mapped to each DDL data source.
    • New database diff window - To compare and synchronize your DDL data source with the real one, use the context menu and select Apply from... or Dump to... from the DDL Mappings submenu.
      • This brand-new window has a better UI and clearly shows in the right-hand pane what result you’ll get after you perform the synchronization. The legend in the right-hand pane shows what the colors mean for your potential result:
        • Green and italic: object will be created.
        • Grey: object will be deleted.
        • Blue: object will be changed.
      • The Script preview tab shows the result script, which can be either opened in a new console or run from this dialog. The result of this script is applying changes to make the database on the right (target) a copy of the database on the left (source).
      • Besides the Script preview tab, there are two more tabs on the bottom pane: Object Properties Diff and DDL Diff. They display the difference between the particular versions of the object in the origin and in the target databases.
      • Just a reminder: if you want to just compare two schemas or objects, select them and press Ctrl + D.
    • File-related actions - All actions for files are now available on DDL data source elements as well. For example, you can delete, copy, or commit files related to the schema elements from the database explorer.
    • Auto-sync - If this option is turned on, your DDL data source will be automatically refreshed with changes to the corresponding files. This was already the default behavior, but now you have the option to disable it. If you do disable it, changes in the source files will not automatically be reflected in the DDL data source, so you'll need to click Refresh to apply them.
    • Setting the default schemas and databases - In the Default schemas/databases pane you can define names for your database and schemas, which will be displayed in the DDL data source. DDL scripts don’t usually contain names, and in these cases there will be dummy names for databases and schemas by default.
  • Connectivity
    • Accidental spaces warning - If any value except User or Password has leading or trailing spaces, DataGrip will warn you about them when you click Test Connection. LocalDB as a dedicated data source (Microsoft SQL Server) - SQL Server LocalDB now has its own dedicated driver in the driver list. This means that it has a separate type of data source which should be used for LocalDB. Here’s how it helps:
      • The LocalDB connection is more explorable.
      • You only need to set the path for the executable once, in the driver options, and it will be applied for all data sources.
    • Kerberos authentication (Oracle and SQL Server) - It is now possible to use Kerberos authentication in Oracle and SQL Server. You need to obtain an initial ticket-granting ticket for the principal by using the kinit command, which DataGrip will use when you choose the Kerberos option.
    • Enable DBMS_OUTPUT (Oracle and IBM Db2) - This new option in the Options tab lets you enable DBMS_OUTPUT by default for new sessions.
    • More options button - Added a More Options button for when you need to configure something unusual for a connection. The currently available options include the ability to add Schema and Role fields for Snowflake connections, and two menu items for configuring SSH and SSL to increase their discoverability.
    • Expert options - The Advanced tab now includes a list of Expert Options. In addition to the option to turn on the JDBC introspector, the following database-specific options are available:
      • Oracle: Disable incremental introspection, Fetch LONG values and Introspect server objects.
      • SQL Server: Disable incremental introspection.
      • PostgreSQL (and similar): Disable incremental introspection and Do not use xmin in queries to pgdatabase.
      • SQLite: Register REGEXP function.
      • MYSQL: Use SHOW/CREATE for source code.
      • ClickHouse: Automatically assign sessionid.
  • Introspection
    • Introspection levels (Oracle)
      • Three levels of introspection for Oracle databases have been introduced:
        • Level 1: Names of all supported objects and their signatures, excluding names of index columns and private package variables.
        • Level 2: Everything except source code.
        • Level 3: Everything.
      • Introspection is fastest on level 1 and slowest on level 3.
      • The introspection level can be set either for a schema or for the whole database. Schemas inherit their introspection level from the database, but it also can be set independently.
      • The introspection level is represented by the pill-looking icons located next to the data source icon. The more the pill is filled, the higher the level. A blue icon means that the introspection level is set directly, while a grey one means that it is inherited.
    • Mapping linked servers and database links to data sources (SQL Server and Oracle) - You can now map your linked server in SQL Server or database link in Oracle to any existing data source. When external objects are mapped to the data source, the code completion and resolve will work for queries using those external objects.
    • Hide system schemas and template databases (PostgreSQL) - Internal system schemas (like pg_toast or pg_temp) and template databases used to be hidden from the schemas list. Now it’s possible to show them by using the corresponding options in the Schemas tab.
    • Support for streams (Snowflake) - Streams are now displayed in the database view in addition to tables and views.
    • Distributed tables (ClickHouse) - Distributed tables are now placed under a dedicated node in the database explorer.
  • Query Console
    • Check for boolean expressions - Added a new check for boolean expressions in WHERE and HAVING clauses. If the expression doesn’t seem to be explicitly boolean, DataGrip will highlight it in yellow and will warn you before you run such a query. It works for ClickHouse, Couchbase, Db2, H2, Hive/Spark, MySQL/MariaDB, Amazon Redshift, SQLite, and Vertica. In all other databases, this will be highlighted as an error.
    • Extract function for queries - Queries can now be extracted as a table function. To do this, select the query, invoke the Refactor menu, and use Extract Routine.
    • JOIN cardinality inlay hint - The new inlay hint will tell you the cardinality of a JOIN clause. There are three possible options: one-to-one, one-to-many, and many-to-many. If you want to turn it off, you can adjust the setting in Preferences | Editor | Inlay Hints | Join cardinality.
    • Code completion for database names (MongoDB) - Database names are now completed when using getSiblingDB, and collection names are completed when using getCollection. Additionally, field names are now completed and resolved if used from a collection that was defined with getCollection.
  • Services tool window
    • Timestamps in output hidden by default - Timestamps are no longer shown for query output by default. If you want to return to the previous behavior, you can adjust the setting in Database | General | Show timestamp for query output.
    • New activation settings - If you use the Services tool window in window mode, by default it is hidden behind the IDE. With the new setting you can pass the focus to it every time you run a query, so it will appear after the query has finished. Also, if you are annoyed when finishing a long query in some other console activates the corresponding tab in the Services tool window, select the Activate Services output pane for selected query console only checkbox.
  • Import/Export
    • New UI for importing data - When importing .csv files or copying tables/result sets, you will observe the following improvements:
      • You can choose an existing table or create a new one.
      • You can change the target schema in the import dialog. The dedicated dialog for the target will not appear if you copy a table or result set.
      • The target is saved as default per schema. So, if you are constantly copying from one particular schema to the other, there will be no need to choose the target each time.
    • First row is header auto-detection - When you open or import a CSV file, DataGrip will now automatically detect that the first row is the header and contains the names of the columns.
    • Automatic column types in CSV files - DataGrip can now detect column types in CSV files. The main benefit of this is that you can sort data by numeric values. Before, they were treated as text and the sorting wasn’t intuitive.
  • Miscellaneous
    • New Bookmarks tool window - Before there were two very similar instances – Favorites and Bookmarks. As the difference between the two could sometimes be confusing, DataGrip has decided to stick to just one – Bookmarks. The workflow has been reworked for this functionality and a new tool window has been made for it. From now on, all the objects or files you mark as important (with the F3 shortcut on Apple macOS or F11 on Microsoft Windows/Linux) will be located in the new Bookmarks tool window.
DataGrip 2021.3.1
Aggregate view

DataGrip

A database IDE that is tailored to suit the specific needs of professional SQL developers and DBAs.

Get Quote Buy Now

DataGrip is also available in:

Got a Question?

Live Chat with our JetBrains licensing specialists now.