QueryAPIGate was previously named SQL2API. Upgrading? See Upgrading from SQL2API for what changed.
Every feature, one by one: the full walkthrough (6 minutes, in chapters)
QueryAPIGate is a self-hosted service that runs SQL against your databases - and against Parquet, CSV and JSON files on disk, S3, GCS or the web, through DuckDB - and returns the results as JSON, NDJSON, XML, YAML, CSV, TSV, Excel or Parquet. Save a query once and it becomes a versioned endpoint with typed, injection-safe parameters and run history - without writing a controller, a repository layer, pagination, auth or serialization boilerplate for it.
Write SQL. Configure the query. Apply access controls. Get an API.
$ curl 'http://127.0.0.1:5000/q/example_film_search?text=Harbor&page_size=2' -H 'X-API-Key: demo-key'
[{"film_id":48,"title":"Broken Harbor","category":"Action","rating":"PG-13"},{"film_id":44,"title":"Electric Harbor","category":"Documentary","rating":"G"}]
$ curl 'http://127.0.0.1:5000/q/example_top_films?top_n=2&category=Comedy&format=csv' -H 'X-API-Key: demo-key'
title,category,rating,rentals,rank
Electric Signal,Comedy,PG,977,1
Crimson Garden,Comedy,PG-13,631,2
The built-in Console at /console: write SQL with completion and a schema browser, run it, then save it as an API - no separate tool to install.
Home: live totals, what needs attention, recent activity, and every recent request - saved APIs and ad-hoc SQL alike.
Manage connections across every supported database, with live usage per connection |
Every saved query becomes a documented REST endpoint - grouped into collections |
Per-key permissions: connections, specific saved queries, expiry, rate limits - and what each key has actually done |
Named roles: a reusable grant template, copied onto a key once at creation |
Run history per saved query: who called it, how long it took, and the request ID to find it in the logs |
A durable audit log of every configuration change, filterable by action, actor and target |
Live metrics with no Prometheus required - or import the bundled Grafana dashboard for history |
Collections: grant a key a whole group of queries - and see exactly which keys gain or lose access before a query moves |
Caching: live hit rate for the response cache backing every saved query's cache_ttl
|
Alerts: failing connections, expiring keys, failing or slow queries, rate limits being hit - each with what to do, clearing by itself once fixed |
Access map: which key can call which query, and why - one grid instead of reading every grant |
Settings: the server's effective configuration, read-only, plus per-browser appearance and editor preferences |
Help: the docs and how-to guides for the version you run, inside the Console |
Light or dark, three font sizes - switched from the top right corner |
Organizations often have valuable SQL sitting inside reports, BI dashboards, ETL pipelines, ad-hoc analysis and application code, with no path to a REST endpoint except writing a backend service around it.
SQL Query
|
v
+------------------+
| QueryAPIGate |
| |
| Parameters |
| SQL Guard |
| Permissions |
| Rate Limiting |
| Caching |
| Connection Pool |
| Streaming |
| Observability |
+--------+---------+
|
v
REST API
|
+--------+--------+
v v v
JSON CSV XLSX
If you already know SQL, you can produce a governed API without building an API application around it.
┌──────────────────────────┐
│ QueryAPIGate │
│ │
│ Governed Data Layer │
└────────────┬─────────────┘
│
┌───────────────────┼──────────────────┐
│ │ │
DATA ACCESS GOVERNANCE DATA DELIVERY
│ │ │
Saved Queries API Keys REST APIs
SQL / Mongo Roles JSON/CSV
Multi-DB Collections Streaming
DuckDB/files Permissions CLI exports
JDBC Rate limits cron/K8s
│ IP controls
│ Audit
│
└───────────────────┬──────────────────┘
│
OBSERVABILITY
│
Metrics / Logs / History
OpenAPI / Postman / UI
Tier 1 - PostgreSQL, MySQL, SQLite, DuckDB and ClickHouse: every feature, tested against a real server in CI, and
covered by the compatibility promise. Experimental - H2, generic JDBC (Oracle, SQL Server, DB2, Snowflake and
anything else with a driver jar) and MongoDB (find() queries): they work, with documented gaps. The same guard,
pooling, parameter binding and output formats - JSON, NDJSON, XML, YAML, CSV, TSV, Excel and Parquet - apply to every one.
What each supports - read-only enforcement, time limits, allowed_tables, schema browsing, streaming - is in the
support matrix.
Files as a source - through DuckDB, Parquet, CSV and JSON files on local disk, S3, GCS, R2 or the web are queried where they are and published like any table, limited to the paths each connection lists (see publishing a bucket as an API).
QueryAPIGate is built to expose specific query results, not database credentials:
- API key authentication, with hashed key storage (SHA-256, never the raw secret) in
queryapigate.db. - Database connection passwords encrypted at rest (
QUERYAPIGATE_SECRET_KEY), decrypted only in memory at the moment a connection is opened; a literal password on disk is never returned to a client either way. - Scoped API keys: restrict a key to a set of connections, and/or to a specific allow-list of saved queries, independent of any connection grant - individual queries in that list can also be curated for write access, without granting it anywhere else reachable through the key.
- Example APIs:
queryapigate examples loadinstalls four worked scenarios - a reporting API, dashboard data, a streaming export and a partner integration - as collections, queries, roles and a ready-to-use API key per scenario, touching nothing of yours, and removable again with one command. - Collections: file saved queries into named groups and grant a key a whole group - read-only, live, and every move that changes who can reach a query is audited with the keys that gained or lost access. Rename a collection without any key losing reach part-way; export one as a portable bundle and import it elsewhere.
- Named permission roles: a reusable template (connections, write access, queries, collections, rate limit, allowed IPs) copied onto a key once at creation, so a shared grant set for many keys lives in one place - editing or deleting a role afterward never affects a key already created from it.
- Read-only by default; write access (
INSERT/UPDATE/DDL) is off unless explicitly enabled server-wide or granted per key, and can be narrowed further to specific write operations a key may perform. - API key expiry (TTL) and revocation.
- Per-key rate limiting, on top of the server-wide, IP-based limit.
- IP allowlisting per key: pin a key to specific addresses or CIDR ranges.
- A row ceiling for streaming exports (
QUERYAPIGATE_STREAM_MAX_ROWS), unbounded by default. - A SQL guard that only allows single
SELECT/WITH/SHOW/DESCRIBE/EXPLAINstatements through by default, with bound:nameparameters rather than string-concatenated SQL.
API Consumer
|
| API Key
v
QueryAPIGate
|
+-- Authentication
+-- Authorization (connections + saved queries)
+-- SQL guard
+-- Rate limiting
+-- Execution
|
v
Database
This lets an organization hand an external client a key that can only ever reach one named query - never arbitrary SQL and never a whole connection - while internal callers keep broader, connection-level access.
A saved query bundles SQL, name, description, parameters, author, version, permissions and (optionally) cache
configuration into one addressable resource: GET /q/<name>. Saving again under the same name creates a new
version rather than overwriting the old one, so a change to the SQL doesn't silently change what's already live;
?version=1 still runs the prior one, and each run is recorded in the query's execution history.
SELECT * FROM film WHERE film_id = :idGET /q/film_by_id?id=7Values are sent to the database separately from the SQL text, so they cannot be injected into it. Parameters can
declare a type, default, required/optional, enum, numeric range, length and pattern - invalid input is rejected
with a field-by-field 400 before it reaches the database.
JSON, NDJSON, XML, YAML, CSV, TSV, XLSX and Parquet are all available per request (?format=), so the same saved
query serves application clients, reporting and data pipelines - a Parquet export keeps column types and is typically
a quarter of the CSV's size.
?page=2&page_size=50 pages results with X-Has-More telling the caller whether more exist. For full exports,
?stream=true&format=csv (or tsv/ndjson) streams the entire result straight from the database cursor rather
than buffering it - verified end-to-end with 1,000,000-row results and flat server memory throughout (MySQL,
PostgreSQL and ClickHouse); see Streaming exports for the measured
numbers. queryapigate export <query> --out '/exports/{name}_{date}.csv' wraps the same streaming path as a CLI
command for cron/systemd/Kubernetes CronJob to call directly - no server needs to be running; see
Scheduled exports to a file.
Saved queries can opt into HTTP-level caching: cache_ttl, Cache-Control, ETag, conditional requests and
304 Not Modified, plus an X-Cache: HIT/MISS header. Never applied to a query that writes.
A server-wide, IP-keyed limit (QUERYAPIGATE_RATE_LIMIT) and an independent, optional per-key limit can both be in
effect at once - a request has to pass both. This lets different API consumers get different budgets:
Internal dashboard -> generous server-wide limit, no per-key limit
Partner key -> 200/hour of its own
Trial key -> 20/hour of its own
Structured JSON logs (QUERYAPIGATE_JSON_LOGS) tagged with a request ID, per-query execution timing, slow-query
warnings (QUERYAPIGATE_SLOW_QUERY_THRESHOLD), and Prometheus metrics at /metrics covering request/query counts and
latencies, connection-pool occupancy and rate-limit rejections.
/openapi.json is a complete, valid OpenAPI 3.0 document (checked in CI against the official validator) with
every saved query listed as its own typed endpoint; /docs serves it through Swagger UI. A key's endpoint list is
filtered to what that key can actually reach, so a query-scoped external key sees only its approved queries.
A built-in UI at /console covers the whole workflow: connection management, a SQL editor with syntax highlighting
and schema browsing (click a table/column to insert it), query execution and preview, EXPLAIN, saved-query and
version management, API key management, an audit log of administrative changes, per-query execution history,
response inspection with a collapsible JSON tree for non-tabular results, a quick bar chart of any numeric
result, and one-click "copy as curl" / "copy as TSV" for any result.
The UI has a collapsible sidebar (Data, Access, Observability; Ctrl+B toggles it) and a read-only Settings
screen that shows every environment variable, its effective value and whether it was set or is the default (secrets are
reported as configured or not, never shown), with a "Copy as .env" button. Theme, table density and the default
result format are per-browser preferences.
Connect Database
|
v
Write SQL
|
v
Test Query
|
v
Save Query
|
v
Configure Access
|
v
Expose API
Unit tests, integration tests against real MySQL, PostgreSQL, ClickHouse and H2 servers (run in CI against service containers), DuckDB integration tests that run unconditionally since it's embedded, SQL-guard fuzz testing with Hypothesis, static type checking with mypy, linting with ruff, and CI on every push. See Development below.
+---------------------+
| API Client |
+----------+----------+
|
v
+---------------------+
| QueryAPIGate |
| |
| Authentication |
| Authorization |
| Rate Limiting |
| SQL Guard |
| Parameter Binding |
+----------+----------+
|
v
+---------------------+
| Query Manager |
| |
| Saved Queries |
| Versions |
| Cache |
| API Metadata |
+----------+----------+
|
v
+---------------------+
| Database Abstraction|
| |
| MySQL |
| PostgreSQL |
| ClickHouse |
| SQLite |
| H2 |
| DuckDB |
| JDBC |
+----------+----------+
|
v
Database
Internal data APIs. Expose internal data to web apps, mobile apps, internal tools and other engineering teams without building a service per query:
GET /q/film_by_id?id=7
GET /q/example_top_films?top_n=5&category=ComedyReporting APIs. Turn an existing analytical SQL report into a reusable endpoint that dashboards or scheduled jobs can call directly, in JSON or as a CSV/XLSX export.
Partner and external-client integrations. Give a partner an API key scoped to a curated list of saved queries, with its own rate limit and expiry, instead of database credentials:
Partner --API Key--> QueryAPIGate --queries grant--> Database
Data engineering to application engineering. Data teams write and version the SQL; application teams consume it as a normal REST endpoint, without either side depending on the other's deploy cycle.
Data export. Stream large results as CSV, TSV, XLSX, JSON or NDJSON without loading the whole result into memory first.
Rapid prototyping. Get a working, parameterized API around an existing query for a proof of concept or internal tool without standing up a backend application for it.
| Approach | Primary model |
|---|---|
| QueryAPIGate | SQL query -> governed REST API |
| PostgREST | PostgreSQL schema -> REST API |
| Hasura | Database -> GraphQL/API platform |
| DreamFactory | Data source -> generated APIs |
| SQLPad | SQL -> interactive query environment |
QueryAPIGate's primary abstraction is the SQL query as an API resource, not the schema as a whole - so it fits well when an API should expose one specific, curated query rather than automatically surface an entire database.
Your database already contains the data logic. The goal isn't to replace application backends; it's to remove repetitive boilerplate when the actual requirement is "expose this query safely as an API" - existing SQL, plus governance, is the API.
pip install "queryapigate[postgres]" # pick the drivers you need: mysql, postgres, clickhouse, h2, duckdb
# or everything: pip install "queryapigate[all]"SQLite needs no extra driver. H2 and generic JDBC connections (queryapigate[h2]) also need a Java runtime; H2's own
driver jar is bundled, a JDBC connection to another vendor brings its own. DuckDB (queryapigate[duckdb]) needs no
external runtime either - it's a native Python extension, same as SQLite. queryapigate[encryption] adds
encryption at rest for connection passwords -
only needed if you set QUERYAPIGATE_SECRET_KEY.
From a clone: pip install -e ".[dev]". Or use Docker - see below.
Fastest: queryapigate examples load && queryapigate serve installs four worked example APIs
(reporting, dashboard, export, partner) you can try, and queryapigate examples unload removes them again. Loading
them creates a real API key per scenario, which also means the server now requires a key for every request - set
QUERYAPIGATE_API_KEY yourself first so you have an admin key on hand for ad-hoc SQL too:
QUERYAPIGATE_API_KEY=demo-key queryapigate examples load # also prints one scoped key per scenario - store them now
QUERYAPIGATE_API_KEY=demo-key queryapigate serve # http://127.0.0.1:5000The examples come with a small generated examples database (films, customers and rentals), so you can also run ad-hoc SQL
straight away:
curl -X POST 'http://127.0.0.1:5000/execute_sql?page_size=3' -H 'X-API-Key: demo-key' -H 'Content-Type: application/json' \
-d '{"sql": "SELECT film_id, title, rating FROM film WHERE film_id > :min", "params": {"min": 10}, "connection_name": "examples"}'Open http://127.0.0.1:5000/docs for the interactive API reference, or http://127.0.0.1:5000/console for the admin UI to manage connections and saved queries and run ad-hoc SQL without leaving the browser.
For your own databases, run queryapigate init in an empty folder: it creates queryapigate.db with an
inactive template connection for every supported database type. Edit them (admin UI, or PATCH /api/v1/connections/<name>), set "active": true, and start the server there.
This uses the examples connection that queryapigate examples load sets up:
curl -X POST http://127.0.0.1:5000/api/v1/queries -H 'X-API-Key: demo-key' -H 'Content-Type: application/json' -d '{
"name": "film_by_id",
"sql": "SELECT * FROM film WHERE film_id = :id",
"parameters": {"id": {"type": "int", "min": 1, "max": 60, "description": "Film id"}},
"connection_name": "examples",
"author": "me", "description": "Look up a film",
"publish": true
}'
curl 'http://127.0.0.1:5000/q/film_by_id?id=7&format=yaml' -H 'X-API-Key: demo-key'
curl 'http://127.0.0.1:5000/q/film_by_id?id=0' -H 'X-API-Key: demo-key'
# {"error": "Invalid parameters: id must be at least 1", "errors": {"id": "must be at least 1"}}Rules: type (int, float, str, bool), default, required, enum, min/max, min_length/max_length,
pattern and description - see the API reference.
POST /api/v1/queries/film_by_id/versions adds version 2 - a draft until published ("publish": true, or
POST .../publish); DELETE /api/v1/queries/film_by_id/versions/1 removes one version.
Add "cache_ttl": 60 to cache a response for that many seconds (X-Cache: HIT/MISS, ETag, Cache-Control) -
opt-in, and never used for a query that writes. See
Response caching.
Everything is configured through environment variables (all optional):
| Variable | Default | Effect |
|---|---|---|
QUERYAPIGATE_HOME |
current directory | Folder holding queryapigate.db (connections, saved queries, API keys, roles and the audit log). |
QUERYAPIGATE_DATABASE_URL |
unset | A postgresql:// URL: keep connections, saved queries, run history, API keys, roles and the audit log in that PostgreSQL database instead of queryapigate.db, so several instances can share them. Needs queryapigate[postgres]; copy an existing store across with queryapigate migrate-to-postgres. See the setup guide. |
QUERYAPIGATE_ALLOW_WRITES |
off | Allow INSERT/UPDATE/DDL. Otherwise only single read-only statements are accepted. |
QUERYAPIGATE_API_KEY |
unset | The shared admin key, with the owner role. When set (or once a scoped key or a named administrator exists), every request except /health, /docs, /console, /openapi.json and /metrics needs an X-API-Key header. Once named owners exist it is the break-glass key: every use is logged and alerted, and it can be removed. |
QUERYAPIGATE_EXPORT_DIRS |
unset | Comma-separated absolute folders a local-folder export destination may write under. Unset: only object-storage destinations (s3://, gs://, r2://) are allowed. Never the server's own home. |
QUERYAPIGATE_MASK_KEY |
unset | The key column masking's hash action uses. Unset: one is made once in the store and shared by every instance. Changing it changes every hashed value. |
QUERYAPIGATE_REDIS_URL |
unset | A redis:// URL: share rate limits and the response cache across every instance and process (serve, mcp, events). Needs queryapigate[redis]. Without it, each process counts and caches on its own. |
QUERYAPIGATE_MAX_PAGE_SIZE |
1000 |
Upper limit for page_size. |
QUERYAPIGATE_STREAM_MAX_ROWS |
unset | Row cap for a ?stream=true export. Off (unbounded) by default; a malformed value stops startup. |
QUERYAPIGATE_CORS_ORIGINS |
unset | Websites allowed to call the API from a browser: comma-separated origins such as https://app.example.com, or *. Off by default. |
QUERYAPIGATE_RATE_LIMIT |
unset | Requests allowed per client address, e.g. 60/minute (also second, hour, day). Off by default; a malformed value stops startup. |
QUERYAPIGATE_TRUST_PROXY |
0 |
Number of reverse proxies in front of the app whose X-Forwarded-* headers are trusted. Set it (usually 1) behind nginx, a load balancer or a platform router, or every client looks like the proxy. |
QUERYAPIGATE_HSTS_MAX_AGE |
(unset) | Seconds browsers should use only HTTPS for this host (Strict-Transport-Security), sent only on requests that arrived over HTTPS. Unset: not sent - set it once every client reaches the server over HTTPS. |
QUERYAPIGATE_POOL_SIZE |
5 |
Idle connections kept per distinct connection setting. 0 turns pooling off. |
QUERYAPIGATE_POOL_IDLE_TIMEOUT |
300 |
Seconds an idle pooled connection is kept before it is closed. |
QUERYAPIGATE_QUERY_TIMEOUT |
30 |
Seconds a query may run before it is cancelled (HTTP 504). 0 disables the limit. A request can lower it with ?timeout=, never raise it. |
QUERYAPIGATE_HOST / QUERYAPIGATE_PORT |
127.0.0.1 / 5000 |
Bind address for queryapigate serve. |
QUERYAPIGATE_DEBUG |
off | Flask debug mode. Never enable on a reachable host. |
QUERYAPIGATE_H2_JAR |
bundled | Path to a different H2 JDBC jar. |
QUERYAPIGATE_SECRET_KEY |
unset | A Fernet key encrypting connection passwords at rest. Off by default (stored as given); needs queryapigate[encryption]. A malformed value stops startup. |
QUERYAPIGATE_JSON_LOGS |
off | Emit one JSON object per log line, tagged with the request ID, instead of plain text. |
QUERYAPIGATE_SLOW_QUERY_THRESHOLD |
1 |
Seconds a query may take before it is logged as a warning. 0 disables it. |
QUERYAPIGATE_HISTORY_LIMIT |
50 |
Runs kept per saved-query version (and shown in lists). |
QUERYAPIGATE_HISTORY_RETENTION_DAYS |
unset | Keep every run for this many days instead of a per-version count - browse it with GET /api/v1/history. Best with QUERYAPIGATE_DATABASE_URL. |
QUERYAPIGATE_HISTORY_SAMPLE_RATE |
1 |
Fraction of successful runs recorded; failed runs always are. |
QUERYAPIGATE_HISTORY_ADHOC_LIMIT |
1000 |
Ad-hoc SQL runs (/execute_sql, MCP) kept in all, unless a retention period is set. |
QUERYAPIGATE_HISTORY_ADHOC_SQL |
text |
What an ad-hoc run keeps of its SQL: text, hash (a SHA-256 only) or none - choose hash or none if SQL may embed sensitive literals. |
QUERYAPIGATE_HISTORY_FLUSH_INTERVAL |
1 |
Seconds between batched history writes; 0 writes inside each request. See Run history. |
QUERYAPIGATE_AUDIT_LOG_LIMIT |
500 |
Administrative-change entries kept in queryapigate.db's audit log; older ones roll off. Always a positive count; a malformed value stops startup. |
QUERYAPIGATE_LOAD_EXAMPLES |
unset | yes loads the example APIs (reporting, dashboard, export, partner) at startup - idempotent; a malformed value stops startup. Never removes anything: use queryapigate examples unload. |
QUERYAPIGATE_AUDIT_LOG_EXPORT_FILE |
unset | Path to also append every audit entry to, one JSON object per line, never capped - for retention beyond the rolling window above. |
QUERYAPIGATE_JWT_JWKS_URL / QUERYAPIGATE_JWT_SECRET |
unset | Accept signed-in users' tokens (Authorization: Bearer) from your identity provider or your own backend, with a role (QUERYAPIGATE_JWT_ROLE / QUERYAPIGATE_JWT_ROLE_CLAIM) for what they may do, and parameters bound to their claims (from_claim). See Signed-in users for the related QUERYAPIGATE_JWT_* settings. |
QUERYAPIGATE_EVENTS_MAX_STREAMS |
4 |
Concurrent GET /events streams the main server holds open (each takes a request thread); beyond it, 503. 0 turns it off. |
QUERYAPIGATE_EVENTS_PORT |
5002 |
Port of queryapigate events, the separate live-events server for many clients - see Live events. |
QUERYAPIGATE_EVENTS_MAX_CONNECTIONS |
10000 |
Open streams one queryapigate events process accepts. |
QUERYAPIGATE_EVENTS_POLL_INTERVAL |
1 |
Seconds between queryapigate events checks for new runs (on PostgreSQL a notification usually arrives first). |
QUERYAPIGATE_MCP_PORT |
5001 |
Bind port for queryapigate mcp - see documentation/MCP.md. |
QUERYAPIGATE_MCP_MAX_ROWS |
200 |
Row cap for an MCP tool call's result. Always a positive count; a malformed value stops startup. |
QUERYAPIGATE_SSO_ISSUER / QUERYAPIGATE_SSO_CLIENT_ID / QUERYAPIGATE_SSO_CLIENT_SECRET / QUERYAPIGATE_SSO_REDIRECT_URL |
unset | Administrators sign in to the Console through your identity provider, their role from their groups (QUERYAPIGATE_SSO_ROLES). Experimental - see Console sign-in through SSO. |
QUERYAPIGATE_MCP_RESOURCE_URL / QUERYAPIGATE_MCP_AUTH_SERVER |
unset | Let hosted AI clients sign in through your identity provider (OAuth) - the MCP server's own URL and the provider's issuer, with QUERYAPIGATE_MCP_ROLE / QUERYAPIGATE_MCP_ROLE_CLAIM and QUERYAPIGATE_MCP_SCOPE. Experimental - see Sign-in for AI clients. |
QueryAPIGate runs whatever SQL it is given against your databases, so it ships locked down and expects you to finish the job:
- Set
QUERYAPIGATE_API_KEYand serve over TLS (put it behind a reverse proxy). It's the shared admin key; give the people who manage the server their own admin tokens and roles instead, and keep it as a break-glass key (guide 43). For anyone who only needs to run queries against specific connections, create a scoped key instead (POST /api/v1/api-keys, admin only) - see documentation/API.md. For an external client that should only reach a curated handful of saved queries and nothing else, scope the key to those query names specifically (queries) instead of a whole connection - see Per-saved-query access. - Connect with a database account that only has the privileges the API needs - the read-only guard is defence in depth, not a replacement for grants. (H2's driver cannot enforce read-only, so H2 relies on the guard.)
- Use bound
:nameparameters. The older{name}placeholders paste text into the SQL and are therefore restricted to numbers and plain text. - Passwords are never returned by the API.
- Set
QUERYAPIGATE_SECRET_KEYto encrypt connection passwords at rest instead of relying solely on the${VAR}convention - keep the key itself outsideQUERYAPIGATE_HOMEand out of version control, the same as any other credential; there is no way to recover an encrypted password without it. - Set query timeouts and result-size expectations deliberately (
QUERYAPIGATE_QUERY_TIMEOUT,QUERYAPIGATE_MAX_PAGE_SIZE), enable rate limiting, and route logs and/metricsinto your existing monitoring. - Plan for backup and recovery of
queryapigate.db(QUERYAPIGATE_HOME), and put QueryAPIGate behind your normal reverse-proxy/TLS-termination setup rather than exposing it directly.
See documentation/THREAT_MODEL.md for the reasoning behind these controls - what's actually defended against and where it stops - and SECURITY.md to report a vulnerability.
Browsers refuse cross-origin JSON calls unless the server allows them. List the sites that may call the API:
QUERYAPIGATE_API_KEY=change-me QUERYAPIGATE_CORS_ORIGINS=https://app.example.com queryapigate servePreflight checks are answered automatically, and the pagination headers (X-Has-More etc.) are exposed to the page's
JavaScript. CORS only tells the browser which sites may call; it is not authentication, so keep the API key. Avoid
* without a key: any website a visitor opens could then reach your databases through their browser (the server logs
a warning if you start that way).
QUERYAPIGATE_RATE_LIMIT=60/minute gives each client address a bucket of 60 requests that refills steadily, so short bursts
work but the sustained rate is capped. Over the limit, requests get 429 with a Retry-After header, and every
response carries X-RateLimit-Limit and X-RateLimit-Remaining. The limit is applied before the API key check, so
guessing keys is throttled too; /health and CORS preflights are never counted. With QUERYAPIGATE_REDIS_URL set, limits are counted in Redis - one budget per client address and per key across every instance and process (serve, mcp, events); if Redis fails, each process falls back to counting on its own until it's back, and an alert says so. Without Redis,
state is per process: with several workers or instances, the effective limit is multiplied by their number.
An API key can also carry its own rate_limit (same grammar), checked in addition to the server-wide limit, never
instead of it - a request has to pass both. See Per-key rate limiting.
Every release is published to GitHub Container Registry for linux/amd64 and linux/arm64:
docker run -p 5000:5000 -v queryapigate-data:/data -e QUERYAPIGATE_API_KEY=change-me ghcr.io/anantharajuc/queryapigate:latest| Tag | Contents |
|---|---|
X.Y.Z, latest |
QueryAPIGate with every optional feature: the PostgreSQL, MySQL, ClickHouse, DuckDB and MongoDB drivers (SQLite is built in), allowed_tables, password encryption, JWT, the Redis cache and queryapigate mcp |
X.Y.Z-h2, latest-h2 |
The same plus Java and the H2 driver - also the variant to use for a generic jdbc connection (mount your vendor's jar) |
The container keeps queryapigate.db in /data (create a starter with docker run --rm -v queryapigate-data:/data ghcr.io/anantharajuc/queryapigate queryapigate init). The named volume above works
out of the box. To use a folder on the host instead (-v "$PWD/data:/data"), create it yourself first (mkdir data) and make sure
it is writable by uid 1000, the container's user - a folder that Docker creates for you is owned by root, which the container
cannot write to (or run with --user "$(id -u):$(id -g)"). It runs as a non-root user under
gunicorn with one worker (each process keeps its own /metrics; scale out with more containers - see
Scaling out and the
reference deployment) and a health check on /health. Behind a
reverse proxy or load balancer, set QUERYAPIGATE_TRUST_PROXY=1. To build it yourself:
docker build -t queryapigate . (add --build-arg WITH_H2=true for H2).
For a real deployment - TLS via a reverse proxy, secrets in an env file, backups, Prometheus/Grafana - see documentation/DEPLOYMENT.md.
docker-compose.yml starts QueryAPIGate in front
of a PostgreSQL database seeded with sample films:
docker compose up --build
curl -H 'X-API-Key: demo-key' 'http://127.0.0.1:5000/q/films_by_rating?rating=PG&max_length=90'Open http://127.0.0.1:5000/docs, paste demo-key into the box at the top, and both saved queries appear as endpoints.
The demo listens on localhost only. Once the server is healthy, a one-shot seed container
(demo/seed.sh) creates the connection -
its password stored as a reference to an environment variable, ${DEMO_DB_PASSWORD} - and the two saved queries
through the Management API, the same calls the Console makes. Clean up with docker compose down -v.
| Endpoint | Method | Purpose |
|---|---|---|
/execute_sql |
POST | Run ad-hoc SQL (sql, connection_name, optional params). |
/q/<name> |
GET, POST | Run a saved query; query-string or body values become parameters. |
/connections/<name>/schema |
GET | List a connection's tables/views and their columns. |
/catalog |
GET | The saved queries this caller can reach, and the terms they're offered under. |
/events |
GET | Live saved-query runs (Server-Sent Events). |
/api/v1/... |
GET, POST, PATCH, DELETE | The Management API (admin only): saved queries and their versions, drafts and publishing, connections, API keys, roles, collections, history, the audit log, settings, the response cache and the example APIs - see Management API (v1). |
/health, /docs, /openapi.json |
GET | Liveness, Swagger UI, OpenAPI spec. |
/metrics |
GET | Prometheus text-format metrics: request/query counts and latencies, pool occupancy, rate-limit rejections. |
/console |
GET | The admin UI (the QueryAPIGate Console). /ui redirects here. |
Full details are in documentation/API.md. Saved queries can also be reached as MCP tools for an AI agent - see documentation/MCP.md.
/openapi.json is a complete, valid OpenAPI 3.0 document (checked in CI against the official validator), so a typed
client for Java, TypeScript, Go, or any of the ~50 languages openapi-generator supports
costs nothing in application code - generate it from the running server's own spec:
npx @openapitools/openapi-generator-cli generate \
-i http://127.0.0.1:5000/openapi.json -g java -o clients/java
# or: -g typescript-fetch, -g go, -g python, ...This is deliberately not something QueryAPIGate ships pre-generated: the spec already includes every saved query
as its own typed /q/<name> endpoint (see Quick start above), so a client generated against
your server reflects your saved queries, not a generic snapshot.
pip install -e ".[dev]"
ruff check .
mypy queryapigate
python -m unittest discover -s tests -t .The integration tests in tests/test_integration.py run against real MySQL, PostgreSQL, ClickHouse and H2 servers when
the matching QUERYAPIGATE_IT_* variables are set, and are skipped otherwise; CI runs them against service containers.
DuckDB's integration tests need no such variable - being embedded, they run unconditionally whenever the duckdb
package is installed.
tests/test_sql_guard_fuzz.py fuzzes the SQL guard and parameter binder with Hypothesis.
See CONTRIBUTING.md for the pull request process, CHANGELOG.md for what changed, and
BACKLOG.md for what's planned, in priority order.
Full documentation covers installation, configuration, database connections (including JDBC and DuckDB),
saved queries and parameters, authentication and authorization, API keys, rate limiting, caching, streaming,
OpenAPI, metrics, the admin UI, security and deployment. Start at the
documentation site or the documentation/ directory in this repo.
See BACKLOG.md for the full, prioritized list with rationale. Next is 1.0: a release candidate that freezes the REST and management APIs, the CLI, settings, error codes and the store, followed by 1.0.0. After that, all additive:
- Admin identity, phase 2 (#84): sign in to the Console through your identity provider (OIDC), groups mapped to admin roles.
- AI agents (#85-#88): OAuth 2.1 for the MCP server, descriptions for agents, a per-agent usage view, and write tools with a confirmation step.
- More sources (#76-#80): Trino, cloud warehouses, MongoDB aggregation pipelines, and more.
- Exports to object storage, incremental (#89), and column masking for partners (#63).
Contributions are welcome - database drivers, security, performance, UI, documentation, testing and observability are all useful areas. Please review CONTRIBUTING.md before submitting changes.
The wheel bundles the H2 Database JDBC driver (MPL 2.0 / EPL 1.0). The example data is generated
locally by queryapigate examples load; no third-party datasets are included.
QueryAPIGate is source-available, under the
Functional Source License, Version 1.1, MIT Future License
(FSL-1.1-MIT) © Anantha Raju C. It is not an OSI-approved open source license.
In plain terms - the LICENSE is the only authoritative text:
- You may use, copy, modify and redistribute it for any purpose that is not a Competing Use - including running it inside your own company, non-commercial education and research, and professional services you provide to someone who is themselves licensed to use it.
- You may not make it available to others in a commercial product or service that substitutes for QueryAPIGate or offers the same or substantially similar functionality (for example, selling it, or hosting it as a paid service).
- Each version becomes MIT on the second anniversary of the date it was released, and can then be used under MIT terms.
- Versions up to and including 0.6.1 were released under the MIT license. They are no longer distributed (and the repository history was reset in 0.7.0), but a copy you already obtained under MIT remains yours under those terms.
Need something the license does not cover, such as offering it as part of a hosted service? Get in touch - commercial licensing may be available. This is a summary, not legal advice.
Anantha Raju C - @anantharajuc - arcswdev@gmail.com
Project link: https://github.com/AnanthaRajuC/QueryAPIGate