1. MooseStack
  3. Moose OLAP
  5. Querying Data

Querying Data

Moose provides type-safe SQL querying for your OlapTable and MaterializedView instances. Use cases include:

  • Building APIs to expose your data to client/frontend applications
  • Building transformation pipelines inside your database with materialized views

Querying with MooseClient

Use MooseClient to query data from existing tables and materialized views.

Basic Querying

BasicQuerying.ts
import { MooseClient, sql } from "@514labs/moose-lib";import { UserTable } from "./tables/UserTable"; const client = new MooseClient(); // Query existing tableconst query = sql`  SELECT id, name, email  FROM ${UserTable}  WHERE status = 'active'  LIMIT 10`; const result = await client.query.execute(query);const data = await result.json();

Querying Materialized Views

QueryMaterializedView.ts
import { MooseClient, sql } from "@514labs/moose-lib"; const client = new MooseClient(); // Query existing materialized viewconst query = sql`  SELECT user_id, total_orders, average_order_value  FROM user_stats_view  WHERE total_orders > 10  ORDER BY average_order_value DESC`; const result = await client.query.execute(query);

Select With Column and Table References

TypedReferences.ts
import { sql } from "@514labs/moose-lib";import { UserTable, OrderTable } from "./tables"; // Reference table columns with type safetyconst cols = UserTable.columns;const query = sql`  SELECT     ${cols.id},    ${cols.name},    ${cols.email}  FROM ${UserTable}  WHERE ${cols.status} = 'active'`; // Multiple table referencesconst joinQuery = sql`  SELECT     ${UserTable.columns.id},    ${UserTable.columns.name},    ${OrderTable.columns.order_value}  FROM ${UserTable}  JOIN ${OrderTable} ON ${UserTable.columns.id} = ${OrderTable.columns.user_id}`;

When you query a materialized view, you reference the MaterializedView.targetTable to get the columns of the target table.

TypedReferences.ts
import { sql } from "@514labs/moose-lib";import { ExampleMaterializedView } from "./materialized-views";  const query = sql`  SELECT     ${ExampleMaterializedView.targetTable.columns.id},    ${ExampleMaterializedView.targetTable.columns.name},    ${ExampleMaterializedView.targetTable.columns.email}  FROM ${ExampleMaterializedView.targetTable}`;

In ClickHouse, when you query a Materialized View that has columns of type AggregateFunction in the result set, ordinarily you would need to run:

SELECT sumMerge(amount) FROM {ExampleMaterializedView}

When querying this with Moose, you can just reference the column name in the sql template literal. The interpolation will be replaced with the correct ClickHouse function:

TypedReferences.ts
import { sql } from "@514labs/moose-lib";import { ExampleMaterializedView } from "./materialized-views"; const query = sql`  SELECT ${ExampleMaterializedView.targetTable.columns.amount}  FROM ${ExampleMaterializedView.targetTable}`; // This will be replaced with:// SELECT sumMerge(amount) FROM {ExampleMaterializedView}

Filtering with WHERE Clauses

WhereClauses.ts
import { sql } from "@514labs/moose-lib"; // Multiple WHERE conditionsconst filterQuery = sql`  SELECT ${UserTable.columns.id}, ${UserTable.columns.name}  FROM ${UserTable}  WHERE ${UserTable.columns.status} = 'active'    AND ${UserTable.columns.created_at} > '2024-01-01'    AND ${UserTable.columns.email} ILIKE ${'%' + searchTerm + '%'}`; // Using IN clausesconst inQuery = sql`  SELECT * FROM ${UserTable}  WHERE ${UserTable.columns.id} IN (${userIds})`; // Using BETWEENconst rangeQuery = sql`  SELECT * FROM ${UserTable}  WHERE ${UserTable.columns.age} BETWEEN ${minAge} AND ${maxAge}`;

Dynamic Query Building

Use the sql template literal to build safe queries:

SqlTemplateLiterals.ts
import { sql } from "@514labs/moose-lib"; // Safe interpolation with sql template literalconst status = 'active';const limit = 10; const query = sql`  SELECT id, name, email  FROM ${UserTable}  WHERE ${UserTable.columns.status} = ${status}  LIMIT ${limit}`; // Conditional WHERE clausesinterface FilterParams {  minAge?: number;  status?: "active" | "inactive";  searchText?: string;} const buildConditionalQuery = (filters: FilterParams) => {  let conditions = [];    if (filters.minAge !== undefined) {    conditions.push(sql`age >= ${filters.minAge}`);  }    if (filters.status) {    conditions.push(sql`status = ${filters.status}`);  }    if (filters.searchText) {    conditions.push(sql`(name ILIKE ${'%' + filters.searchText + '%'} OR email ILIKE ${'%' + filters.searchText + '%'})`);  }    let query = sql`SELECT * FROM ${UserTable}`;    if (conditions.length > 0) {    query = sql`${query} WHERE ${conditions.join(' AND ')}`;  }    return sql`${query} ORDER BY created_at DESC`;};

Building APIs

To build REST APIs that expose your data, see the Bring Your Own API Framework documentation for comprehensive examples and patterns using Express, Koa, Fastify, or FastAPI.

Common Pitfalls

Common TypeScript Issues
  • Column name typos: Use UserTable.columns.columnName for autocomplete
  • Type mismatches: Ensure your schema types match ClickHouse types
  • Missing imports: Import your table definitions before using them
  • Template literal syntax: Use backticks sql not regular strings
  • Forgetting await: Always await client.query.execute()

Performance Optimization

If your query is slower than expected, there are a few things you can check:

  • If using filters, try to filter on a column that is defined in the orderByFields of the table
  • For common queries, consider creating a materialized view to pre-compute the result set

Further Reading

Moose APIs
Build REST APIs for your data with built-in Moose API module
Learn More →
Creating Materialized Views
Define data transformations
Learn More →

On this page

Querying with MooseClientBasic QueryingQuerying Materialized ViewsSelect With Column and Table ReferencesFiltering with WHERE ClausesDynamic Query BuildingBuilding APIsCommon PitfallsPerformance OptimizationFurther Reading
FiveonefourFiveonefour
Fiveonefour Docs
MooseStackTemplatesGuides
Release Notes
Source523
  • Overview
Build a New App
  • 5 Minute Quickstart
  • Browse Templates
  • Existing ClickHouse
Add to Existing App
  • Next.js
  • Fastify
Fundamentals
  • Moose Runtime
  • MooseDev MCP
  • Data Modeling
Moose Modules
  • Moose OLAP
    • Data Modeling
    • Tables
    • Views
    • Materialized Views
    • Materialized Columns
    • External Data & Introspection
    • External Tables
    • Introspecting Tables
    • Data Access
    • Inserting Data
    • Reading Data
    • Performance & Optimization
    • Schema Optimization
    • Secondary & Data-skipping Indexes
    • TTL (Time-to-Live)
    • Schema Versioning
  • Moose Streaming
  • Moose Workflows
  • Moose APIs & Web Apps
Deployment & Lifecycle
  • Moose Migrate
  • Moose Deploy
Reference
  • API Reference
  • Data Types
  • Table Engines
  • CLI
  • Configuration
  • Observability Metrics
  • Help
  • Release Notes
Contribution
  • Documentation
  • Framework
BasicQuerying.ts
import { MooseClient, sql } from "@514labs/moose-lib";import { UserTable } from "./tables/UserTable"; const client = new MooseClient(); // Query existing tableconst query = sql`  SELECT id, name, email  FROM ${UserTable}  WHERE status = 'active'  LIMIT 10`; const result = await client.query.execute(query);const data = await result.json();
QueryMaterializedView.ts
import { MooseClient, sql } from "@514labs/moose-lib"; const client = new MooseClient(); // Query existing materialized viewconst query = sql`  SELECT user_id, total_orders, average_order_value  FROM user_stats_view  WHERE total_orders > 10  ORDER BY average_order_value DESC`; const result = await client.query.execute(query);
TypedReferences.ts
import { sql } from "@514labs/moose-lib";import { UserTable, OrderTable } from "./tables"; // Reference table columns with type safetyconst cols = UserTable.columns;const query = sql`  SELECT     ${cols.id},    ${cols.name},    ${cols.email}  FROM ${UserTable}  WHERE ${cols.status} = 'active'`; // Multiple table referencesconst joinQuery = sql`  SELECT     ${UserTable.columns.id},    ${UserTable.columns.name},    ${OrderTable.columns.order_value}  FROM ${UserTable}  JOIN ${OrderTable} ON ${UserTable.columns.id} = ${OrderTable.columns.user_id}`;

When you query a materialized view, you reference the MaterializedView.targetTable to get the columns of the target table.

TypedReferences.ts
import { sql } from "@514labs/moose-lib";import { ExampleMaterializedView } from "./materialized-views";  const query = sql`  SELECT     ${ExampleMaterializedView.targetTable.columns.id},    ${ExampleMaterializedView.targetTable.columns.name},    ${ExampleMaterializedView.targetTable.columns.email}  FROM ${ExampleMaterializedView.targetTable}`;

In ClickHouse, when you query a Materialized View that has columns of type AggregateFunction in the result set, ordinarily you would need to run:

SELECT sumMerge(amount) FROM {ExampleMaterializedView}

When querying this with Moose, you can just reference the column name in the sql template literal. The interpolation will be replaced with the correct ClickHouse function:

TypedReferences.ts
import { sql } from "@514labs/moose-lib";import { ExampleMaterializedView } from "./materialized-views"; const query = sql`  SELECT ${ExampleMaterializedView.targetTable.columns.amount}  FROM ${ExampleMaterializedView.targetTable}`; // This will be replaced with:// SELECT sumMerge(amount) FROM {ExampleMaterializedView}
WhereClauses.ts
import { sql } from "@514labs/moose-lib"; // Multiple WHERE conditionsconst filterQuery = sql`  SELECT ${UserTable.columns.id}, ${UserTable.columns.name}  FROM ${UserTable}  WHERE ${UserTable.columns.status} = 'active'    AND ${UserTable.columns.created_at} > '2024-01-01'    AND ${UserTable.columns.email} ILIKE ${'%' + searchTerm + '%'}`; // Using IN clausesconst inQuery = sql`  SELECT * FROM ${UserTable}  WHERE ${UserTable.columns.id} IN (${userIds})`; // Using BETWEENconst rangeQuery = sql`  SELECT * FROM ${UserTable}  WHERE ${UserTable.columns.age} BETWEEN ${minAge} AND ${maxAge}`;

Use the sql template literal to build safe queries:

SqlTemplateLiterals.ts
import { sql } from "@514labs/moose-lib"; // Safe interpolation with sql template literalconst status = 'active';const limit = 10; const query = sql`  SELECT id, name, email  FROM ${UserTable}  WHERE ${UserTable.columns.status} = ${status}  LIMIT ${limit}`; // Conditional WHERE clausesinterface FilterParams {  minAge?: number;  status?: "active" | "inactive";  searchText?: string;} const buildConditionalQuery = (filters: FilterParams) => {  let conditions = [];    if (filters.minAge !== undefined) {    conditions.push(sql`age >= ${filters.minAge}`);  }    if (filters.status) {    conditions.push(sql`status = ${filters.status}`);  }    if (filters.searchText) {    conditions.push(sql`(name ILIKE ${'%' + filters.searchText + '%'} OR email ILIKE ${'%' + filters.searchText + '%'})`);  }    let query = sql`SELECT * FROM ${UserTable}`;    if (conditions.length > 0) {    query = sql`${query} WHERE ${conditions.join(' AND ')}`;  }    return sql`${query} ORDER BY created_at DESC`;};
Common TypeScript Issues
  • Column name typos: Use UserTable.columns.columnName for autocomplete
  • Type mismatches: Ensure your schema types match ClickHouse types
  • Missing imports: Import your table definitions before using them
  • Template literal syntax: Use backticks sql not regular strings
  • Forgetting await: Always await client.query.execute()