SQLite Adapter

The SQLite adapter provides lightweight embedded database support for Text2SQL. Perfect for local development, testing, and single-file databases.

Installation

npm install @deepagents/text2sql
npm install better-sqlite3
npm install -D @types/better-sqlite3

Basic Usage

import { Sqlite, tables, views, info } from '@deepagents/text2sql/sqlite';
import Database from 'better-sqlite3';

const db = new Database('./my-database.db');

const adapter = new Sqlite({
  execute: (sql) => db.prepare(sql).all(),
  grounding: [
    tables(),
    views(),
    info()
  ]
});

Configuration Options

interface SqliteAdapterOptions {
  // Required: Function to execute SQL queries
  execute: (sql: string) => any[] | Promise<any[]>;
  
  // Optional: Custom validation function
  validate?: (sql: string) => string | void | Promise<string | void>;
  
  // Required: Grounding functions for schema introspection
  grounding: GroundingFn[];
}

Execute Function

The execute function receives SQL and returns query results.

Using better-sqlite3

import Database from 'better-sqlite3';

const db = new Database('./database.db');

const adapter = new Sqlite({
  execute: (sql) => db.prepare(sql).all(),
  grounding: [tables()]
});

Using node-sqlite3

import sqlite3 from 'sqlite3';
import { open } from 'sqlite';

const db = await open({
  filename: './database.db',
  driver: sqlite3.Database
});

const adapter = new Sqlite({
  execute: async (sql) => {
    return await db.all(sql);
  },
  grounding: [tables()]
});

Custom Validation

By default, validation uses EXPLAIN to check SQL syntax. You can provide a custom validator:

const adapter = new Sqlite({
  execute: (sql) => db.prepare(sql).all(),
  validate: async (sql) => {
    // Custom validation logic
    if (sql.includes('DROP')) {
      throw new Error('DROP statements not allowed');
    }
    
    // Use EXPLAIN for syntax check
    try {
      db.prepare(`EXPLAIN ${sql}`).all();
    } catch (error) {
      throw error;
    }
  },
  grounding: [tables()]
});

Grounding Functions

All SQLite grounding functions are available:

import {
  tables,        // Tables, columns, and primary keys
  views,         // Database views
  info,          // SQLite version and metadata
  indexes,       // Index information
  constraints,   // UNIQUE, CHECK, FOREIGN KEY constraints
  rowCount,      // Table row counts and size hints
  columnStats,   // Min/max/null distribution
  columnValues   // Distinct values for low-cardinality columns
} from '@deepagents/text2sql/sqlite';

const adapter = new Sqlite({
  execute: (sql) => db.prepare(sql).all(),
  grounding: [
    tables(),
    views(),
    info(),
    indexes(),
    constraints(),
    rowCount(),
    columnStats(),
    columnValues({ limit: 20 })
  ]
});

SQLite-Specific Features

No Schema Support

SQLite does not have separate schemas. All tables are in a single namespace:

// No schema prefix needed
const sql = 'SELECT * FROM customers';

Identifier Quoting

SQLite uses double quotes for identifiers:

adapter.quoteIdentifier('my_table');  // "my_table"
adapter.quoteIdentifier('my column'); // "my column"

String Escaping

SQLite escapes single quotes by doubling them:

adapter.escape("O'Reilly");  // O''Reilly

LIMIT Clause

SQLite uses LIMIT for row limits:

SELECT * FROM customers LIMIT 10

Error Handling

SQLite errors are automatically classified:

try {
  const sql = await text2sql.toSql('Show me invalid_table');
} catch (error) {
  console.error(error);
}

Error types:

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	Use table aliases to disambiguate
`near "X": syntax error`	SYNTAX_ERROR	Review query structure, keywords, and punctuation
`no tables specified`	SYNTAX_ERROR	Add FROM clause
`attempt to write a readonly database`	CONSTRAINT_ERROR	Database is read-only

Complete Example

sqlite-example.ts

import { Text2Sql } from '@deepagents/text2sql';
import { Sqlite, tables, views, info, indexes } from '@deepagents/text2sql/sqlite';
import { InMemoryContextStore } from '@deepagents/context';
import { openai } from '@ai-sdk/openai';
import Database from 'better-sqlite3';

// Create database
const db = new Database('./ecommerce.db');

// Create sample schema
db.exec(`
  CREATE TABLE IF NOT EXISTS customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
  );
  
  CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total REAL NOT NULL,
    status TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
  );
  
  CREATE INDEX IF NOT EXISTS idx_orders_customer
    ON orders(customer_id);
  
  CREATE INDEX IF NOT EXISTS idx_orders_status
    ON orders(status);
`);

// Create adapter
const adapter = new Sqlite({
  execute: (sql) => {
    console.log('Executing:', sql);
    return db.prepare(sql).all();
  },
  grounding: [
    tables(),
    views(),
    info(),
    indexes()
  ]
});

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

// Generate queries
async function demo() {
  // Simple query
  const sql1 = await text2sql.toSql('Show all customers');
  console.log('Query 1:', sql1);
  console.log('Results:', adapter.execute(sql1));
  
  // Join query
  const sql2 = await text2sql.toSql('Show customer names with their order count');
  console.log('Query 2:', sql2);
  console.log('Results:', adapter.execute(sql2));
  
  // Aggregation
  const sql3 = await text2sql.toSql('What is the total revenue by customer?');
  console.log('Query 3:', sql3);
  console.log('Results:', adapter.execute(sql3));
}

demo().catch(console.error);

In-Memory Databases

Perfect for testing:

import Database from 'better-sqlite3';

const db = new Database(':memory:');

// Create test schema
db.exec(`
  CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
  INSERT INTO users (name) VALUES ('Alice'), ('Bob');
`);

const adapter = new Sqlite({
  execute: (sql) => db.prepare(sql).all(),
  grounding: [tables()]
});

const sql = await text2sql.toSql('Show all users');
console.log(sql);
// SELECT * FROM users

Read-Only Databases

Open database in read-only mode:

import Database from 'better-sqlite3';

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

const adapter = new Sqlite({
  execute: (sql) => db.prepare(sql).all(),
  grounding: [tables()]
});

Performance Tips

1. Use Indexes

SQLite benefits greatly from indexes:

CREATE INDEX idx_customers_email ON customers(email);
CREATE INDEX idx_orders_created_at ON orders(created_at);

2. Enable WAL Mode

Write-Ahead Logging improves concurrency:

const db = new Database('./database.db');
db.pragma('journal_mode = WAL');

3. Optimize Introspection

Minimize grounding functions for faster startup:

// Fast introspection
grounding: [tables(), info()]

// Slower but more comprehensive
grounding: [
  tables(),
  views(),
  info(),
  indexes(),
  constraints(),
  rowCount(),
  columnStats()
]

4. Prepare Statements

Reuse prepared statements:

const stmts = new Map();

const adapter = new Sqlite({
  execute: (sql) => {
    let stmt = stmts.get(sql);
    if (!stmt) {
      stmt = db.prepare(sql);
      stmts.set(sql, stmt);
    }
    return stmt.all();
  },
  grounding: [tables()]
});

Limitations

No Advanced Constraints

SQLite has limited constraint support:

No CHECK constraints with subqueries
No partial indexes (before SQLite 3.8)
No generated columns (before SQLite 3.31)

Limited ALTER TABLE

SQLite cannot:

Drop columns (before SQLite 3.35)
Modify column types
Add constraints to existing tables

No Stored Procedures

SQLite does not support:

Stored procedures
User-defined functions (in SQL)
Triggers with multiple statements

Best Practices

1. Use Transactions

db.exec('BEGIN');
try {
  // Multiple operations
  db.exec('INSERT INTO ...');
  db.exec('UPDATE ...');
  db.exec('COMMIT');
} catch (error) {
  db.exec('ROLLBACK');
  throw error;
}

2. Close Connections

process.on('exit', () => db.close());
process.on('SIGINT', () => {
  db.close();
  process.exit(0);
});

3. Backup Regularly

import { copyFileSync } from 'fs';

setInterval(() => {
  const timestamp = new Date().toISOString();
  copyFileSync('./database.db', `./backups/database-${timestamp}.db`);
}, 3600000);  // Hourly backups

Next Steps

PostgreSQL Adapter

Setup for PostgreSQL databases

Teachables

Inject domain knowledge

Getting Started

Complete getting started guide

API Reference

Full adapter API documentation

Overview

Guides

Database Adapters

API Reference

​SQLite Adapter

​Installation

​Basic Usage

​Configuration Options

​Execute Function

​Using better-sqlite3

​Using node-sqlite3

​Custom Validation

​Grounding Functions

​SQLite-Specific Features

​No Schema Support

​Identifier Quoting

​String Escaping

​LIMIT Clause

​Error Handling

​Complete Example

​In-Memory Databases

​Read-Only Databases

​Performance Tips

​1. Use Indexes

​2. Enable WAL Mode

​3. Optimize Introspection

​4. Prepare Statements

​Limitations

​No Advanced Constraints

​Limited ALTER TABLE

​No Stored Procedures

​Best Practices

​1. Use Transactions

​2. Close Connections

​3. Backup Regularly

​Next Steps

PostgreSQL Adapter

Teachables

Getting Started

API Reference

SQLite Adapter

Installation

Basic Usage

Configuration Options

Execute Function

Using better-sqlite3

Using node-sqlite3

Custom Validation

Grounding Functions

SQLite-Specific Features

No Schema Support

Identifier Quoting

String Escaping

LIMIT Clause

Error Handling

Complete Example

In-Memory Databases

Read-Only Databases

Performance Tips

1. Use Indexes

2. Enable WAL Mode

3. Optimize Introspection

4. Prepare Statements

Limitations

No Advanced Constraints

Limited ALTER TABLE

No Stored Procedures

Best Practices

1. Use Transactions

2. Close Connections

3. Backup Regularly

Next Steps