Query Builder

A Spring Boot application that converts natural language text to SQL queries using OpenAI's GPT models and Spring AI. This tool leverages AI to understand user intent and generate appropriate SQL queries based on your database schema.

Table of Contents

• Features
• Prerequisites
• Installation
• Configuration
• Usage
• Project Structure
• API Endpoints
• Technology Stack
• Building and Running
• Testing

Features

• Natural Language to SQL Generation: Convert plain English queries to SQL using AI
• Schema-Aware Query Generation: Understands your database schema and generates contextually appropriate queries
• Automatic Table Identification: Intelligently identifies relevant tables based on user input
• Spring Boot Integration: Built on Spring Boot 3.5.10 with Spring Web and Spring AI
• OpenAI Integration: Uses GPT-4o-mini model for intelligent query generation
• RESTful API: Simple POST endpoint for query generation

Prerequisites

• Java 21 or higher
• Maven 3.6+
• OpenAI API key
• macOS, Linux, or Windows with a terminal

Installation

1. Clone the repository:

   git clone https://github.com/navneetprabhakar/query-builder.git
   cd query-builder

2. Install dependencies:

   mvn clean install

3. Set up environment variables:

   export OPENAI_API_KEY="your-openai-api-key-here"

Configuration

The application is configured via application.yaml in src/main/resources/:

spring:
  application:
    name: query-builder
  ai:
    openai:
      base-url: https://api.openai.com
      api-key: ${OPENAI_API_KEY}
      chat:
        options:
          model: gpt-4o-mini
          temperature: 0.7

Key configurations:

• model: Uses gpt-4o-mini for cost-effective query generation
• temperature: Set to 0.7 for balanced creativity and consistency
• API Key: Retrieved from environment variable OPENAI_API_KEY

Database Schema

Database schema information is stored in table-schemas.json. The application includes sample schemas for:

• users: User account information with columns like id, username, email, created_at, status
• orders: Customer order records with user relationships
• And additional tables as needed

Usage

Starting the Application

mvn spring-boot:run

The application will start on http://localhost:8080

API Endpoints

Generate SQL Query

Endpoint: POST /v1/query/generate

Request Body: Plain text query (sent as a raw string)

Example Request:

curl -X POST http://localhost:8080/v1/query/generate \
  -H "Content-Type: text/plain" \
  -d "Get all active users who made purchases in the last 30 days"

Example Response:

{
  "query": "SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE u.status = 'active' AND o.order_date >= CURRENT_DATE - INTERVAL 30 DAY",
  "relevantTables": ["users", "orders"],
  "message": "SQL query generated successfully"
}

Query Examples

The application can handle various natural language queries:

• "Find all users with an email containing 'example.com'"
• "Show me the top 10 orders by total amount"
• "List users who haven't placed any orders"
• "Get revenue by product category for this year"

Project Structure

src/
├── main/
│   ├── java/com/navneet/query/
│   │   ├── QueryBuilderApplication.java      # Main Spring Boot application
│   │   ├── controller/
│   │   │   └── QueryController.java          # REST endpoint definitions
│   │   ├── models/
│   │   │   ├── QueryGenerationResponse.java  # Response model
│   │   │   └── sql/
│   │   │       ├── ColumnSchema.java         # Database column metadata
│   │   │       ├── ForeignKey.java           # Foreign key definitions
│   │   │       ├── TableSchema.java          # Table metadata
│   │   │       └── TableSchemaConfig.java    # Schema configuration
│   │   └── service/
│   │       ├── QueryService.java             # Query service interface
│   │       ├── SchemaService.java            # Schema service interface
│   │       ├── helper/
│   │       │   └── QueryServiceHelper.java   # Helper for query generation
│   │       └── impl/
│   │           ├── QueryServiceImpl.java      # Query service implementation
│   │           └── SchemaServiceImpl.java     # Schema service implementation
│   └── resources/
│       ├── application.yaml                  # Spring Boot configuration
│       └── table-schemas.json                # Database schema definitions
└── test/
    └── java/com/navneet/query/
        └── QueryBuilderApplicationTests.java # Unit tests

Technology Stack

Technology	Version	Purpose
Spring Boot	3.5.10	Application framework
Spring Web	Latest	RESTful API support
Spring AI	1.1.2	AI model integration
OpenAI API	gpt-4o-mini	Natural language processing
Lombok	Latest	Annotation-based code generation
Java	21	Programming language
Maven	3.6+	Build tool

Building and Running

Build the Application

mvn clean install

This creates an executable JAR at target/query-builder-0.0.1-SNAPSHOT.jar

Run the Application

Using Maven:

mvn spring-boot:run

Or using the JAR file:

java -jar target/query-builder-0.0.1-SNAPSHOT.jar

Build with Custom Configuration

mvn clean install -DskipTests

Testing

Run the unit tests:

mvn test

Run tests with detailed output:

mvn test -X

How It Works

1. User Input: User provides a natural language query via the REST API
2. Table Identification: The QueryServiceHelper identifies relevant tables from the query
3. Schema Retrieval: The SchemaService retrieves schema information for identified tables
4. Context Generation: Schema context is formatted for the AI model
5. Query Generation: GPT-4o-mini generates a SQL query based on the natural language input and schema context
6. Response: The generated SQL query is returned to the user along with metadata

Error Handling

The application handles various error scenarios:

• Invalid API keys
• Network errors with OpenAI API
• Missing schema information
• Malformed requests

Future Enhancements

• Support for multiple database types (PostgreSQL, MySQL, SQL Server)
• Query validation and optimization
• Batch query generation
• Query caching for similar requests
• Advanced schema metadata (indexes, constraints)
• Multi-language support
• Web UI for interactive query building

License

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

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Support

For issues, questions, or suggestions, please open an issue on the GitHub repository.