2 posts tagged with "sql"

An AI agent becomes much more useful when it can answer questions that an application team did not anticipate when the system was built.

Consider this request:

Find customers who placed more than three orders in the last 30 days, have a refund rate above 20%, and still have an unresolved support ticket.

A conventional application may not have an endpoint for that exact question. A model with unrestricted SQL access could try to construct it, but it would also need to know the physical schema, joins, enum encodings, tenant filters, authorization rules, soft-delete conventions, and database dialect. That is a large and dangerous contract to place in a prompt.

TeaQL offers a different boundary:

Give the model composable business semantics, not database authority.

TeaQL sits above SQL dialects as a governed business query language. The model can combine approved domain concepts into a query that has never been exposed as a dedicated endpoint, while the application retains control of identity, permissions, limits, audit, and execution.

TeaQL generates query APIs such as Q::platforms() and Q::merchants(). That immediately raises a fair question: why not just write SQL?

The short answer is that TeaQL is not trying to replace handwritten SQL everywhere. Generated query APIs are useful when the repeated work is not the SQL text itself, but the domain semantics around the SQL: field names, relation names, relation loading, statistics, validation, JSON search, and graph-shaped results.

The Handwritten SQL Case​

There are many cases where handwritten SQL is the right tool:

• a query is performance-critical and carefully tuned;
• the shape is reporting-specific rather than domain-object-specific;
• the team wants exact control over joins, hints, indexes, and execution plans;
• the service is small enough that generated APIs would add more weight than value.

TeaQL should not get in the way of those cases. The Rust runtime keeps a generic query AST and SQL compiler, but the design does not require every query to be expressed through generated methods.

If explicit SQL is the clearest abstraction, use explicit SQL.

The Generated API Case​

Generated APIs become useful when a large domain model produces many ordinary queries with the same repeated structure:

let merchants = Q::merchants().select_name()
    .select_platform_with(Q::platforms().comment("Query platforms").purpose("Load data").select_name())
    .which_names_contain("tea")
    .order_by_create_time_desc()
    .page(1, 20)
    .comment("Query merchants").purpose("Load data")
    .execute_for_list(&ctx)
    .await?;

This is not shorter than SQL in every case. The value is that the query is tied to the generated domain model:

• fields are exposed as methods;
• object relations are selected by relation name, not storage column name;
• readable filters follow the model vocabulary;
• subqueries can be constructed from the target entity's own generated API;
• execution returns typed entities or typed SmartList<T> collections.

That matters when the model is large and the same entity relationships appear across many services, pages, and business workflows.

Relation Semantics​

A generated API can encode relation semantics that plain SQL does not name.

For example:

let platforms = Q::platforms().select_merchant_list_with(
        Q::merchants()
            .comment("Query merchants").purpose("Load data").select_name()
            .select_platform(),
    )
    .comment("Query platforms").purpose("Load data")
    .execute_for_list(&ctx)
    .await?;

The application is not just asking for a join. It is asking for a platform list where each platform contains a merchant_list, and each merchant can carry its reverse platform relation when selected. That object shape is a domain result, not only a SQL result set.

The runtime still compiles to SQL and fetches records. The generated API gives the caller a domain-oriented way to request the shape.

Subqueries Without Stringly-Typed Entity Names​

TeaQL still has SelectQuery internally. Application code can use it directly, but generated crates should prefer the entity-specific Q entrypoints:

let platforms = Q::platforms().select_merchant_list_with(
        Q::merchants()
            .comment("Query merchants").purpose("Load data").which_names_are("TeaQL Merchant"),
    )
    .comment("Query platforms").purpose("Load data")
    .execute_for_list(&ctx)
    .await?;

This keeps the application out of string-based construction such as SelectQuery::new("Merchant") for ordinary code. The generic query type remains available for runtime and escape-hatch scenarios.

Statistics​

Large business systems rarely stop at list queries. They also need counts, sums, grouped aggregates, relation counts, and derived summary rows.

A generated API can give those operations domain names:

let platforms = Q::platforms().select_name()
    .count_merchant_list_as("merchant_count")
    .comment("Query platforms").purpose("Load data")
    .execute_for_list(&ctx)
    .await?;

The runtime can attach relation aggregate results back to parent rows, while the generated request builder keeps the caller focused on domain concepts.

This does not remove the need for handwritten reporting SQL. It gives ordinary domain statistics a consistent path.

Why Keep a Generic Runtime?​

If generated APIs are useful, why not make the whole runtime typed?

Because TeaQL still needs dynamic behavior:

• generated service crates vary by domain model;
• aggregate rows can contain dynamic projection fields;
• JSON-expression search and raw SQL escape hatches need flexible query metadata;
• graph planning operates across entity types;
• relation enhancement starts with records and then maps into typed entities.

The generated layer is typed for application ergonomics. The runtime layer is generic so it can plan, compile, enhance, validate, and execute across many generated models.

Where Generated APIs Hurt​

Generated code is not free:

• it increases compile time;
• it creates large diffs when templates change;
• it can hide SQL details if developers treat it as magic;
• it needs strong generated-crate tests to catch template regressions.

TeaQL tries to offset that with explicit generated code, local runtime crates, and generated service tests that run real SQLite scenarios. The code is not hidden behind a server or reflection system; it is Rust source that can be inspected.

Still, generated APIs should be used where they buy something. A small service with ten queries may be better served by direct sqlx.

A Practical Boundary​

The boundary I use is this:

• if the query expresses a domain object shape, relation graph, validation path, or repeated business filter, prefer the generated Q API;
• if the query expresses a one-off report, hand-tuned performance path, or database-specific operation, prefer handwritten SQL;
• if generated code starts hiding a critical execution detail, drop down a level.

TeaQL's design only works if the lower level remains available.

The Real Goal​

The goal is not to win an argument against SQL. SQL remains the execution language and often the best authoring language.

The goal is to avoid making every application workflow manually rediscover the same domain relationships. In a large model, the generated API becomes a shared vocabulary: Q::platforms(), select_merchant_list_with(...), have_platform(), count_merchant_list_as(...), and so on.

That vocabulary is where TeaQL is useful. Not because SQL is bad, but because large domain models need more than SQL strings to stay coherent.