Text-to-SQL Agent

A Next.js application that converts natural language queries into SQL using Claude AI (Anthropic) and LangChain. The agent uses tool calling to autonomously generate and execute SQL queries against a persistent SQLite database containing customer and order data.

Features

• AI-Powered SQL Generation: Uses Claude Sonnet 4.5 to convert natural language to SQL queries
• Tool-Based Agent: LangGraph ReAct agent with database query tool for autonomous SQL execution
• Interactive Chat Interface: Real-time conversation with the AI agent
• Persistent SQLite Database: File-based database (data.db) with sample customer and order data
• Message History: Maintains full conversation context across queries
• TypeScript: Full type safety throughout the application

Tech Stack

• Framework: Next.js 16
• AI/LLM: Anthropic Claude (via @langchain/anthropic)
• Agent Framework: LangGraph
• Database: SQLite3
• Styling: Tailwind CSS
• Language: TypeScript

Project Structure

text-to-sql/
├── src/
│   ├── app/                    # Next.js app directory
│   │   ├── actions.ts          # Server actions for AI agent
│   │   └── page.tsx            # Main chat interface
│   └── lib/                    # Shared libraries
│       ├── constants.ts        # Database table schemas
│       └── database.ts         # SQLite database setup
├── scripts/
│   └── seed.ts                 # Database seeding script
├── .env.local                  # Environment variables (not in repo)
├── .env.local.dist             # Environment template
└── package.json

Getting Started

Prerequisites

• Node.js 20.9.0 or higher
• npm, yarn, or pnpm
• Anthropic API key

Installation

1. Clone the repository

   git clone https://github.com/marac19901990/text-to-sql.git
   cd text-to-sql

2. Install dependencies

   npm install

3. Set up environment variables

   Copy the template and add your API key:

   cp .env.local.dist .env.local

   Edit .env.local and add your Anthropic API key:

   ANTHROPIC_API_KEY=sk-ant-api03-xxxxxxxxxxxxx
   

   Get your API key from: https://console.anthropic.com/settings/keys

4. Seed the database

   npm run seed

   This creates data.db and populates it with 10 customers and 20 orders.

5. Run the development server

   npm run dev

6. Open the application

   Navigate to http://localhost:3000

Usage

Asking Questions

Simply type natural language questions about the data in the chat interface:

• "Show me all customers"
• "Which customer has the most orders?"
• "What are the total shipping costs by customer?"
• "Find orders created in August 2024"

The AI agent will:

1. Analyze your natural language question
2. Use the get_from_db tool to generate and execute SQL queries
3. Return the results in a readable format

Database Schema

The application includes two tables:

Customer Table:

• id - Customer ID
• email - Customer email
• name - Customer name

Order Table:

• id - Order ID
• createdate - Order creation date
• shippingcost - Shipping cost
• customerid - Foreign key to customer
• carrier - Shipping carrier
• trackingid - Tracking number

Available Scripts

• npm run dev - Start development server
• npm run build - Build for production
• npm start - Start production server
• npm run seed - Seed the database with sample data
• npm run lint - Run ESLint

Configuration

VSCode Settings

The project includes VSCode settings for:

• Format on save
• Format on paste

Settings are in .vscode/settings.json

Environment Variables

• ANTHROPIC_API_KEY - Your Anthropic API key (required)

Development

Adding New Tables

1. Define the schema in src/lib/constants.ts
2. Add seed data in src/lib/database.ts
3. Run npm run seed to populate

Modifying the AI Agent

The agent configuration is in src/app/actions.ts:

• Model: claude-sonnet-4-20250514 (Claude Sonnet 4.5)
• Temperature: 0 (deterministic)
• Tools: get_from_db - executes SQL queries against the database
• System prompt: Defined in src/app/page.tsx

The get_from_db tool:

• Receives SQL query as input
• Validates schema using Zod
• Executes query via execute() function
• Returns JSON stringified results

Database Queries

Execute SQL queries using the execute function from src/lib/database.ts

Architecture

Client-Server Communication

1. User sends message from page.tsx (client component)
2. Message serialized to StoredMessage format
3. Server action in actions.ts processes with Claude
4. Response serialized and sent back to client
5. Client updates UI with new messages

Message Flow

User Input → HumanMessage → serialize → Server Action
                                         ↓
                                    Claude Agent (ReAct)
                                         ↓
                                    Tool: get_from_db
                                         ↓
                                    Execute SQL Query
                                         ↓
AI Response ← deserialize ← StoredMessage ← Agent Response

How the Agent Works

1. User sends a message: Natural language question typed in the chat
2. Message serialization: Converted to LangChain's StoredMessage format
3. Server action invoked: message() function in actions.ts receives the message history
4. Agent reasoning: Claude analyzes the question and determines it needs database data
5. Tool calling: Agent calls get_from_db tool with generated SQL query
6. Query execution: SQL query runs against the SQLite database
7. Tool response: Results returned to the agent as JSON
8. Final response: Agent formulates natural language response with the data
9. UI update: Response displayed in the chat interface

Troubleshooting

API Key Not Found

• Ensure .env.local exists and contains ANTHROPIC_API_KEY
• Restart the dev server after adding the key

Node Version Error

• Update to Node.js 20.9.0 or higher
• Use nvm: nvm install 20 && nvm use 20

Database Not Seeding

• Ensure data.db file was created (check with ls -lh data.db)
• Check that src/lib/constants.ts exports table schemas
• Run npm run seed manually
• Check console for error messages
• Verify the database has data: sqlite3 data.db "SELECT COUNT(*) FROM customer;"

Database File is Empty (0 bytes)

• The seed() function must properly wait for async operations
• Ensure all db.run() calls use callbacks and resolve/reject appropriately
• Re-run npm run seed after fixing async issues

License

MIT

Acknowledgments

• Built with Next.js
• Powered by Anthropic Claude
• Agent framework by LangChain