Embed ClickHouse analytics in your Fastify backend

Overview

This quickstart guide shows how to embed MooseStack into an existing Fastify app so you can define ClickHouse schema in TypeScript and serve type-safe analytics endpoints from Fastify—while keeping Fastify as your only deployed backend.

From this you will be able to:

• Model ClickHouse schema using Moose OLAP objects (TypeScript) alongside your Fastify code
• Integrate type-safe ClickHouse queries (via the Moose OLAP client) into your Fastify API endpoints
• Generate/apply migrations against production ClickHouse when you’re ready to deploy

Guide outline

• Get started: add moose/ as a sibling workspace and configure dependencies + env vars.
• Model table: define a table model (for example, Events) in moose/src/index.ts.
• Start local ClickHouse: run pnpm dev:moose.
• Use in Fastify: define a client + query helper in moose/, then call it from a Fastify route.
• Deploy to production: generate/apply migrations and set production env vars.

Project structure

This guide’s examples mirror our reference project, which you can find in the /examples/fastify-moose directory of the MooseStack GitHub repository:

• The Fastify app is at the repo root (./)
• A sibling moose/ workspace package that contains your ClickHouse models and query helpers

If you’re applying this to an existing repo with a different layout, keep the package name moose the same and adjust only the paths/commands.

Prerequisites

Get started

This section adds a moose/ workspace package next to your Fastify app and configures it for local development.

Confirm your workspace setup (pnpm)

This guide assumes you’re using pnpm workspaces. Your Fastify app package will depend on your local moose workspace package.

In the Fastify starter example, pnpm-workspace.yaml lives at the repo root and includes two packages: the Fastify app (.) and the Moose package (moose).

packages:  - "."  - "moose"

pnpm dlx tiged 514-labs/moose/examples/fastify-moose/moose moose

pnpm install
pnpm -C moose run build

Add your moose workspace as a dependency

Add moose as a workspace dependency in your Fastify app workspace package.json:

{  "dependencies": {    "moose": "workspace:*"  }}

Optional: add convenience scripts at the repo root

These scripts are not required, but they make it easy to run Moose commands from your repo root (the Fastify app root in the starter). In the starter layout, the moose package lives at ./moose.

{  "scripts": {    "dev:fastify": "cross-env NODE_ENV=development node --env-file=.env --watch src/index.ts",    "dev:moose": "pnpm -C moose run dev",    "build:moose": "pnpm -C moose run build",    "moose": "pnpm -C moose run moose"  }}

Configure environment variables (Fastify runtime)

This step is required: the moose package initializes its ClickHouse client from environment variables, so these values must be present when your Fastify server starts.

Create or update your Fastify app's environment variables with the following ClickHouse connection details for your local development environment:

MOOSE_CLIENT_ONLY=true
 
MOOSE_CLICKHOUSE_CONFIG__DB_NAME=local
MOOSE_CLICKHOUSE_CONFIG__HOST=localhost
MOOSE_CLICKHOUSE_CONFIG__PORT=18123
MOOSE_CLICKHOUSE_CONFIG__USER=panda
MOOSE_CLICKHOUSE_CONFIG__PASSWORD=pandapass
MOOSE_CLICKHOUSE_CONFIG__USE_SSL=false

Update your .gitignore to exclude MooseStack generated files:

# MooseStack generated files# ignore your moose workspace build output/moose/dist.moose/.ts-node/

Model table

In this step you’ll define your first ClickHouse model as an OlapTable object.

The starter moose package you copied into your repo already includes an example model at moose/src/index.ts:

import { OlapTable } from "@514labs/moose-lib"; export interface EventModel {  id: string;  amount: number;  event_time: Date;  status: 'completed' | 'active' | 'inactive';} export const Events = new OlapTable<EventModel>("events", {  orderByFields: ["event_time"],});

Start local ClickHouse

Start the Moose Runtime in dev mode. This brings up a local ClickHouse instance (via Docker) and hot-reloads schema changes to it whenever you edit your models.

Leave this running in its own terminal for the rest of the guide:

# run this from your repo root
pnpm dev:moose

The provided moose workspace is configured to automatically rebuild the package for use inside your Fastify app whenever you make changes to your models.

[http_server_config]on_reload_complete_script = "pnpm build"

See Dev Environment Configuration for more details.

1) Set up a shared ClickHouse client in moose/

First, create a shared ClickHouse client initializer inside your moose package. This keeps connection logic in one place and lets Fastify handlers call simple query helpers.

import { getMooseClients, Sql, QueryClient } from "@514labs/moose-lib"; async function getClickhouseClient(): Promise<QueryClient> {  const { client } = await getMooseClients({    host: process.env.MOOSE_CLICKHOUSE_CONFIG__HOST ?? "localhost",    port: process.env.MOOSE_CLICKHOUSE_CONFIG__PORT ?? "18123",    username: process.env.MOOSE_CLICKHOUSE_CONFIG__USER ?? "panda",    password: process.env.MOOSE_CLICKHOUSE_CONFIG__PASSWORD ?? "pandapass",    database: process.env.MOOSE_CLICKHOUSE_CONFIG__DB_NAME ?? "local",    useSSL:      (process.env.MOOSE_CLICKHOUSE_CONFIG__USE_SSL ?? "false") === "true",  });   return client.query;} export async function executeQuery<T>(query: Sql): Promise<T[]> {  const queryClient = await getClickhouseClient();  const result = await queryClient.execute(query);  return result.json();}

2) Define a query helper in moose/src/index.ts

Next, define a query helper that uses the shared client and export it from "moose". The example defines a very basic query for you to use as a starting point:

import { OlapTable, sql } from "@514labs/moose-lib";import { executeQuery } from "./client"; export interface EventModel {  id: string;  amount: number;  event_time: Date;  status: 'completed' | 'active' | 'inactive';} export const Events = new OlapTable<EventModel>("events", {  orderByFields: ["event_time"],}); export async function getEvents(limit: number = 10): Promise<EventModel[]> {  return await executeQuery<EventModel>(    sql`SELECT * FROM ${Events} ORDER BY ${Events.columns.event_time} DESC LIMIT ${limit}`,  ); }

For more query patterns and details on using the sql template tag, see Reading Data.

3) Import and use the helper in Fastify

Now your Fastify app can import and use the helper from "moose" in any of your route handlers, like this:

import type { FastifyInstance, FastifyRequest, FastifyReply } from "fastify"; import { getEvents } from "moose"; type RecentEventsQuery = {  limit?: string;}; export default async function clickhouseController(fastify: FastifyInstance) {  // GET /api/v1/clickhouse/recent?limit=10  fastify.get(    "/recent",    async function (      request: FastifyRequest<{ Querystring: RecentEventsQuery }>,      reply: FastifyReply,    ) {      const limit = Math.min(        100,        Math.max(1, Number(request.query.limit ?? 10)),      );       const rows = await getEvents(limit);      reply.send({ rows });    },  );}

Deploy to production

There’s no separate production Moose Runtime to deploy. You just need to:

1. Apply your schema to production ClickHouse
2. Configure your production environment with production credentials

Enable planned migrations

Make sure ddl_plan = true is set in [features] in moose/moose.config.toml:

[features]streaming_engine = falseddl_plan = true

Generate a migration plan

For detailed information about migration workflows, lifecycle management, and plan formats, see the Migrations documentation.

pnpm moose generate migration \
  --clickhouse-url "clickhouse://user:password@your-prod-host:8443/db?secure=true" \
  --save

This creates files in migrations/ including plan.yaml.

Apply the migration

pnpm moose migrate \
  --clickhouse-url "clickhouse://user:password@your-prod-host:8443/db?secure=true"

Configure production environment

In production, set these environment variables in your deployment platform. Make sure to use your production ClickHouse URL + credentials.

MOOSE_CLIENT_ONLY=true
MOOSE_CLICKHOUSE_CONFIG__DB_NAME=production_db
MOOSE_CLICKHOUSE_CONFIG__HOST=your-clickhouse-host.example.com
MOOSE_CLICKHOUSE_CONFIG__PORT=8443
MOOSE_CLICKHOUSE_CONFIG__USER=prod_user
MOOSE_CLICKHOUSE_CONFIG__PASSWORD=prod_password
MOOSE_CLICKHOUSE_CONFIG__USE_SSL=true

Troubleshooting

If you see import errors for import ... from "moose", confirm:

• Your root pnpm-workspace.yaml includes both your Fastify app and the moose workspace
• You ran pnpm install so workspace:* links are created
• You ran pnpm -C moose run build (or your equivalent script) to generate moose/dist/

Next steps

• OlapTable Reference — Primary keys, engines, and configuration
• Read Data — Query patterns and the Moose client
• Migrations — Schema versioning and migration strategies
• Schema Optimization — Ordering keys and partitioning