Connect a database query tool to Vercel AI SDK

The Vercel AI SDK database query tool pattern lets language models execute structured SQL against your data while keeping full control over schema access, query validation, and result formatting. This tutorial walks through building a typed, streaming-enabled tool that exposes a read-only SQLite database to an LLM, using the AI SDK’s tool function and Next.js App Router. You’ll end up with a working chat interface that can answer questions like “Which customers spent over $500 last month?” by generating and executing parameterized queries.

Step 1: Set up the project and dependencies

Create a new Next.js project with the App Router and install the required packages. We’ll use better-sqlite3 for a zero-config local database and zod for schema validation — both are production-grade choices.

npx create-next-app@latest ai-db-tool --typescript --tailwind --eslint --app --src-dir --import-alias "@/*"
cd ai-db-tool
npm install ai @ai-sdk/openai zod better-sqlite3
npm install -D @types/better-sqlite3

The ai package provides the tool abstraction and streaming helpers. @ai-sdk/openai is the model provider; swap it for Anthropic, Google, or any OpenAI-compatible endpoint (including n4n.ai) by changing the import.

Step 2: Create the database schema and seed data

Add a scripts folder at the repository root and create a seed script. This keeps the tutorial self-contained — no external database required.

// scripts/seed.ts
import Database from 'better-sqlite3'
import { resolve } from 'path'

const db = new Database(resolve(process.cwd(), 'data.db'))

db.exec(`
  CREATE TABLE IF NOT EXISTS customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TEXT DEFAULT (datetime('now'))
  );

  CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    amount_cents INTEGER NOT NULL,
    currency TEXT NOT NULL DEFAULT 'USD',
    placed_at TEXT DEFAULT (datetime('now'))
  );
`)

const customers = [
  { name: 'Aisha Patel', email: 'aisha@example.com' },
  { name: 'Ben Carter', email: 'ben@example.com' },
  { name: 'Carla Ruiz', email: 'carla@example.com' },
  { name: 'Dmitri Volkov', email: 'dmitri@example.com' },
]

const orders = [
  { customer_id: 1, amount_cents: 12500, currency: 'USD', placed_at: '2024-11-15' },
  { customer_id: 1, amount_cents: 8900, currency: 'USD', placed_at: '2024-11-28' },
  { customer_id: 2, amount_cents: 45000, currency: 'USD', placed_at: '2024-11-03' },
  { customer_id: 3, amount_cents: 22000, currency: 'USD', placed_at: '2024-11-20' },
  { customer_id: 3, amount_cents: 31000, currency: 'USD', placed_at: '2024-12-02' },
  { customer_id: 4, amount_cents: 7500, currency: 'USD', placed_at: '2024-11-10' },
]

const insertCustomer = db.prepare('INSERT OR IGNORE INTO customers (name, email) VALUES (?, ?)')
const insertOrder = db.prepare('INSERT INTO orders (customer_id, amount_cents, currency, placed_at) VALUES (?, ?, ?, ?)')

for (const c of customers) insertCustomer.run(c.name, c.email)
for (const o of orders) insertOrder.run(o.customer_id, o.amount_cents, o.currency, o.placed_at)

console.log('Database seeded at', resolve(process.cwd(), 'data.db'))

Run it once:

npx tsx scripts/seed.ts

You now have a data.db file in the project root with two tables and realistic test data.

Step 3: Build the database query tool

Create a reusable tool definition under src/lib/db-tool.ts. The tool accepts a natural-language question, converts it to a parameterized SELECT query using a small prompt template, executes it safely, and returns typed rows.

// src/lib/db-tool.ts
import { tool } from 'ai'
import { z } from 'zod'
import Database from 'better-sqlite3'
import { resolve } from 'path'

const db = new Database(resolve(process.cwd(), 'data.db'), { readonly: true })

// Schema the model must satisfy when invoking the tool
const QueryInput = z.object({
  question: z.string().describe('Natural language question about customers or orders'),
})

// Schema for the tool's return value
const QueryOutput = z.object({
  sql: z.string().describe('The parameterized SQL that was executed'),
  params: z.array(z.unknown()).describe('Bound parameters'),
  rows: z.array(z.record(z.unknown())).describe('Result rows'),
  rowCount: z.number().describe('Number of rows returned'),
})

// A minimal system prompt that teaches the model our schema
const SYSTEM_PROMPT = `
You are a SQL generator for a SQLite database with two tables:

customers(id INTEGER PRIMARY KEY, name TEXT, email TEXT, created_at TEXT)
orders(id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id), amount_cents INTEGER, currency TEXT, placed_at TEXT)

Rules:
- Only emit SELECT statements. No INSERT, UPDATE, DELETE, DDL, or PRAGMA.
- Use parameterized queries with ? placeholders. Never interpolate values.
- Join customers to orders when the question involves spending.
- Amounts are stored in cents. Convert to dollars in the SELECT if helpful.
- Dates are ISO strings (YYYY-MM-DD). Use date() for comparisons.
- Return only the JSON object matching the QueryOutput schema.
`.trim()

function generateSql(question: string): { sql: string; params: unknown[] } {
  // In production, call an LLM here with SYSTEM_PROMPT + question.
  // For this tutorial we use a deterministic heuristic so the example runs without extra API keys.
  const q = question.toLowerCase()

  if (q.includes('spent') || q.includes('spending') || q.includes('revenue')) {
    // Top spenders or total per customer
    if (q.includes('over') || q.includes('more than') || q.includes('>')) {
      const match = q.match(/over\s+\$?(\d+)/i) || q.match(/more than\s+\$?(\d+)/i)
      const threshold = match ? parseInt(match[1], 10) * 100 : 50000
      return {
        sql: `
          SELECT c.name, c.email, SUM(o.amount_cents) as total_cents
          FROM customers c
          JOIN orders o ON o.customer_id = c.id
          GROUP BY c.id
          HAVING total_cents > ?
          ORDER BY total_cents DESC
        `.trim(),
        params: [threshold],
      }
    }
    return {
      sql: `
        SELECT c.name, c.email, SUM(o.amount_cents) as total_cents
        FROM customers c
        JOIN orders o ON o.customer_id = c.id
        GROUP BY c.id
        ORDER BY total_cents DESC
      `.trim(),
      params: [],
    }
  }

  if (q.includes('order') || q.includes('purchase')) {
    return {
      sql: `
        SELECT o.id, c.name as customer, o.amount_cents, o.currency, o.placed_at
        FROM orders o
        JOIN customers c ON c.id = o.customer_id
        ORDER BY o.placed_at DESC
        LIMIT 20
      `.trim(),
      params: [],
    }
  }

  // Default: list customers
  return {
    sql: 'SELECT id, name, email, created_at FROM customers ORDER BY created_at',
    params: [],
  }
}

export const dbQueryTool = tool({
  inputSchema: QueryInput,
  outputSchema: QueryOutput,
  execute: async ({ question }) => {
    const { sql, params } = generateSql(question)
    const stmt = db.prepare(sql)
    const rows = stmt.all(...params) as Record<string, unknown>[]
    return { sql, params, rows, rowCount: rows.length }
  },
})

Why this structure matters: The tool wrapper gives you automatic JSON schema generation for the model, runtime validation of both input and output, and TypeScript inference across the boundary. The readonly: true flag on the database connection enforces safety at the driver level.

Step 4: Register the tool with Vercel AI SDK

Create an API route that streams responses using streamText. The route receives a conversation history, passes the tool to the model, and returns a ReadableStream the frontend can consume incrementally.

// src/app/api/chat/route.ts
import { streamText, convertToModelMessages } from 'ai'
import { openai } from '@ai-sdk/openai'
import { dbQueryTool } from '@/lib/db-tool'

export const maxDuration = 30

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

  const result = streamText({
    model: openai('gpt-4o-mini'),
    system: 'You are a data analyst. Use the dbQuery tool to answer questions about customers and orders. Always cite the SQL you ran.',
    messages: convertToModelMessages(messages),
    tools: { dbQuery: dbQueryTool },
    toolChoice: 'auto',
    maxSteps: 3,
  })

  return result.toDataStreamResponse()
}

maxSteps: 3 allows the model to call the tool, receive results, and optionally call it again (for example, to drill down). convertToModelMessages normalizes the UI message format into what the provider expects.

Step 5: Build a simple frontend to test

Replace the default page with a minimal chat interface that streams tokens and tool results as they arrive.

// src/app/page.tsx
'use client'

import { useChat } from 'ai/react'
import { useState } from 'react'

export default function Chat() {
  const { messages, input, handleInputChange, handleSubmit, isLoading, stop } = useChat({
    api: '/api/chat',
  })
  const [toolCalls, setToolCalls] = useState<Record<string, unknown>>({})

  return (
    <main className="flex min-h-screen flex-col items-center p-8 gap-4">
      <h1 className="text-2xl font-semibold">Customer data analyst</h1>

      <div className="w-full max-w-2xl space-y-4">
        {messages.map((m) => (
          <div key={m.id} className={`flex gap-3 ${m.role === 'assistant' ? 'justify-start' : 'justify-end'}`}>
            <div
              className={`rounded-lg px-4 py-2 max-w-[70%] ${
                m.role === 'user' ? 'bg-blue-600 text-white' : 'bg-gray-100'
              }`}
            >
              <p className="whitespace-pre-wrap">{m.content}</p>
              {m.toolInvocations?.map((inv) => (
                <details key={inv.toolCallId} className="mt-2 text-xs">
                  <summary className="cursor-pointer text-gray-500">Tool: {inv.toolName}</summary>
                  <pre className="mt-1 overflow-auto rounded bg-gray-50 p-2">
                    {JSON.stringify(inv.args, null, 2)}
                  </pre>
                  {inv.result && (
                    <>
                      <summary className="cursor-pointer text-gray-500 mt-1">Result</summary>
                      <pre className="mt-1 overflow-auto rounded bg-gray-50 p-2">
                        {JSON.stringify(inv.result, null, 2)}
                      </pre>
                    </>
                  )}
                </details>
              ))}
            </div>
          </div>
        ))}

        {isLoading && <div className="text-sm text-gray-500">Thinking…</div>}
      </div>

      <form onSubmit={handleSubmit} className="w-full max-w-2xl flex gap-2">
        <input
          value={input}
          onChange={handleInputChange}
          placeholder="Ask about customers or orders…"
          className="flex-1 rounded border px-4 py-2 focus:outline-none focus:ring-2 focus:ring-blue-500"
          disabled={isLoading}
        />
        {isLoading ? (
          <button type="button" onClick={stop} className="px-4 py-2 rounded bg-red-600 text-white">
            Stop
          </button>
        ) : (
          <button type="submit" className="px-4 py-2 rounded bg-blue-600 text-white">
            Send
          </button>
        )}
      </form>
    </main>
  )
}

The useChat hook handles streaming, tool invocation rendering, and optimistic updates. Tool calls and results appear in expandable <details> blocks so you can inspect the exact SQL and rows returned.

Step 6: Run and verify

Start the development server:

npm run dev

Open http://localhost:3000. Try these prompts and verify the behavior:

Success criteria:

1. The assistant responds in natural language, not raw JSON.
2. Each tool invocation shows the generated SQL and bound parameters in the UI.
3. No errors appear in the browser console or terminal.
4. Streaming works — tokens appear incrementally, not all at once after a long pause.

If the model hallucinates a column name, the SQLite prepare step will throw and the tool’s execute will return a structured error (you can add a try/catch in execute to surface it cleanly). That’s a feature — it proves the validation boundary works.

Step 7: Harden for production

The heuristic generateSql function is a placeholder. In a real deployment, replace it with an LLM call that uses the same SYSTEM_PROMPT and returns structured JSON. A minimal implementation:

// src/lib/sql-generator.ts
import { generateObject } from 'ai'
import { openai } from '@ai-sdk/openai'
import { z } from 'zod'

const SqlSchema = z.object({
  sql: z.string(),
  params: z.array(z.unknown()),
})

export async function generateSql(question: string) {
  const { object } = await generateObject({
    model: openai('gpt-4o-mini'),
    system: SYSTEM_PROMPT,
    prompt: question,
    schema: SqlSchema,
    temperature: 0,
  })
  return object
}

Then import and call it from db-tool.ts. This keeps the SQL generation logic testable and swappable.

Additional production considerations:

• Rate limiting: Wrap the route with next-rate-limit or Vercel Edge Middleware.
• Authentication: Add a session check before streamText; attach user ID to the tool context for row-level security.
• Observability: Log sql, params, rowCount, and latency to your tracing backend (OpenTelemetry, Datadog, etc.).
• Provider fallback: If you route through a gateway that honors x-provider headers and returns cache-control hints, you can swap models without changing this code.

Step 8: Extend the pattern

Once the read-only pattern is solid, you can add:

• Write tools guarded by confirmation prompts and idempotency keys.
• Vector search tools that hit pgvector or a dedicated index.
• Multi-tenant tools that inject WHERE tenant_id = ? automatically from the session.
• Explain tools that run EXPLAIN QUERY PLAN and feed the plan back to the model for self-correction.

The Vercel AI SDK database query tool architecture scales because the tool contract — input schema, output schema, execution sandbox — stays the same regardless of backend complexity. Your frontend, streaming logic, and model orchestration remain untouched.

Next steps: Clone the repo, swap the OpenAI provider for your preferred gateway, and replace the heuristic generator with a prompt-tuned model. The rest is iteration.