Use AI to chat with a SQL Database 2.0 - powerful new features!

Introduction

Welcome back to the Code Wolf! I’m thrilled to announce the release of the follow-up to our most popular video. Even if you haven't seen that video, you can still understand this one without a hitch.

A few months ago, I created a tutorial demonstrating how to leverage AI and natural language to interact with relational SQL databases, without relying on search services or indexing data. In this approach, the AI doesn’t access the database data directly; rather, it uses the database schema to generate SQL queries in response to your questions and commands. You then execute these queries against your database.

After receiving considerable feedback and inquiries, I continued to enhance the application, and today I’m excited to unveil version 2.0. In this article, we'll explore the new user interface and a plethora of features such as:

• Auto schema extraction
• Multi-database support
• Query history and favorites
• Data grid views
• SQL query editing and more

Moreover, I’ve made the source code available on GitHub, which you can find in the description below. Towards the end, I’ll address some common questions and hurdles encountered with this tool, as well as a potential version 3.0.

Before diving in, I’d like to remind you to subscribe or use the super thanks feature to support the app and the channel. We're on a mission to hit the 5,000 subscriber milestone by the end of 2024, and there's more AI coding and cloud content on the horizon!

Setting Up

When you first launch the application, you’ll be prompted to connect to a database. Head to the connections page to set this up. The good news with version 2.0 is that you can supply a connection string directly to the app, which will automatically load your database schema. This eliminates the prior cumbersome manual extraction process.

Let’s set up a connection using the Northwind sample database, which you can easily find online. I’ll enter my localhost instance of SQL Server, click generate schema, and voilà! The various tables and their corresponding columns emerge, allowing for easy verification.

I’ll add another connection with the Stack Overflow database. Simply replacing Northwind, I’ll generate the schema once again, which presents a rich database structure complete with badges, posts, users, and votes.

Exploring the Dashboard

Once back on the dashboard, you’ll notice multiple components at your disposal. Selecting a database allows for effective querying. The presence of tabs for schema, history, and favorites aids in navigating through your queries more efficiently.

Let’s run a simple query: listing 50 products. Hitting submit generates a well-formatted data grid view. The application employs MudBlazor and Blazor server for this neat UI, complete with paging options to manage large result sets effectively.

Additionally, users can save their commonly used queries in a favorites section, ensuring quick access in the future. However, please note that this feature currently resets when the app restarts.

Querying and Insights

Another significant addition is the SQL editor tab, where queries can be manually edited. This empowers users who are more experienced with SQL to tweak the query before execution.

After running queries, an insights tab offers a glimpse of the AI’s thought process, thus allowing users to adjust their queries for better results.

For instance, if you were to ask, “What is the most expensive product?” the application will fetch the right data, demonstrating the app's ability to handle more complex queries involving joins and nested queries, capability critical for any relational database interface.

App Architecture

The app architecture is structured to optimize user experience and performance. At its core, it uses MudBlazor for UI components. Key elements in managing displays include the contact with the OpenAI service, which is where the schema is sent, and SQL queries are formulated in response.

In the OpenAI service portion of the code, make sure to integrate your unique OpenAI service endpoint key and deployment name to get started seamlessly.

Common Issues and Future Features

As with all evolving tools, common feedback arises—one being challenges with more complex databases. The schema's structure is crucial; if the tables and columns are ambiguously named or overly complex, the AI struggles to execute queries accurately.

There’s also been interest in adding a real-time conversational interface with the AI. This is a complex feature I'm still exploring due to the difficulty in maintaining conversational context.

On questions of caching the schema to save on token usage, I have not yet found an effective solution, but switching to models that accept larger token inputs may help mitigate this.

Lastly, while utilizing search or indexing services to connect AI to actual data is an option, it often results in less tailored responses compared to the schema-based approach we are currently employing.

Conclusion

That wraps up our overview of the new version of the app. I hope you'll experiment with it and share your thoughts. Your support through subscriptions helps us keep creating content and tools like this!

Keyword

• AI
• SQL Database
• Schema Extraction
• Multi-database support
• Query History
• Data Grid View
• SQL Query Editing

FAQ

Q: What is the main functionality of the app?
A: The app generates SQL queries based on user prompts by utilizing the database schema.

Q: Can it handle complex databases?
A: It can, but the schema must be well-structured with logical naming conventions to ensure clarity for the AI.

Q: Is there a way to maintain conversational context with the AI?
A: Currently, this feature is being explored but remains a challenge due to complexity in context management.

Q: How do I change the connection settings for OpenAI?
A: You’ll need to enter your OpenAI service endpoint key and deployment name in the configuration settings of the app.

Q: Can I save my favorite queries?
A: Yes, there is a favorites feature to save commonly used queries, but please note that it resets when the app restarts.