Getting Started

This guide will walk you through the basics of using SQL operations with the Boltic Tables SDK. You'll learn how to set up the client, generate SQL from natural language, and execute queries to retrieve data.

Your First SQL Operations

1. Generate SQL from Natural Language

Start by converting a simple natural language request into SQL:

async function firstTextToSQL() {
    try {
        // Generate SQL from natural language
        const sqlStream = await client.sql.textToSQL("Show me all users");

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

        console.log("Generated SQL:", generatedSQL);
        // Output: SELECT * FROM users
    } catch (error) {
        console.error("Error generating SQL:", error);
    }
}

2. Execute Your First Query

Execute a simple query to retrieve data:

async function firstQueryExecution() {
    try {
        // Execute a simple query
        const result = await client.sql.executeSQL("SELECT * FROM users LIMIT 5");

        // Access the results
        const [rows, metadata] = result.data;

        console.log(`Found ${rows.length} users:`);
        rows.forEach((user, index) => {
            console.log(`${index + 1}. ${user.name} (${user.email})`);
        });
    } catch (error) {
        console.error("Error executing query:", error);
    }
}

3. Complete Workflow Example

Combine both operations for a complete natural language to data workflow:

async function completeWorkflow() {
    try {
        // Step 1: Generate SQL from natural language
        console.log('Generating SQL from: "Show me the top 3 users by creation date"');

        const sqlStream = await client.sql.textToSQL("Show me the top 3 users by creation date");
        let generatedSQL = "";

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

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

        // Step 2: Execute the generated query
        console.log("Executing query...");
        const result = await client.sql.executeSQL(generatedSQL);

        // Step 3: Process the results
        const [rows, metadata] = result.data;
        console.log(`Query returned ${rows.length} users:`);

        rows.forEach((user, index) => {
            console.log(`${index + 1}. ${user.name} - Created: ${user.created_at}`);
        });
    } catch (error) {
        console.error("Workflow error:", error);
    }
}

Common Patterns

Basic Data Retrieval

// Get all records from a table
async function getAllUsers() {
    const result = await client.sql.executeSQL("SELECT * FROM users");
    return result.data[0]; // Returns the rows array
}

// Get specific columns
async function getUserNames() {
    const result = await client.sql.executeSQL("SELECT id, name, email FROM users");
    return result.data[0];
}

// Get limited results
async function getRecentUsers(limit = 10) {
    const result = await client.sql.executeSQL(
        `SELECT * FROM users ORDER BY created_at DESC LIMIT ${limit}`,
    );
    return result.data[0];
}

Filtering and Searching

// Filter by specific criteria
async function getActiveUsers() {
    const result = await client.sql.executeSQL("SELECT * FROM users WHERE status = 'active'");
    return result.data[0];
}

// Search by name
async function searchUsersByName(name: string) {
    const result = await client.sql.executeSQL(`SELECT * FROM users WHERE name ILIKE '%${name}%'`);
    return result.data[0];
}

// Date range filtering
async function getUsersFromLastWeek() {
    const result = await client.sql.executeSQL(`
    SELECT * FROM users 
    WHERE created_at >= NOW() - INTERVAL '7 days'
  `);
    return result.data[0];
}

Data Aggregation

// Count records
async function getUserCount() {
    const result = await client.sql.executeSQL("SELECT COUNT(*) as count FROM users");
    const [rows] = result.data;
    return rows[0].count;
}

// Group and aggregate
async function getUsersByStatus() {
    const result = await client.sql.executeSQL(`
    SELECT status, COUNT(*) as count 
    FROM users 
    GROUP BY status
  `);
    return result.data[0];
}

Error Handling

Always implement proper error handling for your SQL operations:

async function safeSQLOperation(operation: () => Promise<any>) {
    try {
        const result = await operation();
        return { success: true, data: result };
    } catch (error) {
        console.error("SQL operation failed:", error);

        // Handle specific error types
        if (error.message?.includes("syntax error")) {
            return { success: false, error: "Invalid SQL syntax" };
        } else if (error.message?.includes("permission denied")) {
            return { success: false, error: "Insufficient permissions" };
        } else {
            return { success: false, error: "Unknown error occurred" };
        }
    }
}

// Usage
const result = await safeSQLOperation(async () => {
    return await client.sql.executeSQL("SELECT * FROM users");
});

if (result.success) {
    console.log("Data retrieved:", result.data);
} else {
    console.error("Operation failed:", result.error);
}

Natural Language Examples

Here are some common natural language prompts and their expected SQL outputs:

Basic Queries

const examples = [
    {
        prompt: "Show me all users",
        expected: "SELECT * FROM users",
    },
    {
        prompt: "Get the first 10 users",
        expected: "SELECT * FROM users LIMIT 10",
    },
    {
        prompt: "Find users created today",
        expected: "SELECT * FROM users WHERE DATE(created_at) = CURRENT_DATE",
    },
];

// Test these examples
for (const example of examples) {
    const sqlStream = await client.sql.textToSQL(example.prompt);
    let generatedSQL = "";

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

    console.log(`Prompt: "${example.prompt}"`);
    console.log(`Generated: ${generatedSQL}`);
    console.log(`Expected: ${example.expected}`);
    console.log("---");
}

Advanced Queries

// Complex analytical queries
const advancedPrompts = [
    "Show me the top 5 users by total order value",
    "Find all users who haven't logged in for 30 days",
    "Get a count of users by registration month",
    "Show me users with more than 10 orders",
];

for (const prompt of advancedPrompts) {
    const sqlStream = await client.sql.textToSQL(prompt);
    let generatedSQL = "";

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

    console.log(`Advanced prompt: "${prompt}"`);
    console.log(`Generated SQL: ${generatedSQL}`);
    console.log("---");
}

Query Refinement

Learn to refine and improve generated queries:

async function refineQuery() {
    // Start with a basic query
    let currentQuery = await generateSQL("Show me all users");
    console.log("Initial query:", currentQuery);

    // Refine with additional requirements
    const refinements = [
        "Add ORDER BY created_at DESC",
        "Limit to 5 results",
        "Only show active users",
    ];

    for (const refinement of refinements) {
        const refinedStream = await client.sql.textToSQL(refinement, { currentQuery });
        let refinedSQL = "";

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

        currentQuery = refinedSQL;
        console.log(`After "${refinement}":`, currentQuery);
    }

    return currentQuery;
}

async function generateSQL(prompt: string, options?: any) {
    const sqlStream = await client.sql.textToSQL(prompt, options);
    let sql = "";

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

    return sql;
}

Next Steps

Now that you understand the basics, explore these advanced topics:

• Text-to-SQL Conversion - Deep dive into natural language to SQL
• Execute SQL Queries - Advanced query execution patterns

You're now ready to start using SQL operations with the Boltic Tables SDK! Start with simple queries and gradually build more complex operations as you become familiar with the capabilities. 🚀