Metadata-Version: 2.5
Name: django-safeql
Version: 1.1.0
Summary: A whitelisted SQL-to-QuerySet transpiler for Django — run untrusted or AI-generated SQL safely.
Project-URL: Homepage, https://github.com/lpauloin/django-safeql
Project-URL: Issues, https://github.com/lpauloin/django-safeql/issues
Author: Laurent Pauloin
License-Expression: MIT
License-File: LICENSE
Keywords: django,llm,orm,queryset,security,sql,sqlglot
Classifier: Framework :: Django
Classifier: Framework :: Django :: 4.2
Classifier: Framework :: Django :: 5.0
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Topic :: Database
Requires-Python: >=3.10
Requires-Dist: django>=4.2
Requires-Dist: sqlglot<31,>=30.8
Provides-Extra: dev
Requires-Dist: black==24.*; extra == 'dev'
Requires-Dist: build>=1.2; extra == 'dev'
Requires-Dist: flake8>=7; extra == 'dev'
Requires-Dist: psycopg[binary]>=3.1; extra == 'dev'
Requires-Dist: pytest-cov>=5; extra == 'dev'
Requires-Dist: pytest-django>=4.8; extra == 'dev'
Requires-Dist: pytest-randomly>=3.15; extra == 'dev'
Requires-Dist: pytest-xdist>=3.6; extra == 'dev'
Requires-Dist: pytest>=8; extra == 'dev'
Requires-Dist: twine>=5; extra == 'dev'
Description-Content-Type: text/markdown

<p align="center">
  <img src="assets/banner.svg" alt="django-safeql — run untrusted or AI-generated SQL safely" width="840">
</p>

<p align="center">
  <a href="https://pypi.org/project/django-safeql/"><img src="https://img.shields.io/pypi/v/django-safeql" alt="PyPI"></a>
  <a href="https://github.com/lpauloin/django-safeql/actions/workflows/ci.yml"><img src="https://github.com/lpauloin/django-safeql/actions/workflows/ci.yml/badge.svg" alt="CI"></a>
  <img src="https://img.shields.io/badge/python-3.10%2B-blue" alt="Python">
  <img src="https://img.shields.io/badge/django-4.2%2B-092E20" alt="Django">
  <img src="https://img.shields.io/badge/license-MIT-green" alt="License">
</p>

# django-safeql

A whitelisted SQL-to-QuerySet transpiler for Django.

`django-safeql` parses a restricted subset of PostgreSQL SQL, validates it
against a schema you declare (which tables, columns, functions and operators
are allowed), and compiles it straight into a real Django `QuerySet` — no
raw SQL ever touches the database. It's built for situations where a SQL
query comes from an untrusted or semi-trusted source — an LLM answering
questions over your data, an end-user-facing query box, a saved-report
feature — and has to run safely against your existing Django models.

- **No SQL injection surface** — the input is parsed into an AST and
  compiled to ORM calls; nothing is ever executed as a raw string.
- **Whitelist by construction** — only the tables, columns, operators,
  functions and aggregates you declare in the schema are reachable. Anything
  else is rejected before it gets near the database.
- **Real QuerySets out** — the result is a normal Django `QuerySet`, so it
  composes with everything else in your app (pagination, further
  `.filter()`, `select_related`, etc.).

## Install

```bash
pip install django-safeql
```

Requires Django ≥ 4.2 and a PostgreSQL database (some supported functions —
JSON operators, `ARRAY_AGG`, `STRING_AGG` — are Postgres-specific).

## Quickstart

```python
from django_safeql import SQLTranspilerSchema, TableSchema, JsonFieldSchema, SQLToQuerySetTranspiler
from myapp.models import Book, Author

schema = SQLTranspilerSchema(
    base_table="book",
    base_queryset=Book.objects.all(),
    tables={
        "book": TableSchema(
            queryset=Book.objects.all(),
            relation="",
            allowed_fields={"id", "title", "status", "author_id", "pages", "details"},
            json_fields={
                "details": JsonFieldSchema(schema={
                    "type": "object",
                    "properties": {
                        "language": {"type": "string"},
                        "edition": {"type": "integer"},
                    },
                }),
            },
        ),
        "author": TableSchema(
            queryset=Author.objects.all(),
            relation="author",
            allowed_fields={"id", "name"},
        ),
    },
    max_limit=1000,
)

transpiler = SQLToQuerySetTranspiler(schema)

queryset = transpiler.to_queryset("""
    SELECT book.title, author.name
      FROM book
      JOIN author ON book.author_id = author.id
     WHERE book.status = 'published'
       AND book.details->>'language' = 'en'
     ORDER BY book.pages DESC
     LIMIT 20
""")

list(queryset)  # a normal QuerySet — evaluate it however you like
```

Anything outside the declared schema is rejected before touching the
database:

```python
transpiler.to_queryset("SELECT * FROM pg_catalog.pg_user")
# django_safeql.ValidationError: Unknown table: pg_user

transpiler.to_queryset("DELETE FROM book WHERE id = 1")
# django_safeql.UnsupportedSQL: ...
```

## How it works

`SQLToQuerySetTranspiler` runs SQL through four stages:

1. **Parse** — `sqlglot` parses the SQL text (Postgres dialect) into an
   internal AST.
2. **Annotate** — every column, join and function call is resolved against
   your `SQLTranspilerSchema` (including JSON Schema-typed JSON fields).
3. **Validate** — anything not explicitly whitelisted (unknown table,
   disallowed function, unsupported syntax, LIMIT above your ceiling, …)
   raises `ValidationError` or `UnsupportedSQL`.
4. **Codegen** — the validated AST is compiled into Django ORM constructs
   (`Q`, `F`, `Case`/`When`, `Subquery`, `Exists`, aggregates, JSON key
   transforms, casts, date truncation, …) and returned as a `QuerySet`.

## What's supported

- `SELECT` / `WHERE` / `JOIN` / `GROUP BY` / `HAVING` / `ORDER BY` / `LIMIT`
- Comparison, boolean and arithmetic operators
- Scalar aggregates (`COUNT`, `SUM`, `AVG`, `MIN`, `MAX`) and Postgres
  collection aggregates (`ARRAY_AGG`, `STRING_AGG`, `JSON_AGG`, …)
- String functions (`LOWER`, `UPPER`, `TRIM`, `SUBSTRING`, `CONCAT`, …) and
  date functions (`EXTRACT`, `DATE_TRUNC`, …)
- Read-only JSON/JSONB access and functions, validated against a JSON
  Schema you provide per field
- `LATERAL` joins over `jsonb_array_elements`, `EXISTS` subqueries

Anything not explicitly listed — DDL, writes, arbitrary functions, joins to
undeclared tables — is rejected.

## Changelog

[CHANGELOG.md](CHANGELOG.md)

## License

MIT — see [LICENSE](LICENSE).
