Use AI Assistant
This functionality relies on the AI Assistant plugin that requires an additional license.
For more information about licensing and enabling AI Assistant, refer to JetBrains AI Service licensing and Enable AI Assistant plugin.
This functionality relies on the AI Assistant plugin, which you need to install and enable.
Press CtrlAlt0S to open settings and then select Plugins.
Open the Marketplace tab, find the AI Assistant plugin, and click Install (restart the IDE if prompted).
AI Assistant provides AI-powered features for software development. For more information about AI Assistant, refer to the AI Assistant topic.
In this tutorial, we will learn how to use AI Assistant for the following tasks:
note
The AI Assistant response and suggestions might not be exaclty the same as those given in this tutorial.
If you need a custom data aggregator or extractor, you can ask AI Assistant to create one for your task, and then add the created script to the aggregators or extractors directory.
Providing the LLM with an example of a working script might shorten the number of prompts and corrections. In DataGrip, you can find bundled scripts in the Files tool window Alt02 under Scratches and Consoles
For more information about aggregators and extractors, refer to Aggregate view and Data extractors.
In this tutorial, we will create an aggregator and use the following AVG.groovy aggregator as an example:
/*
{...}
In DataGrip, you can find this script in the Files tool window under Scratches and Consoles
On the toolbar, click More tool windows and select AI Assistant to open the AI Assistant tool window.
In the input field, type your prompt. Describe the required script and provide the LLM with an example of a working script.
In the upper-right corner of the field with the generated script, click Copy to Clipboard to copy the code to clipboard.
The generated script:
/*
{...}
You can open the Files tool window by doing one of the following:
In the main menu, go to View | Tool Windows | Files.
On the right tool window bar, click Files.
Press Alt02.
Navigate to Scratches and Consoles
| Extensions .| Database Tools and SQL | data | aggregators Right-click the aggregators node and select New | File.
In the New File popup, type the new file name. For example,
most_frequent_values.groovy.
Paste your new data aggregator script into the file.
(Optional) If the Convert Pasted Code dialog appears, press Cancel. In this tutorial, we save the generated script itself.
New data aggregator appears on the aggregator list in status bar for data editor and can be used with your data.
AI Assistant can give you insights about your schema, and it can also generate SQL queries for the schema based on a natural language request. Having access to your schema, it will analyze the structure and provide you with the requested query or information.
For illustration purposes, we will use the PostgreSQL Sakila schema imported to the ai_demo
database. You can get the dump files by cloning the dumps
repository.
git clone https://github.com/DataGrip/dumps.git
For more information about cloning repositories, refer to Set up a Git repository. For more information about running dump files in DataGrip, refer to Import SQL dump files.
To grant the LLM access to your database schema, attach the schema to chat in the AI Assistant tool window.
On the toolbar, click More tool windows and select AI Assistant to open the AI Assistant tool window.
In the AI Assistant tool window, click Attach Schema above input field and select the schema that you want to attach. In our case, it is
ai_demo.public
.If the Attach Schema dialog appears, click Attach to attach the schema.
For more information about attaching schemas to the chat, refer to Attach schema using schema selector.
Ask AI Assistant to generate a certain query or to provide you with certain information about your schema. Consider the following examples:
Request to generate a query.
Request for the insights about your schema.
Request to perform a search.
AI Assistant can help you with optimizing your schema and queries using the EXPLAIN PLAN
command. Having access to your schema, it can analyze the plan and suggest optimizations.
For illustration purposes, we will use the PostgreSQL Sakila schema imported to the ai_demo
database. You can get the dump files by cloning the dumps
repository.
git clone https://github.com/DataGrip/dumps.git
For more information about cloning repositories, refer to Set up a Git repository. For more information about running dump files in DataGrip, refer to Import SQL dump files.
In this tutorial, we will use the following query:
SELECT
customer.first_name,
customer.last_name,
category.name
FROM
customer
INNER JOIN
rental ON customer.customer_id = rental.customer_id
INNER JOIN
inventory ON rental.inventory_id = inventory.inventory_id
INNER JOIN
film_category ON inventory.film_id = film_category.film_id
INNER JOIN
category ON film_category.category_id = category.category_id
WHERE
category.name = 'Comedy';
Obtain and copy the EXPLAIN PLAN
that you want AI Assistant to analyze.
In the query console, right-click your query and navigate to Explain Plan | Explain Plan (Raw).
In the result tab, press Ctrl0A and Ctrl0C to copy the result to the clipboard.
For more information about EXPLAIN PLAN commands, refer to Query execution plan.
EXPLAIN PLAN
for the original query:
Hash Join (cost=141.42..159.52 rows=86 width=81)
{...}
To grant the LLM access to your database schema, attach the schema to chat in the AI Assistant tool window.
On the toolbar, click More tool windows and select AI Assistant to open the AI Assistant tool window.
In the AI Assistant tool window, click Attach Schema above input field and select the schema that you want to attach. In our case, it is
ai_demo.public
.If the Attach Schema dialog appears, click Attach to attach the schema.
For more information about attaching schemas to the chat, refer to Attach schema using schema selector.
Ask AI Assistant to analyze the EXPLAIN PLAN
of your query and to suggest the possible optimizations.
In the input field, type your prompt, press Ctrl0V to paste the
EXPLAIN PLAN
, and press Enter.Type another prompt requesting the necessary commands for the suggested optimizations and press Enter.
Select the commands and run them by clicking Execute on the toolbar, or by pressing CtrlEnter.
The suggested commands:
CREATE INDEX idx_customer_customer_id ON customer(customer_id);
{...}
IDE will create the defined database object, you can view the output in the Output tab of Services tool window.
In the upper-right corner of the field with the generated
CREATE INDEX
commands, click Insert Snippet at Caret to insert the code to query console.The suggested optimized query:
SELECT
{...}EXPLAIN PLAN
for the optimized query:Hash Join (cost=27.80..236.36 rows=1003 width=81)
{...}
Once provided with access to your database schema, AI Assistant can analyze it and compare DDL of the database objects.
Let us compare the actor
and actor_test
tables of the testing.public
schema.
To grant the LLM access to your database schema, attach the schema to chat in the AI Assistant tool window.
On the toolbar, click More tool windows and select AI Assistant to open the AI Assistant tool window.
In the AI Assistant tool window, click Attach Schema above input field and select the schema that you want to attach. In our case, it is
ai_demo.public
.If the Attach Schema dialog appears, click Attach to attach the schema.
For more information about attaching schemas to the chat, refer to Attach schema using schema selector.
On the toolbar, click More tool windows and select AI Assistant to open the AI Assistant tool window.
In the input field, type your prompt and press Enter.
AI Assistant will compare the DDL and explain the differences.
If some code is not working, you can ask AI Assistant to check if there are any mistakes in it and to correct them.
Let us use the following code with a mistake in it as an example:
CREATE FUNCTION film_not_in_stock(p_film_id integer, p_store_id integer, OUT p_film_count integer) RETURNS SETOF integer
AS $_$
SELECT inventory_id
FROM inventory
WHERE film_id = $1
AND store_id = $2
AND NOT inventory_in_stock(inventory_id);
$_$
LANGUAGE sql;
The mistake here is that the declared function has an output parameter p_film_count
, but the SQL body does not calculate or return it. Additionally, the function is declared as returning SETOF integer
, but only returns result from a SELECT
statement.
On the toolbar, click More tool windows and select AI Assistant to open the AI Assistant tool window.
In the input field, enter your prompt, paste the code, request code correction, and press Enter.
If required, make corrections by adding them to your next prompts.
The corrected code:
CREATE FUNCTION film_not_in_stock(p_film_id integer, p_store_id integer)
{...}
tip
You can also use the Find Problems and Suggest Refactoring AI actions in the editor to improve your code. For more information about AI actions, refer to Use AI prompts to explain and refactor your code.
Migrating database objects from one DBMS to another is a complex task that involves steps such as data migration, schema conversion, and so on. You can ask AI Assistant to generate migration scripts for the source code of your database objects.
Let us use the following inventory_held_by_customer
procedure of a PostgreSQL Sakila schema as an example:
CREATE FUNCTION inventory_held_by_customer(p_inventory_id integer) RETURNS integer
LANGUAGE plpgsql
AS
$$
DECLARE
v_customer_id INTEGER;
BEGIN
SELECT customer_id INTO v_customer_id
FROM rental
WHERE return_date IS NULL
AND inventory_id = p_inventory_id;
RETURN v_customer_id;
END $$;
ALTER FUNCTION inventory_held_by_customer(integer) OWNER TO guest;
For AI Assistant to migrate your code, provide the migration details in your prompt.
On the toolbar, click More tool windows and select AI Assistant to open the AI Assistant tool window.
In the input field, type your prompt. Define the original DBMS and the target DBMS, paste the DDL of the database object that you want to migrate, and press Enter.
If required, make corrections by adding them to your next prompts.
The generated migration script:
CREATE FUNCTION inventory_held_by_customer(p_inventory_id integer)
{...}
It can be difficult to read the code that has no indentation, line breaks, consistent capitalization, and so on. To make such code easier to read, use AI Assistant to format it in accordance with your preferred code style.
We will use the following poorly-formatted SQL code as an example:
SELECT e.firstName,e.lastName,d.name,p.projectName FROM employees
e join departments d on e.departmentId=d.id INNER JOIN employee_projects ep on e.id=ep.employeeId
INNER JOIN projects p on ep.projectId=p.id where e.salary>50000 AND d.name='Sales' AND p.status='active'
For AI Assistant to format your code, describe your preferred code formatting rules in the prompt.
On the toolbar, click More tool windows and select AI Assistant to open the AI Assistant tool window.
In the input field, type your prompt with the formatting rules, paste the code that you want to format, and press Enter.
If required, make corrections by adding them to your next prompts.
The formatted code:
SELECT
{...}
Thanks for your feedback!