Text-to-SQL

The textToSQL function transforms natural language descriptions into executable SQL queries using advanced AI. This powerful feature allows you to generate complex database queries without writing SQL manually.

Function Signature

textToSQL(
  prompt: string,
  options?: TextToSQLOptions
): Promise<AsyncIterable<string>>

Parameters

• prompt (string): Natural language description of the desired query
• options (TextToSQLOptions, optional): Configuration options
  • currentQuery (string, optional): Existing query to refine or modify

Return Value

Returns a Promise<AsyncIterable<string>> that streams the generated SQL query in real-time.

Basic Usage

Simple Query Generation

import { createClient } from "@boltic/sdk";

const client = createClient("your-api-key");

// Generate a basic query
const sqlStream = await client.sql.textToSQL("Show me all users from California");

// Process the streaming result
let generatedSQL = "";
for await (const chunk of sqlStream) {
    process.stdout.write(chunk); // Display in real-time
    generatedSQL += chunk;
}

console.log("\nGenerated SQL:", generatedSQL);
// Output: SELECT * FROM users WHERE state = 'CA'

Query Refinement

Use the currentQuery option to refine or modify existing queries:

// Start with a basic query
const initialQuery = "SELECT * FROM users WHERE state = 'CA'";

// Refine the query
const refinedStream = await client.sql.textToSQL("Add ORDER BY clause and limit to 5 results", {
    currentQuery: initialQuery,
});

let refinedSQL = "";
for await (const chunk of refinedStream) {
    refinedSQL += chunk;
}

console.log("Refined SQL:", refinedSQL);
// Output: SELECT * FROM users WHERE state = 'CA' ORDER BY created_at DESC LIMIT 5

Advanced Examples

Complex Analytical Queries

// Generate complex analytical queries
const analyticsStream = await client.sql.textToSQL(
    "Show me the top 10 customers by total order value, including their names and order counts",
);

let analyticsSQL = "";
for await (const chunk of analyticsStream) {
    analyticsSQL += chunk;
}

console.log("Analytics SQL:", analyticsSQL);

Data Exploration Queries

// Explore data relationships
const explorationStream = await client.sql.textToSQL(
    "Find all products that have been ordered by customers in the last 30 days, grouped by category",
);

let explorationSQL = "";
for await (const chunk of explorationStream) {
    explorationSQL += chunk;
}

console.log("Exploration SQL:", explorationSQL);

Report Generation

// Generate reporting queries
const reportStream = await client.sql.textToSQL(
    "Create a monthly sales report showing revenue, order count, and average order value for each month in 2024",
);

let reportSQL = "";
for await (const chunk of reportStream) {
    reportSQL += chunk;
}

console.log("Report SQL:", reportSQL);

Error Handling

Handle errors gracefully when text-to-SQL conversion fails:

async function safeTextToSQL(prompt: string) {
    try {
        const sqlStream = await client.sql.textToSQL(prompt);
        let generatedSQL = "";

        for await (const chunk of sqlStream) {
            generatedSQL += chunk;
        }

        return generatedSQL;
    } catch (error) {
        console.error("Text-to-SQL conversion failed:", error);

        // Fallback to manual query or user input
        return null;
    }
}

Best Practices

Writing Effective Prompts

1. Be Specific: Provide clear, detailed descriptions

   // Good
   "Show me all users who registered in the last 30 days and have made at least one purchase";
   
   // Less effective
   "Show me some users";

2. Use Table and Column Names: Reference actual database structure

   // Good
   "Find all orders in the orders table where the status is 'completed' and the total_amount is greater than $100";
   
   // Less effective
   "Find some completed orders";

3. Specify Requirements: Include sorting, filtering, and limiting requirements

   // Good
   "Get the top 5 customers by total order value, ordered by highest value first";
   
   // Less effective
   "Get some customers";

Integration with Query Execution

Combine text-to-SQL with query execution for a complete workflow:

async function generateAndExecute(prompt: string) {
    // Generate SQL from natural language
    const sqlStream = await client.sql.textToSQL(prompt);
    let generatedSQL = "";

    for await (const chunk of sqlStream) {
        generatedSQL += chunk;
    }

    console.log("Generated SQL:", generatedSQL);

    // Execute the generated query
    try {
        const result = await client.sql.executeSQL(generatedSQL);
        const [rows, metadata] = result.data;

        console.log(`Query executed successfully. Found ${rows.length} results.`);
        return { sql: generatedSQL, result };
    } catch (error) {
        console.error("Query execution failed:", error);
        return { sql: generatedSQL, error };
    }
}

The textToSQL function transforms how you interact with your data, making complex queries accessible through natural language. Start with simple prompts and gradually build more sophisticated queries as you become familiar with the capabilities! 🚀

⚠️ IMPORTANT: Provide Correct Table Context

Before performing any SQL operations, ensure that you provide accurate context about your database schema to the text-to-SQL function. The AI generates queries based on your natural language input and the context you provide about your tables and relationships.

• Provide table names that actually exist in your database
• Mention column names that are present in your tables
• Describe data relationships accurately for JOIN operations
• Use correct table and column names that match your actual database schema
• Include relevant context about table structure when asking complex queries

This ensures the generated SQL queries are accurate and executable against your actual database structure.