🎵 Chinook Music Database Chat

An intelligent chat interface for querying the Chinook music database using natural language, powered by LangChain v1.0, DuckDB, and NiceGUI.

NOTE: Pydantic AI version here

✨ Features

• Natural Language Queries: Ask questions about music, artists, albums, tracks, and genres in plain English
• Intelligent SQL Generation: Automatically converts your questions into optimized SQL queries
• DuckDB Integration: Lightning-fast analytical queries on the Chinook music database
• Real-time Chat Interface: Beautiful, responsive chat UI powered by NiceGUI
• LangChain v1.0 Compatible: Built with the latest LangChain architecture and patterns

🚀 Quick Start

Prerequisites

• Python 3.13+
• OpenAI API Key (set in .env file)

Installation

1. Clone the repository:

git clone <your-repo>
cd nl-to-sql-agent

2. Install dependencies:

uv sync

3. Set up your environment variables:

cp .env.example .env
# Edit .env and add your API keys:
# - OPENAI_API_KEY=your_openai_api_key_here
# - GOOGLE_API_KEY=your_google_api_key_here (optional, alternative to OpenAI)
# - ANTHROPIC_API_KEY=your_anthropic_api_key_here (optional, alternative to OpenAI)

4. The database will be automatically set up on first run

Running the Chat Interface

uv run python chat_app.py

Then open your browser to: http://localhost:8080

💬 Example Queries

Try asking these questions in the chat interface:

• "Which genre has the longest average tracks?"
• "Who are the top 5 artists by album count?"
• "Show me tracks longer than 5 minutes"
• "What are the most popular genres by track count?"
• "Which albums have the most tracks?"
• "Find customers from Brazil"
• "Show me the highest grossing invoices"

🏗️ Architecture

Database

• DuckDB: High-performance analytical database
• Chinook Dataset: Music store database with artists, albums, tracks, customers, and sales data
• Auto-migration: Automatically converts from SQLite to DuckDB on first run

AI Components

• LangChain v1.0: Modern agent framework with StateGraph
• OpenAI GPT-4: Natural language understanding and SQL generation
• LangGraph: Sophisticated multi-step reasoning workflow

Web Interface

• NiceGUI: Modern Python web framework
• Real-time Chat: Instant responses with typing indicators
• Responsive Design: Works on desktop and mobile

📁 Project Structure

├── chat_app.py          # NiceGUI chat interface (main application)
├── pyproject.toml       # uv project configuration
├── .env                 # Environment variables
├── Chinook.db          # SQLite source database (auto-downloaded)
├── Chinook.duckdb      # DuckDB database (auto-created)
└── README.md           # This file

🔧 Technical Details

LangChain v1.0 Migration

• Updated from langchain.agents.ToolNode to langgraph.prebuilt.ToolNode
• Fixed conversation flow for OpenAI API compatibility
• Proper message type handling with AIMessage and HumanMessage
• StateGraph-based agent architecture

DuckDB Integration

• Automatic SQLite to DuckDB migration
• Optimized for analytical queries
• Connection pooling and proper resource management
• Support for complex aggregations and joins

Chat Interface Features

• Typing indicators and loading states
• Message timestamps and avatars
• Scrollable chat history
• Mobile-responsive design
• Error handling and recovery

🛠️ Development

Adding New Features

The agent architecture is modular and extensible:

1. Database Tools: Add new tools in the SQLDatabaseToolkit
2. Agent Nodes: Create new nodes in the StateGraph
3. UI Components: Extend the NiceGUI interface

Testing

# Test the chat interface
uv run python chat_app.py
# Then open http://localhost:8080 and try some queries

📊 Database Schema

The Chinook database contains:

• Artists: Music artists and bands
• Albums: Music albums with release information
• Tracks: Individual songs with duration, genre, etc.
• Genres: Music genres and categories
• Customers: Customer information and demographics
• Invoices: Sales transactions and line items
• Employees: Store employee records

🤝 Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Test thoroughly
5. Submit a pull request

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

🙏 Acknowledgments

• LangChain for the agent framework
• DuckDB for the analytical database
• NiceGUI for the web interface
• Chinook Database for the sample data