How DBX's AI SQL Assistant Works with OpenAI: Architecture and Implementation

DBX integrates with OpenAI by normalizing configuration presets, constructing Chat Completion payloads with database schema context, and sending authenticated POST requests to the OpenAI API endpoint.

DBX is a desktop database client that ships with a built-in AI-SQL assistant. This feature transforms natural language questions into executable SQL queries by leveraging OpenAI's language models. Understanding how DBX handles the OpenAI integration reveals the configuration logic, request construction, and security patterns implemented in the t8y2/dbx source code.

Configuration and Provider Presets

The OpenAI Preset Definition

The integration begins in apps/desktop/src/stores/settingsStore.ts, where DBX defines a static preset for OpenAI. This preset specifies the endpoint, default model, authentication method, and request style.

The preset configures the following defaults:

• Endpoint: https://api.openai.com/v1/chat/completions
• Model: gpt-4o-mini
• Auth Method: bearer (requiring an API key)
• Icon: Visual identifier for the UI

When the user selects OpenAI as the provider in the desktop interface, the application binds to this preset through the AI_PROVIDER_PRESETS constant.

Runtime Configuration Normalization

At startup, DBX calls normalizeAiConfig() to merge user settings with preset defaults. This function ensures that critical OpenAI parameters are always present, even if the user has partially configured the connection.

The normalization process:

1. Loads the saved user configuration from the settings store
2. Applies the OpenAI preset values for any missing fields
3. Validates that the authMethod is set to "bearer"
4. Returns a complete AiConfig object ready for the agents layer

This approach guarantees that the endpoint and model are always correctly defined before any API request occurs.

Prompt Construction and Schema Context

Building the Chat Completion Payload

When the user invokes the assistant, DBX constructs a Chat Completion request following OpenAI's JSON schema. The payload is assembled in the agents layer, which reads the normalized AiConfig to determine the model and endpoint.

The request structure includes:

• System role: Instructions for SQL generation with schema context
• User role: The natural language question
• Temperature: Set to 0 for deterministic output
• Max tokens: Limited to 512 to prevent excessive generation

const payload = {
  model: cfg.model, // "gpt-4o-mini" from preset
  messages: [
    { 
      role: "system", 
      content: "You are a SQL-generation assistant. Use the supplied schema..." 
    },
    { 
      role: "user", 
      content: "<user question>" 
    }
  ],
  temperature: 0,
  max_tokens: 512
};

System Prompts and Database Schema

Before sending the request, DBX extracts the current database schema (tables, columns, and data types) from the active connection. This schema information is embedded into the system prompt, enabling the model to generate syntactically correct SQL specific to the database dialect.

The system prompt construction happens in the agents layer, which bridges the database metadata with the AI configuration.

API Communication and Authentication

HTTP Request Formation

DBX uses a fetch-compatible client to send a POST request to the configured OpenAI endpoint. The request includes specific headers required by the OpenAI API:

Authorization: Bearer <user-provided-API-key>
Content-Type: application/json

The aiClient module (located in the agents layer or packages/node-core/src/aiClient.ts) handles the actual HTTP transmission. It receives the normalized AiConfig containing the endpoint and API key, submits the payload, and parses the JSON response to extract the generated SQL string.

The response processing extracts choices[0].message.content and returns the raw SQL to the editor UI, where it is inserted into the query editor with syntax highlighting.

OpenAI-Compatible Services and Fallback Inference

DBX supports OpenAI-compatible services (such as Azure OpenAI) through the inferAiProviderFromConfig() helper function. This utility analyzes the user configuration to automatically detect OpenAI patterns:

• URLs containing openai.com
• Model names beginning with gpt-

When these patterns match, the function automatically selects the openai provider even if the user has configured a custom endpoint. This inference mechanism allows DBX to work with third-party OpenAI proxies without requiring manual provider selection.

Implementation Examples

Configuring OpenAI in the Settings Store

To programmatically configure the OpenAI provider:

import { AI_PROVIDER_PRESETS } from "./stores/settingsStore";

const openAiPreset = AI_PROVIDER_PRESETS.openai;

// Configure settings
settings.aiProvider = openAiPreset.provider;
settings.apiKey = "<YOUR_OPENAI_API_KEY>";
settings.endpoint = openAiPreset.endpoint; // https://api.openai.com/v1/chat/completions

Generating SQL with the AI Assistant

The following pattern demonstrates how the agents layer invokes the assistant:

import { normalizeAiConfig } from "./stores/settingsStore";
import { fetchOpenAiChat } from "./lib/aiClient";

async function generateSql(question: string, schema: SchemaInfo) {
  // Normalize ensures OpenAI preset is applied
  const cfg = normalizeAiConfig(getSavedAiConfig());
  
  const payload = {
    model: cfg.model,
    messages: [
      { role: "system", content: buildSystemPrompt(schema) },
      { role: "user", content: question }
    ],
    temperature: 0,
    max_tokens: 512,
  };

  const response = await fetchOpenAiChat(
    cfg.endpoint, 
    cfg.apiKey, 
    payload
  );
  
  return response.choices[0].message.content; // Generated SQL
}

CLI Usage

For command-line operation, set environment variables before invoking the assistant:

export DBX_AI_PROVIDER=openai
export DBX_OPENAI_API_KEY=sk-XXXXXXXXXXXXXXXXXXXX

dbx sql-assist "Show me the total sales per month for 2023"

Summary

• DBX stores OpenAI configuration in settingsStore.ts with presets for the endpoint (https://api.openai.com/v1/chat/completions), model (gpt-4o-mini), and bearer authentication.
• Runtime normalization via normalizeAiConfig() merges user settings with defaults to ensure valid API requests.
• Prompt construction combines database schema context with the user question to create a Chat Completion payload with temperature set to 0 for deterministic SQL generation.
• Authentication uses Bearer token headers with the user-provided API key, sent via POST requests handled by the agents layer.
• Inference fallback through inferAiProviderFromConfig() enables compatibility with Azure OpenAI and other OpenAI-compatible services by detecting URL and model patterns.

Frequently Asked Questions

How does DBX authenticate with the OpenAI API?

DBX uses Bearer token authentication. The settingsStore.ts defines authMethod: "bearer" in the OpenAI preset, which requires users to provide an API key. This key is sent in the Authorization: Bearer <key> header with every request to the OpenAI endpoint.

Can I use Azure OpenAI or other compatible services with DBX?

Yes. DBX includes an inferAiProviderFromConfig() helper that automatically detects OpenAI-compatible configurations. If your custom endpoint URL contains openai.com or your model name starts with gpt-, DBX selects the OpenAI provider logic, allowing integration with Azure OpenAI or proxy services without code changes.

What model does DBX use by default for SQL generation?

The default model is gpt-4o-mini, defined in the OpenAI preset within apps/desktop/src/stores/settingsStore.ts. Users can override this in their settings, but the normalization function ensures a valid model is always present.

Where is the HTTP request to OpenAI actually triggered?

The HTTP POST request is triggered in the agents layer (typically implemented in packages/node-core/src/aiClient.ts or similar modules). This layer reads the normalized AiConfig from the settings store, constructs the fetch-compatible request, and parses the Chat Completion response to return the generated SQL.

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