Implement API Route Handler Function

To create an API route handler in Moose, open the file corresponding to your route and define your API using an asynchronous function.

Moose simplifies this process by providing helper functions for building and executing dynamic SQL queries, making it easy to define your logic.

Example

Below is a structure for an API that returns daily active users with a limit and a minimum threshold:

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

In this example:

• The handle function is the default export, making it the entry point for your API route.
• The function accepts two arguments:
  1. QueryParams: This object contains your query parameters with default values.
  2. Helper object: This includes client (for executing the query) and sql (for constructing safe SQL queries).

Query Parameters

Query parameters are parsed from the URL as strings. You’ll need to convert them to appropriate types (e.g., integers) before injecting them into your SQL query.

Example:

Here's a slightly modified version of the previous example, which demonstrates parsing the query parameters before building the SQL query:

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

In this example, notice how:

• Parameters limit and minDailyActiveUsers are parsed as integers before being injected into the SQL query
• The sql tagged template literal is used to safely construct the query

This ensures proper type conversion, which will prevent SQL injection and maintain data type consistency.

Handling More Complex Queries

You can implement more complex logic by expanding the SQL query or adjusting the query parameters. Here’s an example where we add a date range filter:

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

In this case, we’re using additional query parameters (startDate and endDate) to filter records based on a date range.

Testing Consumption APIs Locally

Once your route handler is defined, you can test it by making a request to your local Moose developer server:

https://localhost:4000/consumption/dailyActiveUsers?limit=10&minDailyActiveUsers=5

This example passes limit and minDailyActiveUsers as query parameters. Be sure that the endpoint URL matches the filename of your API handler, as Moose maps endpoints based on the filenames in the /apis folder.