Skip to content

Changelog

All notable changes to this project are documented here. The format follows Keep a Changelog and the project uses Semantic Versioning.

Unreleased

Added

  • Live usage on the Connections and API Keys admin-UI screens (closing #32): GET /connections and GET /api_keys now carry a usage object per entry (queries run, failures, rows returned - plus average latency for a connection) aggregated from the same in-process counters /metrics already renders, via two new metrics.summary_for_key()/metrics.summary_for_connection() functions. Previously that data existed only in Prometheus text form; a key or connection's row showed grants and, at best, a single last_used_at timestamp, with no way to see how much it had actually been used or how often it had failed without a separate /metrics scrape. See Connections and Authentication and permissions.
  • A saved query's History tab (closing #33) now shows request_id and key_name columns (execution_history already recorded both, added for the log-correlation work closing #19, but the admin UI never rendered either), plus a client-side status filter (all/success/failed) and a search box over connection, caller, request ID and error text. See Admin UI.
  • The Audit Log tab (closing #34) now has an action-type filter and a search box (actor, target, timestamp), filtering client-side over what it already fetched, and a create/delete snapshot's changes omits unset fields (null/""/[]) instead of always listing every field on the entity - a diff (an update) is unaffected, since it never had unset fields to begin with. See Audit log.
  • GET /catalog (closing #26): every saved query a caller can reach, and the terms it's offered under - cache_ttl, whether that specific caller can write through it (per-query write curation may differ query to query), and the caller's own rate limit alongside the server-wide one. Assembled from data that already existed but was scattered across admin-only screens a scoped key can never reach; scoped by the same reachability rule /openapi.json already uses, so a query a caller can't use is never listed. New config.format_rate_limit() (the inverse of parse_rate_limit()) renders a resolved (count, seconds) limit back into its human form, e.g. "100/minute". See The API catalogue.
  • A Metrics tab in the admin UI (closing #29) and a pre-built Grafana dashboard (documentation/grafana-dashboard.json). The tab parses /metrics client-side into stat tiles (requests, error rate, active queries, pool idle connections, rate-limit rejections, rows returned), two bar charts (requests by status, queries by connection) and a per-connection latency/error table - a live snapshot, deliberately with no history or trends, so a deployment with no Prometheus/Grafana stack still gets some visibility; needs no API key, since /metrics is already public. The dashboard is for deployments that do have that stack: request/query rate and latency (p50/p95/p99), error rate, rows returned, active queries, pool occupancy and rate-limit rejections, ready to import against a Prometheus scrape of /metrics. See Seeing it: a built-in view, or a real dashboard.
  • Refreshed admin UI screenshots in the README (Connections, Saved Queries, API Keys, Roles, Run SQL), plus new ones for a saved query's History tab, the Audit Log and the Metrics tab.

Fixed

  • The admin UI header broke at common laptop widths once it reached eight tabs: tab labels wrapped onto two lines at 1280px, the API key bar's Apply button was clipped at the right edge, and between roughly 980px and 1200px the header overflowed and scrolled the whole page sideways. Labels no longer wrap, the key bar never shrinks, the tab row scrolls horizontally if it ever runs out of room, and below 1240px the tabs move onto their own row. Checked with no horizontal page scroll at every width from 390px to 1440px.
  • The action and status dropdowns on the Audit Log and saved-query History filters stretched to the full row width, pushing the search box onto its own line; they now size to their content.
  • The Connections, API Keys, Roles and Audit Log tables now scroll horizontally inside their panel on narrow screens instead of widening the whole page.

[0.4.0] - 2026-09-25

Added

  • Named permission roles (closing #22): a role (/roles, admin only) is a reusable template for a key's connections, allow_writes, queries, rate_limit, allowed_ips and allowed_write_ops - POST /api_keys with "role": "<name>" copies those fields onto the new key once, at creation. It's a template, not a live link: editing or deleting a role afterward never touches a key already created from it, since authenticate() only ever reads the key's own stored entry. A key records which role it came from in created_from_role, purely informational. role can't be combined with an explicit grant field in the same request (400) - expires_at is the one exception, since it's inherently per-key rather than part of a shared template. Admin UI gained a Roles tab and a "Create from" picker in the New API key form. See Permission roles.
  • Encryption at rest for connection passwords (SQL2API_SECRET_KEY, closing out #24): a literal password is now encrypted with Fernet before it touches disk - existing connections immediately at startup, new ones the moment they're saved - and decrypted only in memory at the instant a connection is actually opened. Needs the new optional sql2api[encryption] extra (bundled in [all]); a clear startup error names the missing package or a malformed key rather than a raw ImportError or silent failure. A ${VAR} reference is untouched either way - never a secret stored in the file to begin with. An encrypted password masks the same as a literal one in GET /connections and the audit log; a missing or rotated SQL2API_SECRET_KEY fails a request clearly (500, naming the problem) rather than passing ciphertext to the driver, and a startup warning fires if encrypted passwords exist on disk with no key configured to read them. See Encryption at rest.
  • Per-query write curation for API keys: a queries entry can now be {"name": ..., "allow_writes": true} instead of a plain name, granting write access to that one saved query specifically - on top of, never instead of, the key's blanket allow_writes. Lets a key have connections: [] and allow_writes: false yet still write through one curated endpoint (e.g. submit_order), the shape the external-client queries grant was originally built for but couldn't quite express. The "*" wildcard can never carry a write grant - wanting write access to a specific query means enumerating the whole queries list explicitly, the same explicit opt-in shape allow_writes already has everywhere else. Admin UI's saved-query checkbox grid gained a "write" checkbox per query. See Per-query write curation.
  • Finer-grained query governance, two of three gaps: an API key with write access can now be narrowed to specific allowed_write_ops (e.g. ["insert", "update"]) rather than every write keyword being equally permitted once allow_writes is on - checked in sqltools.validate_sql(), never restricts a read-only statement. A new SQL2API_STREAM_MAX_ROWS caps a ?stream=true export, which was previously unbounded regardless of SQL2API_MAX_PAGE_SIZE; past the cap the export ends early, a WARNING is logged, and sql2api_stream_exports_total counts it under a new status="truncated", distinct from "success". Admin UI's API Keys tab gained an "Allowed write operations" form field and shows a write-op count on the Access column. Table-level allow-listing (the third, hardest gap) remains open - it needs real SQL parsing, not the lightweight guard this project deliberately uses. See Write operation granularity and Streaming exports.
  • Structured per-query observability fields, second slice (SQL hash, serialization time): the per-query and streaming-start log lines now carry a full SHA-256 sql_hash alongside the existing full SQL text, so a log aggregator can spot "did this same query run elsewhere/before" without storing or searching the SQL itself. Response body serialization (JSON/CSV/TSV/XML/YAML/XLSX encoding) is now timed separately from query execution - a new sql2api_serialization_duration_seconds metric by output format, a serialization_ms field on the per-request access log line, and a serialization_ms field on a saved query's execution_history entries alongside the existing duration_ms (query time) - so encoding cost (which can rival query time for XLSX or other large-page exports) is no longer invisible, folded into "whatever's left over" between total and query latency. Closes out #19 entirely. See Observability.
  • Structured per-query observability fields, first slice (query correlation): a saved query's execution_history entries now carry request_id and key_name, so a slow or failed run visible in the admin UI's History tab can be traced back to the exact log line and caller that produced it. In SQL2API_JSON_LOGS mode, the per-query, streaming-start, slow-query and per-request access log lines now also carry their key fields (connection, dialect, limit/offset/timeout, duration_ms, method/path/status) as real top-level JSON keys via extra={...}, not just folded into message - a log aggregator can filter or aggregate on them directly. See Observability.
  • Basic data visualization in the admin UI: a "Chart" toggle on any tabular result (Run SQL and saved-query Run tabs) draws a quick bar chart of the current page, off by default. Deliberately scoped to the page on screen, not the full result - a visible note says so, since page_size is capped and a chart of one page of a much larger result could otherwise look complete without being one. Label/value columns are pickable; no charting library, inline SVG matching the editor's own no-dependency approach.
  • Audit logging for administrative actions (GET /audit_log, admin only): a durable, capped record of every API key, connection and saved query created, changed or removed, distinct from live request/query observability. An update records a diff of only the fields that actually changed; a create or delete records a full snapshot instead. A connection's password is never a value in either form - masked as ******** in a snapshot, reported only as the literal string "changed" in a diff - and an API key's entry never includes its secret or hash. Admin UI gained an "Audit Log" tab. See Audit log.
  • A startup warning for connections storing a literal, non-empty password directly in db_connections.json instead of a ${VAR} reference to an environment variable - names every affected connection in one line. Doesn't block startup or change stored data; a nudge toward the existing ${VAR} convention, not new enforcement. See Connections.
  • Two new /metrics series: sql2api_rows_returned_total (rows actually returned, by connection, dialect and calling key - the trimmed page for a paged query, or however many rows made it out of a streaming export before it finished or failed partway through) and sql2api_active_queries (a gauge of queries currently executing, paged or mid-stream - for a streaming export this stays incremented for as long as the client keeps reading, since the connection stays checked out the whole time, not just for the initial dispatch). See Observability.
  • IP allowlisting per API key (allowed_ips, a list of IP addresses or CIDR ranges - IPv4 or IPv6, mixed freely): real defense in depth for a key handed to an external party with known, stable infrastructure, since even a leaked key then only authenticates from an expected address. Checked in apikeys.authenticate() against the same client address SQL2API_TRUST_PROXY/ProxyFix already establish as trustworthy for rate limiting, not re-derived. Uses the stdlib ipaddress module for parsing and containment - no new dependency. A request from outside the list fails exactly like a wrong key (401), not a distinct error. Restricts who may use a key at all, independent of per-key rate limiting (how much a caller who is already allowed may do); the admin key is never restricted by it. PATCH /api_keys/<name> with an explicit {"allowed_ips": null} clears an existing restriction, the same pattern expires_at/rate_limit use. Admin UI gained an "Allowed IPs" form field and an "IPs" table column. See IP allowlisting.
  • Per-key rate limiting (rate_limit, e.g. "100/minute"): an API key can now carry its own quota, checked in addition to SQL2API_RATE_LIMIT, never instead of it - so handing scoped keys to several external clients no longer means they all draw from one shared server-wide budget where a single noisy integration can exhaust it for everyone else. Applies even when the server-wide limit is unset entirely. Rejections from a key's own limit read {"error": "Rate limit exceeded for this API key", ...}, distinguishable from the server-wide rejection's plain "Rate limit exceeded", and surface their own X-RateLimit-Key-Limit/X-RateLimit-Key-Remaining headers alongside the existing server-wide pair. PATCH /api_keys/<name> with an explicit {"rate_limit": null} clears an existing per-key limit. Required a small architectural addition, not just a new field: RateLimiter only ever enforces one (count, period) spec per instance (by design - it wipes every bucket when a different spec arrives, so an admin changing SQL2API_RATE_LIMIT doesn't mix old and new rules), so two keys with different limits need genuinely separate limiter instances - see the new ratelimit.KeyRateLimiters, one instance per distinct spec actually in use, with keys sharing a spec correctly sharing an instance too. Admin UI gained a "Rate limit" field and table column. See Per-key rate limiting.
  • Per-key usage visibility (last_used_at): GET /api_keys now reports when a key last authenticated a request, so a stale key nobody has called in months is easy to spot, or a newly-issued external key's wiring can be confirmed. Updated at most once a minute per key (not on every single request, which for a busy key would turn every call into a disk write for no real benefit) - read it as "roughly how recently," not an exact timestamp. A never-used key simply has no last_used_at field. The admin UI's API Keys table gained a "Last used" column alongside "Created". See Last used.
  • API key expiry (expires_at, YYYY-MM-DD): a key stops authenticating on its own once the date passes (valid through the end of that date), checked live on every request the same way active already is - no background sweep, nothing to schedule or fail silently. For time-boxed access (a trial integration, a partner engagement with a known end date) without anyone having to remember to come back and revoke it. PATCH /api_keys/<name> with an explicit {"expires_at": null} clears an existing expiry without rotating the secret; omitting the field from a PATCH body leaves whatever expiry a key already had untouched. The admin UI's API Keys form gained an "Expires" date field, and the table shows each key's expiry (or "never"), visually distinguishing an already-expired key from an active one. See Key expiry.
  • A "Curl" tab on each saved query in the admin UI, after Run/SQL/History: a ready-to-copy curl command for that query's GET /q/<name> endpoint, with each parameter that has no declared default shown as a readable <name> placeholder to fill in (not percent-encoded - built as a plain string rather than through URL.href, which would otherwise turn <id> into %3Cid%3E) and the API key, if any, redacted to a placeholder rather than the session's real value, same policy as the Run SQL tab's own "Copy as curl".
  • Per-saved-query API key access (queries): a key can now be scoped to a specific list of saved-query names, independent of and additive with connections - so an external-client key can reach exactly monthly_revenue and a handful of other approved queries, with no connection access of its own and no ad-hoc SQL access, while internal keys keep the existing coarser connection-wide grant unchanged. queries may also be "*" for every saved query by name without ad-hoc access, a middle tier between a single connection and full admin. /openapi.json//docs now reflect a key's actual reach - a queries-scoped key sees only its own approved catalogue, not the full internal list of saved queries, closing a pre-existing gap where any authenticated key could see every saved query's name, description and parameters regardless of its own connection scope. Backward compatible: a key created before this field existed keeps behaving exactly as it did. See Per-saved-query access.
  • Streaming exports: ?stream=true on POST /execute_sql and GET/POST /q/<name> (csv/tsv/ndjson only) streams the whole result straight from the database cursor instead of capping it at page_size - always read-only regardless of SQL2API_ALLOW_WRITES, since a large export has no business mutating data (this also sidesteps a lot of incidental complexity around commit timing on a connection held open for a long download). MySQL (an unbuffered cursor), PostgreSQL (a named, server-side cursor) and ClickHouse (execute_iter) stream without the driver buffering the whole result client-side first - verified end-to-end against real servers: 1 million rows streamed over real HTTP with the server process's own memory sampled throughout stayed flat (~49-55MB for MySQL/PostgreSQL), against several hundred MB fetching the same result the ordinary way. SQLite, H2, the generic jdbc type and DuckDB still bound this project's own memory to one batch at a time regardless of result size, even where the underlying engine or driver holds more than that internally (documented per-dialect, not assumed - see the _DuckDB/_H2/ _Postgres driver docstrings in runners.py). A PostgreSQL-specific quirk only a real server surfaced: a named cursor's execute() is really a DECLARE CURSOR under the hood and does not run the query at all
  • column info and statement_timeout cancellation are only available after the first fetch, the reverse of every other driver here. See Streaming exports.
  • Generic DuckDB connections (db: "duckdb") - opt-in via sql2api[duckdb], needing no external runtime (a native Python extension, like SQLite). One connection type covers two uses: a genuinely capable embedded database (database: <path>, same shape as SQLite) and querying CSV/JSON/Parquet files directly from a saved query's own SQL (SELECT * FROM read_csv(:path)), no import step or new connection fields. Schema introspection, pooling and the SQL guard's ANSI (quote-doubling) literal rules all apply unchanged. Two things a real DuckDB database surfaced that aren't in its docs: opening a second connection to a file with a different read-only setting than one already open on it in-process fails outright, so - like h2/jdbc - every pooled connection here is opened read-write and the read-only guarantee rests on the SQL guard alone; and unlike h2/jdbc, the query time limit is enforced, via Connection.interrupt() on a background timer, since DuckDB's Python client (unlike JDBC through jaydebeapi) exposes a real cancellation hook. See DuckDB connections.
  • A show/hide toggle on the connection form's password field, in both create and edit mode - verified with a real headless-Chrome test that it doesn't disturb the existing password-mask round-trip.
  • The Run SQL tab's stat bar now leads with the response's HTTP status code and status text (Postman-style "200 OK · 1 row · json · 8 ms"), accent-colored to read as success at a glance; the error path is unchanged. Verified with a real headless-Chrome test.
  • The admin UI's Run SQL tab and New saved query drawer have a Schema panel next to the SQL editor: lists the selected connection's tables, expands to show columns (type/nullability as a tooltip), and clicking a table or column inserts its name at the cursor. Updates automatically when the connection changes; a connection whose schema isn't available shows that message inline rather than through the page's error banner. A thin client of the existing GET /connections/<name>/schema endpoint - no new backend logic. Verified end-to-end with a real headless Chrome (Playwright) test covering both mount points, expand/ collapse, click-to-insert for both tables and columns, connection-switch reactivity, the unsupported- dialect message, and the refresh button - zero uncaught JS errors.
  • mypy runs in CI (gradual typing - [tool.mypy] in pyproject.toml; the codebase has no type hints yet, so it catches genuine static errors rather than demanding annotations everywhere). It found two real, if low-impact, issues fixed here: runners._Driver.DIALECT was untyped, so mypy inferred None as its exact type and flagged every dialect subclass for assigning a string to it; and metrics.py's module-level counters had no annotation for their (tuple key -> value) shape. mypy 2.x dropped support for running on Python 3.9 (Requires-Python >=3.10), so CI's 3.9 job resolves the last compatible 1.x release instead - which, it turned out, disagrees with 2.x about whether ignore_missing_imports alone covers a module that's installed but ships no type stubs (PyYAML's import-untyped error, as opposed to one mypy can't find at all). disable_error_code = ["import-untyped"] covers it on both mypy generations.
  • Test coverage is measured in CI and uploaded to Codecov (a badge is in the README) - currently 93% across sql2api/ from the unit test suite alone (not counting the real-database integration tests).
  • Python 3.14 added to the CI test matrix.
  • Generic JDBC connections (db: "jdbc") reach any database not covered by a dedicated driver - Oracle, SQL Server, DB2, Snowflake and others - by generalising the embedded-JVM approach h2 already used. A connection needs jar (the vendor's driver jar), driver_class and jdbc_url instead of host/port/database; everything else (the SQL guard, parameter binding, pooling, output formats) is unchanged. Two limits are inherent to sharing one JVM per process, not specific to this connection type, and are documented in DATABASE_CONNECTION_CONFIGURATION.md: SQL2API_QUERY_TIMEOUT is not enforced (no portable way to cancel a statement across arbitrary JDBC drivers), and a new jdbc connection whose jar was not already on the classpath when the JVM first started needs the server restarted before it can be used. Schema introspection (GET /connections/<name>/schema) answers 400 for this connection type rather than guessing at a vendor's system catalogue. Verified end-to-end against a real H2 server reached through the generic driver (not the dedicated h2 one) - connection pooling, bound parameters, client-side pagination (LIMIT/OFFSET is not portable SQL either, so it is no longer appended server-side for this type), the read-only guard, and the schema-browser boundary - plus the JVM classpath-union logic that lets an H2 and a jdbc connection share the one JVM regardless of which one is used first.
  • The admin UI (/ui) has an API Keys tab: create, edit and revoke scoped API keys, matching the connections/saved-query tabs' style. A freshly created key's secret is shown once, with a copy button, the same one-time reveal the API itself enforces. A scoped (non-admin) key sees the same "only the admin key" message here as on the Connections and Saved Queries tabs. Verified end-to-end with a real headless Chrome browser: create, the secret-reveal, edit (including switching between the "*" wildcard and specific connections), revoke, and the admin-only empty state for a scoped key - zero uncaught JS errors across the whole flow.
  • Audit logging: log lines and /metrics now carry the name of the API key that made the request - admin for SQL2API_API_KEY, a scoped key's own name, or - when no key is configured at all. Distinguishing admin from - needed splitting what was one "no key configured" state into two in apikeys.Permission. Kept off the latency histograms so the number of keys never multiplies their bucketed output; requests and query counts still carry it.
  • Opt-in response caching for saved queries: set cache_ttl (seconds) when saving one. A cache hit answers with the identical body and X-Cache: HIT (X-Cache: MISS on a fresh response), carries ETag and Cache-Control: max-age=<cache_ttl>, and honours If-None-Match with a bodyless 304. Never used for a saved query whose SQL is a write, regardless of cache_ttl - serving a cached response in its place would silently skip that write - and a cache hit is not recorded in execution_history, since nothing ran against the database. Verified against a real MySQL server, including that a cached write still runs on every call.
  • A documentation site (MkDocs, Material theme), built from this README and documentation/ - there is still exactly one place to edit each document; docs/ only mirrors their paths so cross-links keep working. Deployed to GitHub Pages by .github/workflows/docs.yml on every push to main that touches a doc file (needs a one-time Settings -> Pages -> Source: GitHub Actions from a repository admin).
  • Per-key API permissions: SQL2API_API_KEY stays a full-access admin key, unchanged. New scoped keys (POST /api_keys, admin only) are each limited to a list of connection names (or every connection) and can be denied write access even when the server otherwise allows it - a key's allow_writes can only narrow SQL2API_ALLOW_WRITES, never widen it. Only the admin key can manage connections, saved queries or other API keys. A key's secret is never stored, only its SHA-256 hash in api_keys.json; the server generates it and shows it exactly once, when the key is created. Creating the first scoped key turns on authentication for the whole server immediately, even without SQL2API_API_KEY set (the server warns at startup if that would lock configuration changes out, since only the admin key can manage the server).
  • Observability: every response carries X-Request-Id, and log lines written while handling that request carry the same ID (plain text by default; SQL2API_JSON_LOGS=1 for one JSON object per line). A query taking at least SQL2API_SLOW_QUERY_THRESHOLD seconds (default 1) is logged as a warning. GET /metrics (always public, like /health) serves Prometheus text-format metrics: request and query counts/latency histograms (by endpoint/status and by connection/dialect), idle pool occupancy, and rate-limit rejections. Logging is now configured once inside create_app(), so it applies under gunicorn/WSGI too, not just sql2api serve - previously INFO-level application logs were silently dropped in that path.
  • GET /connections/<name>/schema lists a connection's tables and views with their columns (name, type, nullability, position) - self-service query writing without leaving the API. One catalogue query per database (information_schema for MySQL/PostgreSQL/H2, system.tables/system.columns for ClickHouse, sqlite_master/pragma_table_info for SQLite), run through the normal read-only execution pipeline, so it needs no new driver logic. Capped at 5000 columns per connection (truncated: true if a schema is larger than that).
  • A small admin UI at /ui: manage connections (including proper password-mask round-tripping) and saved queries (create, run, per-version delete, an execution-history view per version), and run ad-hoc SQL with a syntax-highlighted editor, page-size presets and Next/Previous paging - without leaving the browser. Self-contained (no build step, no external script or stylesheet) and a pure client of the existing JSON API - no new server-side logic. Linked from /docs, and shares its X-API-Key storage with the docs page. Query results are always rendered through DOM APIs, never innerHTML, so a value coming back from a database can never execute as markup - verified with a 39-assertion real-browser (Playwright) test, including an XSS-payload check, run both unauthenticated and with an API key set.
  • A documentation callout pointing out that /openapi.json can be imported directly by URL into Postman or Insomnia to get a ready-made request collection - no separate export step.
  • An "Explain" button next to Run on the Run SQL tab: runs EXPLAIN <current query> through the existing execute path and shows the plan, a UI shortcut for a statement the SQL guard already allows.
  • A collapsible "Headers" panel under query results, listing every header the response actually carries (X-Page, X-Request-Id, etc.), built from data renderResponse() already has.
  • "Copy as curl" and "Copy as TSV" buttons on the Run SQL tab's results: the former builds the exact curl command for the request just made (with the API key, if any, redacted to a YOUR_KEY_HERE placeholder so copying the command doesn't leak the key), the latter copies the current result rows as a paste-ready TSV table for Excel/Sheets - both pure client-side transforms of data already on hand.
  • A sessionStorage-backed ad-hoc query history on the Run SQL tab: the last 20 distinct queries run, clickable to restore into the editor, surviving a page reload within the same tab.
  • A "preview" affordance next to each table in the schema browser (both the Run SQL tab's panel and the New saved query drawer's) that fills in and runs SELECT * FROM <table> through the existing execute path, so a table's data can be seen without hand-writing SQL.
  • A collapsible JSON tree for non-tabular /execute_sql responses (anything that isn't a JSON array), replacing the previous flat <pre> dump - each object/array level can be expanded or collapsed.

All eight of the above were verified end-to-end with real headless-Chrome (Playwright) tests against a live server, including a dedicated clipboard-reading test for "Copy as curl"/"Copy as TSV" and coverage of both schema-browser preview entry points - zero uncaught JS errors across the runs.

Fixed

  • GET /list_files returned 404 ("Folder not found") on a brand-new install before anything had ever been saved, instead of an empty list - inconsistent with the very similar latest_versions() used for the OpenAPI catalogue, which already handled this correctly. A list endpoint with nothing to list now answers {"files": []} with 200, as it always should have.
  • Security hardening (MySQL/ClickHouse): the single-statement/read-only SQL guard now reads string literals with the quoting rules the target database actually uses. MySQL and ClickHouse honour a backslash escape inside '...'/"..." string literals by default; PostgreSQL, SQLite and H2 do not. The guard previously used one, doubling-only rule for every database. For MySQL/ClickHouse connections, a crafted value (ending in an escaped quote, more text, then a closing quote) could make the guard think a ; was safely inside a string literal when the database would treat it as a live, second statement - confirmed against real MySQL and ClickHouse servers. No path to unauthorized data access or modification was found on the current codebase (this project's runners never call cursor.nextset(), so on MySQL the smuggled statement was queued but never pulled, and MySQL's own read-only-transaction mode - already set on every read-only connection - independently rejects a smuggled write; ClickHouse's server independently refuses multi-statement queries outright) - but it was a real gap in an explicitly documented guarantee and is now fixed with a dialect-aware guard, covered by a fuzz/property test suite (tests/test_sql_guard_fuzz.py, using Hypothesis) that pins the exact confirmed payload and its outcome on each database. -- comments now also require a following whitespace character or end of input, matching real SQL comment syntax, and backtick-identifier doubling ( ``) is now recognised - both changes only make the guard more likely to reject ambiguous input, never less.

0.3.0 - 2026-09-21

Added

  • Docker images are published to GitHub Container Registry on every release (ghcr.io/anantharajuc/sql2api, tags X.Y.Z and latest, plus -h2 variants with Java and the H2 driver), for linux/amd64 and linux/arm64. The workflow tests each image before publishing and can be run by hand as a dry run.
  • docker compose up --build starts a self-contained demo: SQL2API in front of a seeded PostgreSQL database, with example saved queries, an API key and a rate limit. CI runs it on every change.
  • The image has a HEALTHCHECK on /health, OCI labels, and access logging.
  • Parameter rules for saved queries: besides a type, query_parameters can declare default, required, enum, min/max, min_length/max_length, pattern and description. Violations are rejected with a 400 that lists every problem in an errors map; optional parameters without a value are bound as NULL.
  • Every saved query is documented as its own endpoint in /openapi.json and /docs, with its parameters, rules and default connection (never its SQL). With an API key set, this section is only shown to authenticated readers, and the docs page has a box for the key.
  • The Release workflow now refuses to publish when the tag does not match sql2api.__version__, is not on main, or has no dated changelog section.
  • CORS support for browser clients (SQL2API_CORS_ORIGINS, off by default): allowed origins are echoed back, preflight requests are answered without an API key, and the pagination and rate-limit headers are exposed to the page. Starting with * and no API key logs a warning.
  • Rate limiting (SQL2API_RATE_LIMIT, e.g. 60/minute, off by default): a per-client token bucket answering 429 with Retry-After, plus X-RateLimit-Limit/X-RateLimit-Remaining headers. It runs before the API key check so key guessing is throttled; /health and preflights are exempt. A malformed value stops startup.
  • SQL2API_TRUST_PROXY (number of reverse proxies) makes the app use the client address and scheme from X-Forwarded-* headers; without it those headers are ignored so they cannot be forged.

Changed

  • Saving a query validates its query_parameters and rejects declarations that the SQL does not use.
  • Requests rejected by parameter validation are not recorded in execution_history.

Fixed

  • The Docker image ran gunicorn with its default 30 second worker timeout, the same as the default query time limit, so a query hitting its limit raced gunicorn killing the worker. The timeout is now 120 seconds.
  • The OpenAPI document was not valid OpenAPI 3.0 (exclusiveMinimum: 0 and empty required lists), which strict tools and client generators reject. It is now validated in the test suite.

0.2.0 - 2026-09-21

Added

  • Connection pooling for MySQL, PostgreSQL, ClickHouse and H2: connections are reused between requests instead of opened per request (SQL2API_POOL_SIZE, default 5 idle connections per distinct setting, 0 disables; SQL2API_POOL_IDLE_TIMEOUT, default 300 s). Against a local server, per-request time for a trivial query dropped from about 14 ms to 0.5 ms on MySQL and H2; ClickHouse barely changed (about 1.2 ms to 1.0 ms). Connections are reset between users, health-checked after idling, discarded after errors, and closed at once when a connection is changed or deleted through the API.
  • Query time limit: statements are cancelled on the database after SQL2API_QUERY_TIMEOUT seconds (default 30, 0 disables) and the request fails with HTTP 504. A request can lower the limit with ?timeout= (or a timeout field in the body) but never raise it. Enforced natively on MySQL/MariaDB, PostgreSQL, ClickHouse, SQLite and H2.

Changed

  • Queries that run longer than 30 seconds are now cancelled by default. Set SQL2API_QUERY_TIMEOUT=0 to restore the previous unlimited behaviour.

Fixed

  • The process could hang on exit after H2 had served concurrent requests: JPype waited forever for worker threads that jaydebeapi had attached to the JVM as non-daemon threads. Threads that use H2 are now attached as daemons.

0.1.0 - 2026-09-21

First public release, restructured from the original single-file application.

Added

  • Installable sql2api package with a sql2api serve / sql2api init command line and python -m sql2api.
  • Bound query parameters (:name) for every database, so values never become part of the SQL text.
  • Saved queries served as endpoints: GET|POST /q/<name> with typed parameters and an optional default connection.
  • DELETE /saved_sql/<name> (whole query or one ?version=) and DELETE /connections/<name>.
  • Execution history recorded per saved-query version (last 50 runs, with status, row count and duration).
  • X-Page, X-Page-Size and X-Has-More response headers; ndjson output format.
  • ${ENV_VAR} references in connection settings so secrets can stay out of db_connections.json.
  • OpenAPI description at /openapi.json, Swagger UI at /docs (/ redirects there), and a /health endpoint.
  • Read-only-by-default execution (SQL2API_ALLOW_WRITES), optional API key (SQL2API_API_KEY), page size limit (SQL2API_MAX_PAGE_SIZE) and configurable data folder (SQL2API_HOME).
  • Dockerfile, GitHub Actions CI (unit tests plus integration tests against PostgreSQL, MySQL, ClickHouse and H2), Dependabot, and runnable examples.

Changed

  • Saved-query and connection files are read from SQL2API_HOME (default: the current directory) instead of the folder next to the source file; file access is confined to saved_sql/.
  • Passwords are masked by GET /connections.
  • Unknown format values now return 400; page_size is capped; errors use proper HTTP status codes.
  • The development server no longer runs in debug mode by default.

Fixed

  • Arbitrary file read through /view_file_content and path traversal through saved-query filenames.
  • Pagination now works on every database; trailing LIMIT/OFFSET handling is case-insensitive.
  • Database connections are always closed; ClickHouse queries no longer run twice.
  • JSON column order is preserved; Decimal, date and driver-specific number types serialise correctly.
  • Concurrent saves can no longer lose a version.