Metadata-Version: 2.5
Name: pysqlsuggestions
Version: 0.13.0
Summary: Context-aware, schema-aware SQL completion as a library
Project-URL: Homepage, https://github.com/discrimy/pysqlsuggestions
Project-URL: Demo, https://discrimy.github.io/pysqlsuggestions/
License: MIT License
        
        Copyright (c) 2026 Alexander Bespalov
        
        Permission is hereby granted, free of charge, to any person obtaining a copy
        of this software and associated documentation files (the "Software"), to deal
        in the Software without restriction, including without limitation the rights
        to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
        copies of the Software, and to permit persons to whom the Software is
        furnished to do so, subject to the following conditions:
        
        The above copyright notice and this permission notice shall be included in all
        copies or substantial portions of the Software.
        
        THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
        IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
        FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
        AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
        LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
        OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
        SOFTWARE.
License-File: LICENSE
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Classifier: Typing :: Typed
Requires-Python: >=3.10
Provides-Extra: cache-redis
Requires-Dist: redis>=3.0; extra == 'cache-redis'
Provides-Extra: clickhouse-driver
Requires-Dist: clickhouse-driver>=0.2.9; extra == 'clickhouse-driver'
Provides-Extra: demo
Requires-Dist: fastapi>=0.110; extra == 'demo'
Requires-Dist: uvicorn>=0.29; extra == 'demo'
Provides-Extra: pg8000
Requires-Dist: pg8000>=1.30; extra == 'pg8000'
Provides-Extra: psycopg2
Requires-Dist: psycopg2-binary>=2.9; extra == 'psycopg2'
Provides-Extra: trino
Requires-Dist: trino>=0.328; extra == 'trino'
Description-Content-Type: text/markdown

# pysqlsuggestions

Context-aware, schema-aware SQL completion for Python. A library, not a CLI and
not a language server — importable into a FastAPI service, a notebook kernel or
an internal reporting tool without dragging a process boundary along.

Zero runtime dependencies. PostgreSQL, ClickHouse and Trino, plus an `ansi`
fallback so an unknown backend degrades instead of failing.

## Status

The whole pipeline works end to end against real servers: lex, analyse, request,
resolve, rank. Value hints, FK-derived joins, star expansion, bound parameters,
cross-schema search and per-role availability landed since; still to come are
physical layout ranking and history ranking, plus the syntax extensions.

## Usage

```python
from pysqlsuggestions import complete
from pysqlsuggestions.catalogs.memory import MemoryCatalog
from pysqlsuggestions.dialects.postgres import POSTGRES

catalog = MemoryCatalog({('public', 'users'): [('id', 'bigint'), ('name', 'text')]})

sql = 'SELECT * FROM users u WHERE u.'
[s.text for s in complete(sql, len(sql), POSTGRES, catalog)]
# ['id', 'name']
```

Any PEP 249 cursor works as a catalog, with no driver imported by the library:

```python
import psycopg2
from pysqlsuggestions.catalogs.dbapi import DbapiCatalog

connection = psycopg2.connect(...)
catalog = DbapiCatalog(connection.cursor, POSTGRES, paramstyle=psycopg2.paramstyle)
```

The pure half is usable on its own, which is what a caller with no reachable
catalog wants:

```python
from pysqlsuggestions import derive_request

request = derive_request('SELECT id, na FROM users u', 13, POSTGRES)

request.prefix        # 'na'
request.clause        # 'SELECT'
request.replace_span  # (11, 13) — what the editor overwrites
request.kinds         # (Kind.COLUMN, Kind.FUNCTION)
request.scope         # relations in view, built from the whole statement
```

The scope comes from the entire statement, not the text left of the caret — the
`FROM` clause that answers the question above sits to the right of it.

A qualifier collapses the answer:

```python
derive_request('SELECT * FROM users u WHERE u.', 30, POSTGRES).kinds
# (Kind.COLUMN,)  — no keywords, no functions, no tables
```

And one tuple per dialect gives three different answers to the same text:

```python
from pysqlsuggestions.dialects.trino import TRINO

sql = 'SELECT * FROM analytics.'
derive_request(sql, len(sql), POSTGRES).kinds  # (Kind.COLUMN, Kind.TABLE)  a schema, or a relation
derive_request(sql, len(sql), TRINO).kinds     # (Kind.SCHEMA,)  analytics is a catalog
```

## Caching catalog reads

A completion makes up to six catalog reads, and none of the answers change
between keystrokes. Pass a cache and they are made once:

```python
from pysqlsuggestions.caches import MemoryCache

cache = MemoryCache()
complete(sql, len(sql), POSTGRES, catalog, cache=cache, identity='analyst')
```

A cache satisfies one of two protocols, and implements whichever it can.
`ObjectCache` — `get(key)` and `set(key, value, ttl=None)` — keeps Python
objects, which is what a process holds for itself. `ByteCache` —
`get_bytes(key)` and `set_bytes(key, value, ttl=None)` — keeps bytes, which is
everything across a process boundary; the library encodes and decodes, so an
implementation never sees a `Table`.

A plain dict satisfies neither and `complete` says so rather than silently
caching nothing. `MemoryCache` is what to pass instead.

`MemoryCache` is bounded, expiring and safe to share between threads. It holds
1024 entries and evicts least-recently-used, expires an entry after five
minutes, and takes a lock on every operation — pass `maxsize=None` or
`default_ttl=None` to turn either off for a catalog whose size and churn you
know. The expiry is what makes a `CREATE TABLE` visible to a long-lived session
without a restart, since nothing in the process hears about DDL.

```python
cache = MemoryCache(default_ttl=300, maxsize=1024)   # the defaults

cache.delete(cache_key('analyst', 'postgres', 'tables', 'public'))   # after DDL
cache.clear()                                                        # or all of it

cache.stats()   # hits, misses, expiries, evictions, entries, maxsize
```

`delete` takes a key from `cache_key` rather than a prefix: the key's grammar is
not a format, and matching on part of it would make it one.

For redis:

```bash
pip install 'pysqlsuggestions[cache-redis]'
```

```python
from pysqlsuggestions.caches.redis import RedisCache

cache = RedisCache.from_url('redis://localhost:6379/0', namespace='prod-pg')
```

`RedisCache` never imports redis except in `from_url`; hand it any client with
`get` and `set` — redis-py 3 through 6, valkey, a cluster client, a pool your
application already owns — and it works.

**One namespace per database, and per identity you cannot name.** The key leads
with the role and carries the dialect, but nothing in it names the *server*, and
these reads are privilege-filtered. Two databases sharing a namespace, or two
end users sharing one without `identity=` to tell them apart, serve each other's
readable sets — which is silent, and reads as a database permission bug.

Entries expire after five minutes by default, and every key carries a
fingerprint of the shapes it holds, so upgrading the library misses rather than
decoding an old shape.

If you write your own `ByteCache`, `pysqlsuggestions.testing.CacheConformance`
is shipped in the wheel to check it:

```python
from pysqlsuggestions.testing import CacheConformance

failures = CacheConformance.check(MyCache())
assert not failures, failures
```

## Demo

```bash
docker compose -f docker/docker-compose.yml up -d --wait
uv run uvicorn demo.app:app --port 8000
```

Completion against real PostgreSQL, ClickHouse and Trino, with a panel showing
the derived `Request` as you type. See `demo/README.md` for what to try.

## Value suggestions

Right of a comparison, a literal is usually what is wanted, so `WHERE type = `
offers the values that column actually holds:

```python
complete("SELECT * FROM reports_database d WHERE d.type = ", 48, POSTGRES, catalog)
# [Suggestion(text="'postgres'", kind=Kind.VALUE, ...), ...]
```

Nothing reads the table — a completion engine may not start a scan. There are
two sources, and the exhaustive one wins:

| source | where it comes from | cost |
| --- | --- | --- |
| boolean | the type: `true` / `false` | free, every dialect |
| enum | ClickHouse writes its labels into the type text; Postgres keeps them in `pg_enum` | free / one read |
| frequent values | Postgres `pg_stats.most_common_vals` | one read |

A type that enumerates itself is complete, so statistics could only narrow it.
Everything else falls back to whatever the planner already recorded, which for
Postgres is also filtered to what the connected role may read.

Statistics appear once `ANALYZE` has run and only for columns whose values
repeat, so a column of distinct values has none — that is the feature working,
not failing. Fetching them is a capability (`SupportsColumnValues`): a catalog
that cannot answer offers columns and functions there instead. ClickHouse and
Trino keep no most-common-values, so ClickHouse answers from its enums and
Trino from booleans alone.

## Columns before a FROM

`SELECT ema⌶` with nothing in the FROM offers the column *and* the relation it
belongs to, because choosing one is choosing the other:

```
SELECT ema⌶   ->   SELECT auth_user.email FROM auth_user
```

The suggestion carries two edits, and `plan_insertion` returns both. The FROM
goes where a FROM goes — after the select list, before whatever follows it.

It needs `SupportsColumnValues`'s sibling, `SupportsColumnSearch`. Postgres and
ClickHouse ship the query; Trino does not, since answering would mean asking
every catalog's connector in turn — the same reason its unqualified `tables` is
empty. A prefix is required: every column in the database is not an answer.

## Qualified columns

A column is offered as `<alias>.<column>`, or `<relation>.<column>` when there
is no alias — always, not only when two relations are in view:

```
SELECT * FROM auth_user u WHERE ⌶       u.id  u.username  u.email
SELECT * FROM auth_user WHERE ⌶         auth_user.id  auth_user.username
SELECT * FROM auth_user u WHERE u.⌶     id  username  email
```

A bare name is unambiguous only until a second relation joins, and the caret is
usually in a query still being written. Where the qualifier is already typed the
column comes back bare — it is in the text already — and a relation with no name
to qualify with, an unaliased derived table, stays bare too.

Matching is unaffected: it runs against the column name, so `usern` still finds
`u.username`.

## Joins

Type `JOIN` and the whole clause comes back — relation, alias and condition in
one accept — from the foreign keys the database already declares:

```
SELECT * FROM booking b JOIN ⌶

  flight f ON b.flight_id = f.id              fk: flight.id
  passenger p ON b.passenger_id = p.id        fk: passenger.id
  baggage bag ON b.id = bag.booking_id        fk: baggage.booking_id
  revenue.refund r ON b.id = r.booking_id     fk: refund.booking_id
```

At `ON ⌶` the whole condition arrives the same way, and once a qualifier has
committed the left side — `ON b.⌶` — it degrades to ranking that relation's
foreign key columns up, since a condition is no longer expressible there.

A constraint is directed and a join is not, so proposals fire from both ends: a
query starting at `airline` is offered the tables that reference *it*.
Many-to-one ranks above one-to-many, being both more often wanted and unable to
multiply the result set. Two constraints to the same target stay two proposals
with different aliases, because choosing between them is the user's to make.

**Postgres only, and deliberately.** ClickHouse and Trino declare no
constraints, so both positions there behave exactly as they always have. The
tempting fallback — matching `<singular>_id` against `<table>.id` — is rejected
rather than unbuilt: it is right often enough to be inviting and wrong often
enough to matter, and a wrong join condition is valid SQL that silently returns
the wrong rows. No parser catches that, and neither does the person reading the
result. Observed joins mined from query history would be a real answer here; an
inferred one is not.

## What the role may not read

A column can be visible as metadata and unreadable as data — `pg_attribute`
lists every column to every role regardless of column grants. Such a column is
still offered, but last, and it says why:

```
SELECT * FROM reports_database d WHERE d.⌶

  id            reports_database.id :: bigint
  title         reports_database.title :: character varying(200)
  …
  password      reports_database.password :: text   no SELECT privilege
```

Sunk rather than hidden, because a name that vanishes reads as the engine not
knowing about it. A privilege error names its own cause, and asking for the
grant is a move the user can make.

Three things follow from it, and the second is the one that matters:

```python
complete('SELECT * FROM mattermost_mattermostchannel', 8, POSTGRES, catalog)
# expand *  →  'id, name'   1 column omitted: no SELECT privilege
```

`SELECT *` over a partly-granted relation is refused by the server outright —
table-level `SELECT` implies every column, so withholding one means there is no
table-level grant. The expansion is therefore not a filtered convenience but the
repair: it turns a statement that errors into one that runs, which is why it
stays insertable and merely explains itself. Second, no value literal is ever
drawn from a restricted column, from statistics or from a self-enumerating type.
Third, a join proposal to a relation with no grant at all sinks and keeps its
`fk:` annotation, since the constraint is real either way.

**Postgres only, and by the same rule as joins.** `has_column_privilege` is
evaluated by the server against the connected role, so it costs one column on
queries that already run. ClickHouse has no equivalent — effective privileges
through role inheritance would have to be reconstructed by hand — and Trino
keeps access control outside SQL entirely, in the connector or a file rule set.
Both report `UNKNOWN`, which renders exactly as an ordinary suggestion. A wrong
grey is as bad as a wrong promise, and neither is worth guessing at.

The engine reports; the server enforces. An editor that inserts a restricted
suggestion produces a query that fails exactly as it would have anyway — the
engine's job is to have said so first. Over LSP that means a `Deprecated` tag
and the reason in `detail`; the protocol has no disabled state, and this does
not fake one.

**One hazard worth naming.** `has_column_privilege` evaluates against the
*current connection's* role. A service account shared across end users reports
the service account's privileges, so nothing looks restricted and column names
may be shown to somebody whose own role could not see them. Carry end-user
identity on the connection, and pass it as `identity=` so it leads the cache key
— a cache shared across roles leaks one user's readable set into another's.

## Browser demo

The same page, with no server and no database — the library has no runtime
dependencies and its core is pure, so the whole pipeline loads into the page
under Pyodide and completes against a schema carried as data:

```bash
uv build --wheel
uv run python -m scripts.build_pages
python3 -m http.server -d site 8001
```

The page reaches nothing. Pyodide is carried in `site/` rather than fetched from
a CDN, pinned by digest in `scripts/pyodide.lock`, and the build refuses to
assemble a site whose files name any absolute URL. That costs 11.7 MiB against a
demo payload of 135 kB, and buys a page that works on an air-gapped laptop and
cannot be broken by somebody else's outage — which is the claim the demo exists
to make.

`.github/workflows/pages.yml` publishes `site/` to GitHub Pages when a `v*` tag
is pushed, and refuses to if the tag and `pyproject.toml` disagree about the
version. The page installs a wheel, so the published demo is a released
version's behaviour rather than whatever `main` reached this morning:

```bash
git tag v0.1.0 && git push origin v0.1.0
```

The schema is `demo/schema.py` — a small flight-booking database invented for
the demo, written as data rather than exported from anywhere. That matters:
value suggestions come from statistics, statistics are literal values out of
the rows, and this page is published. There is deliberately no step that could
be pointed at a real database.

It is shaped to exercise the engine rather than to be realistic — enums and
booleans so values come from the type, skewed columns so they come from
statistics, relations three orders of magnitude apart in size, two schemas, a
materialized view and a mixed-case name that only Postgres has to quote.

All three backends are there, Trino included. The `Catalog` port passes one
name at each level — a catalog names the schemas below it, a schema names its
relations — so a snapshot with a catalog mapping serves three levels as readily
as two:

```python
MemoryCatalog(tables, catalogs={'warehouse': ['public', 'revenue']})
```

That is a `MemoryCatalog` feature rather than a demo one: anyone pre-fetching a
Trino schema into a snapshot needed it.

## In an editor

The engine speaks LSP, so any client can drive it:

```bash
uv run python -m pysqlsuggestions_lsp
```

The connection profile arrives in `initializationOptions`. The database is not
contacted until the first completion request — opening a document opens no
socket — and an unreachable one degrades to completing from the statement alone
rather than failing the request.

It is a separate distribution in `lsp/`, not part of the library: a server needs
pygls and a driver, and the library's promise is that importing it pulls in
neither. See `lsp/README.md`.

`editors/vscode/` is a VS Code extension over that server. It carries its own
CPython with the server already installed into it, so it needs no Python on the
machine and touches neither the system's nor the project's — nothing is
downloaded and nothing is built on first run. Connections are managed from a
view in the Explorer, passwords in secret storage rather than settings.

It reads a schema from all three backends: Postgres through pg8000, ClickHouse
and Trino through the library's own HTTP readers, which exist because both of
those clients hard-require compression codecs that ship compiled. That every
bundled wheel is `none-any` is what lets the same wheel set install into all
nine platform builds. See `editors/vscode/README.md`.

## Design

See `docs/request-pipeline.md` for how the stages fit together,
`docker/README.md` for what each fixture exercises, and
`docs/superpowers/specs/` for the full design. `docs/gaps.md` records what is
missing and why, measured against DBeaver.

## Development

```bash
uv sync
./scripts/check.sh                      # ruff format, ruff check, mypy strict, pytest
uv run pytest -m 'not integration'      # without the docker backends
```
