NaturalQuery 1.0.1

NaturalQuery

NL2SQL engine for .NET — convert natural language questions into SQL queries using LLMs.

"top 10 products by sales"  →  SELECT product AS label, SUM(amount) AS value FROM orders GROUP BY product ORDER BY value DESC LIMIT 10

What it does

NaturalQuery takes a natural language question, sends it to an LLM with your database schema, and returns:

• A validated SQL query
• A recommended chart type (bar, pie, line, table, etc.)
• Title, description, and follow-up suggestions
• Executed results ready for visualization

Quick Start

dotnet add package NaturalQuery

With AWS Bedrock + Athena

builder.Services.AddNaturalQuery(options =>
{
    options.Tables = new List<TableSchema>
    {
        new("orders", new[]
        {
            new ColumnDef("id", "string"),
            new ColumnDef("product", "string"),
            new ColumnDef("amount", "double"),
            new ColumnDef("status", "string", "Active, Cancelled, Refunded"),
            new ColumnDef("created_at", "string", "ISO 8601 timestamp"),
        })
    };
    options.TenantIdColumn = "tenant_id";
    options.TenantIdPlaceholder = "{TENANT_ID}";
})
.UseBedrockProvider("us.anthropic.claude-haiku-4-5-20251001-v1:0")
.UseAthenaExecutor("my_database", "my_workgroup", "s3://my-results/");

With OpenAI + PostgreSQL

builder.Services.AddNaturalQuery(options =>
{
    options.Tables = new List<TableSchema>
    {
        new("users", new[]
        {
            new ColumnDef("id", "int"),
            new ColumnDef("name", "string"),
            new ColumnDef("email", "string"),
            new ColumnDef("created_at", "timestamp"),
        })
    };
})
.UseOpenAiProvider("sk-your-api-key", model: "gpt-4o-mini")
.UsePostgresExecutor("Host=localhost;Database=mydb;Username=user;Password=pass");

Usage

app.MapGet("/ask", async (string question, INaturalQueryEngine engine) =>
{
    var result = await engine.AskAsync(question, tenantId: "my-tenant");
    return Results.Ok(result);
});

Architecture

Question (natural language)
    ↓
[Cache check] → hit? return cached result
    ↓
[Rate limiter] → exceeded? throw
    ↓
ILlmProvider (Bedrock / OpenAI / custom)
    ↓
SQL generation + validation + schema check
    ↓
IQueryExecutor (Athena / PostgreSQL / custom)
    ↓
QueryResult (data + chart type + metadata)
    ↓
[Cache store] + [Diagnostics]

Features

Providers

Query Cache

Avoid redundant LLM calls for repeated questions:

.UseInMemoryCache()

// Configure TTL
options.CacheTtlMinutes = 10;  // default: 5

Cache key is a SHA256 hash of (question + tenantId) for privacy. Implement IQueryCache for Redis/distributed caching.

Rate Limiting

Per-tenant sliding window rate limiter:

.UseInMemoryRateLimiter()

// Configure limit
options.RateLimitPerMinute = 30;  // default: 60

Implement IRateLimiter for distributed rate limiting.

Conversation Context (Follow-up Questions)

Support "now filter by cancelled ones" style follow-up questions:

var context = new ConversationContext();

var result1 = await engine.AskAsync("all orders this month", tenantId: "t1", context: context);
// context now contains the first question + SQL

var result2 = await engine.AskAsync("now only cancelled ones", tenantId: "t1", context: context);
// LLM receives conversation history and generates a filtered query

Query Explanation

Let users understand what the generated SQL does:

var result = await engine.InterpretAsync("top products by revenue");
var explanation = await engine.ExplainAsync(result.Sql);
// "This query groups products by name and sums their revenue, returning the top results."

Suggested Questions

Help users discover what they can ask:

var suggestions = await engine.SuggestQuestionsAsync(count: 5);
// ["What are the top 10 products by sales?", "How many orders per month?", ...]

Error Handling

Track NL2SQL failures for monitoring and prompt refinement:

// Inline handler
.UseErrorHandler(async (error, ct) =>
{
    logger.LogError("NL2SQL error: {Type} - {Message}", error.ErrorType, error.Message);
    await myErrorStore.SaveAsync(error);
})

// Or implement IErrorHandler
.UseErrorHandler<MyErrorHandler>()

NaturalQueryError includes: Question, Sql, ErrorType, Message, TenantId, TokensUsed, ElapsedMs, Exception.

OpenTelemetry Diagnostics

All operations emit System.Diagnostics.Activity spans, compatible with any OpenTelemetry exporter:

// In your OpenTelemetry config
builder.Services.AddOpenTelemetry()
    .WithTracing(tracing => tracing
        .AddSource(NaturalQueryDiagnostics.SourceName));

Spans: NaturalQuery.Ask, NaturalQuery.LlmGenerate, NaturalQuery.Execute with tags for tokens, chart type, cache hits, and errors.

Schema Discovery

Auto-discover table schemas from your database:

// PostgreSQL
.UsePostgresSchemaDiscovery("Host=localhost;Database=mydb;Username=user;Password=pass")

// Then in your app
var discovery = app.Services.GetRequiredService<ISchemaDiscovery>();
var tables = await discovery.DiscoverAsync(schemaFilter: "public");

Also supports Athena/Glue catalog discovery via AthenaSchemaDiscovery.

Schema Validation

Validates that SQL references only known tables:

var error = SchemaValidator.ValidateColumns(sql, options.Tables);
// null = valid, or "Unknown table(s): foo. Available: users, orders"

Automatically runs during AskAsync when tables are configured (warning level, non-blocking).

Query Cost Estimation (Athena)

Estimate scan cost before executing:

var estimator = app.Services.GetRequiredService<IQueryCostEstimator>();
var cost = await estimator.EstimateAsync(sql);
// cost.EstimatedBytes, cost.EstimatedCostUsd, cost.FormattedSize

SQL Validation

Built-in security — all generated SQL is validated:

• Only SELECT and WITH (CTE) queries allowed
• Blocks: DELETE, UPDATE, INSERT, DROP, ALTER, CREATE, TRUNCATE, GRANT, REVOKE
• String literals are ignored to prevent false positives
• Tenant isolation enforced when configured
• Custom forbidden keywords via options.ForbiddenSqlKeywords

Multi-Tenancy

Built-in tenant isolation:

options.TenantIdColumn = "tenant_id";
options.TenantIdPlaceholder = "{TENANT_ID}";

// Every query MUST contain WHERE tenant_id = '{TENANT_ID}'
// The placeholder is replaced with the actual tenant ID at runtime
var result = await engine.AskAsync("all users", tenantId: "abc-123");

Configuration Reference

options.Tables = new List<TableSchema> { ... };       // Database schema
options.TenantIdColumn = "tenant_id";                  // Multi-tenant column
options.TenantIdPlaceholder = "{TENANT_ID}";           // Placeholder in SQL
options.MaxTokens = 1000;                              // LLM max tokens
options.Temperature = 0.1;                             // LLM temperature (0=deterministic)
options.CacheTtlMinutes = 5;                           // Cache TTL (0=disabled)
options.RateLimitPerMinute = 60;                       // Rate limit per tenant
options.CustomSystemPrompt = "...{TABLES_SCHEMA}...";  // Override entire prompt
options.AdditionalRules = new() { "Use UTC dates" };   // Extra instructions for LLM
options.ForbiddenSqlKeywords = new() { "UNION " };     // Extra blocked SQL keywords

Custom Providers

Custom LLM Provider

public class OllamaProvider : ILlmProvider
{
    public async Task<LlmResponse> GenerateAsync(
        string systemPrompt, string userPrompt, CancellationToken ct)
    {
        // Your implementation
        return new LlmResponse(responseText, tokensUsed);
    }
}

.UseLlmProvider<OllamaProvider>()

Custom Query Executor

public class BigQueryExecutor : IQueryExecutor
{
    public async Task<List<DataPoint>> ExecuteChartQueryAsync(string sql, CancellationToken ct)
    {
        // Return label + value pairs
    }

    public async Task<List<Dictionary<string, string>>> ExecuteTableQueryAsync(string sql, CancellationToken ct)
    {
        // Return rows as dictionaries
    }
}

.UseQueryExecutor<BigQueryExecutor>()

Chart Types

The LLM automatically selects the best visualization:

License

MIT — see LICENSE.