SQL to AI: Build Your First Smart Assistant

SQL to AI: Build Your First Smart Assistant

Tony Fadell, in his book "Build," emphasizes that the first step in creating a product is to chase your curiosity rather than the latest business trends. He argues that unflinching curiosity, not market hype, is the most powerful motivator for meaningful invention. It's not about whether your product is ultimately a success or failure, but about your own genuine belief and passion for your ideas. While AI might be the current buzzword, and tomorrow it might be robotics or crypto, don't feel pressured to build things you have no personal interest in. Follow your own unique spark, and you'll be more likely to create something truly worthwhile.

With AI, our curiosity may evolve, leading us to discover new interests. I have a profound appreciation for AI, prompting me to contemplate how I can integrate it into both my professional work and daily life. Initially, I began incorporating AI into my coding experiences by seeking assistance when I encountered challenges or enhancing my coding proficiency through clean coding practices and performance optimizations.

Nevertheless, I came to the realization that this approach may not fully leverage AI's potential in a meaningful way. As a programmer who regularly uses code editors like Rider, I have benefited from the abundance of hints and programming assistance they provide during code composition. Motivated to explore the capabilities of AI further, I decided to integrate it more extensively into my coding endeavors.

Upon conducting thorough research, I discovered several AI tools tailored to enhance my coding skills, such as Phind and Codium. These tools have proven invaluable in my quest to become a more proficient coder.

If you're a programmer in a startup, there are instances where you find yourself having to write SQL queries for reporting or data analysis. However, when you encounter unfamiliar tables or struggle to understand the data structure, it can be a time-consuming and challenging process. This is particularly true when you need specific data for a report that you generate infrequently, perhaps on a weekly or monthly basis. In such cases, you either need to familiarize yourself with the tables or seek assistance from someone who has the requisite knowledge.

In this scenario, AI can prove to be a valuable ally in navigating these challenges. Imagine creating a custom SQL generator powered by AI to assist you in crafting effective SQL queries for reporting or data analysis. Once you've built this AI-powered SQL Generator, you can leverage it whenever needed, streamlining your workflow. Additionally, you can share this tool with your teammates, collectively resolving the problem at hand.

As the saying goes, "Build once, sell twice." but in our context, we can rephrase it as "Build once, use a thousand times." This encapsulates the idea that creating a robust solution like an AI-powered SQL Generator can lead to repeated efficiency gains and ease of use, making it a valuable asset for your team.

Let's begin. When the topic of AI arises, OpenAI is often the first thought that comes to mind. However, due to its paid nature, we opt for Gemini, a platform developed by Google AI. To kickstart the process of building your custom AI-powered tool, navigate to the Build with the Gemini API | Google AI for Developers link to obtain your API Key through Google Studio. Upon clicking, you'll be directed to the Google AI dashboard, resembling the snapshot below:

You can obtain a key from this section, making you ready to create your first AI generator. To initiate the process, click on the "Create New" button. Here, you will be presented with three options that you can select.

Select the 'Chat prompt' option since we aim to create a conversation bot capable of generating an ask-and-answer structure. Click on "Chat prompt" and you will see a screen resembling the one below:

On the left side, you'll define the SQL Generator. Type your text in the "User's input" area.

Before starting, it's helpful to have some knowledge of prompt engineering. While it's a deep topic, you can learn the basics at this excellent guide: promptingguide.ai It covers how to craft effective prompts and teach your AI to generate the desired responses.

For this example, we'll create a basic SQL Generator with minimal description. Remember, you can always enhance its performance later by fine-tuning the AI. But first, let's clearly define its purpose and capabilities.

Here's a sample prompt to get you started:

Your name is Super AI. Don't forget to introduce yourself. You are custom SQL generator that provide perfect queries. We use this queries for analyze or report.

Next, add prompts to restrict the AI's responses to specific tables, columns, relationships, and the database management system (DBMS). This ensures it only generates SQL queries within those defined parameters.

Please restrict your SQL queries to the Customer, Product, or Sale tables. You can only respond to questions related to these tables. If your query involves other tables, you won't be able to provide a response.

In this scenario, I use three tables: Customer, Product, and Sale. Additionally, apply restrictions based on your Database Management System (DBMS). Different databases may have unique syntax. For example, PostgreSQL uses the "LIMIT" syntax to constrain the amount of data retrieved from a table, while SQL Server employs "TOP" for the same purpose. Define your DBMS restriction as follows:

Your database management system is PostgreSQL. Do not generate any SQL for other DBMS.'

So, is our tool ready to generate custom SQL? Not quite yet.

Before it can effectively create accurate queries, we need to teach the AI about the table structure within the database. If we don't provide this information, it will be forced to guess column names on its own, which can lead to errors and inconsistencies.

To address this, we'll provide the table structure in JSON format, as AI models can understand and process JSON data efficiently.

Here's an example prompt to introduce the table structure:

These tables are structured as follows:
  "tables": [
      "name": "Customer",
      "columns": [
        {"name": "customer_id", "type": "serial", "primary_key": true},
        {"name": "name", "type": "varchar(255)"},
        {"name": "email", "type": "varchar(255)"},
        {"name": "phone_number", "type": "varchar(20)"}
      "name": "Product",
      "columns": [
        {"name": "product_id", "type": "serial", "primary_key": true},
        {"name": "name", "type": "varchar(255)"},
        {"name": "price", "type": "numeric(10,2)"}
      "name": "Sale",
      "columns": [
        {"name": "sale_id", "type": "serial", "primary_key": true},
        {"name": "customer_id", "type": "integer", "foreign_key": {"table": "Customer", "column": "customer_id"}},
        {"name": "product_id", "type": "integer", "foreign_key": {"table": "Product", "column": "product_id"}},
        {"name": "quantity", "type": "integer"},
        {"name": "total_amount", "type": "numeric(10,2)"},
        {"name": "sale_date", "type": "date"}

With this structure in place, you're almost ready to harness the AI for generating custom SQL queries. However, before we dive in, let's provide some final suggestions to ensure the highest quality responses. Remember, even though this tool is powered by AI, it might not always meet your exact expectations.

Here are some last tips to enhance the AI's accuracy:

I expect very accurate predictions. Please prioritize short and clear answers.

Now, we're ready to test our AI's ability to generate SQL queries, create views, and more! The screenshot below highlights additional options for customizing your prompt to fine-tune creativity or accuracy. To delve deeper into these settings, I highly recommend exploring this excellent guide: promptingguide.ai

To put this into practice, feel free to copy and paste my provided prompts into the "User Input" area on your screen. Once you've finished prompting, it's time to test the AI's capabilities! Navigate to the right side of the interface and start asking your questions to generate SQL queries.

To give you a glimpse of its potential, I'll share some sample questions and answers below:

To demonstrate how the AI handles queries related to tables outside its defined structure, here's an example response:

Congratulations! You've successfully crafted your first AI-powered SQL generator, capable of creating custom queries from your defined table structure.

To seamlessly integrate this AI into your existing codebase, Google AI Studio offers the ability to export the code. This provides a powerful way to leverage its capabilities within your own projects.

While this example showcases a basic AI prompt tool, your specific needs will likely differ. That's where the beauty of customization lies! You can tailor this tool to fit your requirements perfectly, enhancing its capabilities and refining its output.

As some say, "AI will change our lives completely" and they're not wrong. As our world evolves, embracing innovative tools like this one becomes crucial to thrive in the new landscape. Thank you for reading!