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

# 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.

```mermaid theme={"theme":{"light":"vitesse-light","dark":"vitesse-dark"}}
graph LR
    User([User]) --> NL[NL Postgres]
    NL --> Agent[Agent]

    classDef agent fill:#8B0000,stroke:#7C90A0,color:#fff
    classDef tool fill:#189AB4,stroke:#7C90A0,color:#fff

    class Agent agent
    class User,NL tool
    classDef agent fill:#8B0000,color:#fff
    classDef tool fill:#189AB4,color:#fff

```

## Quick Start

**1. Simple Usage**

```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);
```

**2. With 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

> [accordion] 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

> [card] [Database](https://praison.ai/docs/js/database.md)
>
> Database adapters

> [card] [Agent Tools](https://praison.ai/docs/js/tools.md)
>
> Creating custom tools


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.