Building a SQL Expert Bot: A Step-by-Step Guide with Vercel AI SDK and OpenAI API

OpenAI has brought the revolution in the AI field by creating the ChatGPT and now we can say the actual AI era has started and everyone from individuals to businesses using AI.

OpenAI has even created an API to build custom AI solutions like chatbots, assistants, and more. We can access the APIs using SDKs provided by OpenAI for different programming languages. There are also wrappers built on top of the API to help you build the interface easily.

Vercel has built an AI SDK for constructing the streaming user interface with TypeScript and JavaScript. The best part is that it is open-source and has support for Vercel Edge runtime.

In this article, we will build a SQL Expert ChatBot using the OpenAI API and Vercel AI SDK. We will discuss streaming responses, custom prompts, and much more.

Setup OpenAI Account

First, we will need to create an account on OpenAI and get an API key. You can sign up for a free account on OpenAI's website. Once you're logged in, go to the API keys section in the menu on the left side of the screen. From there, you can create a new API key. I have created one and named it vercel-ai-sdk, but you can choose any name you like.

Okay, now that we have the OpenAI API key, let's move on to the next section where we will talk about the Vercel AI SDK and how to set it up on your computer.

Setup Vercel AI SDK

The Vercel AI SDK is built for OpenAI APIs and includes a range of tools for utilizing OpenAI APIs.

To begin, let's create a Next.js application and install the dependencies ai for the Vercel AI SDK and openai for the OpenAI API client.

pnpm dlx create-next-app ai-sql-expert
cd ai-sql-expert
pnpm install ai openai

Make sure to have the same configuration as shown in the image below

Create a .env file in your project root and add your OpenAI API Key

OPENAI_API_KEY=xxxxxxxxx

Great, now that we have set up the OpenAI and Vercel AI SDKs, let's proceed to create API routes and a user interface for our SQL Expert Bot.

Create API Routes

We will set up an API route to manage user message requests. When a user sends a message, the OpenAI API will process it and send a response back to Next.js.

Create a file named route.ts inside the api/chat folder in your project's src/app directory. Then, add the following code snippet

# app/api/chat/route.ts

import OpenAI from 'openai';
import { OpenAIStream, StreamingTextResponse } from 'ai';

const openai = new OpenAI({
  apiKey: process.env.OPENAI_API_KEY,
});

export const runtime = 'edge';

export async function POST(req: Request) {
  const { messages } = await req.json();

  const response = await openai.chat.completions.create({
    model: 'gpt-3.5-turbo',
    stream: true,
    messages,
  });

  const stream = OpenAIStream(response);
  return new StreamingTextResponse(stream);
}

This section is for setting up the OpenAI instance by providing the API key.

const openai = new OpenAI({
  apiKey: process.env.OPENAI_API_KEY,
});

By adding this line, we are instructing Next.js to utilize edge runtime for processing the API requests.

export const runtime = 'edge';

The POST function extracts the messages field from the JSON payload sent in the request.

It then uses the openai.chat.completions.create method to send these messages to OpenAI's GPT-3.5-turbo model for processing. This method creates completions (responses) based on the provided messages.

The stream parameter is set to true, indicating that the responses should be streamed back to the client in real time.

After receiving the response from OpenAI, the code converts it into a friendly text stream using a function called OpenAIStream.

Finally, the function constructs a response using the StreamingTextResponse class and returns it. This response contains the streamed text generated by OpenAI in response to the user's messages.

export async function POST(req: Request) {
  const { messages } = await req.json();

  const response = await openai.chat.completions.create({
    model: 'gpt-3.5-turbo',
    stream: true,
    messages,
  });

  const stream = OpenAIStream(response);
  return new StreamingTextResponse(stream);
}

Okay, we have created the API route to handle user requests and process messages with the OpenAI API. Next, we will move on to creating the user interface for our SQL Expert Bot.

Create The User Interface

Before adding code for the user interface we will add the contants for initial chat messages which we will be using as custom prompt for our bot to set it's behaviour.

Create constant/chat.constants.ts in the project's src/app directory. Then, add the following code snippet

import { Message } from 'ai/react';

export const INITIAL_MESSAGES: Message[] = [
  {
    id: '',
    role: 'system',
    content: `You are an SQL expert. you can write generate SQL queries for any given problem statement.
        Make sure to return the query with proper formatting and indentation.
        Make sure to return the query with proper explanation and comments.
        If you are not able to solve the problem, you can ask for more details.
        If user is not able to understand the query, you can explain the query in simple words.
        If user is providing wrong prompt, you can ask for correct prompt.
        `,
  },
];

We are providing instructions to the system on how to behave in various scenarios, determining the appropriate responses, and specifying how these responses should be delivered.

Next, add the following code snippet in src/app/pages.tsx.

'use client';

import { useChat } from 'ai/react';
import Markdown from 'react-markdown';
import { INITIAL_MESSAGES } from './constant/chat.constants';

export default function Chat() {
  const { messages, input, handleInputChange, handleSubmit } = useChat({
    initialMessages: INITIAL_MESSAGES,
  });
  return (
    <div>
      <div className="text-center py-8">
        <h2 className="text-center text-2xl font-bold mb-2">SQL Expert</h2>
        <p>
          Welcome to the SQL Expert. You can ask any SQL related questions and
          expert will help you out.
        </p>
      </div>

      <div className="flex flex-col w-full max-w-2xl pb-24 mx-auto stretch gap-4">
        {messages
          .filter((m) => m.role !== 'system')
          .map((m) => (
            <div
              key={m.id}
              className="bg-gray-100 p-4 rounded flex gap-2 flex-col"
            >
              <span className="font-medium">
                {m.role === 'user' ? 'You' : 'Expert'}
              </span>
              <Markdown>{m.content}</Markdown>
            </div>
          ))}

        <form
          className="flex gap-4 fixed bottom-0 w-full mb-8"
          onSubmit={handleSubmit}
        >
          <input
            autoFocus
            className="p-2 border border-gray-300 rounded shadow-xl outline-purple-500 focus:outline-none focus:ring-2 focus:ring-purple-500 flex-grow max-w-xl"
            value={input}
            placeholder="Ask your SQL related question.."
            onChange={handleInputChange}
          />

          <button
            className="border p-2 px-4 rounded shadow-xl border-gray-300 bg-purple-500 text-white"
            type="submit"
          >
            Send
          </button>
        </form>
      </div>
    </div>
  );
}

We will use a client component to display messages and a form for users to send their messages.

'use client';

Since the API will return the response with markdown content, we will utilize react-markdown to parse the markdown content.

import Markdown from 'react-markdown';

The useChat hook helps you create a chat interface for your chatbot app. It makes it simple to show messages from your AI provider, handle chat input, and update the UI when new messages come in. It accepts a bunch of options, and for our case, we are using the initialMessages option to set the system's behavior.

import { useChat } from 'ai/react';

const { messages, input, handleInputChange, handleSubmit } = useChat({
    initialMessages: INITIAL_MESSAGES,
  });

This part is for displaying the messages we receive from the API. We have set a custom prompt for the system, so we will filter it out and only show the user and expert messages.

<div className="flex flex-col w-full max-w-2xl pb-24 mx-auto stretch gap-4">
        {messages
          .filter((m) => m.role !== 'system')
          .map((m) => (
            <div
              key={m.id}
              className="bg-gray-100 p-4 rounded flex gap-2 flex-col"
            >
              <span className="font-medium">
                {m.role === 'user' ? 'You' : 'Expert'}
              </span>
              <Markdown>{m.content}</Markdown>
            </div>
          ))}
...
</div>

Finally, we have the form for users to send their messages. We are using the handleSubmit function provided by the useChat hook to listen for the form submit event.

For the input field, we are using handleInputChange and input to control the input value.

Then, we have a button for submitting the form.

<div className="flex flex-col w-full max-w-2xl pb-24 mx-auto stretch gap-4">
        ...
        <form
          className="flex gap-4 fixed bottom-0 w-full mb-8"
          onSubmit={handleSubmit}
        >
          <input
            autoFocus
            className="p-2 border border-gray-300 rounded shadow-xl outline-purple-500 focus:outline-none focus:ring-2 focus:ring-purple-500 flex-grow max-w-xl"
            value={input}
            placeholder="Ask your SQL related question.."
            onChange={handleInputChange}
          />

          <button
            className="border p-2 px-4 rounded shadow-xl border-gray-300 bg-purple-500 text-white"
            type="submit"
          >
            Send
          </button>
        </form>
</div>

Thanks to Vercel AI SDK for providing a hook like useChat that takes care of everything from displaying messages to managing user input.

Great! We have built the user interface for our SQL Expert bot. Now, in the next section, we will start the development server and test it out.

Test The Bot

To start the development server, run the following command in your terminal

pnpm run dev

OR

yarn run dev

OR

npm run dev

Visit http://localhost:3000 in your browser to view the application.

You can try out the bot with the provided sample prompts below

1. "Create a SQL query to find the total number of orders placed by each customer in the past year, ordered by the highest number of orders."

2. "Write a SQL query to identify customers who have spent more than $1000 in total on purchases within the last 3 months."

3. "Design a SQL query to calculate the average time it takes for an order to be fulfilled from the moment it's placed, considering the timestamps of order placement and fulfillment."

4. "Develop a SQL query to list the products with the highest profit margin, considering both the cost price and the selling price."

5. "Construct a SQL query to find the monthly revenue trend for the past year, broken down by month."

6. "Create a SQL query to identify customers who have not made a purchase in the last 6 months."

7. "Write a SQL query to find the top 5 categories with the highest number of products sold in the last quarter."

8. "Design a SQL query to identify orders with items that are out of stock at the time of purchase."

9. "Develop a SQL query to calculate the total revenue generated from repeat customers versus new customers in the last month."

10. "Construct a SQL query to identify any anomalies or irregularities in order quantities compared to historical averages for each product."

Let's see the demo with the prompt provided below

Imagine you're tasked with optimizing the database performance of a large e-commerce platform. Write a SQL query to identify the top 10 selling products by revenue over the past month, considering both quantity sold and unit price.

Conclusion

Awesome! Now that we have a functioning SQL Expert Bot, feel free to experiment with it and customize it to suit your requirements. That's all for this topic. Thank you for reading! If you found this article helpful, please consider liking, commenting, and sharing it with others.

Resources

• SQL Expert GitHub Repository

• Vercel AI SDK Docs

• useChat Hook Docs