Understanding the Setup

Moose runs an HTTP server to handle incoming GET requests. When you send a GET request to your Moose server using the /consumption/<endpoint-name> path, Moose finds the corresponding file named <endpoint-name>.ts in the /apis directory of your project.

Moose then executes the default exported function within that file. It also parses the URL query parameters and passes them as arguments to the function, ensuring that each parameter is correctly mapped to the corresponding function argument. This setup allows you to easily define and manage your API endpoints within your project’s directory structure.

Getting Started

Initialize Your API Endpoint

To create a new API endpoint, run this command in your CLI:

moose consumption init <your-endpoint-name>

This command creates a new file named <your-endpoint-name>.ts in the /apis folder of your Moose project. Moose uses this file and folder structure to map functions to API endpoints, ensuring that each function corresponds to the correct endpoint. The /apis folder serves as the directory where all your API endpoint definitions reside, making it easy for Moose to locate and manage your endpoints.

Implement your API function handler

Open the newly created file and define your API using an asynchronous function. Moose provides helper functions to simplify building and running dynamic SQL queries, making it easy to define your API logic.

Example structure:

interface QueryParams {
  limit: string;
  minDailyActiveUsers: string;
}
 
export default async function handle(
  { limit = "10", minDailyActiveUsers = "0" }: QueryParams,
  { client, sql },
) {
  return client.query(
    sql`SELECT 
      date,
      dailyActiveUsers
    FROM DailyActiveUsers
    WHERE dailyActiveUsers >= ${parseInt(minDailyActiveUsers)}
    LIMIT ${parseInt(limit)}`,
  );
}

Your function handler should be an asynchronous function and the default export of the file. This tells Moose which function to execute when handling API requests.

Additionally, the function takes two arguments:

Specifically, the sql helper lets you wrap your parameterized SQL query in a tagged template literal with a sql prefix (sql<SELECT STATEMENT HERE>), which prevents SQL injection as well as giving syntax highlighting in your IDE.

Working with Query Parameters

Define your parameter names and data types in the QueryParams interface. Inject them into your SQL query template string. Currently, URL query parameters are parsed as strings, so you need to convert them to the correct data type within your function. This step ensures your query parameters are correctly typed.

You can either parse your parameters within the template string directly or separately in your function before injecting them into the template string, as shown below. Both methods work with TypeScript string literals.

Example:

interface QueryParams {
  limit: string;
  minDailyActiveUsers: string;
}
 
export default async function handle(
  { limit = "10", minDailyActiveUsers = "0" }: QueryParams,
  { client, sql },
) {
  const limitInt = parseInt(limit);
  const minUsersInt = parseInt(minDailyActiveUsers);
  return client.query(
    sql`SELECT 
      date,
      dailyActiveUsers
    FROM DailyActiveUsers
    WHERE dailyActiveUsers >= ${minUsersInt}
    LIMIT ${limitInt}`,
  );
}

We are working on improvements to handle type parsing directly from the QueryParams interface in the future so you can skip the manual parsing step altogether. Stay tuned for updates on when this developer experience enhancement will be ready for you!

Executing Your Query

Moose provides helper functions, including a database client, to build dynamic, parameterized SQL queries. The client handles database connections, query execution, SQL injection prevention, and data type casting from TypeScript to Clickhouse types.

Refer to the Supported Data Types table for more information on how Moose maps Typescript data types to Clickhouse.

Test Your API

Test your API by making an HTTP GET request to your local Moose developer server at: https://localhost:4000/consumption/<your-endpoint-name>

Include the necessary query parameters for your function arguments. The name of your API endpoint should match the name of the Typescript file you created in the /apis folder.