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

Reuse the shared model setup from Getting Started in each example.

import { DatabaseSync } from 'node:sqlite';

import { 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
  model,
  adapters: {
    main: new Sqlite({
      execute: (sql) => db.prepare(sql).all(),
      grounding: [
        sqlite.tables(),
        sqlite.views(),
        sqlite.info(),
        sqlite.indexes(),
        sqlite.constraints(),
        sqlite.rowCount(),
        sqlite.columnStats(),
        sqlite.columnValues(),
      ],
    }),
  },
});

import Database from 'better-sqlite3';

import { 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
  model,
  adapters: {
    main: new Sqlite({
      execute: (sql) => db.prepare(sql).all(),
      grounding: [
        sqlite.tables(),
        sqlite.views(),
        sqlite.info(),
        sqlite.indexes(),
        sqlite.constraints(),
        sqlite.rowCount(),
        sqlite.columnStats(),
        sqlite.columnValues(),
      ],
    }),
  },
});

import initSqlJs from 'sql.js';

import { 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
  model,
  adapters: {
    main: 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.columnValues(),
      ],
    }),
  },
});

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
`columnValues()`	Detects enum-like and low-cardinality columns

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