Named Queries

Named queries let you define custom query methods on your repositories. You declare methods with the @Query decorator, sync them with the server, and the CLI generates the implementation.

A named query belongs to the entity of its repository and acts on that entity alone: the statement names that entity, and a statement that names any other entity fails.

Workflow

1. Run kinotic sync to sync your entities and generate the base repository classes
2. Add custom methods with the @Query decorator to the generated repository class
3. Run kinotic generate to regenerate the repository with your query implementations (generate runs locally without syncing to the server)

Defining Named Queries

Add methods with the @Query decorator to your repository class. Use :paramName syntax for parameter binding; each parameter takes the method argument of the same name.

import { Query } from '@kinotic-ai/persistence'

export class OrderRepository extends BaseOrderRepository {

    @Query('SELECT * FROM Order WHERE status = :status ORDER BY placedAt DESC LIMIT 100')
    findByStatus(status: string): Promise<Order[]> {
        // Implementation is generated by the CLI
    }

    @Query('UPDATE Order SET status = :status WHERE id = :id WITH REFRESH')
    updateStatus(id: string, status: string): Promise<WriteCount[]> {
        // Implementation is generated by the CLI
    }

    @Query('SELECT COUNT(*) AS count FROM Order WHERE status = :status')
    countByStatus(status: string): Promise<StatusCount[]> {
        // Implementation is generated by the CLI
    }
}

The method body is left empty because kinotic generate fills in the implementation.

Named queries must be synced to the server before they can be used. If a query has not been synced, calling it will fail at runtime.

Statements

A named query is one statement. SELECT, INSERT, UPDATE and DELETE are statements of the Kinotic SQL grammar, the one migrations use, written without the terminating semicolon. Keywords match in any case, but entity and field names are case-sensitive and must be written the way the entity class spells them: Person and lastName, not person and LASTNAME. A field name in the wrong case is not an error: the query gets nothing back, no rows read and none written. See Case.

SELECT

Returns the rows of the entity that match the WHERE clause, shaped the way the entity class shapes them: nested objects and lists come back whole, since the rows are read from the stored documents. SELECT * returns whole rows; SELECT firstName, address returns the listed top-level fields only, each whole.

• ORDER BY orders the rows. A sort the caller gives on a Pageable follows it.
• A method that returns an array gets every matching row up to LIMIT, or 1000 rows when the statement names no limit.
• A method with a pageable: Pageable parameter returns a Page, and the Pageable sets the page size. Cursor paging orders by the statement's ORDER BY, so give it one that orders the rows fully, such as ORDER BY lastName, id.

INSERT, UPDATE and DELETE

A write returns one row, { count }, holding the number of rows it wrote. Add WITH REFRESH when the next read must see the write.

• INSERT stores the row the way the repository's save would: the row states the entity's id field, and the platform stores it under the participant's tenant, so the row is found by id afterwards. ROUTING and DOCUMENT_ID are not accepted, since both are derived.
• UPDATE and DELETE act on the rows that match the WHERE clause.

Aggregates

A SELECT that calls a function (COUNT(*), SUM, AVG, MIN, MAX, PERCENTILE, HISTOGRAM, …) or has a GROUP BY is an aggregate. It runs on Elasticsearch SQL, so it can call any of its functions, and returns one row per group, shaped by the projection: AS names a column, and a column or group can be a sub-field (address.city). It is written in the same grammar as every other statement, with the same WHERE clause and :name parameters, and names the query's own entity in FROM; see SELECT. A paged aggregate takes a cursor Pageable.

Multi-tenancy

Every named query is confined to the participant's tenant, the way every repository method is: a SELECT or aggregate reads only the rows of that tenant, an INSERT stores its row under it, and an UPDATE or DELETE touches only its rows, whatever the WHERE clause says. An INSERT may state the entity's tenant field with the participant's own tenant; any other value is refused.

Admin Repository Queries

A named query on an admin repository runs against the tenants the caller selects, so it declares a TenantSelection parameter like every other admin repository method. The generated implementation sends it as the query's tenant selection rather than as a query parameter. A read runs against every tenant the selection names; a write acts on one tenant, so its selection names exactly one.

import { Query, type TenantSelection } from '@kinotic-ai/persistence'

export class OrderAdminRepository extends BaseOrderAdminRepository {

    @Query('SELECT COUNT(*) AS count FROM Order WHERE status = :status')
    countByStatus(status: string, tenantSelection: TenantSelection): Promise<StatusCount[]> {
        // Implementation is generated by the CLI
    }
}

const openOrders = await new OrderAdminRepository().countByStatus('OPEN', ['tenant-123', 'tenant-456'])

kinotic generate fails when a query on an admin repository has no TenantSelection parameter, and when a query on the tenant-scoped repository declares one.