Queries

Status: [Designed] -- Area 2 of the Grove language. Syntax and semantics are defined; implementation is in progress.

Queries declare named, parameterized database lookups. They define the arguments a caller must provide, the SQL (or GSQL) statement to execute, the shape of the output, and optional indexes. Activities and routes reference queries by name through the query data operation, keeping database access type-safe and centralized.

Prerequisites: Concepts Overview, Activities. What you'll learn: How to declare queries with SQL and GSQL, define typed arguments and outputs, add indexes, and reference queries from activities and routes.

Declaring a Query

A query is a top-level declaration with up to four parts:

query recent_orders_by_customer {
  args {
    customer_id: Id
    limit: Int = 10
  }

  sql {
    "SELECT id, status, total, created_at
     FROM orders
     WHERE customer_id = @customer_id
     ORDER BY created_at DESC
     LIMIT @limit"
  }

  output: {
    id: Id
    status: String
    total: Decimal
    created_at: DateTime
  }

  index {
    customer_id asc
    created_at desc
  }
}

Arguments

The args block declares the parameters the query accepts. These become bind parameters in the SQL statement:

args {
  customer_id: Id
  status: String?
  limit: Int = 25
}

Arguments use standard Grove types.
Optional arguments are marked with ?.
Default values can be provided with =.
Argument names map to bind parameters in the SQL statement (referenced as @arg_name).

SQL Blocks

The sql block contains a SQL string. Bind parameters use @-prefixed names that correspond to declared arguments:

sql {
  "SELECT id, title, status, priority
   FROM todos
   WHERE assignee_id = @assignee_id
   AND status = @status
   ORDER BY priority DESC"
}

SQL Guidelines

Use @name syntax for bind parameters, matching argument names.
The SQL dialect depends on the configured database backend (SQLite, PostgreSQL, etc.).
The runtime prepares the statement and binds parameters -- no string interpolation occurs.
Complex SQL (joins, subqueries, aggregates) is supported as long as the backend supports it.

GSQL Blocks

As an alternative to raw SQL, queries can use gsql -- Grove's structured query syntax:

query active_orders {
  args {
    customer_id: Id
  }

  gsql {
    "orders
     | filter customer_id == @customer_id && status != 'cancelled'
     | sort created_at desc
     | take 50"
  }

  output: {
    id: Id
    status: String
    total: Decimal
  }
}

GSQL is compiled to SQL at build time. It provides a more concise syntax for common query patterns while remaining backend-agnostic.

Output Types

The output declaration specifies the shape of each row returned by the query:

output: {
  id: Id
  name: String
  email: String
  created_at: DateTime
}

Output types can be:

Inline object types -- As shown above, with field names and types.
Named types -- Reference a type declared in the module: output: CustomerSummary.

The query result is always a list of the output type. When used in a query data operation, the data block's result is List<OutputType>.

Indexes

The index block declares database indexes that should exist to support the query:

index {
  customer_id asc
  created_at desc
}

Each entry specifies a column name and sort direction (asc or desc). The runtime uses this declaration to create or verify indexes during schema management.

Composite indexes (multiple columns) are declared as multiple entries within a single index block. The column order matters -- it defines the index key order.

Referencing Queries

From Activities

activity get_customer_orders v1 {
  data orders {
    query orders.recent_orders_by_customer {
      customer_id: input.customer_id
      limit: 20
    }
  }

  apply(input: OrderLookupInput) -> OrderList {
    return {
      orders: orders
      count: orders.len
    }
  }
}

From Routes

route "/customers/:id/orders" {
  GET {
    data orders {
      query orders.recent_orders_by_customer {
        customer_id: input.params.id
      }
    }

    apply {
      return { status: 200, body: orders }
    }
  }
}

The qualified name module.query_name resolves the query across modules. Within the same module, the module prefix can be omitted.

Multiple Queries

A module can declare as many queries as needed:

query orders_by_status {
  args { status: String }
  sql { "SELECT * FROM orders WHERE status = @status" }
  output: OrderRow
}

query order_total_by_customer {
  args { customer_id: Id }
  sql {
    "SELECT customer_id, SUM(total) as total_spent, COUNT(*) as order_count
     FROM orders
     WHERE customer_id = @customer_id
     GROUP BY customer_id"
  }
  output: {
    customer_id: Id
    total_spent: Decimal
    order_count: Int
  }
}