In IntelliJ IDEA, you can leverage AI Assistant’s capabilities within Database Tools for more efficient data querying and handling. It can significantly speed up SQL query generation, explain code, suggest fixes, and even create tables and populate them with test data on your command! Let’s get started!
To enable the AI Assistant plugin, navigate to View | Tool Windows | AI Assistant or Settings | Plugins | Marketplace | AI Assistant.
In-editor AI actions
Generate SQL code in the editor
AI Assistant can generate SQL code right in the query console, which is accessible via the Database Tools window.
Simply click on the AI Assistant icon or invoke it using ⌘\ (macOS) or Ctrl+\ (Windows/Linux), write your request in the input field, and watch AI Assistant transform your words into precise SQL code.
Modify existing SQL code
You can also alter existing SQL queries by following the same steps. Just select a block of code, call the AI Assistant’s input field, and type in the changes or additions you would like to make to your current query.
Test data tables
AI Assistant can create tables and populate them with sample data right in the editor. Click the AI Assistant icon to open the Generate Code with AI dialog and ask AI Assistant to, for example, “Add queries that will create a books table and populate it with three sample rows”.
After AI Assistant generates the code, you can preview and apply the result.
Cloud completion
AI-powered cloud completion autocompletes code in real time based on the context of your SQL query. You don’t need to perform any extra actions to invoke cloud completion in a console, as it’s enabled by default – just keep on querying!
Chat with AI
In the chat, you can ask AI Assistant any database or query-related question, get it to perform actions, and so much more!
Tip: Use /explain or /refactor with #thisFile for quick and precise responses in the current context.
Compare the DDL of two tables
In the chat, you can also tell AI Assistant to compare the DDL of two tables to get a detailed response highlighting the differences between the tables’ structures. In our example, we use the following prompt: “compare the DDL of actor and film_actor tables”.
AI Assistant will generate a detailed comparison analyzing primary keys, columns, indexes, foreign keys, default values, and constraints. It will even give you a summary:
Optimize the query’s performance using Explain Plan
Explain Plan is crucial for maintaining robust, scalable database applications by providing insights into how queries are executed. AI Assistant can help identify issues with a query and optimize its overall performance.
First, run Explain Plan for a query. Right-click an SQL statement and select Explain Plan | Explain Plan (Raw) from the context menu. Then, copy and paste the results into a chat with a request for analysis and optimization. The initial cost in our example is 93.22..134.83.
AI Assistant will suggest an optimized query code, which you can insert into the console right away by clicking on Insert Snippet at Caret.
You can now run the Explain Plan command once again and check the cost. Our example now shows the lower cost of 52.80..94.62.
AI prompts
To invoke a list of AI prompts, right-click an SQL statement or press ⌥↩ (macOS) or Alt+Enter (Windows/Linux), select AI Actions from the context menu, and choose an action you’d like to perform.
Explain a query
It’s pretty straightforward: Explain Code gives you context-aware explanations within your database environment.
Refactor SQL code
Select Suggest Refactoring from the context menu, and AI Assistant will provide a refactored piece of code alongside an explanation of why these changes would work better.
You can check both versions side by side by clicking on Show Diff or selecting Apply Immediately to accept the changes right away.
Find problems and suggest a fix
AI Assistant can find and fix problems with SQL queries. Select Find Problems from the AI actions list and AI Assistant will provide an explanation in the chat.
You can then ask AI Assistant to fix the code right in the chat.
Add your own prompts
You can add custom prompts and use them via the AI Actions menu. You can set this up by going to Settings | Tools | AI Assistant | Prompt Library and clicking +.
Modify tables
AI Assistant can help you modify tables. Call the Modify dialog by right-clicking on the table in the Database Tools window and selecting Modify Table. Click the AI Assistant icon and type in your request, for example: “Switch all the VARCHAR data types to CHAR”.
Once AI Assistant generates the requested code, you’ll be able to view it in the preview pane of the dialog and then accept the suggestion.
All these AI Assistant features can greatly enhance your productivity when working with Database Tools.
