Store your queries
This tutorial describes several ways to store your queries in DataGrip. You can use the one that is more suitable for your daily tasks.
The examples are shown for the guest.public schema of the Sakila PostgreSQL database. To learn how to create and run the Sakila PostgreSQL database in DataGrip, refer to the Run the PostgreSQL Sakila database in a Docker container topic.
Virtual view is an IDE virtual object that lets you have the result set available in the Database Explorer. For more information about virtual views, refer to the Virtual views topic.
For example, the following SELECT
statement has to be run daily against the public schema of the Sakila PostgreSQL database to get a list of currently active customers:
SELECT
customer_id, first_name, last_name, store_id, email,
address_id, activebool, create_date, last_update, active
FROM customer
WHERE activebool = TRUE
ORDER BY last_name, first_name;
Right-click the public schema node and select New | Virtual View.
In the Create dialog that opens, enter
customers_active
in the Name field.In the Source text field, paste your
SELECT
statement.Click OK to create your virtual view.
DataGrip creates a new virtual view and displays it in Database Explorer. To open your new virtual view, double-click it.

Another way of storing statements is by using live templates which allow you to insert code blocks into your code editor.
For more information about live templates, refer to the corresponding topic.
For example, if you use the following statement regularly to delete records with an actor_id
greater than 200
:
DELETE FROM guest.public.actor
WHERE actor_id > 200;
Press CtrlAlt0S to open settings and then select Editor | Live Templates.
From the template groups, expand the SQL one.
Click
and select Live Template.
Specify the context in which the template will be available.
Click Define below the message and select the SQL checkbox.
In the Abbreviation field, specify the characters that will be used to expand the template. For example:
del200
.(Optional) In the Description field, describe the template for reference in the future.
For example:
Delete all records in the actor table that have an actor_id greater than 200
.In the Template text field, specify the body of your template. For example:
Click OK to apply all your changes and close the dialog.
In the editor, type
del200
and press Tab.
With live templates, you can also store statements with user parameters.
Consider the following example: to add a new record to the actor table, an INSERT
statement is used. First and last names of the actor have to be entered manually each time.
INSERT INTO guest.public.actor (first_name, last_name, last_update)
VALUES (?, ?, CURRENT_TIMESTAMP);
Press CtrlAlt0S to open settings and then select Editor | Live Templates.
From the template groups, expand the SQL one.
Click
and select Live Template.
Specify the context in which the template will be available.
Click Define below the message and select the SQL checkbox.
In the Abbreviation field, specify the characters that will be used to expand the template. For example:
newact
.(Optional) In the Description field, describe the template for reference in the future.
For example:
Insert a new actor record with the current date
.In the Template text field, specify the body of your template. For example:
Click OK to apply all your changes and close the dialog.
In the editor, type
newact
and press Tab.Press to run the statement.
In the Parameters dialog that appears, enter the values and click Execute to run the statement.
For example, enter
'Jay'
as the first parameter and'Doe'
as the second one.
After the statement is executed, the new record appears in the actor table.

Depending on your needs and workflow, you can either store all your queries in a single SQL file or create and use multiple SQL files for it.
tip
SQL files can also be put under VCS. For more information about working with VCS, refer to Version control.
In this tutorial, we will take a closer look at all those cases. For illustration purposes, we will use the following sets of queries as file contents:
One SQL file: my_queries.sql
Queries
{...}Several SQL files: :
SELECT.sql
SELECT queries
{...}other_queries.sql
Other queries
{...}
You can attach an existing directory where you store your SQL files to the DataGrip project, and have access to it in Files tool window . In the attached directory, you can open the existing SQL files and also create new ones.
For example, you have a query_book directory where you already store the SQL files, and you want to have access to that directory and its files in the IDE. To do that, you need to attach the directory to the DataGrip project.
In the Files tool window , click the Attach Directory to Project button (
) in the toolbar.
Alternatively, right-click in the area of the Files tool window and select Attach Directory to Project.
In the file browser, navigate to the directory that you want to attach. In our case, it is the query_book directory.
Click Open.
The directory will become available in the Files tool window . To open your SQL file in the editor, expand the attached directory node and double-click the filename.
In the Files tool window , click the Attach Directory to Project button (
) in the toolbar.
Alternatively, right-click in the area of the Files tool window and select Attach Directory to Project.
In the file browser, navigate to the directory that you want to attach. In our case, it is the query_book directory.
Click Open.
The directory will become available in the Files tool window . To open your SQL file in the editor, expand the attached directory node and double-click the filename.
You can store and run your queries using the Database Script type of run configurations.
When a SELECT
query is run from a run configuration, the result set is displayed in the Services tool window.
For example, to fetch the customers who have made more payments than others, the following query can be used:
-- Most frequent payers
SELECT c.customer_id, COUNT(p.payment_id) AS payment_count,
c.first_name, c.last_name
FROM guest.public.customer c
JOIN guest.public.payment p ON c.customer_id = p.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY payment_count DESC;
Open the Run/Debug Configuration dialog in one of the following ways:
Select Run | Edit Configurations from the main menu.
With the Navigation bar visible (View | Appearance | Navigation Bar), choose Edit Configurations from the run/debug configuration selector.
Press AltShiftF10 and then press 00.
In the Run/Debug Configurations dialog, click the Add New Configuration icon (
) and select Database Script to create a new run configuration.
For more information about settings of the configuration type, refer to Database script.
In Name, enter your new run configuration name. For example,
most frequent payers
.In Target data source / schema, select the schema that you want to run your query against.
In this tutorial, it is the guest.public schema of the PostgreSQL data source.
In Execute, select Script text.
In the text field, enter your query.
Click OK to save the run configuration.
To run your new run configuration, click Run '<run_configuration_name>' in the run widget on the title bar.

For more information about run configuration, refer to Using run/debug configurations.
Thanks for your feedback!