Metadata-Version: 2.4
Name: hayate-sql
Version: 0.1.1
Summary: SQL-first query contracts for Python, PostgreSQL, SQLite, and Cloudflare D1
Keywords: hayate,sql,postgresql,sqlite,cloudflare-d1
Author: Yusuke Hayashi
License-Expression: MIT
License-File: LICENSE
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Topic :: Database
Classifier: Typing :: Typed
Requires-Dist: asyncpg>=0.30 ; extra == 'postgres'
Requires-Python: >=3.12
Project-URL: Repository, https://github.com/hayatepy/hayate-sql
Project-URL: Documentation, https://github.com/hayatepy/hayate-sql#readme
Project-URL: Changelog, https://github.com/hayatepy/hayate-sql/blob/main/CHANGELOG.md
Provides-Extra: postgres
Description-Content-Type: text/markdown

# hayate-sql

> **Hayate ecosystem:** [Start here](https://github.com/hayatepy/.github/blob/main/docs/START.md)
> · [Production golden app](https://github.com/hayatepy/golden-app)
> · [Tested compatibility](https://github.com/hayatepy/.github/blob/main/docs/COMPATIBILITY.md)

Native SQL with a checked, typed execution boundary — not an ORM and not a
query builder.

`hayate-sql` keeps each database's SQL, placeholders, plans, and transaction
semantics intact. A small contract above each statement adds named application
arguments, result cardinality, result shape, timeout, and parameter-free
telemetry.

> **Status: alpha (0.1.x), typed.** SQLite, Cloudflare D1, and
> asyncpg-compatible PostgreSQL execution are implemented. SQLite/D1 contracts
> can be compiled against an in-memory schema; PostgreSQL contracts can be
> prepared against an ephemeral PostgreSQL transaction. The design memo
> (Japanese, per project convention) is in [DESIGN.md](DESIGN.md).

## Install

```sh
uv add hayate-sql

# Include asyncpg when PostgreSQL is the target.
uv add "hayate-sql[postgres]"
```

## Write SQL

One `.sql` file contains one database-native statement:

```sql
-- name: get_document :one?
-- param: workspace_id str
-- param: document_id UUID
-- column: id UUID
-- column: title str
-- column: body str
-- timeout: 50ms
SELECT id, title, body
FROM documents
WHERE workspace_id = ?1
  AND id = ?2
```

The placeholder syntax belongs to the target database. Use `?1` for
SQLite/D1 and `$1` for PostgreSQL. The declared parameter order maps named
Python arguments to those native positions.

Cardinality is explicit:

- `:one` — exactly one row
- `:one?` — zero or one row
- `:many` — zero or more rows
- `:exec` — no result rows; returns `CommandResult`

Supported contract types are `str`, `int`, `float`, `bool`, `bytes`, `object`,
`Any`, `datetime`, `date`, `Decimal`, and `UUID`. Append `?` for a nullable
value. Types generate Python annotations; runtime enforcement intentionally
checks the result's column shape rather than pretending that SQLite and
PostgreSQL use identical value representations. Declarations must therefore
describe the target driver's returned Python values (for example, a raw
SQLite boolean column is normally declared as `int` unless the SQL converts it).

## Compile and generate

Compile D1/SQLite SQL against the real schema without executing queries:

```sh
hayate-sql check queries/ --dialect d1 --schema schema.sql
```

For PostgreSQL, `prepare` every query inside a transaction that is always
rolled back:

```sh
HAYATE_SQL_DATABASE_URL=postgresql://localhost/app_test \
  hayate-sql check queries/postgres/ \
  --dialect postgres \
  --schema schema.sql
```

Generate a typed facade:

```sh
hayate-sql generate queries/ -o app/queries.py
```

The generated module intentionally uses only Python 3.12+ syntax and does not
force postponed annotations. Run the project's formatter after generation
when its style policy is stricter than the standard output.

The generated function has named arguments and a `TypedDict` result:

```python
document = await queries.get_document(
    database,
    workspace_id=workspace_id,
    document_id=document_id,
)
```

Generated code is a build artifact. SQL remains the source of truth.

### Check against migration history

When forward-only SQL migrations are the schema source of truth, replay them
in order before compiling the queries:

```sh
hayate-sql check queries/ --dialect d1 --migrations migrations/
```

Migration files use a fixed-width positive number followed by a lowercase
description, for example `0001_create_documents.sql`. Gaps are allowed, while
duplicate versions, mixed widths, malformed names, and empty migrations fail
the check.

The migrations run only in hayate-sql's disposable check database. Applying
them to development, staging, or production remains the responsibility of the
database-native tool, such as `wrangler d1 migrations apply` or Alembic.

## Execute

### SQLite

```python
from hayate_sql.adapters import SQLiteDatabase

async with SQLiteDatabase("app.db") as database:
    async with database.transaction(mode="immediate"):
        await queries.create_document(
            database,
            document_id=document_id,
            title=title,
        )
```

### Cloudflare D1

```python
from hayate_sql.adapters import D1Database

database = D1Database(context.env.DB)

# D1 consistency is explicit rather than hidden behind a generic transaction.
session = database.with_session("first-primary")
document = await queries.get_document(
    session,
    workspace_id=workspace_id,
    document_id=document_id,
)
bookmark = session.bookmark()
```

The repository's reproducible workerd probe builds the 0.1.0 wheel, injects
that wheel into the Python Workers bundle, applies a real local D1 migration,
and executes `:one`, `:one?`, `:many`, and `:exec`:

```sh
bash scripts/check_workers_d1.sh
```

The same probe asserts that telemetry contains neither SQL text nor a sentinel
bound value.

### PostgreSQL

```python
import asyncpg

from hayate_sql.adapters import AsyncpgDatabase

connection = await asyncpg.connect(dsn)
database = AsyncpgDatabase(connection)

# Delegates to asyncpg's native transaction context.
async with database.transaction(isolation="serializable"):
    await queries.update_document(database, document_id=document_id, title=title)
```

## Observe without leaking parameters

An observer receives only the query name, contract fingerprint, duration,
success, row count, and error type. SQL text and bound values are deliberately
absent.

```python
def observe(event):
    metrics.histogram("db.query.duration", event.duration_ms, query=event.name)


database = SQLiteDatabase("app.db", observer=observe)
```

## Non-goals

- ORM models, identity maps, lazy relations, or repositories
- a database-independent SQL DSL or query builder
- SQL dialect translation
- a universal transaction abstraction
- production migration application, implicit schema migration, or write retries

## License

MIT
