Introduction to Consumption APIs

Consumption APIs make it easy to build type-safe endpoints for surfacing data from your OLAP database. As a developer, you'll write simple, strongly-typed functions that automatically handle:

• URL parameter parsing and type conversion
• SQL query construction and execution
• Response formatting
• Automatically generated OpenAPI documentation

No need to write boilerplate for parameter validation, type conversion, or error handling - the framework handles these for you.

Getting Started

File and Folder Structure

Create your API endpoints by adding .ts.py files to the /apis folder. Each file automatically becomes an API endpoint:

Creating a New API Endpoint

The fastest way to create a new Consumption API endpoint is using the Moose CLI:

npx moose-cli consumption init getBar

moose-cli consumption init get_bar

This command will:

1. Create a new file getBar.tsget_bar.py in your /apis directory
2. Scaffold the basic API structure with type definitions
3. Add example query parameters and SQL query

Basic API Template

The generated template is a basic API endpoint that you can customize to your needs:

 
import { createConsumptionApi } from "@514labs/moose-lib";
 
// This file is where you can define your API templates for consuming your data
interface QueryParams {}
 
// createConsumptionApi uses compile time code generation to generate a parser for QueryParams
export default createConsumptionApi<QueryParams>(
  async (params, { client, sql }) => {
    return client.query(sql`SELECT 1`);
  }
);
 

# This file is where you can define your API templates for consuming your data
# All query_params are passed in as strings, and are used within the sql tag to parameterize you queries
 
from moose_lib import MooseClient
 
@dataclass
class QueryParams:
    ## Define your query parameters here
 
def run(client: MooseClient, params: QueryParams):
    return client.query("SELECT 1", { })
 

You can then customize this template by:

• Defining your query parameters
• Writing your SQL query
• Adding any necessary data transformations

Implementing the API Logic

Each API endpoint is defined by a single function that handles incoming requests. This function receives typed query parameters and returns database query results.

import {
  createConsumptionApi,
  ConsumptionHelpers as CH,
} from "@514labs/moose-lib";
 
interface QueryParams {
  n_days?: number;
  sort_by?: "total_value" | "unique_ids";
}
 
export default createConsumptionApi<QueryParams>(
  async (
    { n_days = 10, sort_by = "total_value" }: QueryParams,
    { client, sql }
  ) => {
    const query = sql`
        SELECT 
            date,
            sumMerge(total_value) as total_value,
            uniqMerge(unique_ids) as unique_ids
        FROM BarAggregated
        GROUP BY date
        ORDER BY 
            ${CH.column(sort_by)} DESC
        LIMIT ${n_days}
        `;
    return client.query(query);
  }
);

from dataclasses import dataclass
from moose_lib import MooseClient
 
@dataclass
class QueryParams:
    n_days: int = 10  # Number of top days to return
    sort_by: str = "total_value"  # Either "total_value" or "unique_ids"
 
def run(client: MooseClient, params: QueryParams):
    # Define the base query to get aggregated metrics
    base_query = """
    SELECT 
        date,
        sumMerge(total_value) as total_value,
        uniqMerge(unique_ids) as unique_ids
    FROM BarAggregated
    GROUP BY date
    """
    
    # Define sort order based on params
    sort_orders = {
        "total_value": "ORDER BY total_value DESC, unique_ids DESC",
        "unique_ids": "ORDER BY unique_ids DESC, total_value DESC"
    }
    
    # Construct final query with sort order and limit
    query = f"""
    {base_query}
    {sort_orders[params.sort_by]}
    LIMIT {params.n_days}
    """
    
    return client.query.execute(query, {"n_days": params.n_days})

Query Parameters

Query parameters allow your APIs to accept dynamic inputs through URL parameters. These parameters are automatically parsed and type-converted before reaching your handler function.

// Define expected parameters and their types
interface QueryParams {
  n_days?: number;          // Optional number parameter
  sort_by?: "total_value" | "unique_ids";  // String union type
}
 
// URL: /consumption/getBar?n_days=5&sort_by=unique_ids
// Automatically provides:
{
  n_days: 5,               // Converted to number
  sort_by: "unique_ids"    // Type-checked
}

@dataclass
class QueryParams:
    n_days: int = 10  # Optional with default value
    sort_by: str = "total_value"  # Optional with default value
 
# URL: /consumption/getBar?n_days=5&sort_by=unique_ids
# Automatically provides:
params = QueryParams(
    n_days=5,           # Converted to int
    sort_by="unique_ids"  # Validated as str
)

import { createConsumptionApi } from "@514labs/moose-lib";
import typia from "typia";
 
// Define an interface strictly for query parameters using Typia tags for validation.
interface QueryParams {
  // Validate that `userId` is a 32-bit unsigned integer.
  userId: number & typia.tags.Type<"uint32">;
 
  // Ensure that `email` is formatted correctly as an email address.
  email: string & typia.tags.Format<"email">;
 
  // Optional `age`: if provided, it must be greater than 17.
  age?: number & typia.tags.ExclusiveMinimum<17>;
}
 
export default createConsumptionApi<QueryParams>(
  async (params, { client, sql }) => {
    // The query parameters are auto-validated against the Typia constraints.
    const query = sql`
      SELECT *
      FROM Users
      WHERE userId = ${params.userId}
        AND email = ${params.email}
        ${params.age ? sql`AND age > ${params.age}` : sql``}
    `;
    return client.query(query);
  }
);

Safe SQL Construction

// Using template literals with the sql helper
const query = sql`
    SELECT *
    FROM MyTable
    ORDER BY ${CH.column(sort_by)} DESC
    LIMIT ${n_days}
`;
 
// ConsumptionHelpers (CH) prevent SQL injection
CH.column(sort_by)  // Safely interpolates column names

# Define valid sort orders
sort_orders = {
    "total_value": "ORDER BY total_value DESC",
    "unique_ids": "ORDER BY unique_ids DESC"
}
 
# Safe query construction using f-strings for structure
# and parameterized values for user input
query = f"""
    {base_query}
    {sort_orders[params.sort_by]}
    LIMIT {params.n_days}
"""
 
# Execute with parameters
client.query.execute(query, {"n_days": params.n_days})

Tips & Best Practices

• Testing Your APIs: Use tools like Postman, Thunder Client, or cURL to test your endpoints.
• Utilize Default Values: Leverage defaults in your interfaces or dataclasses to reduce manual parsing.
• Write Safe SQL: Always use the provided SQL helpers or parameterized queries to avoid injection vulnerabilities.
• Automatic API Documentation: Take advantage of the auto generated OpenAPI specification, hosted on your local dev server at http://localhost:5001/openapi.yaml (opens in a new tab), for interactive API documentation and easier client integration.

Automatically Generated OpenAPI Documentation

The framework automatically generates an up-to-date OpenAPI specification during local development. This spec provides a comprehensive overview of all consumption API endpoints, including:

• Endpoint Paths & HTTP Methods: Lists each API endpoint and the HTTP method (GET) it supports.
• Request Parameters: Details on the expected query parameters, including types, default values, and validation rules.
• Response Formats: Information about the structure of the responses and potential status codes.

How to Use the OpenAPI Spec

1. Visit the Specification URL:
   The OpenAPI specification is hosted on your local development server on port 5001. Simply visit http://localhost:5001/openapi.yaml (opens in a new tab) in your browser to access the YAML file that outlines your API.

2. Integrate with API Tools:
   You can point your API client tool of choice—such as Swagger UI, Postman, or Thunder Client—to http://localhost:5001/openapi.yaml (opens in a new tab). This will automatically generate an interactive UI where you can explore and test your API endpoints.

3. Generate Client Code:
   Leverage the specification to automatically generate client libraries and SDKs. Many development frameworks and tools, such as OpenAPI Generator (opens in a new tab), support importing OpenAPI specs to streamline client integration.

4. Documentation is Automatically Updated:
   The documentation is regenerated each time you save your API files while running your local development server, ensuring that any new endpoints or changes in parameter validation are immediately reflected.