Skip to main content

REST API Overview

pgStack exposes a PostgREST-compatible REST API at /rest/v1/. Any table or view in your PostgreSQL database (that the authenticated role has access to) is queryable via HTTP.

Base URL​

http://127.0.0.1:8080/rest/v1/

Authentication​

Requests authenticate via an Authorization header:

Authorization: Bearer <token>

Where <token> is:

  • Your anon key (ANON_KEY) for unauthenticated/public requests
  • A JWT access token (from login) for authenticated user requests
  • Your service role key (SERVICE_ROLE_KEY) for admin requests that bypass RLS

Alternatively, send the key in an apikey: <key> header (Supabase-compatible) — apikey: <anon-key> alone authenticates as anon, apikey: <service-role-key> as service_role. When JWT_SECRET is unset, requests without credentials pass through as anon.

SDK usage​

The TypeScript SDK wraps the REST API with a fluent query builder:

import { createClient } from '@pgstack/sdk/pgstack';

const pgstack = createClient('http://127.0.0.1:8080', 'your-anon-key');

// After login, the SDK automatically uses the user's access token
await pgstack.auth.signInWithPassword({ email: '...', password: '...' });

// SELECT
const { data, error } = await pgstack.from('todos')
  .select('*')
  .eq('done', false)
  .order('created_at', { ascending: false })
  .limit(20);

// INSERT
const { data, error } = await pgstack.from('todos')
  .insert({ title: 'Buy groceries', done: false });

// UPDATE
const { data, error } = await pgstack.from('todos')
  .update({ done: true })
  .eq('id', 42);

// DELETE
const { data, error } = await pgstack.from('todos')
  .delete()
  .eq('id', 42);

CRUD operations​

SELECT​

GET /rest/v1/todos
GET /rest/v1/todos?select=id,title,done
GET /rest/v1/todos?done=eq.false&order=created_at.desc&limit=20

INSERT​

POST /rest/v1/todos
Content-Type: application/json

{"title": "Buy groceries", "done": false}

Insert multiple rows by sending an array:

POST /rest/v1/todos
Content-Type: application/json

[
  {"title": "Buy groceries"},
  {"title": "Walk the dog"},
  {"title": "Pay bills"}
]

UPDATE​

PATCH /rest/v1/todos?id=eq.42
Content-Type: application/json

{"done": true}

UPSERT​

:::caution Not yet implemented UPSERT is not yet implemented by the proxy. on_conflict and Prefer: resolution=merge-duplicates are ignored — POST always executes a plain INSERT, and a conflicting key returns HTTP 409 {"error": "duplicate value violates unique constraint"}. The SDK upsert() method sends these parameters but they have no effect server-side. :::

DELETE​

DELETE /rest/v1/todos?id=eq.42

Response format​

Successful responses return a JSON array for SELECT, or the modified rows for INSERT/UPDATE/DELETE:

[
  {"id": 1, "title": "Buy groceries", "done": false, "created_at": "2025-01-01T00:00:00Z"},
  {"id": 2, "title": "Walk the dog", "done": true, "created_at": "2025-01-01T01:00:00Z"}
]

Error responses contain a single sanitized error field — raw PostgreSQL messages are never returned, and the status is carried by the HTTP status code, not the body. Example — unknown table returns HTTP 404:

{"error": "relation not found"}

Vertical filtering (select columns)​

Return only specific columns:

GET /rest/v1/todos?select=id,title

With embedding (FK joins):

GET /rest/v1/orders?select=id,status,customers(name,email)

TypeScript types​

Generate TypeScript types from your database schema with the CLI:

npx pgstack generate types --output src/types/database.ts

Then use them with the query builder:

import type { Database } from './types/database';

type Todo = Database['public']['Tables']['todos']['Row'];

const { data } = await pgstack.from<Todo>('todos').select('*');
// data is Todo[] | null — fully typed

Next steps​

  • Filters — all filter operators with examples
  • Embedding — fetch related rows via foreign keys
  • RPC — call PostgreSQL functions
  • Pagination — cursor and offset pagination