Execute SQL

The executeSQL function provides a safe and efficient way to execute SQL queries against your Boltic database. It includes built-in safety measures, performance optimization, and returns structured results following the Boltic API Response Structure.

ℹ️ Default Query Limits

By default, SELECT queries have a LIMIT of 100 records applied automatically. If you need to retrieve more records or all records without any limit, use NOLIMIT in your query:

-- Default behavior (limited to 100 records)
SELECT * FROM users WHERE status = 'active';

-- Remove default limit to get all records
SELECT * FROM users WHERE status = 'active' NOLIMIT;

-- Or specify a custom limit
SELECT * FROM users WHERE status = 'active' LIMIT 500;

Function Signature

executeSQL(query: string): Promise<ExecuteSQLApiResponse>

Parameters

• query (string): The SQL query string to execute

Return Value

Returns a Promise<ExecuteSQLApiResponse> with the query results and metadata.

Response Structure

The executeSQL function returns results in the Boltic API Response Structure:

interface ExecuteSQLApiResponse {
    data: [
        Record<string, unknown>[], // Query result rows
        unknown, // Metadata (count, execution info, etc.)
    ];
    pagination?: {
        total_count: number;
        total_pages: number;
        current_page: number;
        per_page: number;
        type: string;
    };
    message?: string; // Optional message
}

Basic Usage

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

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

// Execute a simple SELECT query
const result = await client.sql.executeSQL("SELECT * FROM users LIMIT 10");

// Access the data
const [rows, metadata] = result.data;
console.log(`Found ${rows.length} users`);

// Display first row
if (rows.length > 0) {
    console.log("Sample user:", rows[0]);
}

Advanced Examples

Complex Queries with JOINs

// Execute complex analytical queries
const complexQuery = `
  SELECT 
    u.name,
    u.email,
    COUNT(o.id) as order_count,
    SUM(o.total_amount) as total_spent
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  WHERE u.created_at >= '2024-01-01'
  GROUP BY u.id, u.name, u.email
  HAVING COUNT(o.id) > 0
  ORDER BY total_spent DESC
  LIMIT 10
`;

const result = await client.sql.executeSQL(complexQuery);
const [topCustomers, metadata] = result.data;

console.log("Top customers by spending:");
topCustomers.forEach((customer, index) => {
    console.log(
        `${index + 1}. ${customer.name}: $${customer.total_spent} (${customer.order_count} orders)`,
    );
});

Data Aggregation Queries

// Monthly sales report
const salesReportQuery = `
  SELECT 
    DATE_TRUNC('month', created_at) as month,
    COUNT(*) as order_count,
    SUM(total_amount) as total_revenue,
    AVG(total_amount) as avg_order_value
  FROM orders
  WHERE created_at >= '2024-01-01'
  GROUP BY DATE_TRUNC('month', created_at)
  ORDER BY month
`;

const report = await client.sql.executeSQL(salesReportQuery);
const [monthlyData, metadata] = report.data;

console.log("Monthly Sales Report:");
monthlyData.forEach((month) => {
    console.log(
        `${month.month}: ${month.order_count} orders, $${month.total_revenue} revenue, $${month.avg_order_value} avg`,
    );
});

Conditional Queries

// Dynamic query building
async function searchUsers(filters: {
    name?: string;
    email?: string;
    status?: string;
    limit?: number;
}) {
    let query = "SELECT * FROM users WHERE 1=1";
    const params: string[] = [];

    if (filters.name) {
        query += " AND name ILIKE ?";
        params.push(`%${filters.name}%`);
    }

    if (filters.email) {
        query += " AND email ILIKE ?";
        params.push(`%${filters.email}%`);
    }

    if (filters.status) {
        query += " AND status = ?";
        params.push(filters.status);
    }

    query += ` ORDER BY created_at DESC LIMIT ${filters.limit || 50}`;

    const result = await client.sql.executeSQL(query);
    return result.data[0]; // Return just the rows
}

Pagination Support

Handle large result sets with built-in pagination:

async function getPaginatedResults(query: string, page: number = 1, perPage: number = 50) {
    // Add pagination to query
    const paginatedQuery = `${query} LIMIT ${perPage} OFFSET ${(page - 1) * perPage}`;

    const result = await client.sql.executeSQL(paginatedQuery);
    const [rows, metadata] = result.data;

    // Check if pagination info is available
    if (result.pagination) {
        console.log(`Page ${result.pagination.current_page} of ${result.pagination.total_pages}`);
        console.log(`Total records: ${result.pagination.total_count}`);
    }

    return {
        data: rows,
        pagination: result.pagination,
        metadata,
    };
}

Working with UUID ID Fields

Important: The id field in Boltic tables contains UUID values. When joining tables or comparing id fields with other column types, you must cast the id field to text using ::text:

// Cast UUID id to text for comparison
const query = `
  SELECT u.name, p.title 
  FROM "users" u
  JOIN "posts" p ON u.id::text = p.user_id
`;

const result = await client.sql.executeSQL(query);

Note: When joining or comparing an id field with a different-typed column, you need to cast using ::text (e.g., u.id::text = p.user_id) since id fields are UUID type.

Error Handling

Implement robust error handling for query execution:

async function safeExecuteSQL(query: string) {
    try {
        const result = await client.sql.executeSQL(query);
        const [rows, metadata] = result.data;

        console.log("Query executed successfully");
        return { success: true, data: rows, metadata };
    } catch (error) {
        console.error("Query execution failed:", error);

        // Handle specific error types
        if (error.message?.includes("syntax error")) {
            console.error("SQL syntax error detected");
        } else if (error.message?.includes("permission denied")) {
            console.error("Insufficient permissions for this query");
        } else if (error.message?.includes("timeout")) {
            console.error("Query execution timed out");
        }

        return { success: false, error: error.message };
    }
}

Query Optimization Tips

// Use appropriate indexes and LIMIT clauses
async function optimizedQueries() {
    // Good: Use LIMIT for large tables
    const limitedQuery = "SELECT * FROM large_table ORDER BY created_at DESC LIMIT 100";

    // Good: Use specific columns instead of SELECT *
    const specificQuery = "SELECT id, name, email FROM users WHERE status = 'active'";

    // Good: Use WHERE clauses to filter early
    const filteredQuery =
        "SELECT * FROM orders WHERE created_at >= '2024-01-01' AND status = 'completed'";

    const results = await Promise.all([
        client.sql.executeSQL(limitedQuery),
        client.sql.executeSQL(specificQuery),
        client.sql.executeSQL(filteredQuery),
    ]);

    return results;
}

Data Processing

Processing Query Results

async function processUserData() {
    const result = await client.sql.executeSQL(`
    SELECT 
      u.*,
      COUNT(o.id) as order_count,
      MAX(o.created_at) as last_order_date
    FROM users u
    LEFT JOIN orders o ON u.id::text = o.user_id
    GROUP BY u.id
  `);

    const [users, metadata] = result.data;

    // Process and transform data
    const processedUsers = users.map((user) => ({
        id: user.id,
        name: user.name,
        email: user.email,
        isActive: user.order_count > 0,
        customerType: user.order_count > 10 ? "VIP" : user.order_count > 0 ? "Regular" : "New",
        lastOrderDate: user.last_order_date,
    }));

    return processedUsers;
}

Integration with Text-to-SQL

Combine with text-to-SQL for a complete natural language workflow:

async function naturalLanguageQuery(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;

        return {
            sql: generatedSQL,
            data: rows,
            metadata,
            success: true,
        };
    } catch (error) {
        return {
            sql: generatedSQL,
            error: error.message,
            success: false,
        };
    }
}

Use Cases

Data Export

async function exportUserData(format: "json" | "csv" = "json") {
    const result = await client.sql.executeSQL(`
    SELECT 
      id,
      name,
      email,
      created_at,
      status
    FROM users
    ORDER BY created_at DESC
  `);

    const [users, metadata] = result.data;

    if (format === "json") {
        return JSON.stringify(users, null, 2);
    } else if (format === "csv") {
        // Convert to CSV format
        const headers = Object.keys(users[0] || {}).join(",");
        const rows = users.map((user) => Object.values(user).join(","));
        return [headers, ...rows].join("\n");
    }
}

Real-time Analytics

async function getRealTimeMetrics() {
    const queries = {
        totalUsers: "SELECT COUNT(*) as count FROM users",
        activeUsers:
            "SELECT COUNT(*) as count FROM users WHERE last_login >= NOW() - INTERVAL '24 hours'",
        totalOrders: "SELECT COUNT(*) as count FROM orders WHERE created_at >= CURRENT_DATE",
        revenue: "SELECT SUM(total_amount) as total FROM orders WHERE created_at >= CURRENT_DATE",
    };

    const results = await Promise.all(
        Object.entries(queries).map(async ([key, query]) => {
            const result = await client.sql.executeSQL(query);
            return [key, result.data[0][0].count || result.data[0][0].total];
        }),
    );

    return Object.fromEntries(results);
}

Data Migration

async function migrateUserData() {
    // Get users to migrate
    const sourceResult = await client.sql.executeSQL(`
    SELECT * FROM old_users 
    WHERE migrated = false 
    LIMIT 100
  `);

    const [usersToMigrate] = sourceResult.data;

    // Process each user
    for (const user of usersToMigrate) {
        try {
            // Transform data
            const newUser = {
                name: user.full_name,
                email: user.email_address,
                created_at: user.registration_date,
            };

            // Insert into new table
            await client.sql.executeSQL(`
        INSERT INTO users (name, email, created_at)
        VALUES ('${newUser.name}', '${newUser.email}', '${newUser.created_at}')
      `);

            // Mark as migrated
            await client.sql.executeSQL(`
        UPDATE old_users 
        SET migrated = true 
        WHERE id = ${user.id}
      `);
        } catch (error) {
            console.error(`Failed to migrate user ${user.id}:`, error);
        }
    }
}

Best Practices

Performance

• Use LIMIT: Always use LIMIT clauses for potentially large result sets
• Optimize Queries: Write efficient queries with proper WHERE clauses and indexes
• Monitor Execution Time: Track query performance and optimize as needed

The executeSQL function provides a powerful and safe way to interact with your database. Combine it with the textToSQL function for a complete natural language to data workflow! 🚀

Troubleshooting and Common Errors

Common SQL Execution Errors

Error Type	Error Message	Cause	Solution
Table Not Found	relation "table_name" does not exist	Referencing a non-existent table	Verify table exists using client.tables.findByName("table_name")
Column Not Found	column "column_name" does not exist	Referencing a non-existent column	Check table schema using client.tables.findByName("table_name")
Invalid JOIN	invalid reference to FROM-clause entry	Incorrect table relationships in JOIN	Verify foreign key relationships and table names
Syntax Error	syntax error at or near "keyword"	Invalid SQL syntax	Review query syntax and ensure proper SQL formatting
Data Type Mismatch	invalid input syntax for type	Column data type doesn't match value	Ensure data types match column definitions
Timeout Error	query execution timed out	Query taking too long to execute	Add LIMIT clauses, optimize WHERE conditions, or use NOLIMIT carefully