Conversational Text2SQL

Text2SQL supports multi-turn conversations where the AI remembers previous queries, results, and context. This enables natural follow-up questions and iterative data exploration.

Basic Conversation

Use the chat() method instead of toSql() for conversational interactions:

import { Text2Sql } from '@deepagents/text2sql';

const text2sql = new Text2Sql({
  version: 'v1',
  model: openai('gpt-4o'),
  adapter: adapter,
  store: store
});

// First message
const stream1 = await text2sql.chat([
  { role: 'user', content: 'Show me all customers from California' }
]);

for await (const chunk of stream1) {
  console.log(chunk);
}

// Follow-up message (remembers previous context)
const stream2 = await text2sql.chat([
  { role: 'user', content: 'Now filter to only active ones' }
]);

Message Format

Messages follow the standard chat format:

type ChatMessage = {
  role: 'user' | 'assistant';
  content: string;
  id?: string;  // Optional message ID
  metadata?: Record<string, unknown>;  // Optional metadata
};

User Messages

const stream = await text2sql.chat([
  {
    role: 'user',
    content: 'How many orders were placed last month?'
  }
]);

Including Previous Messages

const stream = await text2sql.chat([
  { role: 'user', content: 'Show me all customers' },
  { role: 'assistant', content: 'Here are all customers...' },
  { role: 'user', content: 'Now show only VIP customers' }
]);

Streaming Responses

The chat() method returns a streaming response:

const stream = await text2sql.chat([
  { role: 'user', content: 'Show me top 10 customers by revenue' }
]);

for await (const chunk of stream) {
  if (chunk.type === 'text') {
    process.stdout.write(chunk.content);
  } else if (chunk.type === 'tool-call') {
    console.log('Tool:', chunk.toolName);
  } else if (chunk.type === 'tool-result') {
    console.log('Result:', chunk.result);
  }
}

Chunk Types

type Chunk =
  | { type: 'text'; content: string }
  | { type: 'tool-call'; toolName: string; args: unknown }
  | { type: 'tool-result'; toolName: string; result: unknown }
  | { type: 'finish'; finishReason: string; usage: TokenUsage }
  | { type: 'error'; error: string };

Context Persistence

Conversations are automatically saved to the context store:

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

const store = new SqliteContextStore({
  path: './conversations.db'
});

const text2sql = new Text2Sql({
  version: 'v1',
  model: openai('gpt-4o'),
  adapter: adapter,
  store: store  // Persists conversation history
});

Chat IDs

Each conversation has a unique chatId:

const chatId = 'customer-123-session-1';

// First message in conversation
const stream1 = await text2sql.chat(
  [{ role: 'user', content: 'Show me all orders' }],
  { chatId }
);

// Later: continue the same conversation
const stream2 = await text2sql.chat(
  [{ role: 'user', content: 'What about last month?' }],
  { chatId }  // Same chatId
);

User IDs

Track conversations per user:

const userId = 'user-456';
const chatId = 'conversation-789';

const stream = await text2sql.chat(
  [{ role: 'user', content: 'Show my recent orders' }],
  { userId, chatId }
);

Follow-Up Patterns

Text2SQL understands common follow-up patterns:

// Initial query
const stream1 = await text2sql.chat([
  { role: 'user', content: 'Show me all customers' }
]);
// SQL: SELECT * FROM customers

// Refinement
const stream2 = await text2sql.chat([
  { role: 'user', content: 'But only active ones' }
]);
// SQL: SELECT * FROM customers WHERE status = 'active'

Extension (“and also Y”)

// Initial query
const stream1 = await text2sql.chat([
  { role: 'user', content: 'Show customer names and emails' }
]);
// SQL: SELECT name, email FROM customers

// Extension
const stream2 = await text2sql.chat([
  { role: 'user', content: 'And also their total revenue' }
]);
// SQL: SELECT name, email, total_revenue FROM customers

Pivot (“what about Z instead”)

// Initial query
const stream1 = await text2sql.chat([
  { role: 'user', content: 'Show orders from last month' }
]);
// SQL: SELECT * FROM orders WHERE created_at >= '2024-01-01'

// Pivot
const stream2 = await text2sql.chat([
  { role: 'user', content: 'What about this month instead?' }
]);
// SQL: SELECT * FROM orders WHERE created_at >= '2024-02-01'

Reference Resolution (“it”, “those”, “them”)

// Initial query
const stream1 = await text2sql.chat([
  { role: 'user', content: 'Show me customers from California' }
]);

// Reference resolution
const stream2 = await text2sql.chat([
  { role: 'user', content: 'How many of them are active?' }
]);
// SQL: SELECT COUNT(*) FROM customers WHERE state = 'California' AND status = 'active'

Abort Signal

Cancel long-running queries:

const controller = new AbortController();

const stream = await text2sql.chat(
  [{ role: 'user', content: 'Complex aggregation query...' }],
  { abortSignal: controller.signal }
);

// Cancel after 5 seconds
setTimeout(() => controller.abort(), 5000);

try {
  for await (const chunk of stream) {
    console.log(chunk);
  }
} catch (error) {
  if (error.name === 'AbortError') {
    console.log('Query cancelled');
  }
}

Conversation Management

Retrieve Conversation History

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

const context = new ContextEngine({
  store: store,
  chatId: 'conversation-123',
  userId: 'user-456'
});

const history = await context.getHistory();
console.log(history);
// [
//   { role: 'user', content: 'Show me all customers' },
//   { role: 'assistant', content: 'Here are all customers...' },
//   { role: 'user', content: 'Filter to California' }
// ]

Clear Conversation

await context.clear();

Branch Conversation

Create a new conversation branch:

const stream = await text2sql.chat(
  [{ role: 'user', content: 'Try a different approach' }],
  { chatId: 'conversation-123', branch: true }
);

User Memory

Store user-specific preferences and context:

import { identity, preference, alias } from '@deepagents/context';

const context = new ContextEngine({
  store: store,
  chatId: 'chat-123',
  userId: 'user-456'
});

// Store user identity
context.set(
  identity({
    name: 'John Doe',
    role: 'Sales Manager',
    department: 'Sales'
  })
);

// Store preferences
context.set(
  preference('Always show revenue in USD, rounded to 2 decimals')
);

// Store aliases
context.set(
  alias('my team', 'sales_team_id = 5')
);

await context.save();

Now queries use this context:

const stream = await text2sql.chat([
  { role: 'user', content: 'Show me my team\'s performance' }
]);
// SQL: SELECT * FROM performance WHERE sales_team_id = 5

Real-Time Collaboration

Multiple users can collaborate on the same conversation:

const sharedChatId = 'team-exploration-session';

// User 1
const stream1 = await text2sql.chat(
  [{ role: 'user', content: 'Show revenue by product' }],
  { chatId: sharedChatId, userId: 'user-1' }
);

// User 2 (same chat, different user)
const stream2 = await text2sql.chat(
  [{ role: 'user', content: 'Break that down by region too' }],
  { chatId: sharedChatId, userId: 'user-2' }
);

Conversation Metadata

Attach metadata to conversations:

const stream = await text2sql.chat(
  [{
    role: 'user',
    content: 'Show me Q4 revenue',
    metadata: {
      department: 'Finance',
      reportType: 'quarterly',
      timestamp: new Date().toISOString()
    }
  }]
);

Retrieve metadata:

for await (const chunk of stream) {
  if (chunk.type === 'finish') {
    console.log('Metadata:', chunk.metadata);
  }
}

Error Recovery in Conversations

The AI automatically retries failed queries:

const stream = await text2sql.chat([
  { role: 'user', content: 'Show me data from invalid_table' }
]);

// AI detects error and retries with correction:
// Attempt 1: SELECT * FROM invalid_table → Error: MISSING_TABLE
// Attempt 2: Checks schema, finds correct table name
// Attempt 3: SELECT * FROM valid_table → Success

Manual retry after error:

const stream1 = await text2sql.chat([
  { role: 'user', content: 'Show me sales data' }
]);

// If error occurs
const stream2 = await text2sql.chat([
  { role: 'user', content: 'Try again with the orders table' }
]);

Best Practices

1. Use Consistent Chat IDs

// Good: Consistent chatId per conversation
const chatId = `user-${userId}-session-${sessionId}`;

// Bad: Random chatId each time (loses context)
const chatId = Math.random().toString();

2. Store User Context

import { identity, preference } from '@deepagents/context';

// Store user info once at session start
context.set(
  identity({ name: user.name, role: user.role }),
  preference('Show currency in EUR'),
  alias('my region', `region_id = ${user.regionId}`)
);

3. Handle Streaming Errors

try {
  for await (const chunk of stream) {
    if (chunk.type === 'error') {
      console.error('Error:', chunk.error);
      break;
    }
    // Process chunk
  }
} catch (error) {
  console.error('Stream error:', error);
}

4. Implement Timeouts

const controller = new AbortController();
const timeout = setTimeout(() => controller.abort(), 30000);  // 30s

try {
  const stream = await text2sql.chat(
    messages,
    { abortSignal: controller.signal }
  );
  // ...
} finally {
  clearTimeout(timeout);
}

5. Persist Important Conversations

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

// Production: Use SQLite for persistence
const store = new SqliteContextStore({
  path: './conversations.db'
});

// Development: Use in-memory
const store = new InMemoryContextStore();

Complete Example

conversation-app.ts

import { Text2Sql } from '@deepagents/text2sql';
import { SqliteContextStore, identity, preference } from '@deepagents/context';
import { Postgres, tables, constraints } from '@deepagents/text2sql/postgres';
import { openai } from '@ai-sdk/openai';
import pg from 'pg';

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL
});

const store = new SqliteContextStore({
  path: './conversations.db'
});

const text2sql = new Text2Sql({
  version: 'v1',
  model: openai('gpt-4o'),
  adapter: new Postgres({
    execute: async (sql) => {
      const result = await pool.query(sql);
      return result.rows;
    },
    grounding: [tables(), constraints()]
  }),
  store
});

async function chat(userId: string, sessionId: string, message: string) {
  const chatId = `user-${userId}-session-${sessionId}`;
  
  const stream = await text2sql.chat(
    [{ role: 'user', content: message }],
    { userId, chatId }
  );
  
  let response = '';
  
  for await (const chunk of stream) {
    if (chunk.type === 'text') {
      response += chunk.content;
      process.stdout.write(chunk.content);
    } else if (chunk.type === 'tool-result') {
      console.log('\nQuery result:', chunk.result);
    } else if (chunk.type === 'error') {
      console.error('\nError:', chunk.error);
    }
  }
  
  return response;
}

// Example conversation
async function main() {
  const userId = 'user-123';
  const sessionId = 'session-456';
  
  // First message
  await chat(userId, sessionId, 'Show me all customers from California');
  
  // Follow-up
  await chat(userId, sessionId, 'How many are active?');
  
  // Another follow-up
  await chat(userId, sessionId, 'What is their total revenue?');
}

main().catch(console.error);

Conversational

Conversational Text2SQL

Basic Conversation

Message Format

User Messages

Including Previous Messages

Streaming Responses

Chunk Types

Context Persistence

Chat IDs

User IDs

Follow-Up Patterns

Refinement (“but only X”)

Extension (“and also Y”)

Pivot (“what about Z instead”)

Reference Resolution (“it”, “those”, “them”)

Abort Signal

Conversation Management

Retrieve Conversation History

Clear Conversation

Branch Conversation

User Memory

Real-Time Collaboration

Conversation Metadata

Error Recovery in Conversations

Best Practices

1. Use Consistent Chat IDs

2. Store User Context

3. Handle Streaming Errors

4. Implement Timeouts

5. Persist Important Conversations

Complete Example

Next Steps

Explainability

Core API

Documentation Index

​Conversational Text2SQL

​Basic Conversation

​Message Format

​User Messages

​Including Previous Messages

​Streaming Responses

​Chunk Types

​Context Persistence

​Chat IDs

​User IDs

​Follow-Up Patterns

​Refinement (“but only X”)

​Extension (“and also Y”)

​Pivot (“what about Z instead”)

​Reference Resolution (“it”, “those”, “them”)

​Abort Signal

​Conversation Management

​Retrieve Conversation History

​Clear Conversation

​Branch Conversation

​User Memory

​Real-Time Collaboration

​Conversation Metadata

​Error Recovery in Conversations

​Best Practices

​1. Use Consistent Chat IDs

​2. Store User Context

​3. Handle Streaming Errors

​4. Implement Timeouts

​5. Persist Important Conversations

​Complete Example

​Next Steps

Explainability

Core API

Conversational Text2SQL

Basic Conversation

Message Format

User Messages

Including Previous Messages

Streaming Responses

Chunk Types

Context Persistence

Chat IDs

User IDs

Follow-Up Patterns

Refinement (“but only X”)

Extension (“and also Y”)

Pivot (“what about Z instead”)

Reference Resolution (“it”, “those”, “them”)

Abort Signal

Conversation Management

Retrieve Conversation History

Clear Conversation

Branch Conversation

User Memory

Real-Time Collaboration

Conversation Metadata

Error Recovery in Conversations

Best Practices

1. Use Consistent Chat IDs

2. Store User Context

3. Handle Streaming Errors

4. Implement Timeouts

5. Persist Important Conversations

Complete Example

Next Steps