Text2SQL - Natural Language to SQL

The SQL Server adapter supports Microsoft SQL Server databases with automatic schema introspection using INFORMATION_SCHEMA and sys catalog views.

Basic Setup

import sql from 'mssql';

import { Text2Sql, InMemoryHistory } from '@deepagents/text2sql';
import { SqlServer } from '@deepagents/text2sql/sqlserver';
import * as sqlserver from '@deepagents/text2sql/sqlserver';

const pool = await sql.connect({
  server: process.env.DB_HOST,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  options: {
    encrypt: true,
    trustServerCertificate: true,
  },
});

const text2sql = new Text2Sql({
  version: 'v1', // Bump this when your schema changes
  adapter: new SqlServer({
    execute: async (query) => {
      const result = await pool.request().query(query);
      return result.recordset;
    },
    grounding: [
      sqlserver.tables(),
      sqlserver.views(),
      sqlserver.info(),
      sqlserver.indexes(),
      sqlserver.constraints(),
      sqlserver.rowCount(),
      sqlserver.columnStats(),
      sqlserver.lowCardinality(),
    ],
  }),
  history: new InMemoryHistory(),
});

import { Connection, Request } from 'tedious';

import { Text2Sql, InMemoryHistory } from '@deepagents/text2sql';
import { SqlServer } from '@deepagents/text2sql/sqlserver';
import * as sqlserver from '@deepagents/text2sql/sqlserver';

const connection = new Connection(config);

const text2sql = new Text2Sql({
  version: 'v1', // Bump this when your schema changes
  adapter: new SqlServer({
    execute: (sql) => new Promise((resolve, reject) => {
      const rows = [];
      const request = new Request(sql, (err) => {
        if (err) reject(err);
        else resolve(rows);
      });
      request.on('row', (columns) => {
        const row = {};
        columns.forEach((col) => {
          row[col.metadata.colName] = col.value;
        });
        rows.push(row);
      });
      connection.execSql(request);
    }),
    grounding: [
      sqlserver.tables(),
      sqlserver.views(),
      sqlserver.info(),
      sqlserver.indexes(),
      sqlserver.constraints(),
      sqlserver.rowCount(),
      sqlserver.columnStats(),
      sqlserver.lowCardinality(),
    ],
  }),
  history: new InMemoryHistory(),
});

Configuration Options

import * as sqlserver from '@deepagents/text2sql/sqlserver';

new SqlServer({
  // Required: Execute SQL queries
  execute: async (sql) => pool.request().query(sql).then(r => r.recordset),

  // Required: Grounding functions for schema introspection
  grounding: [
    sqlserver.tables(),
    sqlserver.info(),
    // Add more grounding functions as needed
  ],

  // Optional: Custom validation function
  validate: async (sql) => {
    // Return void if valid, or error string if invalid
  },

  // Optional: Filter to specific schemas (default excludes sys, INFORMATION_SCHEMA)
  schemas: ['dbo', 'sales'],
});

See Grounding for the full list of available grounding functions and their configuration options.

Schema Filtering

By default, the adapter excludes system schemas (INFORMATION_SCHEMA, sys). To limit introspection to specific schemas:

new SqlServer({
  execute: async (sql) => pool.request().query(sql).then(r => r.recordset),
  schemas: ['dbo', 'sales', 'inventory'],
});

Available Grounding Functions

The following grounding functions are available for SQL Server. Import them from @deepagents/text2sql/sqlserver:

Function	Description
`tables()`	Discovers tables and columns using INFORMATION_SCHEMA, identifies primary keys
`views()`	Discovers database views
`info()`	Collects SQL Server version and database info
`indexes()`	Queries sys.indexes for clustered/nonclustered types, detects included columns
`constraints()`	Maps foreign key relationships between tables
`rowCount()`	Counts rows and classifies tables by size
`columnStats()`	Collects min, max, null fraction using CONVERT for type-safe aggregation
`lowCardinality()`	Detects columns with few distinct values using `SELECT DISTINCT TOP`
`report()`	Uses an LLM to generate a business context report from your database

See Grounding for detailed configuration options.

Error Handling

The adapter maps SQL Server error codes to helpful suggestions:

Error Code	Type	Suggestion
208	MISSING_TABLE	Check that the table exists and include schema prefix (e.g., dbo.TableName)
207	INVALID_COLUMN	Verify the column exists and aliases are referenced correctly
156	SYNTAX_ERROR	Review keywords, punctuation, and clauses like GROUP BY
4104	INVALID_COLUMN	Qualify columns with table aliases when ambiguous
1934	CONSTRAINT_ERROR	Re-check join logic and filtering

Execute Function Return Types

The execute function can return multiple formats:

// Array of rows directly
execute: async (sql) => {
  const result = await pool.request().query(sql);
  return result.recordset;
}

// Object with recordset property (mssql default)
execute: async (sql) => {
  return await pool.request().query(sql);  // { recordset: [...] }
}

// Object with rows property
execute: async (sql) => {
  return { rows: [...] };
}

// Object with recordsets array (for multi-statement queries)
execute: async (sql) => {
  return { recordsets: [[...]] };  // Uses first recordset
}

Query Validation

By default, validation uses SET PARSEONLY to check syntax without execution:

new SqlServer({
  execute: async (sql) => pool.request().query(sql).then(r => r.recordset),
  // Default validation
  validate: async (sql) => {
    await pool.request().query(`SET PARSEONLY ON; ${sql}; SET PARSEONLY OFF;`);
  },
});

Table Naming Conventions

SQL Server tables are referenced with schema prefix:

dbo.Users
sales.Orders
inventory.Products

The adapter automatically handles bracket quoting: [dbo].[Users]

Size Hints

Tables are classified by row count:

Size Hint	Row Count
tiny	< 100
small	100 - 999
medium	1,000 - 9,999
large	10,000 - 99,999
huge	>= 100,000

SQL Server

On this page