Query Book in DataGrip

Maksim Sobolevskiy

People often ask us:

– Do you have a query book? I have numerous queries I run time after time. I want to store them and to be able to call any of them when I want.

There’s an issue for this feature in our DataGrip issue tracker.

While we don’t have a feature specifically named query book, we believe there are approaches you can take with DataGrip that cover the majority of these needs:

• Virtual views
• Live templates
• Run configurations
• One file with all queries
• Separate query files

Virtual views

Starting from 2023.1, this is the most preferable way to store and maintain SELECT queries in DataGrip.

Live templates

Generally, live templates are created for situations where you just need to use a small code snippet. They include a mechanism for variables, so your template can be more generic than the query you are going to run. Here is how they work:

But there is nothing to stop you from putting any query into your template! Every live template has its own abbreviation – just type it and press Tab to paste the code. For example, our query that shows the staff list has the abbreviation ‘slist’:

You can invoke this template in any context you define. Moreover, you can create different implementations for different SQL dialects, so a template abbreviation will paste a different query depending on which database you are working on.

The shortcut Cmd/Ctrl+J opens the list of live templates. This list is useful because you can use not only the abbreviation but also the description to search here, which means you can name your templates and then search by these names.

Pros:

• They’re fast: live templates can be invoked easily in any context.
• The variables mechanism makes it possible to use more generic approaches.
• Queries can differ depending on the dialect.
• Search throughout the names and descriptions.
• Queries can be parametrized.

Cons:

• You can’t search inside the actual template code.
• Hard to share, impossible to have the synchronized source throughout the team.
• Not practical for really big scripts.

Run configurations

This is a concept DataGrip has borrowed from other IntelliJ-based IDEs. In general, if you want to run something, you just need to create a configuration for it. In the context of a database, this ‘something’ is your SQL script.

There are two types of run configurations: script text and script files.

Script text

In this case, the run configuration executes the script that is hardcoded into this particular configuration. You paste the query there, add the target schema or several targets, and save the configuration.

Script files

The run configuration can be made up of one or several script files. You can either add these files from the ‘Edit configuration’ UI or create a new one from the context menu for several files.

The saved run configurations can be found on the Navigation bar and in the Run menu.

By default, for each configuration, DataGrip shows the Edit window before you run it. But this step can be skipped by unchecking the Show this page checkbox. If you check Store as project file, the configuration can be shared along with the project.

To run any configuration, invoke the Run popup by pressing Ctrl+Alt+R (Alt+Shift+F10), choose the required configuration (search works), and run it. That’s all there is to it!

Pros:

• No need to switch the current context.
• Works well for big and compound scripts.
• Search throughout the names.
• Can be saved as a project item and shared that way.

Cons:

• Cannot show data: it’s not suitable for scripts with SELECT statements.
• Cannot search throughout the configurations’ code.

One single file

Storing your favorite queries inside a single file sounds like a fiddly workaround, but DataGrip provides so many features to work with code that this can be quite a good approach! Say, for example, that we name this file QueryBook.sql.

First, wrap all your queries with region comments, as shown in the screenshot.

This will allow you to search these descriptions in the structure view using Ctrl/Cmd+F12. Don’t forget to mark all the check-boxes in the toolbar.

By and large, your query book is ready! This is how you run the query when you need it:

• Invoke Go to File by Ctrl+Shift+N/Cmd+Shift+O
• Type ‘qubo’ and press Enter. We believe that in 99% of cases this will take you to the QueryBook.sql file :)
• Locate the query you need. You can either search by name – Ctrl/Cmd+F12 and type name. Or you can search by source code – Ctrl/Cmd+F will do the job!
• Execute the query.

The file can only be attached to one session, so if you need to switch sessions or data sources, please do so manually.

If you use several database vendors, create separate files for each of them.

Pros:

• It is part of the project.
• Can be shared via version control systems.
• Can be easily used from other IDEs.
• Search throughout the source code.
• Search throughout the names.
• Queries can be parametrized.

Cons:

• The need to manually switch sessions and data sources.
• The need to switch the context.
• Invoking the query requires lots of clicks.

Several files

This is similar to the previous approach, but instead of putting all the queries into one file, we put each query into its own dedicated file. These files can be stored in a dedicated folder which is, for instance, named QueryBook.

There is no need to switch sessions or data sources every time you run the query: it is set for the file, which is to say, for the query.

Faster navigation: in the Go to File popup just input the name of the file with the required query.

The search inside the source code of your Query Book can be performed with the help of the Find in Files mechanism. All you need to do is add the scope Query Book, which will include the folder QueryBook. Then press Ctrl/Cmd+Shift+F, choose the scope Query Book, and search!

Pros:

• It is part of the project.
• Can be shared via version control systems.
• Can be easily used from another IDE.
• Search throughout the source code.
• Search throughout the names.
• Session and data source are saved per query.
• Queries can be parametrized.

Cons:

• The need to switch the context.

That’s it! I personally like live templates best: they are fast, you don’t need to switch contexts, and they are widely customizable. Which do you prefer? Do you have a use case that none of these variants cover? Please let us know.

Subscribe to DataGrip Blog updates

Subscribe form

By submitting this form, I agree to the JetBrains Privacy Policy Notification icon

By submitting this form, I agree that JetBrains s.r.o. ("JetBrains") may use my name, email address, and location data to send me newsletters, including commercial communications, and to process my personal data for this purpose. I agree that JetBrains may process said data using third-party services for this purpose in accordance with the JetBrains Privacy Policy. I understand that I can revoke this consent at any time in my profile. In addition, an unsubscribe link is included in each email.

Submit

Thanks, we've got you!