# How to Use DBX AI SQL Assistant to Optimize SQL Queries

> Optimize SQL queries using DBX AI SQL Assistant. Get instant suggestions like CREATE INDEX statements by invoking the optimize action or calling the completion assistant endpoint.

- Repository: [skyler/dbx](https://github.com/t8y2/dbx)
- Tags: how-to-guide
- Published: 2026-07-03

---

**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`](https://github.com/t8y2/dbx/blob/main/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`](https://github.com/t8y2/dbx/blob/main/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`](https://github.com/t8y2/dbx/blob/main/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:
   ```sql
   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:

```typescript
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`](https://github.com/t8y2/dbx/blob/main/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:

```rust
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`](https://github.com/t8y2/dbx/blob/main/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`](https://github.com/t8y2/dbx/blob/main/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`](https://github.com/t8y2/dbx/blob/main/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`](https://github.com/t8y2/dbx/blob/main/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`](https://github.com/t8y2/dbx/blob/main/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`](https://github.com/t8y2/dbx/blob/main/schema.rs)), AI tool execution ([`agent_tools.rs`](https://github.com/t8y2/dbx/blob/main/agent_tools.rs)), and safety policy enforcement ([`agent_loop.rs`](https://github.com/t8y2/dbx/blob/main/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`](https://github.com/t8y2/dbx/blob/main/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`](https://github.com/t8y2/dbx/blob/main/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.