detailed guide to using Vercel AI SDK and OpenAI API

OpenAI laid the foundation for the artificial intelligence revolution with the advent of ChatGPTopening a new era in the field A.I., which is actively used by both individuals and business communities. OpenAI has even provided an API for developing personalized AI solutions, including chatbots, virtual assistants, and more. This API can be accessed through SDKs that OpenAI has released for various programming languages, as well as through wrappers designed to make it easy to create interfaces.

Vercel contributed by developing an AI SDK for creating interactive user interfaces using TypeScript and JavaScript. Notably, this SDK is an open source project and supports Vercel Edge runtime.

In this article, we will create a SQL Expert ChatBot using the OpenAI API and Vercel SDK. We'll look at streaming responses, query settings, and much more.

Getting an API key

The first step is to create an account on OpenAI and obtain an API key. First we go through registration and after we have logged into our account, we need to go to the management section API-ключами. Here we can generate a new key and give it a name, in my case – vercel-ai-sdkbut you can choose a different name at your discretion.

I suggest you don’t hesitate and move on to the review of Vercel AI SDK and the setup process on the device.

Vercel AI SDK Configuration

Vercel AI SDK is designed to interact with the OpenAI API and has a number of tools for effectively using the OpenAI API functionality.

To get started, we create an application based on Next.js and connect the necessary dependencies: package ai for Vercel AI SDK and package openai for OpenAI API client:

pnpm dlx create-next-app ai-sql-expert

cd ai-sql-expert

pnpm install ai openai

Make sure your project configuration is as follows:

Create a file .env in the root directory of the project and add the previously generated OpenAI API key to it:

OPENAI_API_KEY=xxxxxxxxx

This completes the setup of the SDK OpenAI and Vercel AI, which means we can move on to setting up API routes.

Setting up API routes

We organize an API route for processing messages from users in such a way that after the user sends a message, the OpenAI API processes the request and returns a response to Next.js.

First we create a file route.ts in folder api/chatfrom directory src/app and write the following code in it:

# 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);

}

A few clarifications:

1. First we provide an API key to set up an OpenAI instance:

   const openai = new OpenAI({

     apiKey: process.env.OPENAI_API_KEY,

   });

2. With this line we tell Next.js to use the edge runtime to handle API requests:

   export const runtime="edge";

3. To extract data from the messages field, which comes in the body of the JSON request, we use POST -function.

4. Next we use the method openai.chat.completions.create to send these messages to the model gpt-3.5-turbo for further processing. This method generates responses based on the messages entered.

5. Parameter stream we set to value trueas this allows responses to be transmitted in real time.

6. Having received a response from OpenAI, the code transforms it into a convenient text stream format using the function OpenAIStream.

7. Finally, the function generates a response, including text streams created by OpenAI in response to user requests, through the class StreamingTextResponse and sends it back:

   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);

   }

We now have an API route to accept and process user requests using the OpenAI API. Let's move on to creating the UI for our bot.

User Interface Development

Before we start working with the UI, let's define constants for the initial chat messages, which we will use as a custom command to direct the behavior of the bot being developed.

Create a file chat.constants.ts in folder constantlocated in the directory src/app and write the following code:

import { Message } from 'ai/react';

export const INITIAL_MESSAGES: Message[] = [

  {

    id: '',

    role: 'system',

    content: 'Вы эксперт по SQL и можете создать генерируемые SQL-запросы для любой проблемы.

        Убедитесь, что возвращаемый вами запрос с правильным форматированием и отступом.

        Убедитесь, что возвращаемый запрос снабжен корректным объяснением и комментариями.

        Если вы не в силах решить проблему, вы вправе запросить дополнительные сведения.

        Если пользователь не может понять запрос, вы можете объяснить запрос простыми словами.

        Если пользователь предоставляет неправильную подсказку, вы можете запросить правильную подсказку.

        `,

  },

];

We give the system guidelines for behavior in various situations, formulate appropriate response models and determine the mechanism for their delivery.

Next, insert the following code into 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</h2>

        <p>

          Добро пожаловать в Эксперта SQL. Вы можете задать любые вопросы, связанные с SQL, и эксперт вам поможет.

        </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' ? 'Вы' : 'Эксперт'}

              </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="Задайте свой вопрос по SQL.."

            onChange={handleInputChange}

          />

          <button

            className="border p-2 px-4 rounded shadow-xl border-gray-300 bg-purple-500 text-white"

            type="submit"

          >

            Отправить

          </button>

        </form>

      </div>

    </div>

  );

}

Let's add a little explanation:

1. We use a component to visualize messages and a form to send messages by the user:

   'use client';

2. Since the API returns responses in markdown format, we use the library react-markdown for their interpretation.

   import Markdown from 'react-markdown';

3. Hook useChat is designed to create a chat interface in our application with chatbot integration. It facilitates the process of displaying messages from our AI provider, managing chat input, and updating the UI when new messages arrive. The hook also supports various options, and in our case we use the option initialMessages to configure the initial behavior of the system.

   import { useChat } from 'ai/react';

   const { messages, input, handleInputChange, handleSubmit } = useChat({

       initialMessages: INITIAL_MESSAGES,

     });

4. We also configure the display of messages coming from the API. Considering that we have given the system a custom message, we filter it out and leave only 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' ? 'Вы' : 'Эксперт'}

                 </span>

                 <Markdown>{m.content}</Markdown>

               </div>

             ))}

   ...

   </div>

5. We are implementing a form for users to send messages. To handle the form submission event, use the function handleSubmitwhich the hook provides useChat.

6. As a mechanism for controlling data entry into a text field, we use the function handleInputChange in combination with the condition input.

7. In addition, we equip the form with a button for submitting it:

   <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="Задайте свой вопрос по SQL.."

               onChange={handleInputChange}

             />

             <button

               className="border p-2 px-4 rounded shadow-xl border-gray-300 bg-purple-500 text-white"

               type="submit"

             >

               Отправить

             </button>

           </form>

   </div>

Thanks to the Vercel AI SDK, we have tools like the useChat hook that make everything from displaying messages to managing user input easier.

We have successfully developed the UI for our SQL Expert bot, which means it's time to launch the development server and conduct testing.

Bot test

Let's start the server with the following command in the terminal:

pnpm run dev

ИЛИ

yarn run dev

ИЛИ

npm run dev

Let's go to the address http://localhost:3000 in our browser.

You can try out the bot using the following examples of prompts:

1. “Formulate an SQL query to identify the total number of orders made by each client over the past year, sorted in descending order by the number of orders.”

2. “Write an SQL query to identify customers whose expenses exceeded $1000 in the last three months.”

3. “Develop a SQL query to determine the average order fulfillment time based on the time stamps of its placement and completion..”

4. “Design an SQL query to identify the highest profitability products based on their cost and selling price.”

5. “Construct an SQL query to analyze the trend of monthly income over the past year, broken down by month.”

6. “Using an SQL query, identify customers who have not made purchases in the last 6 months.”

7. “Create an SQL query to identify the five product categories with the highest sales volumes in the last quarter.”

8. “Develop a SQL query to identify orders that include items that are out of stock at the time of purchase.”

9. “Using an SQL query, calculate the total revenue from regular customers versus new customers for the last month.”

10. “Formulate an SQL query to identify anomalies or deviations in the number of orders compared to the average of each product over certain historical periods.”

For clarity, take a look at demonstration works of the following promt:

"Представим, твоей задачей стоит оптимизация производительности базы данных крупной компании в сфере электронной коммерции. Составь SQL-запрос для выявления десяти наиболее продаваемых товаров по доходу за последний месяц, учитывая количество продаж и цену за единицу."

Conclusion

In this guide, we have written a functioning SQL Expert Bot. Try customizing it according to your preferences, experiment and share your results!

Useful links:

Thank you for attention!