Store your queries

Virtual views for SELECT statements

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:

Create a virtual view for your SELECT statement

1. Right-click the public schema node and select New | Virtual View.

2. In the Create dialog that opens, enter customers_active in the Name field.

3. In the Source text field, paste your SELECT statement.

   

4. 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.

Live templates

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:

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.

Create a live template for an INSERT statement

1. Press Ctrl+Alt+S to open settings and then select Editor | Live Templates.

2. From the template groups, expand the SQL one.

3. Click and select Live Template.

4. Specify the context in which the template will be available.

   Click Define below the message and select the SQL checkbox.

5. In the Abbreviation field, specify the characters that will be used to expand the template. For example: newact.

6. (Optional) In the Description field, describe the template for reference in the future.

   For example: Insert a new actor record with the current date.

7. In the Template text field, specify the body of your template. For example:

   

8. Click OK to apply all your changes and close the dialog.

9. In the editor, type newact and press Tab.

   

10. Press to run the statement.

11. 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.

SQL files

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.

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

  -- Retrieve all actors SELECT actor_id, first_name, last_name, last_update FROM actor; -- Find the number of films each actor has appeared in SELECT a.actor_id, a.first_name, a.last_name, COUNT(fa.film_id) AS film_count FROM actor a JOIN film_actor fa ON a.actor_id = fa.actor_id GROUP BY a.actor_id, a.first_name, a.last_name ORDER BY film_count DESC; -- List all movies by category SELECT c.name AS category, f.title AS film FROM film_category fc JOIN film f ON fc.film_id = f.film_id JOIN category c ON fc.category_id = c.category_id ORDER BY c.name, f.title; -- Insert a new actor with the current timestamp INSERT INTO guest.public.actor (first_name, last_name, last_update) VALUES (?, ?, CURRENT_TIMESTAMP); -- Delete actors with ID greater than 200 DELETE FROM guest.public.actor WHERE actor_id > 200;

• Several SQL files: :

  SELECT.sql

  -- Retrieve all actors SELECT actor_id, first_name, last_name, last_update FROM actor; -- Find the number of films each actor has appeared in SELECT a.actor_id, a.first_name, a.last_name, COUNT(fa.film_id) AS film_count FROM actor a JOIN film_actor fa ON a.actor_id = fa.actor_id GROUP BY a.actor_id, a.first_name, a.last_name ORDER BY film_count DESC; -- List all movies by category SELECT c.name AS category, f.title AS film FROM film_category fc JOIN film f ON fc.film_id = f.film_id JOIN category c ON fc.category_id = c.category_id ORDER BY c.name, f.title;

  other_queries.sql

  -- Insert a new actor with the current timestamp INSERT INTO guest.public.actor (first_name, last_name, last_update) VALUES (?, ?, CURRENT_TIMESTAMP); -- Delete actors with ID greater than 200 DELETE FROM guest.public.actor WHERE actor_id > 200;

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.

Run configurations

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:

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.