SQL2API

Turn SQL into a REST API. SQL2API is a small Flask service that runs SQL against your databases and returns the results as JSON, NDJSON, CSV, TSV, XML, YAML or Excel. Save a query once and it becomes an endpoint with typed, injection-safe parameters, versioning and run history.

$ curl 'http://127.0.0.1:5000/q/actor_by_id?id=7'
[{"actor_id": 7, "first_name": "GRACE", "last_name": "MOSTEL"}]

$ curl 'http://127.0.0.1:5000/q/films_by_rating?rating=PG&max_length=60&format=csv&page_size=2'
film_id,title,rating,length
410,HEAVEN FREEDOM,PG,48
443,HURRICANE AFFAIR,PG,49

Features

• Ad-hoc queries - POST /execute_sql with SQL and a connection name.
• Saved, versioned queries - every save creates a new version; GET /q/<name>?id=7 runs the latest one (or ?version=1). Each run is recorded in the query's execution history.
• Bound parameters - write WHERE id = :id and the value is sent to the database separately from the SQL, so it cannot inject anything. Declare types ({"id": "int"}) and query-string values are converted for you.
• Pagination - ?page=2&page_size=50, with X-Has-More telling you whether another page exists.
• Connection pooling - MySQL, PostgreSQL, ClickHouse and H2 connections are reused between requests instead of opened for each one (about 30x lower per-request overhead on MySQL and H2 against a local server; more over a network).
• Query time limit - runaway queries are cancelled on the database (30 s by default, ?timeout= per request) so they cannot tie up the service.
• Read-only by default - only single SELECT/WITH/SHOW/DESCRIBE/EXPLAIN statements run, and sessions are opened read-only where the database supports it.
• Secrets stay out of files - "password": "${PG_PASSWORD}" in db_connections.json reads the environment.
• Self-documenting - OpenAPI at /openapi.json, Swagger UI at /docs.

Install

pip install "sql2api[postgres]"         # pick the drivers you need: mysql, postgres, clickhouse, h2
# or everything:                        pip install "sql2api[all]"

SQLite needs no extra driver. H2 also needs a Java runtime (the H2 JDBC jar is bundled). From a clone: pip install -e ".[dev]". Or use Docker - see below.

Quick start

The repository ships two sample SQLite databases and a couple of saved queries:

cd examples
cp db_connections.example.json db_connections.json
sql2api serve                            # http://127.0.0.1:5000

curl -X POST 'http://127.0.0.1:5000/execute_sql?page_size=3' -H 'Content-Type: application/json' \
     -d '{"sql": "SELECT * FROM actor WHERE actor_id > :min", "params": {"min": 10}, "connection_name": "sakila-sqlite"}'

Open http://127.0.0.1:5000/docs for the interactive API reference.

For your own databases, run sql2api init in an empty folder: it creates db_connections.json (inactive templates for every supported database) and saved_sql/. Edit the file, set "active": true, and start the server there.

Saving a query as an endpoint

curl -X PATCH http://127.0.0.1:5000/save_sql_to_file -H 'Content-Type: application/json' -d '{
  "filename": "actor_by_id",
  "sql_query": "SELECT * FROM actor WHERE actor_id = :id",
  "query_parameters": {"id": "int"},
  "connection_name": "sakila-sqlite",
  "author": "me", "description": "Look up an actor"
}'

curl 'http://127.0.0.1:5000/q/actor_by_id?id=7&format=yaml'

Saving again under the same name adds version 2; DELETE /saved_sql/actor_by_id?version=1 removes one version.

Configuration

Everything is configured through environment variables (all optional):

Security

SQL2API runs whatever SQL it is given against your databases, so it ships locked down and expects you to finish the job:

• Set SQL2API_API_KEY and serve over TLS (put it behind a reverse proxy).
• 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 :name parameters. The older {name} placeholders paste text into the SQL and are therefore restricted to numbers and plain text.
• Saved-query files are only read from saved_sql/; passwords are never returned by the API.

See SECURITY.md to report a vulnerability.

Docker

docker build -t sql2api .                          # add --build-arg WITH_H2=true for H2 support
docker run -p 5000:5000 -v "$PWD/data:/data" -e SQL2API_API_KEY=change-me sql2api

The container keeps db_connections.json and saved_sql/ in /data. It runs gunicorn with a single worker (the files are protected by an in-process lock).

API overview

Full details are in documentation/API.md.

Development

pip install -e ".[dev]"
ruff check .
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 SQL2API_IT_* variables are set, and are skipped otherwise; CI runs them against service containers. See CONTRIBUTING.md for the pull request process, and CHANGELOG.md for what changed.

Third-party components

The wheel bundles the H2 Database JDBC driver (MPL 2.0 / EPL 1.0). The sample SQLite databases in examples/ derive from the Sakila and Chinook sample datasets.

License

MIT © Anantha Raju C

Contact

Anantha Raju C - @anantharajuc - arcswdev@gmail.com

Project link: https://github.com/AnanthaRajuC/SQL2API