Text2SQL - Natural Language to SQL

The PostgreSQL adapter provides full support for PostgreSQL databases with automatic schema introspection, index detection, and helpful error messages.

Installation

The pg driver is a peer dependency. Install it alongside @deepagents/text2sql:

npm install @deepagents/text2sql pg

Basic Setup

import pg from 'pg';

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

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
});

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

Configuration Options

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

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

  // Required: Grounding functions for schema introspection
  grounding: [
    postgres.tables(),
    postgres.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
  schemas: ['public', 'analytics'],
});

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

Schema Filtering

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

new Postgres({
  execute: async (sql) => pool.query(sql).then((r) => r.rows),
  schemas: ['public', 'sales', 'inventory'],
});

Available Grounding Functions

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

Function	Description
`tables()`	Discovers tables and columns, detects primary keys from table constraints
`views()`	Discovers database views
`info()`	Collects PostgreSQL version and database info
`indexes()`	Identifies indexes including unique, clustered, and B-tree types
`constraints()`	Maps foreign key relationships and other constraints
`rowCount()`	Counts rows and classifies tables by size (tiny, small, medium, large, huge)
`columnStats()`	Collects min, max, null fraction for numeric/date 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 PostgreSQL error codes to helpful suggestions:

Error Code	Type	Suggestion
42P01	MISSING_TABLE	Check the database schema for the correct table name
42703	INVALID_COLUMN	Verify the column exists and use table aliases
42601	SYNTAX_ERROR	Review keywords, punctuation, and query shape
42P10	INVALID_COLUMN	Check column names in GROUP BY/SELECT
42883	INVALID_FUNCTION	Confirm function name and argument types

Execute Function Return Types

The execute function can return either:

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

// Or object with rows property
execute: async (sql) => {
  return await pool.query(sql); // { rows: [...] }
};

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

This helps the AI understand query performance implications.

PostgreSQL

On this page