GitHub

Consumption APIs

Viewing typescript

switch to python

Overview

Consumption APIs make it easy to build type-safe HTTP GET endpoints for surfacing data from your OLAP database. These APIs can help power user-facing analytics, dashboards and other front-end components, or even enable AI tools to interact with your data.

Working with Consumption APIs

Model your API parameters

Define the names and types of the parameters your API will accept

Write a route handler function

Write a simple, strongly-typed function that constructs a SQL query and returns the results

Moose Generates Validation & Docs

Moose automatically generates validation, type conversion, and OpenAPI documentation for your API request parameters and response body

Integrate with your API client

Use the generated API spec for your frontend applications or use other tools to interact with your API

Creating API Endpoints

ExampleApi.ts

import { ConsumptionApi } from "@514labs/moose-lib";
 
interface QueryParams {
  filterField: string;
  maxResults: number;
}
 
interface ResultItem {
  id: number;
  name: string;
  value: number;
}
 
type ResponseBody = ResultItem[];
 
const exampleApi = new ConsumptionApi<QueryParams, ResponseBody>("example_endpoint",  
    async ({ filterField, maxResults }: QueryParams, { client, sql }) => {
        const query = sql`
        SELECT 
          id,
          name,
          value
        FROM example_table
        WHERE category = ${filterField}
        LIMIT ${maxResults}`;
        
        return client.query.execute(query);
    });

ExampleApi.py

from moose_lib import ConsumptionApi
from pydantic import BaseModel
 
# Define the query parameters
class QueryParams(BaseModel):
    filterField: str
    maxResults: int
 
# Define the response body
class ResponseBody(BaseModel):
    id: int
    name: str
 
# Define the route handler function
def run(params: QueryParams) -> ResponseBody:
    ## Your logic here...
    return ResponseBody(id=1, name="Example")
 
# Create the API
example_api = ConsumptionApi[QueryParams, ResponseBody](name="example_endpoint", query_function=run)

The ConsumptionApi class takes:

Route name: The URL path to access your API (e.g., "example_endpoint")
Handler function: Processes requests with typed parameters and returns the result

The generic type parameters specify:

QueryParams: The structure of accepted URL parameters
ResponseBody: The exact shape of your API’s response data

MooseTip:

You can name these types anything you want. The first type generates validation for query parameters, while the second defines the response structure for OpenAPI documentation.

Moose automatically handles:

URL parameter validation and type conversion

SQL query interpolation and execution

Response formatting

Automated OpenAPI documentation

Defining Query Parameters

Define your API’s parameters as a TypeScript interface:

ExampleQueryParams.ts

interface QueryParams {
  filterField: string;
  maxResults: number;
  optionalParam?: string; // Not required for client to provide
}

Define your API’s parameters as a Pydantic model:

ExampleQueryParams.py

from pydantic import BaseModel
 
class QueryParams(BaseModel):
    filterField: str
    maxResults: int
    optionalParam?: str | None = None

Moose automatically handles:

Runtime validation
Clear error messages for invalid parameters
OpenAPI documentation generation

Warning:

Complex nested objects and arrays are not supported. Consumption APIs are GET endpoints designed to be simple and lightweight.

Adding Advanced Type Validation

Moose uses Typia to extract type definitions and provide runtime validation. Use Typia’s tags for more complex validation:

ExampleQueryParams.ts

interface QueryParams {
  filterField: string;
  // Ensure maxResults is a positive integer
  maxResults: number & tags.Type<"int64"> & tags.Minimum<"1">; 
}

Moose uses Pydantic for runtime validation. Use Pydantic’s Field class for more complex validation:

ExampleQueryParams.py

from pydantic import BaseModel, Field
 
class QueryParams(BaseModel):
    filterField: str
    maxResults: int = Field(..., gt=0)

Common Validation Options

ValidationExamples.ts

interface QueryParams {
  // Numeric validations
  id: number & tags.Type<"uint32">;  // Positive integer (0 to 4,294,967,295)
  age: number & tags.Minimum<18> & tags.Maximum<120>;  // Range: 18 <= age <= 120
  price: number & tags.ExclusiveMinimum<0> & tags.ExclusiveMaximum<1000>;  // Range: 0 < price < 1000
  discount: number & tags.MultipleOf<0.5>;  // Must be multiple of 0.5
  
  // String validations
  username: string & tags.MinLength<3> & tags.MaxLength<20>;  // Length between 3-20 characters
  email: string & tags.Format<"email">;  // Valid email format
  zipCode: string & tags.Pattern<"^[0-9]{5}$">;  // 5 digits
  uuid: string & tags.Format<"uuid">;  // Valid UUID
  ipAddress: string & tags.Format<"ipv4">; // Valid IPv4 address
  
  // Date validations
  startDate: string & tags.Format<"date">;  // YYYY-MM-DD format
  
  // Enum validation
  status: string & tags.Enum<"active" | "pending" | "inactive">;  // Must be one of these values
  
  // Optional parameters
  limit?: number & tags.Type<"uint32"> & tags.Maximum<100>;  // Optional, if provided: positive integer <= 100
  
  // Combined validations
  searchTerm?: (string & tags.MinLength<3>) | null;  // Either null or string with ≥3 characters
}

Notice its just regular TypeScript union types. For a full list of validation options, see the Typia documentation.

ValidationExamples.py

from pydantic import BaseModel, Field
 
class QueryParams(BaseModel):
    # Numeric validations
    id: int = Field(..., gt=0)
    age: int = Field(..., gt=0, lt=120)
    price: float = Field(..., gt=0, lt=1000)
    discount: float = Field(..., gt=0, multiple_of=0.5)
    
    # String validations
    username: str = Field(..., min_length=3, max_length=20)
    email: str = Field(..., format="email") 
    zipCode: str = Field(..., pattern=r"^[0-9]{5}$")
    uuid: str = Field(..., format="uuid")
    ipAddress: str = Field(..., format="ipv4")
    
    # Date validations
    startDate: str = Field(..., format="date")
    
    # Enum validation
    status: str = Field(..., enum=["active", "pending", "inactive"])
    
    # Optional parameters
    limit: int = Field(None, gt=0, lt=100)

For a full list of validation options, see the Pydantic documentation.

Setting Default Values

You can set default values for parameters by setting values for each parameter in the API route handler function signature:

ExampleQueryParams.ts

interface QueryParams {
  filterField: string;
  maxResults: number;
  optionalParam?: string; // Not required for client to provide
}
 
const api = new ConsumptionApi<QueryParams, ResponseBody>("example_endpoint",
  async ({ filterField = "example", maxResults = 10, optionalParam = "default" }, { client, sql }) => {
    // Your logic here...
  }
);

You can set default values for parameters by setting values for each parameter in your Pydantic model:

ExampleQueryParams.py

from pydantic import BaseModel
 
class QueryParams(BaseModel):
    filterField: str = "example"
    maxResults: int = 10
    optionalParam: str | None = "default"

Implementing Route Handler

Consunmption API route handlers are regular functions, so you can implement whatever arbitrary logic you want inside these functions. The primary purpose of the handler function is to construct a SQL query and return the results to the client:

/apis/getBar.ts

import {
  createConsumptionApi,
  ConsumptionHelpers as CH,
} from "@514labs/moose-lib";
import { tags } from "typia";
 
// Define expected parameters and their types
interface QueryParams {
  orderBy: "totalRows" | "rowsWithText" | "maxTextLength" | "totalTextLength";
  limit?: number;
  startDay?: number & tags.Type<"int32"> & tags.Minimum<1> & tags.Maximum<31>;
  endDay?: number & tags.Type<"int32"> & tags.Minimum<1> & tags.Maximum<31>;
}
 
interface ResponseBody {
  dayOfMonth: number;
  totalRows?: number;
  rowsWithText?: number;
  maxTextLength?: number;
  totalTextLength?: number;
}
 
// ConsumptionApi uses compile time code generation to generate a parser for QueryParams
const myApi = new ConsumptionApi<QueryParams, ResponseBody>("my_api_route",
  async (
    { orderBy = "totalRows", limit = 5, startDay = 1, endDay = 31 },
    { client, sql }
  ) => {
    const query = sql`
      SELECT 
        dayOfMonth,
        ${CH.column(orderBy)}
      FROM BarAggregated_MV
      WHERE 
        dayOfMonth >= ${startDay} 
        AND dayOfMonth <= ${endDay}
      ORDER BY ${CH.column(orderBy)} DESC
      LIMIT ${limit}
    `;
 
    // Execute query and return results
    const data = await client.query.execute(query);
    return data;
  }
);

/apis/get-bar.py

from moose_lib import ConsumptionApi
from app.views.bar_aggregated import BarAggregatedMV
 
def run(params: QueryParams, client: MooseClient) -> None:
    query = f"""
    SELECT 
      dayOfMonth,
      {orderBy}
    FROM {BarAggregatedMV.target_table.name}
    WHERE 
      dayOfMonth >= {startDay} 
      AND dayOfMonth <= {endDay}
    ORDER BY {orderBy} DESC
    LIMIT {limit}
    """
    data = client.query.execute(query, {
        "orderBy": params.orderBy,
        "startDay": params.startDay,
        "endDay": params.endDay,
        "limit": params.limit
    })
    return data

Connecting to the Database

Moose provides an instance of a MooseClient to your route handler function. This client provides access to the database and other Moose resources:

ExampleApi.ts

 
async function handler({ client, sql }: ConsumptionUtil) {
  const query = sql`SELECT * FROM users`;
  const data = await client.query.execute(query);
}

The MooseClient enables you to execute queries and other database operations. Specifically by using f-string interpolation to build your queries:

ExampleApi.py

 
def run(params: QueryParams, client: MooseClient) -> None:
    query = f"SELECT * FROM users"
    data = client.query.execute(query)

Constructing Safe SQL Queries

The sql template literal in Moose provides type-safe query construction with protection against SQL injection. Below are some examples of common patterns for builing safe queries:

Basic Query Parameter Interpolation

BasicInterpolation.ts

 
const minAge = 18;
const userRole = "admin";
 
const query = sql`
  SELECT * FROM users 
  WHERE age > ${minAge} 
  AND role = ${userRole}
`;
 
// MooseClient handles type conversion and escaping
const data = await client.query.execute(query);
 
// EXECUTION: SELECT * FROM users WHERE age > 18 AND role = 'admin'

BasicInterpolation.py

 
min_age = 18
user_role = "admin"
 
query = f"SELECT * FROM users WHERE age > {min_age} AND role = '{user_role}'"
data = client.query.execute(query, {
    "min_age": min_age,
    "user_role": user_role
})

Table and Column References

Reference tables and columns directly from your Moose objects as variables in your sql template literals:

ExampleTableColumnQuery.ts

import { userTable } from "../tables/userTable";
 
const query = sql`
  SELECT 
    ${userTable.columns.id},
    ${userTable.columns.name},
    ${userTable.columns.email}
  FROM ${userTable}
  WHERE ${userTable.columns.isActive} = true
`;
 
// EXECUTION: SELECT id, name, email FROM users WHERE is_active = true

ExampleTableColumnQuery.py

from tables import userTable
 
query = f"""
  SELECT 
    {userTable.columns["id"]}, 
    {userTable.columns["name"]}, 
    {userTable.columns["email"]} 
  FROM {userTable.name} 
  WHERE {userTable.columns["isActive"]} = true
"""
 
data = client.query.execute(query)

Type Safety

Static type checking ensures you only reference columns that actually exist.

Advanced Query Patterns

Dynamic Column & Table Selection

Use ConsumptionHelpers to handle dynamic column and table references in your queries:

DynamicColumns.ts

import { ConsumptionHelpers as CH } from "@514labs/moose-lib";
 
interface QueryParams {
  sortBy: string;  // Column to sort by
  fields: string;  // Comma-separated list of columns to select (e.g., "id,name,email")
}
 
const queryHandler = async ({ sortBy = "id", fields = "id,name" }: QueryParams, { client, sql }) => {
  // Split the comma-separated string into individual fields
  const fieldList = fields.split(',').map(f => f.trim());
  
  // Build the query by selecting each column individually
  const query = sql`
    SELECT 
      ${fieldList.map(field => sql`${CH.column(field)}`).join(', ')}
    FROM ${userTable}
    ORDER BY ${CH.column(sortBy)}
  `;
  
  // MooseClient converts fieldList to valid ClickHouse identifiers
  return client.query.execute(query);
 
  // EXECUTION: `SELECT id, name FROM users ORDER BY id`
};

DynamicTables.ts

import { ConsumptionHelpers as CH } from "@514labs/moose-lib";
 
interface QueryParams {
  tableName: string;
}
 
const queryHandler = async ({ tableName = "users" }: QueryParams, { client, sql }) => {
  const query = sql`
    SELECT * FROM ${CH.table(tableName)}
  `;
 
  // MooseClient converts tableName to a valid ClickHouse identifier
  return client.query.execute(query);
 
  // EXECUTION: `SELECT * FROM users`
};

DynamicColumns.py

from app.tables import myTable
 
colName = "id"
 
query = f"""SELECT {myTable.columns[colName]} FROM {myTable.name}"""
data = client.query.execute(query)
 
## MooseClient will automatically interpolate the column name into the query as a valid ClickHouse identifier
## EXECUTION: SELECT id FROM users

Conditional `WHERE` Clauses

Build WHERE clauses based on provided parameters:

ConditionalColumns.ts

interface FilterParams {
  minAge?: number;
  status?: string;
  searchText?: string;
}
 
const buildQuery = ({ minAge, status, searchText }: FilterParams, { sql }) => {
  let conditions = [];
  
  if (minAge !== undefined) {
    conditions.push(sql`age >= ${minAge}`);
  }
  
  if (status) {
    conditions.push(sql`status = ${status}`);
  }
  
  if (searchText) {
    conditions.push(sql`(name ILIKE ${'%' + searchText + '%'} OR email ILIKE ${'%' + searchText + '%'})`);
  }
  
  // Build the full query with conditional WHERE clause
  let query = sql`SELECT * FROM ${userTable}`;
  
  if (conditions.length > 0) {
    // Join conditions with AND operator
    let whereClause = conditions.join(' AND ');
    query = sql`${query} WHERE ${whereClause}`;
  }
  
  query = sql`${query} ORDER BY created_at DESC`;
  return query;
};

ConditionalColumns.py

 
def build_query(params: FilterParams, client: MooseClient) -> None:
    conditions = []
    interpolated = {}
    if params.min_age:
        conditions.append(f"age >= {min_age}")
        interpolated["min_age"] = min_age
    
    if params.status:
        conditions.append(f"status = {status}")
        interpolated["status"] = status
    
    if params.search_text:
        conditions.append(f"name ILIKE '%{search_text}%' OR email ILIKE '%{search_text}%'")
        interpolated["search_text"] = search_text
 
    query = f"""SELECT * FROM users WHERE {" AND ".join(conditions)} ORDER BY created_at DESC"""
    data = client.query.execute(query, interpolated)
    return data

Adding Authentication

Moose supports authentication via JSON web tokens (JWTs). When your client makes a request to your Consumption API, Moose will automatically parse the JWT and pass the authenticated payload to your handler function as the jwt object:

async (
  { orderBy = "totalRows", limit = 5 },
  { client, sql, jwt }
) => {
  // Use jwt.userId to filter data for the current user
  const query = sql`
    SELECT * FROM userReports 
    WHERE user_id = ${jwt.userId}
    LIMIT ${limit}
  `;
  
  return client.query.execute(query);
}

Authentication.py

 
def run(params: QueryParams, client: MooseClient, jwt: dict) -> None:
    query = f"""SELECT * FROM userReports WHERE user_id = {jwt["userId"]} LIMIT {limit}"""
    data = client.query.execute(query)
    return data

JWT Error Handling

Moose validates the JWT signature and ensures the JWT is properly formatted. If the JWT authentication fails, Moose will return a 401 Unauthorized error.

Understanding Response Codes

Moose automatically provides standard HTTP responses:

Status Code	Meaning	Response Body
200	Success	Your API’s result data
400	Validation error	`{ "error": "Detailed message"}`
401	Unauthorized	`{ "error": "Unauthorized"}`
500	Internal server error	`{ "error": "Internal server error"}`

In-Database Transforms

Workflow Orchestration

Consumption APIs

Viewing typescript

switch to python

Overview

Working with Consumption APIs

Model your API parameters

Write a route handler function

Moose Generates Validation & Docs

Integrate with your API client

Creating API Endpoints

Moose automatically handles:

URL parameter validation and type conversion

SQL query interpolation and execution

Response formatting

Automated OpenAPI documentation

Defining Query Parameters

Adding Advanced Type Validation

Common Validation Options

Setting Default Values

Implementing Route Handler

Connecting to the Database

Constructing Safe SQL Queries

Basic Query Parameter Interpolation

Table and Column References

Advanced Query Patterns

Dynamic Column & Table Selection

Conditional WHERE Clauses

Adding Authentication

Understanding Response Codes

Conditional `WHERE` Clauses