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:

pip install -e .

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:

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"