ad
ad

Use AI to chat with a relational SQL database with natural language!

Science & Technology

Introduction

In this article, we will explore an exciting integration of OpenAI's capabilities to interact with relational databases using natural language, eliminating the need for end-users to write complex SQL queries. This approach is designed to demonstrate how AI can facilitate communication with relational databases, such as Azure SQL, MySQL, or PostgreSQL, by converting user-friendly language into SQL queries.

Introduction

As developers, we often work with relational databases that form the backbone of many applications. However, querying complex datasets typically requires a good understanding of SQL, which can be a barrier for end-users. By harnessing the capabilities of OpenAI and its GPT-4 model, we can create a solution where users can simply type a question in natural language, and the AI will convert it into SQL queries, fetch the results, and provide a summary of its understanding.

Setting Up the Environment

For this project, we created an application connected to an Azure SQL database and an Azure OpenAI service. However, it is crucial to stress that these concepts can also be applied to other relational databases like MySQL or PostgreSQL.

Creating a Database with Sample Data

To ease the development process, the Azure SQL database can be pre-populated with sample data. During the setup, users can select the option to use sample data from the Adventure Works LT database, which includes tables for customers, employees, orders, and products. This sample data makes it easier to demonstrate the capabilities of the application without the burden of manually feeding data into the database.

The App in Action

The app we built allows users to perform various queries with simple natural language prompts. For instance, when a user types "Show me all of the products," the AI processes this input and generates the corresponding SQL query to retrieve all products.

The interesting aspect of this approach is its flexibility; users can use different variations of the same query, such as "Retrieve all of the customers who live in Germany," and the AI will still correctly interpret and execute the request.

Advanced Queries

Beyond simple data retrieval, the AI can also handle more complex queries involving filtering, joins, and calculations. For example, asking "Show the total price for all orders for Hannah" generates a sophisticated SQL query that combines multiple joins and calculations, demonstrating the power of this system.

To further improve the output format, users can specify requests like "round to the nearest dollar and format as currency," resulting in a neatly formatted response.

App Architecture Breakdown

The architecture of the application includes several components:

  • Frontend: The browser interface where users submit their queries.
  • Backend: Running locally, it processes user requests.
  • OpenAI Service: Converts natural language into SQL queries.
  • Database: Executes the SQL queries and returns the results.

With this configuration, users can communicate naturally without needing extensive technical knowledge.

OpenAI and Prompt Engineering

The key to successfully utilizing AI in database querying lies in prompt engineering. We designed a system message for the AI that outlines the schema of the database, guiding it in generating accurate SQL queries. By specifying the table names, columns, and desired output format, we enable the AI to better understand and generate the required queries.

Final Thoughts and Alternatives

While this application demonstrates a powerful alternative to traditional querying methods, it’s essential to consider the limitations of other approaches. For example, some Azure AI search services require predetermined queries, which may not offer the same flexibility as our AI-based solution.

By leveraging AI for natural language queries, we can improve the accessibility of database interactions for users unfamiliar with SQL, broadening the range of who can utilize data effectively.

Conclusion

Through this project, we have explored how AI can be employed to facilitate communication with relational databases using natural language, showcasing the transformative potential of prompt engineering in query generation.

Keywords

FAQ

Q1: What is the main purpose of this integration between AI and relational databases? A1: The primary goal is to allow users to interact with relational databases using natural language, making it easier for those without SQL knowledge to query complex datasets.

Q2: Can this approach work with databases other than Azure SQL? A2: Yes, the concepts can be applied to any relational database such as MySQL or PostgreSQL, as long as the AI model is configured appropriately.

Q3: How does the AI know the database schema? A3: Through prompt engineering, the schema is defined within the system message sent to the AI, guiding it in generating accurate SQL queries based on the provided table and column names.

Q4: What are some limitations of using AI for database querying? A4: Some limitations include variations in SQL dialects depending on the database provider and the possibility of generating incorrect queries if the schema is not accurately defined or communicated to the AI.