Principle:Helicone Helicone Database Migrations
| Knowledge Sources | |
|---|---|
| Domains | Local Development, Database |
| Last Updated | 2026-02-14 00:00 GMT |
Overview
Database migrations are the ordered, idempotent schema changes applied to ClickHouse (and PostgreSQL via Flyway) to keep the local database schema in sync with the codebase.
Description
Helicone uses two database systems that each require migration management:
- PostgreSQL: Migrations are managed by Flyway, executed automatically by the
migrationsDocker Compose service. Migration SQL files live insupabase/. - ClickHouse: Migrations are managed by a custom Python CLI tool (
clickhouse/ch_hcone.py) that tracks applied migrations in ahelicone_migrationstable within ClickHouse itself. Schema files are stored inclickhouse/migrations/and are sorted by a numeric suffix (schema_1,schema_2, etc.).
The ClickHouse migration system works as follows:
- On startup, it creates the
helicone_migrationstracking table if it does not exist. - It queries all previously applied migration names in a single batch.
- It compares the sorted list of migration files against the applied set.
- For each pending migration, it reads the SQL file, splits it into individual statements, and executes each statement via HTTP calls to the ClickHouse server.
- Successfully applied migrations are recorded in the tracking table.
The migration tool supports retries, confirmation prompts, and detailed error reporting with ClickHouse-specific error detection.
Usage
Run ClickHouse migrations after infrastructure services are started and before launching application services. PostgreSQL migrations run automatically via the Docker Compose migrations service, but ClickHouse migrations may need to be run manually when developing outside the Docker Compose workflow.
Theoretical Basis
Schema migration tools ensure that database structure evolves predictably across all environments. The idempotency guarantee (each migration is applied at most once, tracked by name) prevents duplicate schema changes. The ClickHouse migration tool uses a simple MergeTree table ordered by migration_name as its tracking mechanism, which is lightweight and does not require external state.
The HTTP-based execution model (sending SQL via curl to ClickHouse's HTTP interface on port 8123/18123) aligns with ClickHouse's native HTTP API, avoiding the need for a dedicated ClickHouse client library in Python.