Text2SQL - Natural Language to SQL

Text2SQL improves query accuracy with domain-specific fragments such as terms, hints, guardrails, examples, and workflows. The current API does not expose mutable text2sql.instruct() or text2sql.teach() helpers. Instead, you inject fragments through the ContextEngine for chat() flows, or pass fragments directly to the low-level toSql() helper when you need direct SQL generation.

Teach `chat()` Sessions

Add fragments to the ContextEngine you pass to your chat agent:

import {
  ContextEngine,
  InMemoryContextStore,
  createBashTool,
  createDockerSandbox,
  example,
  guardrail,
  hint,
  npm,
  term,
} from '@deepagents/context';
import { createSqlCommandHooks, instructions } from '@deepagents/text2sql';

const store = new InMemoryContextStore();
const backend = await createDockerSandbox({
  installers: [npm('@deepagents/text2sql', { ensureRuntime: true })],
  env: {
    TEXT2SQL_ADAPTERS: '/workspace/text2sql-adapters.ts',
  },
});
const sandbox = await createBashTool({
  sandbox: backend,
  ...createSqlCommandHooks({ adapters: { main: adapter } }),
});
const engine = new ContextEngine({
  store,
  chatId: 'chat-123',
  userId: 'user-456',
});

engine.set(
  term('NPL', 'non-performing loan - loan past due 90+ days'),
  hint('Always exclude test accounts with email ending in @test.com'),
  guardrail({
    rule: 'Never expose individual salaries',
    reason: 'Confidential HR data',
    action: 'Aggregate by department instead',
  }),
  example({
    question: 'show me top customers',
    answer: 'SELECT * FROM customers ORDER BY lifetime_value DESC LIMIT 10',
  }),
);

const indexResult = await sandbox.sandbox.executeCommand('sql index');
if (indexResult.exitCode !== 0) throw new Error(indexResult.stderr);
const manifest = JSON.parse(indexResult.stdout) as { fragmentsPath: string };
const schemaFragments = JSON.parse(
  await sandbox.sandbox.readFile(manifest.fragmentsPath),
);

engine.set(...instructions(), ...schemaFragments);

That TEXT2SQL_ADAPTERS path must point to a module inside the sandbox whose default export is the adapter map for the sql CLI. Mount, upload, or write that module before starting the agent.

Use that engine when building the chat agent (const ai = agent({ context: engine, ... })) and call chat(ai) from @deepagents/context to make those fragments available on every turn.

Teach Direct SQL Generation with `toSql()`

The high-level Text2Sql.toSql() method only uses schema fragments from adapter indexing. If you want direct SQL generation with extra business vocabulary, use the lower-level toSql() helper:

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);

Fragment Types

All of these domain fragments come from @deepagents/context.

term() - Domain Vocabulary

term('AUM', 'assets under management - total market value of investments');
term('DFW rate', 'percentage of students receiving D, F, or Withdrawal');

hint() - Always-Apply Guidance

hint("Always exclude work orders with status = 'simulation'");
hint('Revenue means gross revenue unless net is explicitly requested');

guardrail() - Hard Boundaries

guardrail({
  rule: 'Never return PHI like SSN, MRN, or full address',
  reason: 'HIPAA compliance',
  action: 'Offer de-identified aggregates instead',
});

explain() - Rich Concept Explanations

explain({
  concept: 'time to fill',
  explanation: 'Measures the time from job posting to accepted offer.',
  therefore: 'Calculate days between job_posted_date and offer_accepted_date',
});

example() - Few-Shot Patterns

example({
  question: 'show me peak demand hours',
  answer: `SELECT DATE_TRUNC('hour', reading_timestamp) AS hour,
    MAX(consumption_kwh) AS peak_demand
  FROM meter_readings
  WHERE reading_timestamp >= CURRENT_DATE - INTERVAL '7 days'
  GROUP BY hour
  ORDER BY peak_demand DESC
  LIMIT 10`,
});

clarification() - Ask Before Guessing

clarification({
  when: "user asks for 'conversion rate'",
  ask: 'Which conversion: click-to-lead, lead-to-opportunity, or opportunity-to-customer?',
  reason: 'Conversion rate means different things at each funnel stage',
});

workflow() - Multi-Step Analysis

workflow({
  task: 'Claims Loss Ratio Analysis',
  triggers: ['loss ratio', 'claims ratio', 'underwriting performance'],
  steps: [
    'Calculate total claims paid for each policy period',
    'Calculate total premiums earned for the same period',
    'Compute loss ratio as (claims_paid / premiums_earned) * 100',
  ],
});

quirk() - Data Edge Cases

quirk({
  issue: 'Citizen IDs have leading zeros but are stored as integers',
  workaround: "Use LPAD(citizen_id::VARCHAR, 10, '0') to restore zeros",
});

styleGuide() - SQL Preferences

styleGuide({
  prefer: "Use donor-centric language: 'donor_name' not 'customer_name'",
  never: 'Never expose internal donor IDs in external reports',
});

analogy() - Concept Comparisons

analogy({
  concept: ['cart abandonment', 'browse abandonment'],
  relationship:
    'Cart abandonment is like leaving items at checkout, while browse abandonment is window shopping.',
  insight: 'Cart shows stronger purchase intent.',
});

Built-In Guidance

Text2SQL already ships with built-in SQL guidance:

chat() injects the default chat-time SQL workflow from packages/text2sql/src/lib/instructions.ts
Direct SQL generation uses the built-in SQL agent policies in packages/text2sql/src/lib/agents/sql.agent.ts

Best Practices

Start with guardrails so safety and compliance rules are explicit.
Add terms early for domain jargon and internal acronyms.
Use examples sparingly for patterns that are otherwise easy to misread.
Keep chat() and toSql() separate in your mental model: ContextEngine fragments affect persistent chat flows, while direct SQL generation requires explicit fragments on each call.

Teach the System