Natural Language Postgres

Query PostgreSQL databases using natural language. The agent translates your questions into SQL and returns results.

Quick Start

Simple Usage

import { createNLPostgres } from 'praisonai-ts';

const db = await createNLPostgres({
  connectionUrl: process.env.DATABASE_URL!,
  readOnly: true, // Safety: read-only by default
});

// Query with natural language
const result = await db.query('Show me all users who signed up last month');
console.log(result.rows);
console.log('SQL:', result.sql);

With Configuration

import { createNLPostgres } from 'praisonai-ts';

const db = await createNLPostgres({
  // Connection
  connectionUrl: process.env.DATABASE_URL!,
  schema: 'public',
  
  // Safety
  readOnly: true,           // Only SELECT queries (default: true)
  allowedTables: ['users', 'orders'], // Whitelist tables
  blockedTables: ['secrets'],         // Blacklist tables
  
  // Limits
  maxRows: 100,             // Max rows returned (default: 100)
  timeout: 30000,           // Query timeout in ms
});

As Agent Tool

import { Agent, createPostgresTool } from 'praisonai-ts';

const dbTool = await createPostgresTool({
  connectionUrl: process.env.DATABASE_URL!,
  readOnly: true,
});

const agent = new Agent({
  name: 'DataAnalyst',
  instructions: 'You analyze data from the database. Always explain your findings.',
  tools: [dbTool],
});

const response = await agent.chat('What are the top 10 products by revenue?');

Schema Introspection

// Get database schema
const schema = await db.getSchema();

console.log('Tables:', schema.tables.map(t => t.name));

// Get specific table schema
const usersTable = await db.getTableSchema('users');
console.log('Columns:', usersTable.columns);

Query Examples

// Simple query
const users = await db.query('List all active users');

// Aggregation
const stats = await db.query('What is the average order value by month?');

// Joins
const orders = await db.query('Show orders with customer names from last week');

// Complex query
const analysis = await db.query(
  'Find customers who made more than 5 purchases but haven\'t ordered in 30 days'
);

Query Result

interface NLQueryResult {
  query: string;      // Original natural language query
  sql: string;        // Generated SQL
  result: {
    rows: any[];      // Query results
    rowCount: number; // Number of rows
    fields: Array<{ name: string; type: string }>;
  };
  explanation?: string; // Optional explanation
}

Safety Features

Read-Only Mode (Default)

const db = await createNLPostgres({
  connectionUrl: process.env.DATABASE_URL!,
  readOnly: true, // Only SELECT queries allowed
});

// This will throw an error
await db.query('Delete all users'); // Error: Write operations not allowed

Table Restrictions

const db = await createNLPostgres({
  connectionUrl: process.env.DATABASE_URL!,
  allowedTables: ['products', 'orders'], // Only these tables
  blockedTables: ['users', 'payments'],  // Never these tables
});

Parameterized Queries

All generated SQL uses parameterized queries to prevent SQL injection.

Write Operations (Use with Caution)

const db = await createNLPostgres({
  connectionUrl: process.env.DATABASE_URL!,
  readOnly: false, // Enable write operations
});

// Now write operations are allowed
await db.query('Update the status of order 123 to shipped');

Environment Variables

Variable	Required	Description
`DATABASE_URL`	Yes	PostgreSQL connection URL
`OPENAI_API_KEY`	Yes	For NL to SQL translation

Connection URL Format

postgresql://user:password@host:port/database?sslmode=require

Best Practices

Guidelines

Always use read-only mode unless writes are necessary
Whitelist tables to limit access
Set row limits to prevent large result sets
Review generated SQL before executing in production
Use connection pooling for high-traffic applications

Database

Database adapters

Agent Tools

Creating custom tools

​Quick Start

​As Agent Tool

​Schema Introspection

​Query Examples

​Query Result

​Safety Features

​Read-Only Mode (Default)

​Table Restrictions

​Parameterized Queries

​Write Operations (Use with Caution)

​Environment Variables

​Connection URL Format

​Best Practices

​Related