PgArachne Logo

PgArachne

Turn PostgreSQL into a secure API. Instantly.

Zero boilerplate. High performance. The middleware that maps HTTP requests directly to database functions.

Get Started GitHub See Demo (Explorer)

Is PgArachne right for you?

PgArachne is not just for prototyping.
It bridges the gap between raw database performance and modern API requirements.

🚀 Rapid Prototyping

Stop writing boilerplate CRUD controllers. Define a SQL function, and your API endpoint is ready instantly. Perfect for MVPs and Hackathons.

🏢 Production Ready

Designed to handle over 90% of standard backend tasks (CRUD, validation, auth). With connection pooling, graceful shutdowns, and Prometheus metrics, it powers production systems reliably.

🧠 AI & LLM Friendly

Since the API is self-documenting via SQL comments, you can feed the schema directly into LLMs (ChatGPT, Claude). This allows AI agents to understand your business logic and construct valid API calls with zero hallucinations.

About & Architecture

PgArachne serves as a lightweight, high-performance web server that sits between your HTTP clients and the PostgreSQL database. It eliminates the need for traditional backend languages (like Python, Node.js, or PHP) by mapping HTTP requests directly to database functions.

Core Technical Capabilities

  • JSON-RPC 2.0 Gateway: All API interactions follow the strict JSON-RPC 2.0 specification. There are no REST endpoints to design; you simply call SQL functions by name via POST requests.
  • Native DB Authentication: No separate user tables. Users log in with their actual PostgreSQL credentials to receive a JWT. Alternatively, service accounts can use persistent API Tokens.
  • Role Masquerading: PgArachne connects as a proxy user but executes every request using SET LOCAL ROLE to switch to the authenticated user's identity. Row-Level Security (RLS) works automatically.
  • Static File Server: PgArachne can serve static assets (HTML, JS, CSS), allowing you to host Single Page Applications (SPAs) or the Explorer tool directly.

This architecture dramatically simplifies the stack: Database ↔ PgArachne ↔ Frontend.

API Endpoint Format: All database functions are exposed at:
POST http://{server}:{port}
/api
/{database_name}
/{schema_name}.{function_name}

Installation

Option A: Download Binaries

Download the latest pre-compiled version for your operating system:

Linux

x64/Intel/AMD ARM64/Aarch64

macOS

ARM64/Apple Silicon x64/Intel

Windows

x64/Intel/AMD ARM64

Option B: Build from Source

git clone https://github.com/heptau/pgarachne.git
cd pgarachne
go build -o pgarachne cmd/pgarachne/main.go

Database Setup

Initialize the pgarachne schema required for API tokens.

psql -d my_database -f sql/schema.sql

Configuration

PgArachne loads configuration from environment variables or a file.

Security Note: PgArachne does not handle database passwords in the configuration file. It relies on the standard PostgreSQL .pgpass file mechanism (or system-wide PGPASSWORD variable) for authentication.
Search Order: If no config file is specified via CLI, it searches:
  1. Current directory: ./pgarachne.env (All OS)
  2. User config:
    • Linux / macOS: ~/.config/pgarachne/pgarachne.env
    • Windows: %USERPROFILE%\.config\pgarachne\pgarachne.env
  3. System config: /etc/pgarachne/pgarachne.env (Linux / macOS only)
Variable Required Description
Database Connection
DB_HOST Yes PostgreSQL server address (e.g., localhost).
DB_PORT No Database port. Default: 5432.
DB_USER Yes The database user PgArachne connects with.
HTTP Server
HTTP_PORT No Port to listen on. Default: 8080.
ALLOWED_ORIGINS No CORS settings. Comma-separated list of allowed domains (e.g., https://myapp.com). Default: *.
STATIC_FILES_PATH No Absolute path to serve static files (Explorer/Frontend).
Security (JWT)
JWT_SECRET Yes A long, random string used to sign session tokens.
JWT_EXPIRY_HOURS No Session validity in hours. Default: 8.
Logging
LOG_LEVEL No Verbosity: DEBUG, INFO, WARN, ERROR. Default: INFO.
LOG_OUTPUT No Where to write logs: stdout or file path.

PgArachne Explorer

The Explorer is a powerful web GUI included in the `tools/` directory. It is not just documentation; it is a fully functional demo application built using HTML/JS that communicates with the database exclusively via PgArachne.

What can it do?

  • Inspect API: It reads the `capabilities` function to display all available endpoints and their parameters.
  • Live Testing: You can execute functions directly from the browser.
  • Auto-Documentation: It renders the SQL comments (including `--- PARAMS ---` metadata) into readable documentation.
How to enable it: Set the `STATIC_FILES_PATH` environment variable to point to the `tools/pgarachne-explorer` folder on your disk. Then visit http://localhost:8080.

Security & Authentication

PgArachne relies entirely on the PostgreSQL permission system. It does not reinvent Access Control Lists (ACLs). All queries are executed under the specific database role of the authenticated user using SET LOCAL ROLE.

1. Interactive Login (JWT)

Users authenticate using their real PostgreSQL username and password via the login function. If successful, they receive a generic JWT. When this token is used, PgArachne switches the active role to that user for the duration of the request.

2. Service Accounts (API Tokens)

For automated systems or scripts, you can use long-lived API keys.

  • Tokens are stored in the pgarachne.api_tokens table.
  • Each token is mapped to a specific database user/role.
  • Send the token via the Authorization: Bearer <token> header.

Critical Configuration: Proxy Privileges

Since PgArachne connects as the user defined in DB_USER (e.g., pgarachne) and switches identity to other users, the proxy user must be a member of those target roles.

Run this SQL for every user/role that needs to log in:

-- Allow 'pgarachne' to switch to 'app_user'
GRANT app_user TO pgarachne;

Deployment & HTTPS

PgArachne is designed to perform one job well: API Gateway. For SSL/TLS (HTTPS), header security, and public routing, you should place a Reverse Proxy in front of it.

Option A: Caddy Server

Best for: Modern production deployments, ease of use.

Caddy is the only web server that obtains and renews SSL certificates (Let's Encrypt) automatically by default. It requires almost zero configuration.

# Caddyfile
example.com {
    reverse_proxy localhost:8080
}

Option B: Nginx

Best for: Enterprise environments, complex routing.

Nginx is the industry standard for high-performance load balancing. Use this if you already have an Nginx infrastructure. You will need to manage Certbot manually.

server {
    server_name example.com;
    location / {
        proxy_pass http://localhost:8080;
    }
}

Option C: Ngrok

Best for: Local development, Demos, Webhook testing.

Ngrok creates a secure tunnel from the public internet directly to your laptop without configuring firewalls. Ideal for showing your work to colleagues instantly.

./ngrok http 8080

Error Codes

PgArachne returns standard JSON-RPC 2.0 error objects. Below are the specific codes you may encounter:

Code Message Meaning
-32700 Parse error Invalid JSON was received by the server.
-32601 Method not found The function does not exist in the schema.
-32602 Invalid params Arguments do not match the function signature.
-32001 Permission denied The current user role lacks privileges to execute the function (DB level).
-32000 Internal Error Generic server error (check logs).

Support the Development

Developing open-source software like PgArachne takes significant time. If this tool saves you time, please consider supporting its maintenance.

☕ Become a Member

Join the community on Buy Me a Coffee. Get exclusive updates, early access to new features, and more.

Support on Buy Me a Coffee

Bank Transfer

Direct support with 0% fees.

🇺🇸 ACH (USA)
Name
Zbynek Vanzura
Account Number
827908168786406
Account Type
Deposit
Routing Number
084009519
Swift/BIC
TRWIUS35XXX
Bank Address
Wise US Inc, 108 W 13th St, Wilmington, DE, 19801, United States
🇪🇺 SEPA (Euro)
Name
Zbynek Vanzura
IBAN
BE68905813473834
Swift/BIC
TRWIBEB1XXX
Bank Address
Wise, Rue du Trône 100, 3rd floor, Brussels, 1050, Belgium
🇨🇿 CZK (Czech)
Name
Zbynek Vanzura
Account No.
2300354/2010

Cryptocurrency

Support via digital assets.

Bitcoin (BTC)
BTC Address
bc1qw8swmnvk48lhcatyw55wz8g4gyjq3vkw0h78g6
USDT (TetherUS)
TRC20 Address
TPemgxu8jxkjyiHpqrUJyc3dNEs3aPSb2T
ERC20 Address
0xa689e1c6c8da09fcecca1b9733a22619f56288ff
Binance Pay
Donate via Binance Pay

Revolut

The fastest way to donate. Supports Apple Pay, debit or credit cards.

Donate via Revolut

PayPal

Easy secure payments. Supports debit or credit cards.

Wise

Easy secure payments.

License & Trademark

The Code (MIT License):
You can use the source code for free for any personal or commercial project. You can modify it, fork it, and distribute it.

The Brand (Trademark):
The name "PgArachne" and the Logo are trademarks of Zbyněk Vanžura. If you fork the project or offer a commercial "Managed PgArachne" service, you must remove the original branding and logo unless you have obtained a special license.

Copyright © 2025 Zbyněk Vanžura.