Create multiple consoles with SQL editors to manage lots of queries at the same time. Each console has a database/schema switcher. If you work with PostgreSQL define the search path here.
Select some code and execute only that selection. Note that the selected query is run as is: DataGrip just sends this string to the database and gets the result. It can be useful when the IDE incorrectly detects errors in queries.
DataGrip provides smart options for executing queries. When the cursor is inside a query, the execution can be customized with several possible options available. Define its intended behavior: execute the smallest statement, the largest statement, ask you to choose what to execute, or execute the whole script.
Moreover, there are Execute actions. Change the behavior, and assign shortcuts: they are completely independent. For example, you can create an action which will execute the whole script, without having to choose the exact query from the script.
That's how it works:
Read-Only can be turned on in the data source properties. In fact, it turns on two read-only modes at the same time: read-only mode in the corresponding jdbc-driver and the IDE-level read-only mode.
Jdbc-driver read-only mode will refuse to run modification queries in MySQL, PostgreSQL, AWS Redshift, H2and Derby. In other DBMS’s it does nothing.
That's why we have an IDE-level read-only mode. It is activated along with the jdbc-level one. Thus, in read-only mode every DDL or DML query will be underlined. If you execute it, there will be a warning. It is possible to press Execute on this bar if you’re totally confident in what you are doing.
We also now index the source code of the functions and procedures, and build a call-tree in the background. It means that if a function executes another function (repeat n times) which executes DDL or DML statements, you’ll also see a warning.
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 do need to.
This will help you see data without having to write a query to the console. In other IntelliJ-based IDEs, Ctrl+Alt+F8 on an object gives you a quick evaluation. In DataGrip, invoke it on a table in a query to see the data of that table. Ctrl+Alt+F8 on a column name will show the values of that column in the expected result-set. 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 will also work for this.
History of run queries
On the toolbar of every console there is a button: . Press it to see the history of all the queries which were run against this data source. Speed search also works!
Also, don't forget about the Local History of each file.
An execution plan for statements visually represents the operations the database performs in order to return the data required by your query and it helps you to optimize the query. Choose to view the plan graphically or in a table format.
For each console or SQL file you have a pop-up where all the statements are submitted as a structure. You can run queries just from this Structure pop-up which will be useful when you need to run several of them in a big script.
Working with files
Attach console to
.sql files, meaning that objects will be associated with the data
source of this console and queries will be run in the context of this console.
Run against data-source, database or schema
Invoke the context menu and run script files against a particular data-source, database or schema. It is much faster than opening these files and then running scripts from the editor.
SQL resolutions scopes
You can map any SQL file or folder to any datasource in Settings → Tools → Database → SQL resolution scopes. If done, all unqualified objects from these locations will be resolved to the selected datasource, database, or schema.
Change a file's language
To get the correct highlighting and resolution suggestions, you can change the language of any file. Or, in the case of SQL, select the appropriate dialect.
In DataGrip the result-set is modifiable, so you can change data returned after you execute a query. It is the similar to the data editor with the same options available including auto-commit, adding/deleting rows, text search, and data navigation.
Two result-sets can be compared in Diff Viewer. DataGrip will highlight the differences and let you manage the comparison criteria via the Tolerance parameter. For instance, if you want to consider two rows as equal if their data differs in a single column only, enter the corresponding value in the Tolerance field. Any column can be excluded from the comparison if needed.