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

---

Installation

Install from source:

pip install -e .

Optional: install dev tooling (tests, type checks, lint):

pip install -e ".[dev]"

Environment

Create .env in project root (optional if you use pgnode connect):

• 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

First-time setup:

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:

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 (if you created one):

source venv/bin/activate

Interactive conversation (context kept only in current session):

./pgnode run

or simply:

./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):

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

SQL-only generation (no execution):

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

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

./pgnode explain "monthly revenue trend"

Schema helpers:

./pgnode tables
./pgnode describe Product

Environment and connectivity checks:

./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:

./pgnode history
./pgnode rerun 12

Write behavior:

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

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

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

---

Contributing

Thanks for wanting to help. Contributions are welcome and appreciated.

Ways to contribute

• Report bugs with reproduction steps
• Propose features with clear use cases
• Improve docs and examples
• Add tests and validation for edge cases

Local development

# Run tests
pytest

# Lint and format checks
ruff check .
ruff format --check .

# Type checking
mypy app

Project structure (high level)

• app/ core agent, CLI, database, and LLM logic
• tests/ unit and integration tests
• web/ documentation site

Pull request checklist

• Add or update tests when behavior changes
• Keep docs in sync with CLI behavior
• Ensure pytest, ruff, and mypy pass
• Keep commits focused and descriptive

---

Open source notes

• This project is designed to stay local-first: no cloud calls are required.
• Avoid adding any telemetry or data collection without clear user consent.
• Please do not commit credentials or private endpoints.