Declare a custom query

Add a custom SQL query to an entity’s .atl file and register it with the server, for aggregations, window functions, DISTINCT ON, multi-entity joins, or any SQL the typed predicate language does not reach.

1. Add a query block to the schema

Append to schema.atl:

query NoteCountByMonth for Note {
  input  { since: timestamptz }
  output { month: timestamptz, count: bigint }
  sql touches(Note) {
    SELECT date_trunc('month', created_at) AS month,
           COUNT(*)                        AS count
    FROM app.note
    WHERE created_at >= $since
    GROUP BY 1
    ORDER BY 1
  }
}

touches(Note) tells the cache which entities to invalidate on writes.

2. Apply

tide apply

The server validates the SQL against the schema and persists the query into the merged schema.

Verify

tide show NoteCountByMonth

tide show prints the query’s SQL and touches set as the server holds them.

The query is callable at once

tide apply hot-reloads the server: a brand-new query answers from the next request, and so does an edit to the SQL of an existing one. The same holds for procedure blocks.

Calling NoteCountByMonth from your service needs the typed client. Run tide generate from your caller repo to write it into the directory that tide.yaml’s output_dir names.

Next steps

• Custom queries and procedures — when to reach for query against procedure, and how touches(...) keeps the cache consistent.
• DSL grammar — the full query and procedure syntax.
• Use the sandbox — paste the query body into a disposable sandbox to verify the result shape before tide apply.