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
import { MooseClient, sql } from "@514labs/moose-lib";import { UserTable } from "./tables/UserTable"; const client = new MooseClient(); // Query existing tableconst query = sql.statement` 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
import { MooseClient, sql } from "@514labs/moose-lib"; const client = new MooseClient(); // Query existing materialized viewconst query = sql.statement` 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
import { sql } from "@514labs/moose-lib";import { UserTable, OrderTable } from "./tables"; // Reference table columns with type safetyconst cols = UserTable.columns;const query = sql.statement` SELECT ${cols.id}, ${cols.name}, ${cols.email} FROM ${UserTable} WHERE ${cols.status} = 'active'`; // Multiple table referencesconst joinQuery = sql.statement` 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.
import { sql } from "@514labs/moose-lib";import { ExampleMaterializedView } from "./materialized-views"; const query = sql.statement` 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:
import { sql } from "@514labs/moose-lib";import { ExampleMaterializedView } from "./materialized-views"; const query = sql.statement` SELECT ${ExampleMaterializedView.targetTable.columns.amount} FROM ${ExampleMaterializedView.targetTable}`; // This will be replaced with:// SELECT sumMerge(amount) FROM {ExampleMaterializedView}Filtering with WHERE Clauses
import { sql } from "@514labs/moose-lib"; // Multiple WHERE conditionsconst filterQuery = sql.statement` 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.statement` SELECT * FROM ${UserTable} WHERE ${UserTable.columns.id} IN (${sql.join( userIds.map((id) => sql.fragment`${id}`), )})`; // Using BETWEENconst rangeQuery = sql.statement` SELECT * FROM ${UserTable} WHERE ${UserTable.columns.age} BETWEEN ${minAge} AND ${maxAge}`;Dynamic Query Building
Use sql.statement for complete SQL statements and sql.fragment for reusable SQL fragments (expressions, conditions, partial clauses). This distinction enables better tooling support — the Moose Language Server uses it to validate complete statements while skipping fragments that aren't valid standalone SQL.
import { sql } from "@514labs/moose-lib"; // Use sql.statement for complete SQL queriesconst status = 'active';const limit = 10; const query = sql.statement` SELECT id, name, email FROM ${UserTable} WHERE ${UserTable.columns.status} = ${status} LIMIT ${limit}`; // Use sql.fragment for partial SQL (conditions, expressions, clauses)interface FilterParams { minAge?: number; status?: "active" | "inactive"; searchText?: string;} const buildConditionalQuery = (filters: FilterParams) => { let conditions = []; if (filters.minAge !== undefined) { conditions.push(sql.fragment`age >= ${filters.minAge}`); } if (filters.status) { conditions.push(sql.fragment`status = ${filters.status}`); } if (filters.searchText) { conditions.push(sql.fragment`(name ILIKE ${'%' + filters.searchText + '%'} OR email ILIKE ${'%' + filters.searchText + '%'})`); } let query = sql.statement`SELECT * FROM ${UserTable}`; if (conditions.length > 0) { query = query.append(sql.fragment` WHERE ${sql.join(conditions, "AND")}`); } return query.append(sql.fragment` ORDER BY created_at DESC`);};Migrating from bare sql tag
The bare sql template tag still works but is deprecated. Replace sql\...`withsql.statement`...`for complete queries orsql.fragment`...`for partial SQL. Both produce the sameSql` object — the only difference is metadata that enables better editor tooling.
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.
Computed Columns in Query Results
By default, ClickHouse excludes ALIAS and MATERIALIZED columns from SELECT * results. When you query through MooseClient.query.execute, Moose sets asterisk_include_alias_columns and asterisk_include_materialized_columns per-query so that SELECT * returns all columns including computed ones.
Complex queries with SELECT *
Because these settings apply per-query on execute, any SELECT * within your query will include computed columns. If you have complex SQL where the extra columns could affect column ordering or subquery results, name your columns explicitly in the SELECT clause instead of relying on *.
Common Pitfalls
Common TypeScript Issues
- Column name typos: Use
UserTable.columns.columnNamefor autocomplete - Type mismatches: Ensure your schema types match ClickHouse types
- Missing imports: Import your table definitions before using them
- Template literal syntax: Use backticks
sqlnot 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
orderByFieldsof the table - For common queries, consider creating a materialized view to pre-compute the result set