# Moose / Apis / Analytics Api Documentation – TypeScript

## Included Files
1. moose/apis/analytics-api/analytics-api.mdx

## APIs

Source: moose/apis/analytics-api/analytics-api.mdx

APIs for Moose

# APIs

## Overview
APIs are functions that run on your server and automatically exposed as HTTP `GET` endpoints.
They are designed to read data from your OLAP database. Out of the box, these APIs provide:

- Automatic type validation and type conversion for your query parameters, which are sent in the URL, and response body
- Managed database client connection
- Automatic OpenAPI documentation generation

Common use cases include:
- Powering user-facing analytics, dashboards and other front-end components
- Enabling AI tools to interact with your data
- Building custom APIs for your internal tools

### Enabling APIs

Analytics APIs are enabled by default. To explicitly control this feature in your `moose.config.toml`:

```toml filename="moose.config.toml" copy
[features]
apis = true
```

### Basic Usage

```ts filename="ExampleApi.ts" copy

// Define the query parameters
interface QueryParams {
  filterField: string;
  maxResults: number;
}

// Model the query result type
interface ResultItem {
  id: number;
  name: string;
  value: number;
}

const SourceTable = SourcePipeline.table!; // Use `!` to assert that the table is not null
const cols = SourceTable.columns;

// Define the result type as an array of the result item type
: QueryParams, { client, sql }) => {
        const query = sql`
        SELECT 
          ${cols.id},
          ${cols.name},
          ${cols.value}
        FROM ${SourceTable}
        WHERE category = ${filterField}
        LIMIT ${maxResults}`;

        // Set the result type to the type of the each row in the result set
        const resultSet = await client.query.execute(query);

        // Return the result set as an array of the result item type
        return await resultSet.json();
    });
```

```ts filename="SourcePipeline.ts" copy

interface SourceSchema {
  id: number;
  name: string;
  value: number;
}

);
```

The `Api` 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

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.

## Type Validation
You can also model the query parameters and response body as interfaces, which Moose will use to provide automatic type validation and type conversion for your query parameters, which are sent in the URL, and response body.

### Modeling Query Parameters

Define your API's parameters as a TypeScript interface:

```ts filename="ExampleQueryParams.ts" copy
interface QueryParams {
  filterField: string;
  maxResults: number;
  optionalParam?: string; // Not required for client to provide
}
```

Moose automatically handles:
- Runtime validation
- Clear error messages for invalid parameters
- OpenAPI documentation generation

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

### Adding Advanced Type Validation

Moose uses [Typia](https://typia.io/) to extract type definitions and provide runtime validation. Use Typia's tags for more complex validation:

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

### Common Validation Options

```ts filename="ValidationExamples.ts" copy
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

  // Literal validation
  status: "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](https://typia.io/api/tags).

You can derive a safe orderBy union from your actual table columns and use it directly in SQL:
```ts filename="ValidationExamples.ts" copy
interface MyTableSchema {
  column1: string;
  column2: number;
  column3: string;
}

const MyTable = new OlapTable("my_table");

interface QueryParams {
  orderByColumn: keyof MyTableSchema; // validates against the column names in "my_table"
}
```

### Setting Default Values

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

```ts filename="ExampleQueryParams.ts" copy {9}
interface QueryParams {
  filterField: string;
  maxResults: number;
  optionalParam?: string; // Not required for client to provide
}

const api = new Api<QueryParams, ResponseBody>("example_endpoint",
  async ({ filterField = "example", maxResults = 10, optionalParam = "default" }, { client, sql }) => {
    // Your logic here...
  }
);
```

## Implementing Route Handler

API route handlers are regular functions, so you can implement whatever arbitrary logic you want inside these functions. Most of the time you will be use APIs to expose your data to your front-end applications or other tools:

### Connecting to the Database
Moose provides a managed `MooseClient` to your function execution context. This client provides access to the database and other Moose resources, and handles connection pooling/lifecycle management for you:

```ts filename="ExampleApi.ts" copy {1}

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

Pass the type of the result to the `client.query.execute()` method to ensure type safety.

```ts filename="UserTable.ts" copy

interface UserSchema {
  id: Key<number>
  name: string
  email: string
}

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`
};
```

```ts filename="DynamicTables.ts" copy

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`
};
```

#### Conditional `WHERE` Clauses

Build `WHERE` clauses based on provided parameters:

```ts filename="ConditionalColumns.ts" copy
interface FilterParams {
  minAge?: number;
  status?: "active" | "inactive";
  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;
};
```

### Adding Authentication

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

```typescript
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);
}
```

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"}` |

## Post-Processing Query Results

After executing your database query, you can transform the data before returning it to the client. This allows you to:

```ts filename="PostProcessingExample.ts" copy
interface QueryParams {
  category: string;
  maxResults: number;
}

interface ResponseBody {
  itemId: number;
  displayName: string;
  formattedValue: string;
  isHighValue: boolean;
  date: string;
}

const processDataApi = new Api<QueryParams, ResponseBody>(
  "process_data_endpoint",
  async ({ category, maxResults = 10 }, { client, sql }) => {
    // 1. Fetch raw data
    const query = sql`
      SELECT id, name, value, timestamp
      FROM data_table
      WHERE category = ${category}
      LIMIT ${maxResults}
    `;

    const rawResults = await client.query.execute<{
      id: number;
      name: string;
      value: number;
      timestamp: string;
    }>(query);

    // 2. Post-process the results
    return rawResults.map(row => ({
      // Transform field names
      itemId: row.id,
      displayName: row.name.toUpperCase(),

      // Add derived fields
      formattedValue: `$${row.value.toFixed(2)}`,
      isHighValue: row.value > 1000,

      // Format dates
      date: new Date(row.timestamp).toISOString().split('T')[0]
    }));
  }
);
```

### Best Practices

While post-processing gives you flexibility, remember that database operations are typically more efficient for heavy data manipulation. Reserve post-processing for transformations that are difficult to express in SQL or that involve application-specific logic.

## Client Integration

By default, all API endpoints are automatically integrated with OpenAPI/Swagger documentation. You can integrate your OpenAPI SDK generator of choice to generate client libraries for your APIs.

Please refer to the [OpenAPI](/moose/apis/open-api-sdk) page for more information on how to integrate your APIs with OpenAPI.