Text2SQL - Natural Language to SQL

The SQLite adapter works with SQLite databases, supporting both file-based and in-memory databases. It uses PRAGMA commands for schema introspection.

Basic Setup

import { DatabaseSync } from 'node:sqlite';

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

const db = new DatabaseSync('./your-database.db', { readOnly: true });

const text2sql = new Text2Sql({
  version: 'v1', // Bump this when your schema changes
  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 Database from 'better-sqlite3';

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

const db = new Database('./database.db', { readonly: true });

const text2sql = new Text2Sql({
  version: 'v1', // Bump this when your schema changes
  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 initSqlJs from 'sql.js';

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

const SQL = await initSqlJs();
const db = new SQL.Database(data);

const text2sql = new Text2Sql({
  version: 'v1', // Bump this when your schema changes
  adapter: new Sqlite({
    execute: (sql) => {
      const stmt = db.prepare(sql);
      const rows = [];
      while (stmt.step()) {
        rows.push(stmt.getAsObject());
      }
      stmt.free();
      return rows;
    },
    grounding: [
      sqlite.tables(),
      sqlite.views(),
      sqlite.info(),
      sqlite.indexes(),
      sqlite.constraints(),
      sqlite.rowCount(),
      sqlite.columnStats(),
      sqlite.lowCardinality(),
    ],
  }),
  history: new InMemoryHistory(),
});

Configuration Options

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

new Sqlite({
  // Required: Execute SQL queries
  execute: (sql) => db.prepare(sql).all(),

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

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

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

Available Grounding Functions

The following grounding functions are available for SQLite. Import them from @deepagents/text2sql/sqlite:

Function	Description
`tables()`	Discovers tables from `sqlite_master`, extracts columns using `PRAGMA table_info`, detects primary keys
`views()`	Discovers database views
`info()`	Collects SQLite version and database info
`indexes()`	Identifies indexes using `PRAGMA index_list` and `PRAGMA index_info`
`constraints()`	Maps foreign keys using `PRAGMA foreign_key_list`
`rowCount()`	Counts rows and classifies tables by size
`columnStats()`	Collects min, max, null fraction for columns
`lowCardinality()`	Detects columns with 20 or fewer distinct values
`report()`	Uses an LLM to generate a business context report from your database

See Grounding for detailed configuration options.

Error Handling

The adapter maps common SQLite errors to helpful suggestions:

Error Pattern	Type	Suggestion
`no such table: X`	MISSING_TABLE	Check the database schema for the correct table name
`no such column: X`	INVALID_COLUMN	Check the table schema for correct column names
`ambiguous column name: X`	INVALID_COLUMN	The column exists in multiple joined tables
`near "X": syntax error`	SYNTAX_ERROR	Review the query structure and punctuation
`attempt to write a readonly database`	CONSTRAINT_ERROR	Read-only queries should not trigger this

Query Validation

By default, validation uses EXPLAIN to check query syntax without execution:

new Sqlite({
  execute: (sql) => db.prepare(sql).all(),
  // Default validation
  validate: (sql) => {
    db.prepare(`EXPLAIN ${sql}`).all();
  },
});

Execute Function Return Types

The execute function can return either:

// Array of rows directly (recommended)
execute: (sql) => db.prepare(sql).all();

// Or object with rows property
execute: (sql) => ({ rows: db.prepare(sql).all() });

In-Memory Databases

For testing or temporary data:

import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec(`
  CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
  INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob');
`);

new Sqlite({
  execute: (sql) => db.prepare(sql).all(),
});

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

SQLite

On this page