AI-Ready Database Tool

AI is a great productivity booster for IT projects, and working with databases is one area where AI is really making inroads for improving efficiency. By leveraging AI in database tools, DBAs and database developers of any skill level can save time and effort with AI-generated SQL scripts and sample data, for instance, as well as query optimization and troubleshooting.

Altova offers an integrated AI Assistant in DatabaseSpy to help with SQL script creation, data modeling, SQL and error explanations, and even SQL pretty-printing. This makes the multi-database tool, which supports all major databases in a single UI, even more useful.

Let’s take a look at how it works.

SQL Editor with AI Assistant

It’s easy to enable AI functionality in the DatabaseSpy SQL editor using your current OpenAI key, which give you access to several versions of ChatGPT. This includes the most recent iteration, which is the GPT-4 Turbo preview announced November 6, 2023. Newer models will automatically be accessible in DatabaseSpy when they are released by OpenAI.

Database AI Assistant

Once enabled, there are multiple ways to use the AI Assistant in DatabaseSpy:

  • AI Chat Dialog
  • AI Extensions

AI Chat Dialog

The AI Chat Dialog allows you to send prompts to ChatGPT for a natural language SQL interface that can create SQL statements, generate sample data for a given table structure, create relations between tables, and so on. Once you send a request and receive the response, you can copy and paste the returned SQL into an existing file or create a new SQL file with the response.

In this example, we use the AI Chat Dialog to generate a sample sabermetrics (data for analyzing baseball stats) database. We’ll specify tables and columns to include and then finally ask AI to generate some sample records using real world data.

Natural language prompt to generate SQL in DatabaseSpy

The AI Assistant returns the required SQL script for creating the database and then inserting the sample records.

Text to SQL using AI in DatabaseSpy

Next, we can use the New SQL button at the top of the AI Assistant window to create a SQL file with the AI response, review it, make any necessary changes, and then execute it. Let’s have DatabaseSpy generate the tables:

Executing AI-generated SQL scripts to create tables

And then insert the sample data.

Inserting AI-generated data in a SQL database

We can continue to refine the structure and add more data, but the AI Assistant has given us a big jumpstart on creating this database without having to write a line of SQL.

Next, let’s call on AI to query some data in the database we just created.

AI prompt for generating SQL query

AI returns the corresponding query, and we can execute it in the SQL editor to view the results.

viewing SQL query results

AI Extensions

In addition to the AI Chat Dialog for generate new queries, the AI Assistant is available when you’re working on an existing file the DatabaseSpy SQL Editor. Here, we can utilize AI Extensions including:

  • AI-Complete: will send a request to complete a SQL statement
  • AI-Explain: requests a textual explanation of a statement
  • AI-Explain Error: request details about an error and possibilities for solving it
  • AI-Pretty Print: formats the current statement in a more readable format

Let’s take advantage of the AI-Explain extension to look at a query that somebody else has written for our sample database and ask the AI to explain it to us:

Explaining a SQL query using ChatGPT

And the response from AI is:

Using ChatGPT in DatabaseSpy to explain a SQL query

Now, in just a few seconds, we have an explanation of a SQL query, which can be really helpful when we are faced with even more complex queries in real life.

Try AI for SQL and Database Tasks

By incorporating these AI-ready features, DatabaseSpy becomes a more powerful and user-friendly platform, boosting productivity for both novice and experienced users. Using the integrated AI Assistant to automate and optimize various tasks frees them to focus on more complex aspects of database management and development.

Try the DatabaseSpy AI Assistant today with a fully functional free trial of the software.  

Tags: , , ,