Generate SQL

The toSql() function generates SQL without executing it. You give it the question, the adapter, the schema fragments, and the model. It runs the SQL generation agent and returns the final SQL after adapter formatting and validation.

Get the schema fragments from Text2Sql.index(). Pass the configured adapter name in names — the same key you used in adapters when constructing Text2Sql.

Adapter names must match /^[A-Za-z_][A-Za-z0-9_]*$/. If you create adapter maps dynamically, validate the keys before constructing Text2Sql with isValidAdapterName(name) or validateAdapterNames(names).

Basic Usage

import { groq } from '@ai-sdk/groq';
import { toSql } from '@deepagents/text2sql';

const fragments = await text2sql.index({ names: ['main'] });

const { sql } = await toSql({
  input: 'Show me top 10 customers by revenue',
  adapter,
  fragments,
  model: groq('openai/gpt-oss-20b'),
});

console.log(sql);

How It Works

1. Load schema - index() introspects the adapter or returns cached fragments.
2. Generate SQL - The SQL agent maps the question to the available schema.
3. Format - The adapter normalizes the generated SQL for its dialect.
4. Validate - The adapter validates the SQL before it is returned.
5. Retry - Validation errors are fed back into the agent for up to three attempts.

Validation Example

If you plan to execute the returned SQL outside the chat agent loop, validate it again in your own execution pipeline:

const { sql } = await toSql({ input: 'Show me sales data', adapter, fragments, model });

const error = await adapter.validate(sql);
if (error) {
  console.error('Validation error:', error);
}

Adding Extra Fragments

Add business vocabulary or guardrails next to the schema fragments:

import { term } from '@deepagents/context';

const { sql } = await toSql({
  input: 'Show ARR by plan',
  adapter,
  model,
  fragments: [
    ...(await text2sql.index({ names: ['main'] })),
    term('ARR', 'annual recurring revenue'),
  ],
});

Error Handling

toSql() feeds validation failures back into the model and retries. Set maxRetries to change the number of attempts (default: 3):

const { sql } = await toSql({
  input: 'Show revenue by customer',
  adapter,
  fragments,
  model,
  maxRetries: 5,
});

When to Use toSql() vs a Chat Agent

The streaming chat agent is built by the caller using agent + chat from @deepagents/context plus instructions() and schema fragments from AdapterIndexer or sql index — see Build Conversations.

Use toSql() when you need to:

• Generate queries for display or review before execution
• Store queries for auditing or later execution
• Integrate with an existing query runner
• Build non-interactive workflows

Use a chat agent when you need to:

• Build interactive query interfaces
• Maintain conversation context across multiple queries
• Stream results to users in real time
• Let the agent validate and run read-only SQL on the user's behalf

Examples

Simple Query

const { sql } = await toSql({ input: 'Count total orders', adapter, fragments, model });
// SELECT COUNT(*) AS total_orders FROM orders

Complex Aggregation

const { sql } = await toSql({
  input: 'Show monthly revenue trends for the last 12 months',
  adapter,
  fragments,
  model,
});

Multi-Table Join

const { sql } = await toSql({
  input: 'Which artists have the most albums in our catalog?',
  adapter,
  fragments,
  model,
});

Performance

• Introspection: each index() call introspects the schema, unless you give Text2Sql a cache
• Retry attempts: up to three attempts on validation errors

Call index() once and reuse its fragments for many toSql() calls. To reuse the schema across processes, pass a cache (for example FileIndexCache) to new Text2Sql({ ... }). See the package README's "Schema index caching" section for the cache, its invalidation namespace, and how to introspect once for concurrent callers.