Metadata-Version: 2.4
Name: pgnode
Version: 0.1.0
Summary: Terminal-native local AI agent for PostgreSQL databases.
Author: Chayan Mann
License-Expression: MIT
Project-URL: Homepage, https://github.com/chayan-mann/pgnode
Project-URL: Repository, https://github.com/chayan-mann/pgnode
Project-URL: Changelog, https://github.com/chayan-mann/pgnode/blob/main/CHANGELOG.md
Classifier: Development Status :: 3 - Alpha
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Requires-Python: >=3.11
Description-Content-Type: text/markdown
Requires-Dist: ollama==0.3.3
Requires-Dist: psycopg[binary]>=3.2.0
Requires-Dist: python-dotenv==1.0.1
Requires-Dist: questionary>=2.0.1
Requires-Dist: rich>=13.7.1
Requires-Dist: SQLAlchemy>=2.0.44
Requires-Dist: sshtunnel>=0.4.0
Requires-Dist: typer>=0.16.0
Provides-Extra: dev
Requires-Dist: build>=1.2.0; extra == "dev"
Requires-Dist: mypy>=1.11.0; extra == "dev"
Requires-Dist: pytest>=8.3.0; extra == "dev"
Requires-Dist: ruff>=0.6.0; extra == "dev"
Requires-Dist: twine>=5.1.0; extra == "dev"

# **pgnode**

**Local LLM (Ollama) + PostgreSQL Agent**

An offline-first AI agent that converts natural language into **validated SQL queries** and executes them safely on your PostgreSQL database.

---

## Overview

**pgnode** is a local AI-powered database operator. It connects to your PostgreSQL instance and allows you to interact with your data using plain English while ensuring safety, control, and privacy.

* No external APIs
* No data leaves your system
* Fully local using Ollama

---

## Core Features

* Natural language → SQL conversion
* Safe query execution with validation layer
* Schema-aware query generation
* Works with existing PostgreSQL databases (pgAdmin compatible)
* CLI-first interface (fast and developer-friendly)
* Fully offline with local LLM

---

## Architecture

```
User Prompt
    ↓
Agent (planner)
    ↓
SQL Generator (LLM)
    ↓
Validator (safety layer)
    ↓
Query Executor (PostgreSQL)
    ↓
Response
```

---

## Tech Stack

### Core

* Python
* PostgreSQL
* Ollama (local LLM runtime)

### Libraries

* SQLAlchemy → DB interaction
* psycopg2 → PostgreSQL adapter
* Typer → CLI interface
* FastAPI (optional) → API layer
* LlamaIndex / FAISS (optional) → schema-aware retrieval

---

## Environment

Create `.env` in project root:

- `OLLAMA_HOST=http://127.0.0.1:11434`
- `DATABASE_URL=postgresql+psycopg2://user:pass@localhost:5432/dbname`
- `LLM_MODEL=deepseek-coder:6.7b` (optional)

## Run CLI

Install from source while developing:

```bash
pip install -e .
```

First-time setup:

```bash
pgnode connect
pgnode doctor
```

`connect` saves your database URL, Ollama host, and exact local model name to your user config. Environment variables still override saved config when present.

SSH tunnel databases are supported too. Choose `ssh` during `pgnode connect` or use flags:

```bash
pgnode connect \
  --connection-type ssh \
  --database-url "postgresql://user:pass@internal-db:5432/dbname" \
  --ssh-host "bastion.example.com" \
  --ssh-port 22 \
  --ssh-user "ubuntu" \
  --ssh-key-path "~/.ssh/id_rsa" \
  --remote-host "127.0.0.1" \
  --remote-port 5432 \
  --local-port 0 \
  --model "deepseek-coder-v2:16b"
```

Activate venv once:

```bash
source venv/bin/activate
```

Interactive conversation (context kept only in current session):

```bash
./pgnode run
```

or simply:

```bash
./pgnode
```

Useful chat commands:

- `/history` show recent turns
- `/clear` clear current session context
- `/exit` or `/quit` leave session
- Natural language meta-questions also work, e.g.:
  - `what question did i ask you last`
  - `which query did you execute last`
  - `last result`

One-shot mode (no prior context):

```bash
./pgnode run "list all users with limit 5"
```

SQL-only generation (no execution):

```bash
./pgnode sql "top 5 customers by revenue last month"
```

Explain mode (SQL + short reasoning, no execution):

```bash
./pgnode explain "monthly revenue trend"
```

Schema helpers:

```bash
./pgnode tables
./pgnode describe Product
```

Environment and connectivity checks:

```bash
./pgnode config
./pgnode config-set --model "deepseek-coder-v2:16b"
./pgnode config-set --database-url "postgresql://user:pass@localhost:5432/dbname"
./pgnode config-set --connection-type ssh --ssh-host "bastion.example.com" --ssh-user "ubuntu" --ssh-key-path "~/.ssh/id_rsa" --remote-host "127.0.0.1" --remote-port 5432
./pgnode doctor
./pgnode models
```

Persistent local history:

```bash
./pgnode history
./pgnode rerun 12
```

Write behavior:

```bash
./pgnode run "update users set phone='999' where id=1"
```

`INSERT/UPDATE` now require confirmation by default. Use `--yes` to skip prompt.

```bash
./pgnode run --yes "update users set phone='999' where id=1"
```
