Downloads 5.0kthis week

GitHub stars 8

Extension descriptor (YAML)

FastAPI-class HTTP framework inside DuckDB — CREATE ROUTE turns SQL into typed, validated endpoints; one process is DB + HTTP (+ PDF/renderer via companion extensions).

Compatibility Matrix

This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

Installing and Loading

INSTALL quackapi FROM community;
LOAD quackapi;

Example

LOAD quackapi;

-- JSON endpoint: the SELECT is the handler
CREATE ROUTE hello GET '/hello' AS SELECT 'world' AS msg;

-- Path params bind as $id; cast failure → FastAPI-shaped 422
CREATE ROUTE item GET '/items/:id' AS SELECT $id::INTEGER AS id;

-- HTML when the single output column is named html
CREATE ROUTE home GET '/' AS SELECT '<h1>quackapi</h1>' AS html;

-- Typed optional query param with constraint
CREATE ROUTE search GET '/search'
  PARAM limit INTEGER DEFAULT 10 LE 100
  AS
SELECT $q::VARCHAR AS q, $limit::INTEGER AS limit;

-- POST mutation (JSON body fields bind as $name / $age)
CREATE ROUTE create_user POST '/users' STATUS 201 AS
SELECT $name::VARCHAR AS name, $age::INTEGER AS age;

-- Serve (background threads; shell stays usable)
-- Optional: static_dir for unrouted GETs; cors_origins for browser CORS
SELECT * FROM quackapi_serve(8000, static_dir := './static');

-- curl http://127.0.0.1:8000/hello          → [{"msg":"world"}]
-- curl http://127.0.0.1:8000/items/42       → [{"id":42}]
-- curl http://127.0.0.1:8000/items/abc      → 422 detail[loc=path,id]
-- curl -X POST …/users -H 'Content-Type: application/json' -d '{"name":"a","age":3}'

SELECT * FROM quackapi_routes();
SELECT * FROM quackapi_stop(8000);

About quackapi

quackapi

A FastAPI-class HTTP framework that lives inside DuckDB. Routes are DDL, handlers are SQL, and request validation is the database type system.

Thesis: the backend IS the database

A conventional stack serializes at every hop (app server → workers → DB → PDF microservice). quackapi collapses that into one DuckDB process:

browser → DuckDB [ quackapi(HTTP) · pdf · tera · fakeit · webbed · … ]

Companion community extensions (pdf, tera, fakeit, crawler/webbed, cronjob, curl_httpfs, …) load into the same address space. A handler that redacts a PDF is literally SELECT pdf_redact(...) — no RPC between tiers.

Showcase: the Closure redaction-review app (DB + HTML UI + PDF word boxes + POST decisions + static assets) runs as a single DuckDB process via CREATE ROUTE + quackapi_serve.

CREATE ROUTE

CREATE [OR REPLACE] ROUTE <name> <METHOD> '<pattern>'
  [STATUS <n>] [REQUIRE <auth>] [GROUP <g>]
  [BODY SCHEMA '<json-schema>']
  [PARAM <name> [<type>] [HEADER|COOKIE|QUERY …] [DEFAULT …] [GE|LE|…] …]
  AS <select>;

• Methods: GET, POST, PUT, DELETE, PATCH, HEAD.
• Path segments :id / {id} and query/body fields bind to $id.
• Cast/constraint failures return 422 with FastAPI-shaped {"detail":[{"loc":[...],"msg":...,"type":...}]}.
• Default JSON response is an array of row objects (SQL result-set semantics). A single column named html or text returns raw HTML/text; location / set_cookie set response headers.
• quackapi_serve([port], host := …, static_dir := …, cors_origins := …, memory_limit := …) uses DuckDB's bundled httplib (background listener + workers).
• Built-in OpenAPI: GET /openapi.json, GET /docs, GET /redoc.

Other DDL / functions

• Auth: CREATE AUTH … AS API_KEY | JWT (SECRET … [, ALGORITHM HS256]), REQUIRE on routes, quackapi_add_api_key, quackapi_auths, claims as $claims_*. JWT verify is self-contained (DuckDB-bundled mbedtls + json_* claims) — does not depend on community crypto (that extension is hash/HMAC only, not JWT parse/exp/nbf/claims).
• Groups: CREATE GROUP … WITH (prefix=…, auth=…, tags=…) — path prefix
  • auth inheritance (quackapi_groups()).
• Table API: CREATE API FOR TABLE t [AT '/path'] [KEY 'id'] → GET list
  • GET by key only.
• Queue: CREATE QUEUE, quackapi_enqueue/dequeue/ack/nack, table quackapi_jobs (broker-less; compose cronjob for workers).
• SSE: CREATE STREAM … GET '/path' AS <select> (text/event-stream; WebSocket not supported on httplib).
• Policies: CREATE ROW ACCESS POLICY / CREATE MASKING POLICY + ALTER TABLE bind; quackapi_policies().
• Inspect: quackapi_routes(), quackapi_servers(), quackapi_http_util_name().

Platforms & build

Targets DuckDB v2.0 (repo.ref) and v1.5.6 (repo.variegata). C++17; depends only on DuckDB-bundled httplib and mbedtls (no vcpkg, no libcurl). CI builds linux/macOS/windows_amd64; excludes wasm and Windows MinGW/rtools/arm64.

Limits (honest)

• DuckDB single-writer / file concurrency — not high-write multi-tenant OLTP.
• Serve memory_limit default is 256MB only when nothing is configured; use memory_limit := '4GB' / SET quackapi_memory_limit (never clobbers an operator SET memory_limit).
• Request body max 8 MiB; JWT HS256 only (no RS256/OIDC); table API is read-only scaffold.
• Route registry is instance-scoped (re-run DDL after reopen); queue jobs persist in quackapi_jobs.

Full reference: https://github.com/asubbarao/quackapi
Community page draft: https://github.com/asubbarao/quackapi/blob/main/docs/community-page.md

Added Functions

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings