pgnode

Getting started

Install and run pgnode in eight steps

pgnode feels like grep for databases, but powered by a local on-device AI agent. It runs against a local Ollama model, so no data leaves your machine. This page covers the essentials: install, configure, run, and stay safe.

Install pgnode

pgnode is a Python package on PyPI. Install it with pip in a virtualenv. Python 3.11+ is required.

bash

pip install pgnode==0.1.0

This installs the pgnode console script onto your PATH while the venv is active. To deactivate later, run deactivate.

Install Ollama

Ollama runs the local LLM that pgnode talks to. Pick the installer for your platform, then make sure the daemon is running on 127.0.0.1:11434.

macOS (Homebrew)

bash

brew install ollama

Linux

bash

curl -fsSL https://ollama.com/install.sh | sh

Windows

Download the installer from ollama.com/download.

Start the daemon

bash

ollama serve

On macOS and Windows, launching the Ollama app starts the daemon automatically. On Linux you usually want the systemd service or ollama serve in a terminal.

Pull a model

Pull at least one local model before configuring pgnode. The recommended default is deepseek-coder-v2:16b for SQL generation quality.

bash

ollama pull deepseek-coder-v2:16b

Smaller alternatives if you need lower memory: deepseek-coder:6.7b, qwen2.5-coder:7b. Use whatever fits in RAM.

List what is locally available at any time:

bash

ollama list

pgnode connect will read this same list and let you pick the exact name to save in config.

Run pgnode connect

pgnode connect saves your database URL, Ollama host, and exact local model name to your user config. Run it once after install. Environment variables still override the saved values when present.

Interactive

bash

pgnode connect

You will be asked to pick the connection type (direct or ssh_tunnel), enter the DATABASE_URL, the Ollama host (defaults to http://127.0.0.1:11434), and the exact model name (pgnode lists your installed models to choose from).

Flag-driven, direct connection

bash

pgnode connect \
  --connection-type direct \
  --database-url "postgresql://user:pass@localhost:5432/dbname" \
  --ollama-host "http://127.0.0.1:11434" \
  --model "deepseek-coder-v2:16b"

Flag-driven, SSH tunnel

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"

Before saving, pgnode validates everything: DB connectivity (through the tunnel if applicable), Ollama reachability, and that the model is actually installed. If any check fails, the config is not saved. Use --skip-validation only when you intentionally want to save first and fix later.

Run pgnode doctor

pgnode doctor runs all the health checks at once. Run it after connect, and any time something starts misbehaving.

bash

pgnode doctor

It prints a PASS/FAIL/WARN table for:

Config file present
DATABASE_URL, OLLAMA_HOST, and LLM_MODEL are set
Connection type (direct or ssh)
For SSH: host, user, key path, key file exists, tunnel comes up
PostgreSQL connection actually opens
Ollama is reachable on the configured host
The configured model is installed locally

Any FAIL row includes a fix hint, for example Fix: run `ollama pull <model>` when the model is missing.

Run pgnode run

pgnode run is the main loop. With no arguments it opens an interactive conversation; with a prompt argument it runs once and exits.

Interactive

bash

pgnode run

In-session commands:

/history — show recent turns from this session
/clear — clear in-session context
/tables — list tables in the connected database
/describe <table> — show columns and types
/exit or /quit — leave the 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

bash

pgnode run "list all users with limit 5"

Useful flags

--dry-run / -n — build and validate SQL, do not execute
--yes / -y — skip the confirmation prompt for writes
--memory-limit N — how many recent turns to include in context (default 5)

Sibling commands

bash

pgnode sql "top 5 customers by revenue last month"   # generate SQL only
pgnode explain "monthly revenue trend"               # SQL + short reasoning
pgnode tables                                        # list tables
pgnode describe Product                              # describe one table
pgnode history                                       # persistent local history
pgnode rerun 12                                      # rerun history entry #12

Safety model for writes

pgnode runs every generated query through a deterministic validator before touching your database. The validator is intentionally narrow: read-mostly with guarded writes.

Allowed statements

The first keyword of the SQL must be one of SELECT, INSERT, or UPDATE. Anything else is rejected before execution.

Hard-blocked keywords

The validator scans the full statement and refuses it if any of these appear, even as a side clause:

DELETEDROPTRUNCATEALTERCREATEGRANTREVOKEEXECUTECALLCOPYDOLISTENNOTIFYVACUUMANALYZEREINDEXCLUSTERREFRESH

Single statement only

Multiple semicolon-separated statements are rejected. You cannot smuggle a DROP behind a SELECT.

Confirmation for writes

When the validator accepts an INSERT or UPDATE, pgnode prints the generated SQL and asks Execute <INSERT|UPDATE> statement?. The default answer is no. Pass --yes to skip the prompt when you are scripting:

bash

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

--dry-run short-circuits earlier: SQL is generated and validated but the query never runs.

Troubleshooting common errors

Most failures come from config drift or a missing dependency. The errors below are surfaced by pgnode with a fix hint; use this table as a quick reference.

Symptom	Cause	Fix
password authentication failed	Username or password in DATABASE_URL is wrong.	Double-check credentials and re-run pgnode connect.
connection refused	Postgres host/port is wrong or the server is not running.	Verify host:port and that postgres is listening.
database … does not exist	The database or schema in DATABASE_URL does not exist.	Create the database or correct the name in DATABASE_URL.
SSH authentication failed / permission denied	Wrong SSH user or the private key is not authorized.	Verify --ssh-user and key permissions (chmod 600 on the key).
timed out / unreachable	SSH host is unreachable from your network.	Check VPN, firewall, and the SSH host/port.
address already in use	Local tunnel port is occupied by another process.	Pass --local-port 0 (auto) or pick a free port.
Model `<name>` is not installed	The configured model is not present in your local Ollama.	Run `ollama pull <name>` then re-run `pgnode doctor`.
pgnode is not configured	DATABASE_URL, OLLAMA_HOST or LLM_MODEL is missing.	Run `pgnode connect` to write the user config.

When in doubt, re-run pgnode doctor — it prints fix hints next to every failed check.