Generate SQL

The high-level Text2Sql.toSql() method generates SQL without executing it. It uses cached schema fragments from adapter indexing, runs the SQL generation agent, and returns the final SQL string after adapter formatting and validation.

The second argument is the configured adapter name — the same key you used in adapters when constructing Text2Sql. It is required, even when only one adapter is configured.

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

const sql = await text2sql.toSql(
  'Show me top 10 customers by revenue',
  'main',
);

console.log(sql);

How It Works

When you call toSql():

1. Load schema - Adapter indexing 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 text2sql.toSql('Show me sales data', 'main');

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

Adding Extra Fragments

Text2Sql.toSql() uses cached schema fragments from adapter indexing. If you want direct SQL generation with extra business vocabulary or guardrails, call the lower-level toSql() helper exported from @deepagents/text2sql and pass your own fragments explicitly:

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

const model = groq('openai/gpt-oss-20b');

const result = await toSql({
  input: 'Show ARR by plan',
  // Reuse your configured database adapter from Getting Started.
  adapter,
  model,
  fragments: [
    ...(await adapter.introspect()),
    term('ARR', 'annual recurring revenue'),
  ],
});

console.log(result.sql);

Error Handling

toSql() includes retry logic that feeds validation failures back into the model:

const sql = await text2sql.toSql('Show revenue by customer', 'main');
console.log(sql);

When to Use toSql() vs a Chat Agent

toSql() is a method on Text2Sql. 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 text2sql.toSql('Count total orders', 'main');
// SELECT COUNT(*) AS total_orders FROM orders

Complex Aggregation

const sql = await text2sql.toSql(
  'Show monthly revenue trends for the last 12 months',
  'main',
);

Multi-Table Join

const sql = await text2sql.toSql(
  'Which artists have the most albums in our catalog?',
  'main',
);

Performance

Query generation is fast:

• Cold start: first call includes schema introspection
• Cached schema: later calls reuse the on-disk cache for the configured version
• Retry attempts: up to three attempts on validation errors

The introspection cache is invalidated when you change the version parameter:

const text2sql = new Text2Sql({
  version: 'v2', // Bump this when schema changes
  adapters: { main: adapter },
  model,
});