hayate-sql 0.1.0

SQL-first query contracts for Python, PostgreSQL, SQLite, and Cloudflare D1

hayate-sql

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.

Install

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:

-- 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:

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

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

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

Generate a typed facade:

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:

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:

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

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

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:

bash scripts/check_workers_d1.sh

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

PostgreSQL

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.

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