Metadata-Version: 2.4
Name: sql2api
Version: 0.1.0
Summary: Turn SQL into a REST API: run queries against MySQL, PostgreSQL, ClickHouse, SQLite or H2 over HTTP and get JSON, CSV, XML, YAML or XLSX back.
Author-email: Anantha Raju C <arcswdev@gmail.com>
License: MIT
Project-URL: Homepage, https://github.com/AnanthaRajuC/SQL2API
Project-URL: Issues, https://github.com/AnanthaRajuC/SQL2API/issues
Project-URL: Changelog, https://github.com/AnanthaRajuC/SQL2API/blob/main/CHANGELOG.md
Keywords: sql,api,rest,flask,database,mysql,postgresql,clickhouse,sqlite,h2
Classifier: Development Status :: 4 - Beta
Classifier: Framework :: Flask
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Topic :: Database
Classifier: Topic :: Internet :: WWW/HTTP :: WSGI :: Application
Requires-Python: >=3.9
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: Flask>=2.2
Requires-Dist: PyYAML>=6.0
Requires-Dist: openpyxl>=3.1
Provides-Extra: mysql
Requires-Dist: mysql-connector-python>=8.0; extra == "mysql"
Provides-Extra: postgres
Requires-Dist: psycopg2-binary>=2.9; extra == "postgres"
Provides-Extra: clickhouse
Requires-Dist: clickhouse-driver>=0.2; extra == "clickhouse"
Provides-Extra: h2
Requires-Dist: JayDeBeApi>=1.2; extra == "h2"
Requires-Dist: JPype1>=1.4; extra == "h2"
Provides-Extra: all
Requires-Dist: sql2api[clickhouse,h2,mysql,postgres]; extra == "all"
Provides-Extra: server
Requires-Dist: gunicorn>=21; extra == "server"
Provides-Extra: dev
Requires-Dist: sql2api[all]; extra == "dev"
Requires-Dist: ruff>=0.5; extra == "dev"
Dynamic: license-file

# SQL2API

[![CI](https://github.com/AnanthaRajuC/SQL2API/actions/workflows/ci.yml/badge.svg)](https://github.com/AnanthaRajuC/SQL2API/actions/workflows/ci.yml)
[![License: MIT](https://img.shields.io/badge/license-MIT-blue.svg)](LICENSE)
![Python](https://img.shields.io/badge/python-3.9%2B-blue)

**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.

~~~bash
$ 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
~~~

| Database   | JSON | NDJSON | XML | YAML | CSV | TSV | XLSX |
|------------|:----:|:------:|:---:|:----:|:---:|:---:|:----:|
| MySQL      | ✅   | ✅     | ✅  | ✅   | ✅  | ✅  | ✅   |
| PostgreSQL | ✅   | ✅     | ✅  | ✅   | ✅  | ✅  | ✅   |
| ClickHouse | ✅   | ✅     | ✅  | ✅   | ✅  | ✅  | ✅   |
| SQLite     | ✅   | ✅     | ✅  | ✅   | ✅  | ✅  | ✅   |
| H2         | ✅   | ✅     | ✅  | ✅   | ✅  | ✅  | ✅   |

## 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.
- **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

~~~bash
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](#docker).

## Quick start

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

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

~~~bash
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

~~~bash
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):

| Variable | Default | Effect |
|----------|---------|--------|
| `SQL2API_HOME` | current directory | Folder holding `db_connections.json` and `saved_sql/`. |
| `SQL2API_ALLOW_WRITES` | off | Allow `INSERT`/`UPDATE`/DDL. Otherwise only single read-only statements are accepted. |
| `SQL2API_API_KEY` | unset | When set, every request (except `/health` and `/docs`) needs a matching `X-API-Key` header. |
| `SQL2API_MAX_PAGE_SIZE` | `1000` | Upper limit for `page_size`. |
| `SQL2API_HOST` / `SQL2API_PORT` | `127.0.0.1` / `5000` | Bind address for `sql2api serve`. |
| `SQL2API_DEBUG` | off | Flask debug mode. Never enable on a reachable host. |
| `SQL2API_H2_JAR` | bundled | Path to a different H2 JDBC jar. |

## 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](SECURITY.md) to report a vulnerability.

## Docker

~~~bash
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

| 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. |
| `/save_sql_to_file` | PATCH | Save a query (creates the next version). |
| `/list_files` | GET | List saved queries and their versions (`sort_by`, `sort_order`). |
| `/saved_sql/<name>` | DELETE | Delete a saved query or one `?version=`. |
| `/view_file_content` | GET | Raw content of a saved query file. |
| `/execute_sql_from_file`, `/execute_sql_with_parameters_from_file` | POST | Run a saved query by `filepath` (same as `/q/<name>`). |
| `/connections` | GET, PATCH | List (passwords masked) / add / update connections. |
| `/connections/<name>` | DELETE | Remove a connection. |
| `/health`, `/docs`, `/openapi.json` | GET | Liveness, Swagger UI, OpenAPI spec. |

Full details are in [documentation/API.md](documentation/API.md).

## Development

~~~bash
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](CONTRIBUTING.md) for the pull request process, and [CHANGELOG.md](CHANGELOG.md) for what changed.

## Third-party components

The wheel bundles the [H2 Database](https://h2database.com) JDBC driver (MPL 2.0 / EPL 1.0). The sample SQLite
databases in `examples/` derive from the Sakila and Chinook sample datasets.

## License

[MIT](LICENSE) © Anantha Raju C

## Contact

Anantha Raju C - [@anantharajuc](https://twitter.com/anantharajuc) - arcswdev@gmail.com

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