Introduction

pgvis turns any PostgreSQL or SQLite database into a fully-featured API — a PostgREST-compatible REST endpoint, Model Context Protocol (MCP) tools for AI agents, and an OpenAPI 3.0 document — from a single Rust engine with zero glue code.

The Problem

Building database APIs is repetitive. Building typed MCP tools for AI agents is new and manual. Keeping REST endpoints, API documentation, and agent tooling consistent is painful. And when you want to support more than one database, maintaining two query builders doubles the work.

The Solution

Point pgvis at a database. It introspects the schema at startup, reloads it when the database signals a change, and serves that schema three ways from one pipeline — one query parser, one planner, one SQL builder:

Surface	What it does
REST	A PostgREST-compatible HTTP API: the same query DSL, Prefer semantics, and PGRST* error codes. Existing PostgREST clients work unchanged.
MCP	Every table and function becomes a typed Model Context Protocol tool an LLM agent can discover and call — no hand-written schemas.
OpenAPI	An OpenAPI 3.0 document generated from the same introspected schema, always in sync with routes.

Feature Highlights

• 24 filter operators — equality, comparison, pattern matching, full-text search, array/JSONB containment, range operators, IS DISTINCT FROM — plus not. negation and any/all quantifiers
• Logic filters — combine conditions with and=/or= using nested boolean expressions
• Resource embedding — traverse foreign-key relationships with select=orders(items(*)) for nested JSON
• Cursor pagination — keyset-based forward pagination via cursor_column/cursor_value with X-Next-Cursor header
• Offset/limit pagination — classic pagination with Content-Range headers and exact/planned/estimated counts
• Mutations — INSERT, UPDATE, DELETE with upsert (Prefer: resolution=merge-duplicates), bulk operations, and returning representations
• Mutation guards — limit/order on PATCH/DELETE bound the rows changed; Prefer: max-affected=N (with handling=strict) and singular-object requests roll the transaction back when violated
• RPC — call database functions with typed arguments; overloads resolved by argument names; volatile functions are refused on GET (405) and always run on the primary
• Database-raised errors — RAISE EXCEPTION maps to 400, and PTxyz SQLSTATEs map to HTTP status xyz
• JWT authentication — HS256/HS384/HS512/RS256/EdDSA with exp/nbf checks, optional audience (jwt_aud), role switching, and Row-Level Security
• Live schema reload — NOTIFY pgrst, 'reload schema', SIGUSR1, or a programmatic reload refreshes REST, OpenAPI, and MCP without a restart
• Pub/sub — opt-in Postgres LISTEN/NOTIFY channels: subscribe over SSE, publish over REST or MCP, with optional per-caller authorization by a SQL function
• Data cache — optional in-memory response cache for primary-key lookups (and, if enabled, list queries), invalidated on schema reload
• Read replicas — lag-aware routing across health-checked Postgres replicas; writes and volatile calls go to the primary
• PostgreSQL 18 — virtual generated columns, NOT VALID NOT NULL constraints, and temporal (WITHOUT OVERLAPS) keys are introspected correctly
• Two backends — PostgreSQL and SQLite from the same engine, auto-detected from DSN
• Embeddable — use pgvis-lib as a Rust library in your own axum app

Key Properties

Property	Details
One engine, three surfaces	REST, OpenAPI, and MCP share the planner and SQL builder — they stay consistent by construction
Backend-agnostic	An I/O-free core works with any database implementing the Backend trait. A Dialect capability system gates features per backend
PostgREST-compatible	Same query DSL, same Prefer header semantics, same PGRST* error codes — drop-in replacement
Safe by construction	Parameterized SQL (never string interpolation), JWT auth, role switching, RLS, statement timeouts. MCP update/delete tools refuse calls without a valid filter
Secure defaults	pgvis serve binds 127.0.0.1 and refuses to start without jwt_secret or anon_role unless --insecure-no-auth is passed; a token without a role claim (and no anon_role) is a 401; unknown config-file keys are an error; TLS follows the DSN’s sslmode with certificate verification; the DSN password is redacted from logs
Fast by design	On Postgres, BEGIN, role/claims, the pre-request hook, and the query go out as one pipelined flight, then COMMIT. Table reads forward Postgres’s JSON text as bytes; bulk inserts bind one JSON parameter (json_populate_recordset); an exhausted pool returns 503
A library, not just a server	Embed pgvis in any Rust application via pgvis-lib::Builder

Architecture at a Glance

flowchart TB
    req(["HTTP request · MCP tool call"])
    subgraph core ["pgvis-core · I/O-free"]
        direction LR
        parse["<b>Parse</b><br/>ApiRequest"] --> plan["<b>Plan</b><br/>ActionPlan"] --> render["<b>Render</b><br/>parameterized SQL"]
    end
    subgraph edge ["backend + surface"]
        direction LR
        exec["<b>Execute</b><br/>Backend::execute"] --> fmt["<b>Format</b><br/>JSON + headers"]
    end
    cache[("Schema cache")] -.-> plan
    req --> parse
    render --> exec
    exec <--> db[("Postgres / SQLite")]
    fmt --> resp(["Response"])

1. Parse — the query DSL, filters, cursor and Prefer header become an ApiRequest.
2. Plan — validated against the schema cache, joins resolved, into an ActionPlan.
3. Render — dialect-aware, CTE-wrapped SQL with every value bound as a parameter.
4. Execute — the backend runs it (a pipelined Postgres transaction, or a SQLite connection).
5. Format — JSON body plus Content-Range, X-Next-Cursor and the other response headers.

Seven crates, all dependencies pointing inward to the I/O-free core:

Crate	Role
pgvis-core	Parser, planner, SQL builder, schema cache types
pgvis-postgres	Postgres backend (pool, introspection, execution, read replicas, LISTEN/NOTIFY)
pgvis-sqlite	SQLite backend (introspection, execution)
pgvis-router	axum REST router, OpenAPI generator, pub/sub SSE endpoints
pgvis-mcp	MCP tool generation, execution, stdio + HTTP transport
pgvis-lib	Builder facade — the single way to assemble the stack; schema reloader
pgvis-server	The pgvis CLI binary

Next Steps

Quick Start Get running in 5 minutes

Installation Cargo, Nix, and the CLI reference

REST API Guide The full query DSL reference

MCP Guide Using pgvis with LLM agents

Configuration All settings and environment variables

Architecture Crates, pipeline, executor and replicas