QueryGateway

A self-hosted platform that turns Oracle SQL queries into secure, versioned REST API endpoints — no application code required. Author a parameterized query in a guided wizard, attach authentication, choose a data-freshness strategy, and publish a live or snapshot-backed endpoint that other systems can consume.

QueryGateway is built for teams that need to expose data from an Oracle database over HTTP safely and quickly. Instead of writing and deploying a bespoke microservice for every report or integration, an administrator defines the query once through a web console, and QueryGateway handles routing, authentication, parameter validation, SQL safety, optional caching, and scheduled refresh.

⭐ If QueryGateway is useful to you, please consider starring the repo — it helps others discover the project.

Contents

• Why QueryGateway?
• Who It's For
• How It Works
• Features
• Platform Support
• Quick Start
• Local Run (Without Docker)
• Development
• Database Migrations
• Roadmap
• Contributing & Community
• Documentation
• Tech Stack
• License

Why QueryGateway?

Exposing data from an Oracle database over an API usually means writing a new service: wiring up a connection pool, validating inputs, guarding against SQL injection, adding authentication, handling caching, and shipping a deployment — repeated for every query. It's slow, and each hand-rolled service is another place for a security mistake.

QueryGateway collapses that work into a configuration step. Define the query once, and you get a hardened, versioned endpoint with parameter validation, configurable authentication, and optional caching built in:

• No glue code — go from SQL to a deployed endpoint through a wizard, not a codebase.
• Built-in security controls — bind-parameter-only SQL, encrypted credentials, and per-endpoint authentication (Bearer/Basic/API key).
• Fast and consistent — every endpoint gets the same validation, logging, and versioning, so quality doesn't depend on who wrote it.
• Cache when you need it — serve heavy queries from scheduled snapshots instead of hitting Oracle on every request.
• Self-hosted — your data and credentials never leave your infrastructure.

Who It's For

• Data & platform teams that field a constant stream of "can you give me an API for this report?" requests.
• Internal tools & dashboards that need read access to Oracle data without direct database credentials.
• Partner / B2B data feeds where each consumer needs its own authenticated, parameterized endpoint.
• BI and analytics tools that consume JSON over HTTP and benefit from cached snapshots of expensive queries.
• Mobile and web backends that need a thin, safe read layer over an existing Oracle system.

How It Works

Define connection ─▶ Author SQL (with :bind params) ─▶ Attach auth ─▶ Choose data strategy ─▶ Publish
                                                                                                  │
   API consumer ──── GET /api/v1/data/<your-endpoint> ──── auth-checked, parameter-validated ─────┘

1. Connect to your Oracle database with securely stored, encrypted credentials.
2. Author a SELECT query using unquoted named bind parameters (:param_name) in a rich SQL editor. The wizard detects parameters as you type and requests temporary sample values before previewing; preview samples are never persisted. Optional live-request parameters can have typed defaults, explicit SQL NULL, or today/yesterday date defaults. Required parameters remain mandatory for both live and snapshot requests even when a default exists, and dates accept YYYY-MM-DD or DD-MM-YYYY.
3. Secure the endpoint by attaching a Bearer token, Basic Auth, or API key policy.
4. Choose a data strategy: serve results live on each request, or from a scheduled snapshot cache. Snapshot endpoints explicitly map each request parameter to a cached output column and comparison. Schedules independently own the SQL values that define cache coverage: bind a fixed value, explicit SQL NULL, logical run date, relative date, or a reusable calendar window, then preview the next three resolved runs before creating the schedule.
5. Publish a versioned endpoint under /api/v1/data/* that resolves dynamically — no service restart needed.

Features

QueryGateway is organized into five admin modules, all driven from the React admin console:

Module	What it does
Connections	Create, edit, test, and delete Oracle database connections. Credentials are encrypted at rest; pool sizing and timeouts are configurable. Uses python-oracledb; thin mode needs no native client, while the backend Docker image includes Oracle Instant Client 19.32 for thick mode.
API Creation Wizard	A multi-step wizard that turns a parameterized SQL query into a deployable GET endpoint: pick a connection, author SQL with a rich editor, supply preview-only sample values, configure optional live-request defaults, preview rows and inferred schema, map/rename output columns, attach an auth method, select a data strategy, and map every snapshot request parameter to a cached output column and comparison.
Authentication	Manage per-endpoint auth methods — Bearer token (JWT), Basic Auth, and API key. Tokens are issued/verified with PyJWT; credentials are hashed with bcrypt. Every /api/v1/data/* request is authenticated; endpoints without a dedicated method require the platform admin Bearer token.
Scheduling & Snapshots	Schedule query refreshes with friendly hourly, daily, weekly, or monthly calendar controls, an advanced custom-cron option, or a fixed interval. Each schedule has an IANA timezone and explicit per-parameter sources: fixed value, SQL NULL, logical run date, relative date, or inclusive calendar-window boundaries such as previous day, last N complete days, week/month to date, previous week, and previous month. Authenticated requests select the newest retained snapshot that covers their typed parameter values, then apply explicit equality or inclusive range filters to cached rows. Preview the next three resolved runs, run now, pause/resume, and inspect persisted logical-date, window, and resolved-bind audit context. Results are cached as PostgreSQL JSONB snapshots. Active APScheduler jobs are restored on API startup; deletion preserves job-run history.
Settings & Health	Configure runtime settings (base URL/port, logging level, query timeouts, CORS/rate-limit inputs) and view a health dashboard covering API, PostgreSQL, Oracle connectivity, scheduler status, and recent job outcomes.

Security by Default

• SQL injection resistant — user-defined SQL runs only through SQLAlchemy text() with named bind parameters. Request values are never concatenated into SQL strings, and bind values are validated through typed schemas before execution.
• Encrypted credentials — Oracle connection secrets are encrypted at rest using an environment-provided key.
• Mandatory data authentication — attach a Bearer token, Basic Auth, or API key policy to an endpoint. If no dedicated method is attached, the endpoint requires the platform admin Bearer token; anonymous data access is never allowed.
• Structured, redacted logging — structlog emits JSON logs with correlation fields (request_id, user, endpoint, status, duration_ms, method, client_ip, event); credentials and tokens are redacted before emission.

Two API Surfaces

Namespace	Purpose	Who calls it
/api/v1/admin/*	Manage connections, auth, endpoints, schedules, settings, and health	The admin SPA
/api/v1/data/*	Serve dynamic data from live queries or cached snapshots	Your API consumers

All routes are versioned from day one; breaking contract changes are introduced under a new version path rather than mutating v1.

Platform Support

• Development: Windows and Ubuntu/Linux
• Deployment: Ubuntu/Linux (recommended)
• Docker workflow: supported on both Windows (Docker Desktop) and Ubuntu/Linux

Python version: Python 3.14 or newer is required (asyncpg 0.31.0+ ships CPython 3.14 wheels). Create the virtual environment with a 3.14 interpreter: py -3.14 -m venv .venv (Windows) or python3.14 -m venv .venv (Linux).

Quick Start

# Install dependencies
make setup

# Run checks
make check

# Start with Docker
cp .env.example .env   # Set all required secrets and admin credentials (see deployment.md)
make docker-up

make docker-up runs the one-shot migrate service first, then starts the API and web containers after PostgreSQL is healthy and Alembic is at head.

The backend image downloads Oracle Instant Client 19.32 Basic from Oracle's versioned HTTPS URL, verifies Oracle's published SHA-256, and registers it with the Linux dynamic linker. Set ORACLE_CLIENT_LIB_DIR=/opt/oracle/instantclient_19_32 in .env to initialize python-oracledb thick mode when the API starts; leave it blank for thin mode.

• Frontend SPA: http://localhost
• API through nginx: http://localhost/api/v1/...

Local Run (Without Docker)

Python version: Use Python 3.14 or newer (asyncpg 0.31.0+ provides CPython 3.14 wheels).

Note (Windows): psycopg2-binary may fail to build from source if PostgreSQL dev tools are not installed. The requirements.txt uses a relaxed pin (>=2.9.9) so pip selects a pre-built wheel automatically. Always run pip install --upgrade pip first.

Windows (PowerShell)

# Step 1 — Start PostgreSQL (skip if already running)
docker run -d --name db2api-pg `
  -e POSTGRES_USER=db2api -e POSTGRES_PASSWORD=db2api -e POSTGRES_DB=db2api `
  -p 5432:5432 postgres:16
# Wait ~5 seconds for PostgreSQL to initialize before continuing

# Step 2 — Backend
cd backend
py -3.14 -m venv .venv
.venv\Scripts\Activate.ps1
pip install --upgrade pip
pip install -r requirements.txt
Copy-Item .env.example .env
# edit .env — set every required secret and admin credential before continuing

# Step 3 — Run database migrations
alembic upgrade head

# Step 4 — Start the backend
uvicorn app.main:app --reload

# Frontend (new terminal)
cd frontend
npm install
npm run dev

Ubuntu/Linux (bash)

# Step 1 — Start PostgreSQL (skip if already running)
docker run -d --name db2api-pg \
  -e POSTGRES_USER=db2api -e POSTGRES_PASSWORD=db2api -e POSTGRES_DB=db2api \
  -p 5432:5432 postgres:16
# Wait ~5 seconds for PostgreSQL to initialize before continuing

# Step 2 — Backend
cd backend
python3.14 -m venv .venv
source .venv/bin/activate
pip install --upgrade pip
pip install -r requirements.txt
cp .env.example .env
# edit .env — set every required secret and admin credential before continuing

# Step 3 — Run database migrations
alembic upgrade head

# Step 4 — Start the backend
uvicorn app.main:app --reload

# Frontend (new terminal)
cd frontend
npm install
npm run dev

Local URLs

• API docs (Swagger): http://localhost:8000/api/docs
• Health check: http://localhost:8000/api/v1/admin/health/live
• Frontend SPA: http://localhost:5173 (start frontend dev server first — see above)

Note: http://localhost:8000/ returns 404 — the backend serves no root route. All API routes are under /api/v1/.

Development

make backend-dev    # FastAPI dev server on :8000 (hot reload)
make frontend-dev   # Vite dev server on :5173 (proxy /api to :8000)

Note: make targets are POSIX-oriented and work natively on Ubuntu/Linux. On Windows, run the platform-specific commands above, or use WSL/Git Bash with make.

Database Migrations

Docker Compose applies migrations automatically through the migrate service before api starts. For non-Docker local runs, run Alembic manually:

# Docker: rerun only the migration service
make docker-migrate

# Equivalent raw Compose command
docker compose up --build --force-recreate migrate

cd backend
# Apply all pending migrations
alembic upgrade head

# Create a new migration after model changes
alembic revision --autogenerate -m "describe change"

# Rollback one step
alembic downgrade -1

Roadmap

QueryGateway currently ships the five core admin modules (Connections, API Creation Wizard, Authentication, Scheduling & Snapshots, Settings & Health) for read-only Oracle endpoints. Areas we'd love help exploring next:

• Additional data sources — PostgreSQL, MySQL, and SQL Server as query targets (today: Oracle only).
• Write endpoints — controlled POST/PUT/DELETE data operations (today: read-only GET).
• Consumer-facing API docs — auto-generated OpenAPI specs and Postman collection export per endpoint.
• Advanced access control — finer-grained RBAC beyond the baseline admin role.
• GraphQL — an alternative query surface alongside REST.

These are not commitments or a release schedule — they're open directions. If one interests you, open an issue to discuss before starting work. See the implementation plan for the full scope and non-goals.

Contributing & Community

Contributions are welcome — whether that's code, docs, bug reports, or ideas.

• 🐛 Found a bug or have a feature idea? Open an issue.
• 🔧 Want to contribute code? Read the contributing guide and conventions, then open a pull request.
• 💬 Questions or feedback? Start a discussion or issue — we'd love to hear how you're using QueryGateway.
• ⭐ Like the project? Star the repo to help others find it.

Documentation

• Architecture — system components, design decisions, directory layout
• Endpoint, scheduler, and snapshot parameter contracts — request defaults, schedule bindings, coverage, filtering, and error semantics
• Implementation Plan — modules, scope, and phased delivery
• Deployment — self-hosted setup and secret generation
• Operations — backup/restore, monitoring, troubleshooting
• Security Checklist — security validation controls
• Conventions — coding standards
• Contributing — onboarding guide
• Progress — implementation status

Tech Stack

Layer	Technologies
Backend	Python 3.14+, FastAPI, SQLAlchemy 2.0, Alembic, APScheduler 3.x, PyJWT, bcrypt, structlog
Frontend	Vite 6, React 18, TypeScript, shadcn/ui, Tailwind CSS 3, Vitest
App DB	PostgreSQL 16 (asyncpg)
Data Source	Oracle (python-oracledb thin or thick mode; Docker includes Instant Client 19.32)
CI/CD	GitHub Actions — backend (ruff/mypy/pytest), frontend (eslint/prettier/vitest), Docker build

License

QueryGateway is licensed under the GNU General Public License v3.0.