> ## Documentation Index
> Fetch the complete documentation index at: https://docs.praison.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Natural Language Postgres

> Query PostgreSQL databases using natural language

# Natural Language Postgres

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

## Quick Start

```typescript theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
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);
```

## Configuration

```typescript theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
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 theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
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 theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
// 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 theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
// 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 theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
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 theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
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 theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
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 theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
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

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](/docs/js/database) - Database adapters
* [Agent Tools](/docs/js/tools) - Creating custom tools