Grounding

Grounding is Text2SQL's database introspection system. It extracts schema metadata, statistics, and relationships from your database to give the AI rich context for generating accurate SQL queries.

Why Grounding Matters

Without grounding, the AI would need to guess table names, column types, and relationships. Grounding provides:

• Schema awareness - Tables, columns, and their types
• Relationship mapping - Foreign keys and join paths
• Performance hints - Indexes, row counts, and size classifications
• Data patterns - Low cardinality values (enums) and column statistics

Quick Start

Each database adapter (PostgreSQL, SQLite, SQL Server) exports grounding factory functions. Import them and pass to your adapter:

import { Text2Sql, InMemoryHistory } from '@deepagents/text2sql';
import { Postgres } from '@deepagents/text2sql/postgres';
import * as postgres from '@deepagents/text2sql/postgres';

const text2sql = new Text2Sql({
  version: 'v1',
  adapter: new Postgres({
    execute: async (sql) => pool.query(sql).then(r => r.rows),
    grounding: [
      postgres.tables(),
      postgres.views(),
      postgres.info(),
      postgres.indexes(),
      postgres.constraints(),
      postgres.rowCount(),
      postgres.columnStats(),
      postgres.lowCardinality(),
    ],
  }),
  history: new InMemoryHistory(),
});

import { Text2Sql, InMemoryHistory } from '@deepagents/text2sql';
import { Sqlite } from '@deepagents/text2sql/sqlite';
import * as sqlite from '@deepagents/text2sql/sqlite';

const text2sql = new Text2Sql({
  version: 'v1',
  adapter: new Sqlite({
    execute: (sql) => db.prepare(sql).all(),
    grounding: [
      sqlite.tables(),
      sqlite.views(),
      sqlite.info(),
      sqlite.indexes(),
      sqlite.constraints(),
      sqlite.rowCount(),
      sqlite.columnStats(),
      sqlite.lowCardinality(),
    ],
  }),
  history: new InMemoryHistory(),
});

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

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

Available Groundings

Core Groundings

These groundings discover your database structure:

tables(config?)

Discovers tables and their relationships via foreign key traversal. This is the foundational grounding that other groundings depend on.

tables({
  // Filter tables - array of names, regex, or predicate function
  filter: ['users', 'orders', 'products'],
  // or
  filter: /^app_/,
  // or
  filter: (name) => !name.startsWith('_'),

  // Traverse forward (child → parent) following FK direction
  // true = unlimited depth, number = max depth
  forward: 2,

  // Traverse backward (parent → child) finding referencing tables
  backward: 1,
})

views(config?)

Discovers database views and their columns.

views({
  // Same filter options as tables()
  filter: ['active_users', 'sales_summary'],
})

info()

Collects database dialect, version, and connection info. Helps the AI understand database-specific SQL syntax.

info()
// Returns: { dialect: 'postgresql', version: '15.0', database: 'mydb' }

Metadata Groundings

These groundings enhance the schema with performance-relevant metadata. They run after tables() and annotate the discovered tables.

indexes()

Marks which columns have indexes. Helps the AI generate queries that use indexed columns for filtering and sorting.

indexes()
// Annotates columns with isIndexed: true

constraints()

Collects CHECK, UNIQUE, NOT NULL, DEFAULT, PRIMARY KEY, and FOREIGN KEY constraints.

constraints()
// Annotates tables with constraint information

rowCount()

Counts rows per table and classifies by size. Helps the AI avoid expensive full table scans on large tables.

rowCount()
// Annotates tables with rowCount and sizeHint

Size classifications:

columnStats()

Collects min/max values and null fraction for numeric and date columns.

columnStats()
// Annotates columns with stats: { min, max, nullFraction }

lowCardinality()

Identifies columns with few distinct values (like status, type, or category columns) and caches their values.

lowCardinality()
// Annotates columns with kind: 'LowCardinality' and values: ['active', 'inactive', ...]

Advanced Groundings

report(config)

Uses an LLM agent to analyze your database and generate a business context report. The agent queries each table's row count and sample data to understand what the database represents.

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

report({
  model: groq('llama-3.3-70b-versatile'),
  // Optional: cache the report
  cache: {
    get: async () => localStorage.getItem('db-report'),
    set: async (value) => localStorage.setItem('db-report', value),
  },
  // Optional: regenerate even if cached
  forceRefresh: false,
})

Execution Order

Groundings execute in the order you specify. Some groundings depend on others:

1. tables() must run first - discovers the base schema
2. views() can run independently
3. info() can run independently
4. indexes(), constraints(), rowCount() - run after tables(), annotate discovered tables
5. columnStats(), lowCardinality() - run after tables(), annotate columns
6. report() - typically runs last, uses the full context

Filtering Tables

All filter options work the same way across tables() and views():

// Include specific tables by name
tables({ filter: ['users', 'orders', 'products'] })

// Include tables matching a pattern
tables({ filter: /^public\./ })

// Custom predicate
tables({ filter: (name) => !name.includes('_archive') })

Relationship Traversal

The forward and backward options control how tables() discovers related tables:

// Start with 'orders' and follow FKs to find 'users', 'products', etc.
tables({
  filter: ['orders'],
  forward: 2,  // Follow FKs up to 2 levels deep
})

// Start with 'users' and find all tables that reference it
tables({
  filter: ['users'],
  backward: 1,  // Find immediate children only
})

// Both directions
tables({
  filter: ['orders'],
  forward: true,   // Unlimited forward traversal
  backward: true,  // Unlimited backward traversal
})

Minimal Configuration

For simple use cases, you can use a minimal grounding setup:

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

grounding: [
  postgres.tables(),
  postgres.info(),
]

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

grounding: [
  sqlite.tables(),
  sqlite.info(),
]

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

grounding: [
  sqlserver.tables(),
  sqlserver.info(),
]

Empty Grounding

Pass an empty array to disable all automatic introspection:

grounding: []

This is useful when providing custom introspection via the adapter's introspect option.

Next Steps

• PostgreSQL Adapter - PostgreSQL-specific configuration
• SQLite Adapter - SQLite-specific configuration
• SQL Server Adapter - SQL Server-specific configuration
• Teach the System - Add domain knowledge