Praison AI

Natural Language Postgres

Query PostgreSQL databases using natural language

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

Diagram

User

NL Postgres

Agent

Quick Start

  1. Simple Usage
    typescript
    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);
  2. With Configuration
    typescript
    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

typescript
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

typescript
// 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

typescript
// 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

typescript
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)

typescript
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

typescript
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)

typescript
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

VariableRequiredDescription
DATABASE_URLYesPostgreSQL connection URL
OPENAI_API_KEYYesFor NL to SQL translation

Connection URL Format

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

Best Practices

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

Related

Database

Database adapters

 Agent Tools

Creating custom tools