AI-Powered SQL Query Data Extractor

What We’ll Do Here:

How Does the App Work?

How to Set Up Your Tools

1. Install Next.js and dependencies:

npx create-next-app@latest my-next-app

cd my-next-app

npm run dev

2. Install CopilotKit and dependencies

npm install @copilotkit/react-ui @copilotkit/react-core dotenv axios

3. Set Up the Database

4. Set Up the LLM for Action:

Structure and Features of the App

How to Build the Back End

NEXT_PUBLIC_COPILOTKIT_BACKEND_URL=http://localhost:3000/api/copilotkit
NEXT_PUBLIC_GROQ_CLOUD_API_KEY=
NEXT_PUBLIC_RESTDB_API_KEY=
NEXT_PUBLIC_RESTDB_BASE_URL=https://demosql-fdcb.restdb.io/rest/demo-data

How to Configure the CopilotKit Back End

import {
  CopilotRuntime,
  GroqAdapter,
  copilotRuntimeNextJSAppRouterEndpoint,
} from "@copilotkit/runtime";

import Groq from "groq-sdk";

const groq = new Groq({ apiKey: process.env.NEXT_PUBLIC_GROQ_CLOUD_API_KEY });
console.log(process.env.NEXT_PUBLIC_GROQ_CLOUD_API_KEY);
const copilotKit = new CopilotRuntime();

const serviceAdapter = new GroqAdapter({
  groq,
  model: "llama-3.1-70b-versatile",
});

export const POST = async (req) => {
  const { handleRequest } = copilotRuntimeNextJSAppRouterEndpoint({
    runtime: copilotKit,
    serviceAdapter,
    endpoint: "/api/copilotkit",
  });

  return handleRequest(req);
};

import {
  CopilotRuntime,
  GroqAdapter,
  copilotRuntimeNextJSAppRouterEndpoint,
} from "@copilotkit/runtime";

import Groq from "groq-sdk";

const groq = new Groq({ apiKey: process.env.NEXT_PUBLIC_GROQ_CLOUD_API_KEY });
console.log(process.env.NEXT_PUBLIC_GROQ_CLOUD_API_KEY);

const copilotKit = new CopilotRuntime();

const serviceAdapter = new GroqAdapter({
  groq,
  model: "llama-3.1-70b-versatile",
});

export const POST = async (req) => {
  const { handleRequest } = copilotRuntimeNextJSAppRouterEndpoint({
    runtime: copilotKit,
    serviceAdapter,
    endpoint: "/api/copilotkit",
  });

  return handleRequest(req);
};

How to Build the Front End

How to Install ShadCN in a Next Project

Which style would you like to use? › New YorkWhich 
color would you like to use as base color? › Zinc
Do you want to use CSS variables for colors? › no / yesadd components

npx shadcn@latest add <component-name>

import {
  Table,
  TableBody,
  TableCaption,
  TableCell,
  TableFooter,
  TableHead,
  TableHeader,
  TableRow,
} from "@/components/ui/table";

export function Tabledata({ data }) {
  return (
    <Table className="text-center">
      <TableCaption className="text-sm text-green-600 font-bold ml-8">
        Live data from database.
      </TableCaption>
      <TableHeader>
        <TableRow className="text-center ">
          <TableHead>Id</TableHead>
          <TableHead>name</TableHead>
          <TableHead>email</TableHead>
          <TableHead>phone_number</TableHead>
          <TableHead>address</TableHead>
          <TableHead>city</TableHead>
          <TableHead>state</TableHead>
          <TableHead>zip_code</TableHead>
          <TableHead>country</TableHead>
          <TableHead className="text-right">created at </TableHead>
        </TableRow>
      </TableHeader>
      <TableBody>
        {data.map((db) => (
          <TableRow key={db._id}>
            <TableCell className="font-medium text-wrap w-12">
              {db._id}
            </TableCell>
            <TableCell className="font-medium">{db.name}</TableCell>
            <TableCell>{db.email}</TableCell>
            <TableCell>{db.phone_number}</TableCell>
            <TableCell className="text-right">{db.address}</TableCell>
            <TableCell className="text-right">{db.city}</TableCell>{" "}
            <TableCell className="text-right">{db.state}</TableCell>
            <TableCell className="text-right">{db.zip_code}</TableCell>{" "}
            <TableCell className="text-right">{db.country}</TableCell>
            <TableCell className="text-right">{db.created_at}</TableCell>
          </TableRow>
        ))}
      </TableBody>
    </Table>
  );
}

"use client";
import React, { useState, useEffect } from "react";
import { useCopilotReadable, useCopilotAction } from "@copilotkit/react-core";
import axios from "axios";
import { Tabledata } from "./Tabledata";

function NLQueryForm() {
  const [nlQuery, setNlQuery] = useState("");
  const [data, setData] = useState([]);
  console.log("🚀 ~ NLQueryForm ~ data:", data);
  const [error, setError] = useState("");
  const [loading, setLoading] = useState(true);

  const API_KEY = process.env.NEXT_PUBLIC_RESTDB_API_KEY;
  const BASE_URL = process.env.NEXT_PUBLIC_RESTDB_BASE_URL;
  console.table({ API_KEY, BASE_URL });
  useEffect(() => {
    async function fetchData() {
      if (!API_KEY || !BASE_URL) {
        setError("API configuration is missing");
        setLoading(false);
        return;
      }
      try {
        const response = await axios.get(BASE_URL, {
          headers: {
            "x-apikey": API_KEY,
            "Content-Type": "application/json",
          },
        });
        setData(response.data);
        setLoading(false);
      } catch (error) {
        console.error("Error fetching data:", error);
        setError(
          error instanceof Error ? error.message : "An unknown error occurred"
        );
        setLoading(false);
      }
    }
    fetchData();
  }, [API_KEY, BASE_URL]);

  useCopilotReadable({
    description: "Query database with detailed information",
    value: JSON.stringify(data.slice(0, 25)),
  });
  useCopilotAction({
    name: "fetchData",
    description: "Search and filter data based on natural language query",
    parameters: [
      {
        name: "nlQuery",
        type: "string",
        description: "Natural language search term for database",
        required: true,
      },
    ],

    handler: async ({ data }) => {
      setNlQuery(data);
      return JSON.stringify(data);
    },
  });

  if (loading) return <div>Loading...</div>;

  return (
    <div>
      {error && <p style={{ color: "red" }}>{error}</p>}
      <div>
        <p className="text-sm text-green-600 font-bold text-center">
          Live data from database.
        </p>
        <p className="text-sm text-green-600 font-bold text-center">
          Total Records: {data.length}
        </p>
        <Tabledata data={data} />
      </div>
    </div>
  );
}
export default NLQueryForm;

Configuring CopilotKit for the Whole App

import "./globals.css";
import { CopilotKit } from "@copilotkit/react-core";
import "@copilotkit/react-ui/styles.css";
export const metadata = {
  title: "Create Next App",
  description: "Generated by create next app",
};

export default function RootLayout({ children }) {
  return (
    <html lang="en">
      <body>
        <CopilotKit runtimeUrl="/api/copilotkit">{children}</CopilotKit>
      </body>
    </html>
  );

Playing with the Database