activerecord-clickhouse-adapter

A fully featured Active Record adapter for ClickHouse

• Native types on every read — Decimal, DateTime64, Enum, Array, Map, Tuple, IPv4/6, UUID, and more
• Server-side bind parameters, never string interpolation
• MergeTree-aware migrations, schema.rb, and structure.sql
• OLAP query surface: FINAL, PREWHERE, SAMPLE, LIMIT BY, time bucketing, approximate aggregates
• Real instrumentation: rows read, bytes read, and server elapsed time on every query
• Fast wire: RowBinary reads and chunked streaming inserts
• Multi-replica failover and server-enforced read-only connections

Tested against a live ClickHouse server only — no mocked responses, ever.

Status: pre-1.0, under active development. See PLAN.md for architecture and roadmap.

Installation

Add this line to your application's Gemfile:

gem "activerecord-clickhouse-adapter"

Requires Active Record 8.1+, Ruby 3.2+, and ClickHouse 25.8+ (each LTS from 25.8 through latest runs in CI).

Getting Started

For a runnable end-to-end tour — fact table, streaming ingestion, dictionary lookups, the aggregate-state pipeline, partitions — see examples/olap_on_rails.rb.

Add a ClickHouse database to config/database.yml:

production:
  primary:
    # ... your existing database ...
  clickhouse:
    adapter: clickhouse
    host: localhost
    port: 8123
    database: analytics_production
    username: rails
    password: <%= ENV["CLICKHOUSE_PASSWORD"] %>
    migrations_paths: db/migrate_clickhouse

Create an abstract base class on its own pool:

class AnalyticsRecord < ActiveRecord::Base
  self.abstract_class = true

  connects_to database: { writing: :clickhouse, reading: :clickhouse }
end

Define models as usual:

class Event < AnalyticsRecord
  include ActiveRecord::ConnectionAdapters::ClickHouse::Querying
end

The Querying concern is optional. It adds the ClickHouse relation methods below.

Migrations

Tables default to id: false — ClickHouse has no autoincrement. The sorting key (order:) is required:

create_table :events, order: "(device_id, ts)", partition: "toDate(ts)", ttl: "toDateTime(ts) + INTERVAL 30 DAY" do |t|
  t.integer  :device_id, limit: 8
  t.datetime :ts, precision: 3, default: -> { "now64(3)" }
  t.string   :event_type, low_cardinality: true, default: ""
  t.integer  :duration_ms, null: true
end

Columns are non-nullable by default, matching ClickHouse. Use null: true for Nullable(...).

t.datetime defaults to precision 6 (microseconds, Rails' convention); pass precision: nil for the second-precision base DateTime. t.binary/t.blob map to String — ClickHouse strings are arbitrary byte sequences.

Rails-style id tables also work — id: :bigint (plain Int64, no autoincrement) or id: :uuid creates the pk column as its own sorting key, ids arrive client-generated, and models auto-detect the primary key:

create_table :accounts, id: :bigint do |t|
  t.string :name, default: ""
end

The full alter surface works on existing tables — rename_column, change_column, change_column_null (with the Rails backfill default), change_column_default, change_column_comment, change_table_comment, and add_index/remove_index for data-skipping indexes. create_join_table defaults its sorting key to the two reference columns.

ClickHouse-specific column options:

t.string  :status, low_cardinality: true
t.integer :bytes, codec: "Delta, ZSTD"
t.date    :day, materialized: "toDate(ts)"
t.string  :upper_status, alias: "upper(status)"

Engines, projections, and materialized views:

create_table :daily_counts, engine: "SummingMergeTree", order: "day"

create_materialized_view :events_to_daily, to: "daily_counts", as: "SELECT toDate(ts) AS day, count() AS n FROM events GROUP BY day"

add_projection :events, :by_type, order: "event_type"
materialize_projection :events, :by_type
optimize_table :events

Partition lifecycle:

partitions :events            # => ["20260701", "20260702", ...]
detach_partition :events, "20260701"
attach_partition :events, "20260701"
drop_partition :events, "20260701"

Dictionaries replace star-schema dimension JOINs with in-memory lookups. Columns are inferred from the source table, and the adapter's credentials are injected into the SOURCE clause:

create_dictionary :device_names, source: "devices", primary_key: :id
create_dictionary :device_names, source: "devices", primary_key: :id, layout: :hashed, lifetime: 60..300
create_dictionary :device_names, source: "devices", database: "dimensions", primary_key: :id
reload_dictionary :device_names
drop_dictionary :device_names, if_exists: true

Dictionaries round-trip through schema.rb (as create_dictionary calls that re-infer columns and re-inject credentials on load) and structure.sql (credentials are masked in the file and swapped back in by db:schema:load).

Set cluster: in database.yml to stamp schema DDL with ON CLUSTER, sending it through the distributed DDL queue:

production:
  adapter: clickhouse
  cluster: my_cluster

Both schema.rb and structure.sql round-trip engines, sorting keys, partitions, TTLs, codecs, settings, and projections (dumped as add_projection statements).

Querying

Standard Active Record works as expected:

Event.where(device_id: 42).order(:ts).limit(10)
Event.group(:event_type).count
Event.where("duration_ms > ?", 100).average(:duration_ms)

ClickHouse dialect methods (via the Querying concern):

Event.final                          # FROM events FINAL
Event.sample(0.1)                    # SAMPLE 0.1
Event.prewhere(device_id: 42)        # PREWHERE, before WHERE
Event.limit_by(1, :device_id)        # LIMIT 1 BY device_id
Event.settings(max_threads: 8)       # SETTINGS max_threads = 8
Event.array_join(:tags, as: :tag)    # one row per array element

Time series:

Event.group_by_period(:hour, :ts).count            # chronological buckets
Event.group_by_period(:day, :ts).fill.count        # gap-filled with WITH FILL
Event.group(:device_id).rollup.count               # totals row, keyed nil

Window functions project alongside the row:

Event.window(:row_number, as: :position, partition_by: :device_id, order_by: :ts)
Event.window(:sum, :duration_ms, as: :running_total, order_by: :ts)
Event.window(:lag, :battery, as: :previous, partition_by: :device_id, order_by: :ts,
             frame: "ROWS BETWEEN 1 PRECEDING AND CURRENT ROW")

Dictionary lookups project alongside the row:

Event.dict_get(:device_names, :name, key: :device_id)                     # ... AS name
Event.dict_get(:device_names, :name, key: :device_id, as: :device_name)
Event.dict_get(:device_names, :name, key: :device_id, default: "unknown") # dictGetOrDefault

Approximate and positional aggregates:

Event.uniq_count(:device_id)                 # uniq() — fast, approximate
Event.uniq_count(:device_id, exact: true)    # uniqExact()
Event.quantile(0.95, :duration_ms)           # p95
Event.top_k(10, :event_type)                 # most frequent values
Event.arg_max(:event_type, :ts)              # value at max ts
Event.estimated_count                        # O(1) row estimate from metadata

All aggregates accept if: for conditional aggregation in one scan:

Event.quantile(0.95, :duration_ms, if: { event_type: "render" })

AggregateFunction state columns merge with merge: true:

DailyRollup.group(:day).uniq_count(:visitors_state, merge: true)

Writing Data

Single-row writes work, but ClickHouse wants batches:

Event.insert_all!(rows)     # one INSERT statement
Event.insert_all(rows)      # same — with no unique constraints, nothing can conflict

Stream any Enumerable without materializing it:

Event.insert_stream(rows)   # one chunked HTTP request, lazy enumerators welcome

Updates and deletes become mutations (ALTER TABLE ... UPDATE / DELETE):

Event.where(device_id: 42).update_all(event_type: "gone")
Event.where(device_id: 42).delete_all

Sorting-key columns cannot be updated. upsert_all raises — use a ReplacingMergeTree or SummingMergeTree engine instead.

For high-frequency small inserts, enable server-side batching:

clickhouse:
  adapter: clickhouse
  async_insert: true

Instrumentation

Every query's sql.active_record notification carries server statistics:

ActiveSupport::Notifications.subscribe("sql.active_record") do |event|
  stats = event.payload[:clickhouse]
  # => { query_id:, read_rows:, read_bytes:, written_rows:, elapsed_ns: }
end

The standard payload[:affected_rows] reports the server's written rows on inserts.

explain supports ClickHouse variants:

Event.where(device_id: 42).explain             # EXPLAIN
Event.where(device_id: 42).explain(:pipeline)  # EXPLAIN PIPELINE
Event.where(device_id: 42).explain(:indexes)   # EXPLAIN indexes = 1

Connection Options

clickhouse:
  adapter: clickhouse
  host: localhost
  port: 8123
  database: analytics_production
  username: rails
  password: secret
  hosts:                   # interchangeable replicas ("host" or "host:port");
    - ch-1.internal        # connections round-robin start positions and fail over
    - ch-2.internal:8124   # to the next host on connect-phase errors
  failover_cooldown: 30    # seconds new connections skip an endpoint that refused
  read_only: true          # server-enforced reads only (readonly=2 on every request)
  ssl: true                # HTTPS to the server
  ssl_verify: false        # escape hatch for self-signed certificates (default: verify)
  connect_timeout: 5
  read_timeout: 60
  write_timeout: 60
  compression: true        # gzip responses (default: on)
  join_use_nulls: 1        # SQL-standard outer-join NULLs (default: on)
  mutations_sync: 1        # block until mutations apply (default: async)
  async_insert: false      # server-side insert batching
  select_format: binary    # RowBinary reads; use `json` to force the JSON wire

Semantics Worth Knowing

• No transactions. ClickHouse has none; transaction blocks run their contents without BEGIN/COMMIT and cannot roll back.
• Primary keys are client-generated and auto-detected. Tables with a single-column integer or UUID sorting key report it as the Active Record primary key — models auto-detect it, and ids are assigned client-side before INSERT (Snowflake-style / UUIDv7). Composite, expression, and other sorting keys report none (ClickHouse PRIMARY KEY is an index prefix, not a uniqueness guarantee); declare self.primary_key explicitly when such a key is unique by design, e.g. a ReplacingMergeTree slug.
• Mutation counts are best-effort. update_all/delete_all return a pre-mutation SELECT count() — ClickHouse reports no affected-row counts.
• Eventual merges. ReplacingMergeTree deduplicates at merge time; read with .final when you need collapsed rows.
• Failover never replays a request. With hosts:, only connect-phase failures (refused, unreachable, open timeout) move to the next replica — those cannot have executed anything. A mid-flight failure (read timeout, reset) raises, because the statement may already have run.
• Read-only is server-enforced. read_only: true stamps readonly=2 on every request; the server's refusal (code 164) raises ActiveRecord::ReadOnlyError — the same class Rails' while_preventing_writes uses — including for server-configured readonly users. Grant refusals (code 497) raise ClickHouse::AccessDenied.
• matches renders ILIKE. ClickHouse LIKE is case-sensitive (unlike MySQL), so Arel's case-insensitive matches/does_not_match use native ILIKE.
• No row locks. Reads are isolated snapshots of parts, so FOR UPDATE renders as nothing and shared lock/with_lock code runs instead of dying. Optimistic locking via lock_version works end-to-end.

Migrating from clickhouse-activerecord

This gem registers the same "clickhouse" adapter name as clickhouse-activerecord and accepts its database.yml shape — swap the gem and the config carries over. The two gems cannot be loaded at once.

Schema-version bookkeeping does not carry over. The incumbent records applied versions in a (version, active, ver) ReplacingMergeTree with active = 0 tombstones; this adapter uses Rails' plain schema_migrations table and does not read that shape, so a sink migrated by the incumbent reports zero applied versions.

Never re-run migrations over a live sink to fix that — replaying DDL over final-state tables can corrupt data (enum narrowing, for one). Adopt the version state once instead:

versions = connection.select_values("SELECT version FROM schema_migrations FINAL WHERE active = 1")
connection.execute("RENAME TABLE schema_migrations TO schema_migrations_legacy")
pool.schema_migration.create_table
versions.each { |v| pool.schema_migration.create_version(v) }

Pending migrations run normally afterwards. To roll back the adoption, drop the new schema_migrations table and rename the legacy one back.

Development

Everything runs against a real ClickHouse server:

docker compose up -d --wait
bundle install
bundle exec rspec
bundle exec rubocop

Run against Rails main:

RAILS_SOURCE=edge bundle install
RAILS_SOURCE=edge bundle exec rspec

The suite includes a Rails compatibility harness that runs vendored upstream Active Record test suites (~5,600 tests — 5,558 runs, 447 skips, each skip documenting the dialect truth behind it) against the adapter. See spec/rails_compat/.

History

View the changelog.

Contributing

Everyone is encouraged to help improve this project. Here are a few ways you can help:

• Report bugs
• Fix bugs and submit pull requests
• Write, clarify, or fix documentation
• Suggest or add new features