How to Use DBX AI SQL Assistant to Optimize SQL Queries

To optimize SQL with DBX, invoke the "Optimize" action from the query editor or call the /schema/completion-assistant endpoint with mode: "optimize"; the assistant gathers live schema metadata via completion_assistant_search_core, prompts an LLM, and returns concrete suggestions such as CREATE INDEX statements that you can review before applying.

The DBX AI SQL Assistant in the t8y2/dbx repository provides automated query optimization by analyzing execution plans, schema metadata, and database-specific characteristics. This tool integrates directly into the query editor and API, offering actionable recommendations like index creation and query rewrites without executing unsafe DDL automatically.

Architecture of the DBX AI SQL Assistant

The DBX AI SQL Assistant operates through three tightly-coupled layers that bridge your database schema with large language model (LLM) analysis.

Schema and Metadata Collection

When you request optimization, DBX first gathers the current connection's schema—including tables, columns, indexes, and foreign keys—via the completion_assistant_search_core function in crates/dbx-core/src/schema.rs (lines 2773-2868). This function dispatches to database-specific drivers (Postgres, MySQL, SQLite, DuckDB, etc.) and normalizes the results into a common CompletionAssistantResponse structure.

AI Tool Call Execution

The normalized schema is embedded into a prompt sent to the configured AI model (Claude, OpenAI, or OpenAI-compatible endpoints). The assistant's tool definition resides in crates/dbx-core/src/agent_tools.rs, where the "Suggest index optimisation" tool is declared at line 172. This tool instructs the LLM to analyze the query and schema, then return specific optimization actions.

Execution Policy and Safety

DBX implements an execution policy layer in crates/dbx-core/src/agent_loop.rs (lines 540-544) that routes assistant actions based on mode. In Ask mode, the system never executes generated SQL directly; it presents suggestions in the UI for manual review. In Agent mode, the policy forwards optimization suggestions to the client, allowing you to inspect proposed CREATE INDEX statements before application.

How to Optimize SQL Using the DBX AI SQL Assistant

You can access the optimization features through three primary interfaces: the web UI, REST API, or direct Rust library calls.

Method 1: Optimize via the Query Editor UI

The simplest approach requires no code:

1. Open the Query Editor (built on CodeMirror 6) in the DBX interface.

2. Write the SQL you want to optimize, for example:

   SELECT * FROM orders WHERE user_id = 42;

3. Click the AI Assistant button (robot icon) in the toolbar.

4. Select "Optimize" from the modal menu.

5. Review the generated suggestions, which may include recommended indexes or rewritten queries using key-set pagination.

6. Click Accept to apply the DDL or copy the SQL for manual execution.

This flow sends your query to the /schema/completion-assistant endpoint automatically.

Method 2: Programmatic API Access (Node.js)

For automated workflows, call the optimization endpoint directly:

import fetch from 'node-fetch';

const DBX_URL = 'http://localhost:8080';
const API_KEY = 'YOUR_API_KEY';  // Keep this secret

async function optimiseSql(sql: string, connectionId: string) {
  const body = {
    sql,
    connection_id: connectionId,
    mode: "optimize"  // Required to trigger optimization analysis
  };

  const resp = await fetch(`${DBX_URL}/schema/completion-assistant`, {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'Authorization': `Bearer ${API_KEY}`
    },
    body: JSON.stringify(body)
  });

  const data = await resp.json();
  // Returns LLM-generated suggestions in assistantContent
  return data.candidates?.[0]?.assistantContent;
}

// Example usage
optimiseSql(
  'SELECT * FROM orders WHERE user_id = 42',
  'postgres-conn-1'
).then(console.log);

The endpoint is implemented in crates/dbx-web/src/routes/schema.rs (lines 164-170), which delegates to the core schema logic.

Method 3: Direct Rust Library Integration

For Rust applications using DBX as a library:

use dbx_core::schema::{CompletionAssistantRequest, completion_assistant_search_core};

#[tokio::main]
async fn main() {
    let request = CompletionAssistantRequest {
        sql: "SELECT * FROM orders WHERE user_id = 42".into(),
        mode: "optimize".into(),
        connection_id: "postgres-conn-1".into(),
        ..Default::default()
    };

    // Returns CompletionAssistantResponse with optimization suggestions
    let result = completion_assistant_search_core(&state, &request).await;
    println!("{:#?}", result);
}

This approach calls completion_assistant_search_core directly, bypassing the HTTP layer while maintaining the same schema analysis and LLM interaction logic.

Key Source Files and Implementation Details

Understanding the source code helps customize or debug the optimization pipeline:

• crates/dbx-core/src/schema.rs (lines 2773-2868): Core logic for completion_assistant_search_core, which gathers schema metadata and dispatches to database drivers.
• crates/dbx-core/src/agent_tools.rs (line 172): Defines the "Suggest index optimisation" tool available to the LLM.
• crates/dbx-core/src/agent_loop.rs (lines 540-544): Execution policy handling that routes "optimize", "generate", and "fix" actions.
• crates/dbx-web/src/routes/schema.rs (lines 164-170): HTTP route handler for the /schema/completion-assistant endpoint.
• crates/dbx-core/src/db/postgres.rs (lines 1391-1396): Driver-specific implementation of completion_assistant_search for PostgreSQL (similar files exist for MySQL, SQLite, and DuckDB).

Summary

• The DBX AI SQL Assistant optimizes queries by analyzing live schema metadata via completion_assistant_search_core and prompting an LLM with database context.
• Use mode: "optimize" when calling the /schema/completion-assistant endpoint to trigger optimization-specific analysis.
• The system operates in three layers: schema collection (schema.rs), AI tool execution (agent_tools.rs), and safety policy enforcement (agent_loop.rs).
• DBX never auto-executes optimization DDL in Ask mode; all suggestions require explicit user approval.
• You can access optimization features through the web UI, Node.js API, or direct Rust library calls.

Frequently Asked Questions

What optimization suggestions can the DBX AI SQL Assistant provide?

The assistant can recommend adding indexes (e.g., CREATE INDEX idx_user_name ON users(name)), rewriting joins for better performance, enabling key-set pagination patterns, and identifying missing constraints that affect query execution. These suggestions are generated based on the actual schema metadata retrieved from your database connection.

How does DBX ensure safety when applying AI-generated SQL optimizations?

DBX implements a strict execution policy in agent_loop.rs that prevents automatic execution of DDL in Ask mode. When the assistant returns an "optimize" action, the system forwards the suggestion to the UI for manual review. You must explicitly accept the proposed CREATE INDEX or ALTER statement before it runs against your database.

Can I use the DBX AI SQL Assistant with any database driver?

Yes, the assistant supports PostgreSQL, MySQL, SQLite, and DuckDB through driver-specific implementations of completion_assistant_search. Each driver in crates/dbx-core/src/db/ (such as postgres.rs at lines 1391-1396) implements schema introspection that feeds into the common CompletionAssistantResponse structure used by the AI.

What is the difference between Ask mode and Agent mode in DBX?

Ask mode is the default safe mode where the assistant only provides suggestions and never executes SQL automatically. Agent mode allows for more autonomous operation but still routes actions through the execution policy layer. For optimization tasks, both modes require explicit confirmation before applying schema changes like index creation.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}