PostgreSQL Adapter

The PostgreSQL adapter provides comprehensive support for PostgreSQL databases with advanced features like schemas, detailed statistics, and constraint introspection.

Installation

npm install @deepagents/text2sql
npm install pg
npm install -D @types/pg

Basic Usage

import { Postgres, tables, views, info, indexes, constraints } from '@deepagents/text2sql/postgres';
import pg from 'pg';

const pool = new pg.Pool({
  host: 'localhost',
  database: 'mydb',
  user: 'postgres',
  password: 'password'
});

const adapter = new Postgres({
  execute: async (sql) => {
    const result = await pool.query(sql);
    return result.rows;
  },
  grounding: [
    tables(),
    views(),
    info(),
    indexes(),
    constraints()
  ]
});

Configuration Options

interface PostgresAdapterOptions {
  // Required: Function to execute SQL queries
  execute: ExecuteFunction;
  
  // Optional: Custom validation function
  validate?: ValidateFunction;
  
  // Required: Grounding functions
  grounding: GroundingFn[];
  
  // Optional: Limit introspection to specific schemas
  schemas?: string[];
}

Execute Function

Using pg (node-postgres)

import pg from 'pg';

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

const adapter = new Postgres({
  execute: async (sql) => {
    const result = await pool.query(sql);
    return result.rows;
  },
  grounding: [tables()]
});

Using pg with connection string

const adapter = new Postgres({
  execute: async (sql) => {
    const client = new pg.Client({
      connectionString: 'postgresql://user:pass@localhost:5432/dbname'
    });
    await client.connect();
    try {
      const result = await client.query(sql);
      return result.rows;
    } finally {
      await client.end();
    }
  },
  grounding: [tables()]
});

Schema Filtering

Limit introspection to specific schemas:

const adapter = new Postgres({
  execute: async (sql) => {
    const result = await pool.query(sql);
    return result.rows;
  },
  grounding: [tables(), constraints()],
  schemas: ['public', 'analytics']  // Only these schemas
});

Without schemas, system schemas are excluded automatically:

pg_catalog
information_schema

Grounding Functions

All PostgreSQL grounding functions:

import {
  tables,        // Tables, columns, data types
  views,         // Database views
  info,          // PostgreSQL version and metadata
  indexes,       // Index information (btree, hash, gin, gist)
  constraints,   // PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK
  rowCount,      // Table row counts
  columnStats,   // Min/max/null/correlation from pg_stats
  columnValues   // Distinct values for enums
} from '@deepagents/text2sql/postgres';

const adapter = new Postgres({
  execute: async (sql) => {
    const result = await pool.query(sql);
    return result.rows;
  },
  grounding: [
    tables(),
    views(),
    info(),
    indexes(),
    constraints(),
    rowCount(),
    columnStats(),
    columnValues({ limit: 20 })
  ]
});

PostgreSQL-Specific Features

Schema Support

PostgreSQL organizes tables into schemas:

// Tables are qualified with schema
const sql = 'SELECT * FROM public.customers';

// Cross-schema joins
const sql2 = `
  SELECT * 
  FROM public.customers 
  JOIN analytics.events ON customers.id = events.customer_id
`;

Identifier Quoting

PostgreSQL uses double quotes:

adapter.quoteIdentifier('my_table');    // "my_table"
adapter.quoteIdentifier('public');      // "public"
adapter.quoteIdentifier('My Column');   // "My Column"

String Escaping

PostgreSQL escapes single quotes by doubling:

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

LIMIT Clause

SELECT * FROM customers LIMIT 10

Advanced Statistics

PostgreSQL provides rich column statistics from pg_stats:

import { columnStats } from '@deepagents/text2sql/postgres';

const adapter = new Postgres({
  // ...
  grounding: [
    tables(),
    columnStats()  // Includes correlation, n_distinct, and more
  ]
});

Statistics include:

correlation - Physical row order correlation with logical sort order
n_distinct - Estimated number of distinct values
null_frac - Fraction of NULL values
min/max - Minimum and maximum values

Error Handling

PostgreSQL errors are classified by error code:

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

Error types:

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	Columns in GROUP BY/SELECT must exist
42883	INVALID_FUNCTION	Function or operator not recognized

Complete Example

postgres-example.ts

import { Text2Sql } from '@deepagents/text2sql';
import { 
  Postgres, 
  tables, 
  views, 
  info, 
  indexes, 
  constraints 
} from '@deepagents/text2sql/postgres';
import { InMemoryContextStore, term, hint } from '@deepagents/context';
import { openai } from '@ai-sdk/openai';
import pg from 'pg';

// Create connection pool
const pool = new pg.Pool({
  host: 'localhost',
  database: 'ecommerce',
  user: 'postgres',
  password: 'password',
  max: 20,
  idleTimeoutMillis: 30000
});

// Create adapter
const adapter = new Postgres({
  execute: async (sql) => {
    const result = await pool.query(sql);
    return result.rows;
  },
  grounding: [
    tables(),
    views(),
    info(),
    indexes(),
    constraints()
  ],
  schemas: ['public']  // Only public schema
});

// Create Text2SQL with domain knowledge
const text2sql = new Text2Sql({
  version: 'v1',
  model: openai('gpt-4o'),
  adapter,
  store: new InMemoryContextStore(),
  teachingsOptions: {
    fragments: [
      term('MRR', 'monthly recurring revenue'),
      hint('Exclude test accounts with email LIKE "%@test.com"')
    ]
  }
});

// Generate queries
async function demo() {
  try {
    // Simple query
    const sql1 = await text2sql.toSql('Show all customers from California');
    console.log('Query 1:', sql1);
    const results1 = await pool.query(sql1);
    console.log('Results:', results1.rows);
    
    // Aggregation with JOIN
    const sql2 = await text2sql.toSql('Show customer names with order count');
    console.log('Query 2:', sql2);
    const results2 = await pool.query(sql2);
    console.log('Results:', results2.rows);
    
    // Complex query with business term
    const sql3 = await text2sql.toSql('What is MRR trend over last 6 months?');
    console.log('Query 3:', sql3);
    const results3 = await pool.query(sql3);
    console.log('Results:', results3.rows);
  } catch (error) {
    console.error('Error:', error);
  } finally {
    await pool.end();
  }
}

demo();

Connection Pooling

Use connection pooling for better performance:

import pg from 'pg';

const pool = new pg.Pool({
  host: 'localhost',
  database: 'mydb',
  user: 'postgres',
  password: 'password',
  max: 20,                    // Max clients
  idleTimeoutMillis: 30000,   // Close idle clients after 30s
  connectionTimeoutMillis: 2000  // Wait 2s for connection
});

const adapter = new Postgres({
  execute: async (sql) => {
    const result = await pool.query(sql);
    return result.rows;
  },
  grounding: [tables()]
});

// Clean shutdown
process.on('SIGTERM', async () => {
  await pool.end();
  process.exit(0);
});

SSL Connections

Connect securely:

import pg from 'pg';
import fs from 'fs';

const pool = new pg.Pool({
  host: 'db.example.com',
  database: 'mydb',
  user: 'postgres',
  password: 'password',
  ssl: {
    rejectUnauthorized: true,
    ca: fs.readFileSync('./ca-cert.pem').toString()
  }
});

Performance Tips

1. Use Indexes

PostgreSQL supports multiple index types:

-- B-tree (default, good for equality and range queries)
CREATE INDEX idx_customers_email ON customers(email);

-- GIN (good for JSONB, arrays, full-text search)
CREATE INDEX idx_products_tags ON products USING GIN (tags);

-- GiST (good for geometric data, full-text search)
CREATE INDEX idx_locations ON stores USING GIST (location);

-- Hash (good for equality comparisons only)
CREATE INDEX idx_orders_status ON orders USING HASH (status);

2. Analyze Statistics

Keep statistics up to date:

ANALYZE customers;
ANALYZE orders;

-- Or analyze all tables
ANALYZE;

3. Use Partial Indexes

Index only relevant rows:

CREATE INDEX idx_active_customers 
  ON customers(email) 
  WHERE status = 'active';

4. Optimize Introspection

// Fast introspection (recommended for large databases)
grounding: [
  tables(),
  constraints()
]

// Comprehensive introspection (slower)
grounding: [
  tables(),
  views(),
  info(),
  indexes(),
  constraints(),
  rowCount(),
  columnStats(),
  columnValues()
]

Advanced Features

JSON Support

PostgreSQL JSON columns work seamlessly:

const sql = await text2sql.toSql(
  'Show customers where metadata contains key "premium"'
);
// SELECT * FROM customers WHERE metadata->>'premium' IS NOT NULL

Array Support

const sql = await text2sql.toSql(
  'Show products with tag "electronics"'
);
// SELECT * FROM products WHERE 'electronics' = ANY(tags)

Full-Text Search

const sql = await text2sql.toSql(
  'Search products containing "laptop" in description'
);
// SELECT * FROM products 
// WHERE to_tsvector('english', description) @@ to_tsquery('laptop')

Window Functions

const sql = await text2sql.toSql(
  'Show top 3 orders per customer by amount'
);
// Uses ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC)

Best Practices

1. Use Schemas for Organization

CREATE SCHEMA analytics;
CREATE SCHEMA staging;

CREATE TABLE analytics.daily_revenue (...);
CREATE TABLE staging.import_data (...);

2. Set Search Path

const pool = new pg.Pool({
  // ...
  options: '-c search_path=public,analytics'
});

3. Use Constraints

ALTER TABLE orders 
  ADD CONSTRAINT fk_customer 
  FOREIGN KEY (customer_id) REFERENCES customers(id);

ALTER TABLE orders
  ADD CONSTRAINT check_positive_amount
  CHECK (amount > 0);

4. Monitor Performance

-- Check slow queries
SELECT * FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Check index usage
SELECT * FROM pg_stat_user_indexes
WHERE idx_scan = 0;

Next Steps

SQL Server Adapter

Setup for SQL Server databases

MySQL Adapter

Setup for MySQL and MariaDB

Teachables

Inject domain knowledge

API Reference

Full adapter API documentation

Overview

Guides

Database Adapters

API Reference

​PostgreSQL Adapter

​Installation

​Basic Usage

​Configuration Options

​Execute Function

​Using pg (node-postgres)

​Using pg with connection string

​Schema Filtering

​Grounding Functions

​PostgreSQL-Specific Features

​Schema Support

​Identifier Quoting

​String Escaping

​LIMIT Clause

​Advanced Statistics

​Error Handling

​Complete Example

​Connection Pooling

​SSL Connections

​Performance Tips

​1. Use Indexes

​2. Analyze Statistics

​3. Use Partial Indexes

​4. Optimize Introspection

​Advanced Features

​JSON Support

​Array Support

​Full-Text Search

​Window Functions

​Best Practices

​1. Use Schemas for Organization

​2. Set Search Path

​3. Use Constraints

​4. Monitor Performance

​Next Steps

SQL Server Adapter

MySQL Adapter

Teachables

API Reference

PostgreSQL Adapter

Installation

Basic Usage

Configuration Options

Execute Function

Using pg (node-postgres)

Using pg with connection string

Schema Filtering

Grounding Functions

PostgreSQL-Specific Features

Schema Support

Identifier Quoting

String Escaping

LIMIT Clause

Advanced Statistics

Error Handling

Complete Example

Connection Pooling

SSL Connections

Performance Tips

1. Use Indexes

2. Analyze Statistics

3. Use Partial Indexes

4. Optimize Introspection

Advanced Features

JSON Support

Array Support

Full-Text Search

Window Functions

Best Practices

1. Use Schemas for Organization

2. Set Search Path

3. Use Constraints

4. Monitor Performance

Next Steps