APIs

import { Api } from "@514labs/moose-lib";import { SourcePipeline } from "path/to/SourcePipeline";  // Define the query parametersinterface QueryParams {  filterField: string;  maxResults: number;} // Model the query result typeinterface ResultItem {  id: number;  name: string;  value: number;}  const SourceTable = SourcePipeline.table!; // Use `!` to assert that the table is not nullconst cols = SourceTable.columns; // Define the result type as an array of the result item typeexport const exampleApi = new Api<QueryParams, ResultItem[]>("example_endpoint",      async ({ filterField, maxResults }: 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<ResultItem>(query);         // Return the result set as an array of the result item type        return await resultSet.json();    });

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

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

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}

interface MyTableSchema {  column1: string;  column2: number;  column3: string;} const MyTable = new OlapTable<MyTableSchema>("my_table"); interface QueryParams {  orderByColumn: keyof MyTableSchema; // validates against the column names in "my_table"}

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...  });

import { ApiUtil } from "@514labs/moose-lib";import { UserTable } from "./UserTable"; async function handler({ client, sql }: ApiUtil) {  const query = sql`SELECT * FROM ${UserTable}`;  const data = await client.query.execute<UserSchema>(query);}

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

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

import { UserTable } from "./UserTable"; const cols = UserTable.columns; // Join column referencesconst selectColumns = sql.join([  sql`${cols.id}`,  sql`${cols.name}`,  sql`${cols.email}`], ",");// Result: `id`, `name`, `email` // Join WHERE conditions with ANDconst conditions = [  sql`${cols.age} >= ${18}`,  sql`${cols.status} = ${"active"}`];const whereClause = sql.join(conditions, "AND");// Result: `age` >= 18 AND `status` = 'active'

// SQL functionsconst timestamp = sql.raw("NOW()");const query = sql`SELECT *, ${timestamp} as query_time FROM ${UserTable}`;// Result: SELECT *, NOW() as query_time FROM `users` // Sort directionconst direction = sql.raw("DESC");const sortedQuery = sql`SELECT * FROM ${UserTable} ORDER BY created_at ${direction}`;// Result: SELECT * FROM `users` ORDER BY created_at DESC

import { Sql } from "@514labs/moose-lib";import { UserTable } from "./UserTable"; interface QueryParams {  minAge?: number;  status?: string;  limit?: number;} const buildQuery = ({ minAge, status, limit = 100 }: QueryParams, { sql }) => {  // Start with base query  let query = sql`SELECT * FROM ${UserTable}`;   // Build conditions  const conditions: Sql[] = [];  if (minAge !== undefined) {    conditions.push(sql`age >= ${minAge}`);  }  if (status) {    conditions.push(sql`status = ${status}`);  }   // Append WHERE clause if conditions exist  if (conditions.length > 0) {    query = query.append(sql` WHERE ${sql.join(conditions, "AND")}`);  }   // Append ORDER BY and LIMIT  query = query    .append(sql` ORDER BY created_at ${sql.raw("DESC")}`)    .append(sql` LIMIT ${limit}`);   return query;};

import { ApiHelpers 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`};

import { ApiHelpers 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`};

import { Sql } from "@514labs/moose-lib";import { UserTable } from "./UserTable"; interface FilterParams {  minAge?: number;  status?: "active" | "inactive";  searchText?: string;} const buildQuery = ({ minAge, status, searchText }: FilterParams, { sql }) => {  const conditions: Sql[] = [];    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) {    // Use sql.join() to combine conditions with AND    query = query.append(sql` WHERE ${sql.join(conditions, "AND")}`);  }    // Use append() to add ORDER BY clause  query = query.append(sql` ORDER BY created_at ${sql.raw("DESC")}`);  return query;};

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

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]    }));  });