AI Agents for SQL and Databases in DataGrip

Hello everyone. In this video, we'll Hello everyone. In this video, we'll show you how to work a data grip using show you how to work a data grip using show you how to work a data grip using AI agents. AI agents. AI agents. Once you install the AI assistant Once you install the AI assistant Once you install the AI assistant plugin, you'll see the AI assistant plugin, you'll see the AI assistant plugin, you'll see the AI assistant button in the top toolbar. button in the top toolbar. button in the top toolbar. Clicking it opens the tool window, where Clicking it opens the tool window, where Clicking it opens the tool window, where you can choose to work in chat mode or you can choose to work in chat mode or you can choose to work in chat mode or use any agent installed on your machine. use any agent installed on your machine. use any agent installed on your machine. As you can see, you can use Claude, As you can see, you can use Claude, As you can see, you can use Claude, Codex, Gemini, or any other agent Codex, Gemini, or any other agent Codex, Gemini, or any other agent available from the ACP registry. available from the ACP registry. available from the ACP registry. For this video, we'll be using Claude. For this video, we'll be using Claude. For this video, we'll be using Claude. There might be a need to configure the There might be a need to configure the There might be a need to configure the MCP server for the specific agent. MCP server for the specific agent. MCP server for the specific agent. Please refer to DataGrip's documentation Please refer to DataGrip's documentation Please refer to DataGrip's documentation to accomplish this. The link is in the to accomplish this. The link is in the to accomplish this. The link is in the video's description. video's description. video's description. First, Claude can create a data source First, Claude can create a data source First, Claude can create a data source directly inside DataGrip using the directly inside DataGrip using the directly inside DataGrip using the provided MCP tools. provided MCP tools. provided MCP tools. Let's look at a couple of ways to do Let's look at a couple of ways to do Let's look at a couple of ways to do this. this. this. The simplest method to create a data The simplest method to create a data The simplest method to create a data source is to use a JDBC URL. source is to use a JDBC URL. source is to use a JDBC URL. We just type create data source from URL We just type create data source from URL We just type create data source from URL and paste our URL into the agent chat. and paste our URL into the agent chat. and paste our URL into the agent chat. The first thing Claude asks for is The first thing Claude asks for is The first thing Claude asks for is permission to run a skill called permission to run a skill called permission to run a skill called database connection management.

database connection management. database connection management. Once allowed, Claude does its job and Once allowed, Claude does its job and Once allowed, Claude does its job and then requests permission to run the MCP then requests permission to run the MCP then requests permission to run the MCP tool called create database connection. tool called create database connection. tool called create database connection. After allowing this, it asks for your After allowing this, it asks for your After allowing this, it asks for your credentials as they aren't transferred credentials as they aren't transferred credentials as they aren't transferred via the URL. via the URL. via the URL. I enter my username and password, click I enter my username and password, click I enter my username and password, click okay, and voila, the data source is okay, and voila, the data source is okay, and voila, the data source is created. created. created. You can see it in the left tool window You can see it in the left tool window You can see it in the left tool window called database explorer, complete with called database explorer, complete with called database explorer, complete with all of its objects. Success. all of its objects. Success. all of its objects. Success. I want to highlight that a connection is I want to highlight that a connection is I want to highlight that a connection is created within DataGrip, and we never created within DataGrip, and we never created within DataGrip, and we never ever pass credentials to the AI agent. ever pass credentials to the AI agent. ever pass credentials to the AI agent. Another straightforward way to create a Another straightforward way to create a Another straightforward way to create a data source is to simply provide the data source is to simply provide the data source is to simply provide the agent with all the connection details agent with all the connection details agent with all the connection details you know. you know. you know. I'll type okay, I'll type okay, I'll type okay, create another data source. It's MySQL, create another data source. It's MySQL, create another data source. It's MySQL, hosted locally, port 33081, hosted locally, port 33081, hosted locally, port 33081, database guest. database guest. database guest. Claude starts thinking and again asks to Claude starts thinking and again asks to Claude starts thinking and again asks to use the database connection management use the database connection management use the database connection management skill since I previously clicked allow skill since I previously clicked allow skill since I previously clicked allow instead of always allow.

instead of always allow. instead of always allow. It does its job, then prompts me for It does its job, then prompts me for It does its job, then prompts me for credentials I haven't provided. credentials I haven't provided. credentials I haven't provided. I enter them, I enter them, I enter them, click okay, click okay, click okay, and the second data source is created. and the second data source is created. and the second data source is created. All the details are visible in the chat, All the details are visible in the chat, All the details are visible in the chat, the test connection is successful, and I the test connection is successful, and I the test connection is successful, and I can see the objects in the database can see the objects in the database can see the objects in the database explorer. explorer. explorer. Let's get to the third way to create Let's get to the third way to create Let's get to the third way to create data sources. Exporting connections from data sources. Exporting connections from data sources. Exporting connections from other tools. other tools. other tools. The agent can locate the relevant files The agent can locate the relevant files The agent can locate the relevant files for whatever database tools you have for whatever database tools you have for whatever database tools you have installed and import the connections for installed and import the connections for installed and import the connections for you. you. you. I'll type, "Great. I'll type, "Great. I'll type, "Great. Now find my connections in pgAdmin and Now find my connections in pgAdmin and Now find my connections in pgAdmin and import them here." import them here." import them here." Claude has to do a bit more work to find Claude has to do a bit more work to find Claude has to do a bit more work to find where the pgAdmin connections are stored where the pgAdmin connections are stored where the pgAdmin connections are stored on this computer. on this computer. on this computer. I grant a couple of permissions, and as I grant a couple of permissions, and as I grant a couple of permissions, and as you can see, two servers are found in you can see, two servers are found in you can see, two servers are found in pgAdmin and they appear on the left side pgAdmin and they appear on the left side pgAdmin and they appear on the left side of the screen. of the screen. of the screen. I need to enter my username and password I need to enter my username and password I need to enter my username and password for both. for both. for both. Once that's done, my data sources are Once that's done, my data sources are Once that's done, my data sources are ready to use. ready to use. ready to use. The agent provides some details about The agent provides some details about The agent provides some details about these connections in the chat and they these connections in the chat and they these connections in the chat and they appear in database explorer.

appear in database explorer. appear in database explorer. They aren't automatically introspected, They aren't automatically introspected, They aren't automatically introspected, the agent decided against doing that, the agent decided against doing that, the agent decided against doing that, but I can manually refresh them to see but I can manually refresh them to see but I can manually refresh them to see all the underlying objects. Now let's get to the real work. Because Now let's get to the real work. Because the AI agent knows about your data the AI agent knows about your data the AI agent knows about your data sources, you can essentially talk to sources, you can essentially talk to sources, you can essentially talk to your connections. your connections. your connections. For example, I want to know which schema For example, I want to know which schema For example, I want to know which schema across all of my data sources contains across all of my data sources contains across all of my data sources contains employee data. employee data. employee data. I'll ask, "In which schema do I have I'll ask, "In which schema do I have I'll ask, "In which schema do I have data about employees?" data about employees?" data about employees?" Let's see what Claude says. Let's see what Claude says. Let's see what Claude says. First, it asks to use a skill. First, it asks to use a skill. First, it asks to use a skill. Then some magic happens behind the Then some magic happens behind the Then some magic happens behind the scenes using the MCP DataGrip tools. scenes using the MCP DataGrip tools. scenes using the MCP DataGrip tools. Finally, Claude asks if it's okay to Finally, Claude asks if it's okay to Finally, Claude asks if it's okay to read my database schema. read my database schema. read my database schema. This is DataGrip's built-in security This is DataGrip's built-in security This is DataGrip's built-in security layer in action. layer in action. layer in action. I click allow to give Claude permission. I click allow to give Claude permission. I click allow to give Claude permission. After a bit more thinking, Claude gives After a bit more thinking, Claude gives After a bit more thinking, Claude gives me the answer and even displays the me the answer and even displays the me the answer and even displays the relevant table. relevant table. relevant table. The layout is a bit cramped, so let's The layout is a bit cramped, so let's The layout is a bit cramped, so let's widen the tool window. widen the tool window. widen the tool window. Claude is correct.

Claude is correct. Claude is correct. It tells me that the locally hosted It tells me that the locally hosted It tells me that the locally hosted MySQL data source contains a database MySQL data source contains a database MySQL data source contains a database with employee data. with employee data. with employee data. Let's try another request. Let's try another request. Let's try another request. Okay, cool. Okay, cool. Okay, cool. Now, let's work with the Sakila database Now, let's work with the Sakila database Now, let's work with the Sakila database and the PG AI data source. and the PG AI data source. and the PG AI data source. Tell me about this database. What is it Tell me about this database. What is it Tell me about this database. What is it about? What tables are there and how are about? What tables are there and how are about? What tables are there and how are they connected? they connected? they connected? Once again, Claude asked for permission Once again, Claude asked for permission Once again, Claude asked for permission to read the database schema and I allow to read the database schema and I allow to read the database schema and I allow it. it. it. Next, I see the actual SQL query that Next, I see the actual SQL query that Next, I see the actual SQL query that Claude intends to run. Claude intends to run. Claude intends to run. It warns me that the statement will read It warns me that the statement will read It warns me that the statement will read database data, which is fine. database data, which is fine. database data, which is fine. I review it and click allow. It prompts me again for a second and It prompts me again for a second and then a third query. then a third query. then a third query. At this point, since I'm perfectly fine At this point, since I'm perfectly fine At this point, since I'm perfectly fine with the agent running read-only with the agent running read-only with the agent running read-only queries, queries, queries, I check the box for always allow Claude I check the box for always allow Claude I check the box for always allow Claude code to read database data. This ensures code to read database data. This ensures code to read database data. This ensures I won't be bombarded with these I won't be bombarded with these I won't be bombarded with these confirmation prompts in the future. confirmation prompts in the future. confirmation prompts in the future. Finally, we get our answer. Finally, we get our answer. Finally, we get our answer. Claude provides a wealth of information Claude provides a wealth of information Claude provides a wealth of information about the database, about the database, about the database, where it's hosted, how tables are where it's hosted, how tables are where it's hosted, how tables are connected, and other important details, connected, and other important details, connected, and other important details, giving us a complete overview of the giving us a complete overview of the giving us a complete overview of the schema.

Now, let's move on to the main event, Now, let's move on to the main event, actually querying some data. actually querying some data. actually querying some data. I'll use a new prompt. Give me a query I'll use a new prompt. Give me a query I'll use a new prompt. Give me a query to show actors who played in more than to show actors who played in more than to show actors who played in more than five traumas and never played in five traumas and never played in five traumas and never played in comedies. comedies. comedies. Claude starts thinking, asks to run the Claude starts thinking, asks to run the Claude starts thinking, asks to run the necessary skill, and after a short wait, necessary skill, and after a short wait, necessary skill, and after a short wait, it uses the MCP tools to generate a it uses the MCP tools to generate a it uses the MCP tools to generate a result. result. result. The output is a fairly long SQL query. The output is a fairly long SQL query. The output is a fairly long SQL query. Because I previously allowed Claude to Because I previously allowed Claude to Because I previously allowed Claude to run read queries automatically, it went run read queries automatically, it went run read queries automatically, it went ahead and executed query for me, ahead and executed query for me, ahead and executed query for me, returning exactly one row, Max Mastel, returning exactly one row, Max Mastel, returning exactly one row, Max Mastel, who starred in seven dramas. who starred in seven dramas. who starred in seven dramas. It even provided some helpful comments It even provided some helpful comments It even provided some helpful comments explaining the logic. explaining the logic. explaining the logic. If you prefer to work with this query If you prefer to work with this query If you prefer to work with this query the old-school way, the old-school way, the old-school way, you can easily copy it from the AI chat you can easily copy it from the AI chat you can easily copy it from the AI chat window and paste it into the standard window and paste it into the standard window and paste it into the standard query console to modify or run it query console to modify or run it query console to modify or run it manually. Let's talk a bit more about the agent's Let's talk a bit more about the agent's ability to run SQL automatically. ability to run SQL automatically. ability to run SQL automatically. I mentioned this earlier, but you can I mentioned this earlier, but you can I mentioned this earlier, but you can always manage these permissions by going always manage these permissions by going always manage these permissions by going to IDE settings, to IDE settings, to IDE settings, database, database, database, AI tools.

AI tools. AI tools. Here, you can review and toggle the Here, you can review and toggle the Here, you can review and toggle the permissions granted to the agent. permissions granted to the agent. permissions granted to the agent. For instance, alongside the permission For instance, alongside the permission For instance, alongside the permission to read database data, I can also grant to read database data, I can also grant to read database data, I can also grant permission to read database schemas. permission to read database schemas. permission to read database schemas. This specific permission doesn't allow This specific permission doesn't allow This specific permission doesn't allow access to actual table data. Instead, it access to actual table data. Instead, it access to actual table data. Instead, it allows the agent to retrieve metadata, allows the agent to retrieve metadata, allows the agent to retrieve metadata, such as schema names, object names, and such as schema names, object names, and such as schema names, object names, and the overall structure. Here's another cool example of how AI Here's another cool example of how AI can help you manage databases. can help you manage databases. can help you manage databases. I've been using the schema on the left I've been using the schema on the left I've been using the schema on the left as a sandbox. as a sandbox. as a sandbox. While it contains the standard Sakila While it contains the standard Sakila While it contains the standard Sakila database, I've also added a few random database, I've also added a few random database, I've also added a few random tables here and there. tables here and there. tables here and there. Let's see if the AI can identify which Let's see if the AI can identify which Let's see if the AI can identify which tables don't actually belong to the tables don't actually belong to the tables don't actually belong to the Sakila schema. Sakila schema. Sakila schema. I'll prompt it. I'll prompt it. I'll prompt it. Which tables don't really belong to this Which tables don't really belong to this Which tables don't really belong to this database? database? database? This is a tricky task. The agent quickly This is a tricky task. The agent quickly This is a tricky task. The agent quickly recognizes that the core schema is the recognizes that the core schema is the recognizes that the core schema is the Sakila DVD rental database, and then it Sakila DVD rental database, and then it Sakila DVD rental database, and then it analyzes the rest to find the outliers.

analyzes the rest to find the outliers. analyzes the rest to find the outliers. Eventually, it provides the answer, Eventually, it provides the answer, Eventually, it provides the answer, listing the tables that belong to listing the tables that belong to listing the tables that belong to Sakila, and pointing out the ones that Sakila, and pointing out the ones that Sakila, and pointing out the ones that are out of place, along with its are out of place, along with its are out of place, along with its reasoning. reasoning. reasoning. I'm satisfied with its analysis, so I'll I'm satisfied with its analysis, so I'll I'm satisfied with its analysis, so I'll ask the agent to clean them up. ask the agent to clean them up. ask the agent to clean them up. Okay. Let's drop all these tables. Okay. Let's drop all these tables. Okay. Let's drop all these tables. Notice how the agent performs safety Notice how the agent performs safety Notice how the agent performs safety checks first. It replies, checks first. It replies, checks first. It replies, "Before dropping, let me check whether "Before dropping, let me check whether "Before dropping, let me check whether anything else, like views or foreign anything else, like views or foreign anything else, like views or foreign keys, depends on these tables." keys, depends on these tables." keys, depends on these tables." This is a very logical precaution, so I This is a very logical precaution, so I This is a very logical precaution, so I let it proceed. let it proceed. let it proceed. Finally, Cloud generates the necessary Finally, Cloud generates the necessary Finally, Cloud generates the necessary drop table statements and asks for drop table statements and asks for drop table statements and asks for confirmation to run them. confirmation to run them. confirmation to run them. Since this is exactly what I wanted, I Since this is exactly what I wanted, I Since this is exactly what I wanted, I approve it. approve it. approve it. As you can see in the database explorer As you can see in the database explorer As you can see in the database explorer on the left, all those unnecessary on the left, all those unnecessary on the left, all those unnecessary tables are now gone from the public tables are now gone from the public tables are now gone from the public schema. schema. schema. The AI chat window also provides a The AI chat window also provides a The AI chat window also provides a summary of the actions performed and the summary of the actions performed and the summary of the actions performed and the final outcome. You can also ask the agent to recap past You can also ask the agent to recap past activities.

activities. activities. I'll type this prompt. I'll type this prompt. I'll type this prompt. "Tell me all the queries you ran over "Tell me all the queries you ran over "Tell me all the queries you ran over the last 2 days." After a moment of thought, the agent After a moment of thought, the agent provides a comprehensive list of those provides a comprehensive list of those provides a comprehensive list of those queries and even politely asks for queries and even politely asks for queries and even politely asks for feedback. Let's widen the pane and review the Let's widen the pane and review the response. As expected, the majority of the queries As expected, the majority of the queries it lists are the ones we just executed it lists are the ones we just executed it lists are the ones we just executed for this video. for this video. for this video. Next, we'll cover mentioning database Next, we'll cover mentioning database Next, we'll cover mentioning database objects directly within the agent chat. objects directly within the agent chat. objects directly within the agent chat. Looking at the database explorer, I have Looking at the database explorer, I have Looking at the database explorer, I have several routines. several routines. several routines. Let's say I want to know more about the Let's say I want to know more about the Let's say I want to know more about the get customer balance function. get customer balance function. get customer balance function. I can type, "What does" and then by I can type, "What does" and then by I can type, "What does" and then by typing the at symbol, typing the at symbol, typing the at symbol, I trigger the mention feature. I trigger the mention feature. I trigger the mention feature. I select the at DB object identifier and I select the at DB object identifier and I select the at DB object identifier and code completion kicks in. I just start code completion kicks in. I just start code completion kicks in. I just start typing get C, typing get C, typing get C, select get customer balance from the select get customer balance from the select get customer balance from the suggestions and insert it.

suggestions and insert it. suggestions and insert it. This is the easiest way to explicitly This is the easiest way to explicitly This is the easiest way to explicitly tell the agent exactly which database tell the agent exactly which database tell the agent exactly which database object you're asking about. object you're asking about. object you're asking about. Shortly after, the agent asks if it's Shortly after, the agent asks if it's Shortly after, the agent asks if it's okay to execute the routine to analyze okay to execute the routine to analyze okay to execute the routine to analyze it. Notice that this falls under the it. Notice that this falls under the it. Notice that this falls under the modify data permission because DataGrip modify data permission because DataGrip modify data permission because DataGrip can't guarantee what a stored procedure can't guarantee what a stored procedure can't guarantee what a stored procedure might do internally. Running any routine might do internally. Running any routine might do internally. Running any routine requires modification privileges just to requires modification privileges just to requires modification privileges just to be safe. be safe. be safe. Once approved, we get a comprehensive Once approved, we get a comprehensive Once approved, we get a comprehensive explanation of the routine. I have to admit, it's [clears throat] a I have to admit, it's [clears throat] a bit more complex than I expected. Let's bit more complex than I expected. Let's bit more complex than I expected. Let's widen the pane again to review its widen the pane again to review its widen the pane again to review its findings. First, it outlines the signature, First, it outlines the signature, intent, and what the body of the intent, and what the body of the intent, and what the body of the function actually does. function actually does. function actually does. But it also found something quite But it also found something quite But it also found something quite surprising. surprising. surprising. It detected MySQL specific syntax within It detected MySQL specific syntax within It detected MySQL specific syntax within this PostgreSQL procedure. It explains this PostgreSQL procedure. It explains this PostgreSQL procedure. It explains that this logic was likely inherited that this logic was likely inherited that this logic was likely inherited from the original Sakila database and from the original Sakila database and from the original Sakila database and was never properly converted to a case was never properly converted to a case was never properly converted to a case when statement during the migration to when statement during the migration to when statement during the migration to Postgres. That's a fascinating catch.

Postgres. That's a fascinating catch. Postgres. That's a fascinating catch. The agent also breaks down what the The agent also breaks down what the The agent also breaks down what the function returns and highlights a few function returns and highlights a few function returns and highlights a few other things worth knowing. other things worth knowing. other things worth knowing. Finally, let's look at working with Finally, let's look at working with Finally, let's look at working with files. files. files. In DataGrip, you can use the files tool In DataGrip, you can use the files tool In DataGrip, you can use the files tool window to attach local directories and window to attach local directories and window to attach local directories and navigate your file system directly from navigate your file system directly from navigate your file system directly from the IDE. the IDE. the IDE. For example, I have a folder attached For example, I have a folder attached For example, I have a folder attached that contains some scripts and a CSV that contains some scripts and a CSV that contains some scripts and a CSV file named housing2.csv. file named housing2.csv. file named housing2.csv. This is just a random data set I This is just a random data set I This is just a random data set I downloaded from the internet. I've never downloaded from the internet. I've never downloaded from the internet. I've never looked at it and I have no idea what looked at it and I have no idea what looked at it and I have no idea what data it holds. This is a perfect use data it holds. This is a perfect use data it holds. This is a perfect use case for AI. case for AI. case for AI. Let's ask the agent to identify any data Let's ask the agent to identify any data Let's ask the agent to identify any data quality problems within the file. Just quality problems within the file. Just quality problems within the file. Just like with database objects, you can use like with database objects, you can use like with database objects, you can use the at symbol to mention files. the at symbol to mention files. the at symbol to mention files. I'll type at, I'll type at, I'll type at, search for the file name, and it gets search for the file name, and it gets search for the file name, and it gets linked directly in the chat prompt. linked directly in the chat prompt. linked directly in the chat prompt. I'll send the request and see what I'll send the request and see what I'll send the request and see what issues it uncovers. issues it uncovers. issues it uncovers. I'll heavily speed up the video here I'll heavily speed up the video here I'll heavily speed up the video here because this was a heavy task for the because this was a heavy task for the because this was a heavy task for the agent, but we finally have our answer. agent, but we finally have our answer. agent, but we finally have our answer. It confirms that there are indeed data It confirms that there are indeed data It confirms that there are indeed data problems in housing2.csv.

problems in housing2.csv. problems in housing2.csv. Let's review them. Let's review them. Let's review them. The first issue it highlights relates to The first issue it highlights relates to The first issue it highlights relates to physical lines and line endings. physical lines and line endings. physical lines and line endings. Oh, I like this one. Oh, I like this one. Oh, I like this one. Header names are hostile to SQL. They Header names are hostile to SQL. They Header names are hostile to SQL. They contain spaces and periods, meaning contain spaces and periods, meaning contain spaces and periods, meaning every reference will require double every reference will require double every reference will require double quoting. Good to know. quoting. Good to know. quoting. Good to know. Next, it points out there's no primary Next, it points out there's no primary Next, it points out there's no primary key and no row ID, nothing to join on. key and no row ID, nothing to join on. key and no row ID, nothing to join on. That's easily fixable once we import it That's easily fixable once we import it That's easily fixable once we import it into a database. into a database. into a database. But the semantic issues are even more But the semantic issues are even more But the semantic issues are even more interesting. For instance, it was interesting. For instance, it was interesting. For instance, it was noticed that four rows have more noticed that four rows have more noticed that four rows have more bedrooms than rooms, logically bedrooms than rooms, logically bedrooms than rooms, logically impossible. impossible. impossible. It also spotted mixed granularity across It also spotted mixed granularity across It also spotted mixed granularity across rows. rows. rows. We won't deep dive into the entire We won't deep dive into the entire We won't deep dive into the entire report, but you get the idea. AI agents report, but you get the idea. AI agents report, but you get the idea. AI agents can process and analyze a tremendous can process and analyze a tremendous can process and analyze a tremendous amount of information, and DataGrip amount of information, and DataGrip amount of information, and DataGrip seamlessly integrates these capabilities seamlessly integrates these capabilities seamlessly integrates these capabilities directly into your database workflow. directly into your database workflow. directly into your database workflow. Whether you're untangling complex joins, Whether you're untangling complex joins, Whether you're untangling complex joins, optimizing slow queries, or just optimizing slow queries, or just optimizing slow queries, or just exploring a new data set, having an AI exploring a new data set, having an AI exploring a new data set, having an AI agent right inside your IDE completely agent right inside your IDE completely agent right inside your IDE completely changes the game.

changes the game. changes the game. If you're already using DataGrip, make If you're already using DataGrip, make If you're already using DataGrip, make sure your IDE is up to date and enable sure your IDE is up to date and enable sure your IDE is up to date and enable the AI Assistant plugin to start the AI Assistant plugin to start the AI Assistant plugin to start experimenting today. All the links you experimenting today. All the links you experimenting today. All the links you need are in the description below. need are in the description below. need are in the description below. Thanks for watching and happy querying.