Building an AI-Powered SQL Analysis Tool

In this guide, we will walk through the process of building an AI-powered application that interacts with a PostgreSQL database using natural language. This application will be capable of generating SQL queries from natural language input, explaining query components in plain English, and creating visual charts to represent query results. Let's dive into the details of setting up and implementing this project.

Project Setup

To get started, we will use the following technology stack:

• Next.js (App Router)
• AI SDK
• OpenAI
• Zod
• Postgres with Vercel Postgres
• shadcn-ui and TailwindCSS for styling
• Recharts for data visualization

Clone the Repository

To focus on the AI functionality, clone the starter repository:

git clone https://github.com/vercel-labs/natural-language-postgres
cd natural-language-postgres
git checkout starter

Install Dependencies

Install the necessary dependencies using pnpm:

pnpm install

Configure Environment Variables

Copy the example environment variables file and add your own values:

cp .env.example .env

Edit the .env file to include your API keys and database connection details.

Database Setup

This project uses the CB Insights' Unicorn Companies dataset. Download and save it as unicorns.csv in your project root. Initialize the database with:

pnpm run seed

Application Structure

The starter repository includes essential components such as:

• Database seed script (lib/seed.ts)
• Basic UI components (components/)
• SQL query function (app/actions.ts)
• Database schema types (lib/types.ts)

Building the Application

Generating SQL Queries

To generate SQL queries from natural language, provide context to the AI model about your database schema and SQL operations. Implement a server action to handle this:

export const generateQuery = async (input: string) => {
  'use server';
  try {
    const result = await generateObject({
      model: openai('gpt-4o'),
      system: `You are a SQL (postgres) expert...`,
      prompt: `Generate the query necessary to retrieve the data the user wants: ${input}`,
      schema: z.object({ query: z.string() }),
    });
    return result.object.query;
  } catch (e) {
    console.error(e);
    throw new Error('Failed to generate query');
  }
};

Updating the Frontend

In app/page.tsx, update the handleSubmit function to call the generateQuery action:

const handleSubmit = async (suggestion?: string) => {
  clearExistingData();
  const question = suggestion ?? inputValue;
  if (question.trim()) {
    setSubmitted(true);
  }
  setLoading(true);
  try {
    const query = await generateQuery(question);
    setActiveQuery(query);
    const companies = await runGeneratedSQLQuery(query);
    setResults(companies);
  } catch (e) {
    toast.error('An error occurred. Please try again.');
  } finally {
    setLoading(false);
  }
};

Explaining SQL Queries

Create a server action to explain SQL queries in plain English:

export const explainQuery = async (input: string, sqlQuery: string) => {
  'use server';
  try {
    const result = await generateObject({
      model: openai('gpt-4o'),
      system: `You are a SQL (postgres) expert...`,
      prompt: `Explain the SQL query you generated...`,
      schema: explanationSchema,
      output: 'array',
    });
    return result.object;
  } catch (e) {
    console.error(e);
    throw new Error('Failed to generate explanation');
  }
};

Visualizing Query Results

Generate a chart configuration based on query results and user intent. Define the schema for the chart configuration:

export const configSchema = z.object({
  type: z.enum(['bar', 'line', 'area', 'pie']),
  xKey: z.string(),
  yKeys: z.array(z.string()),
  colors: z.record(z.string(), z.string()).optional(),
  legend: z.boolean(),
});

Create a server action to generate the chart configuration:

export const generateChartConfig = async (results: Result[], userQuery: string) => {
  'use server';
  try {
    const { object: config } = await generateObject({
      model: openai('gpt-4o'),
      system: 'You are a data visualization expert.',
      prompt: `Given the following data...`,
      schema: configSchema,
    });
    return { config };
  } catch (e) {
    console.error(e);
    throw new Error('Failed to generate chart config');
  }
};

Conclusion

By following this guide, you have built an AI-powered SQL analysis tool that can convert natural language into SQL queries, visualize results, and explain queries in plain English. This application can be extended with additional features and data sources to enhance its capabilities.

Reference: This guide is inspired by the Natural Language Postgres Guide by Vercel.