Ecto.Adapters.ClickHouse

An Ecto.Adapters.SQL adapter for ClickHouse, built on ch_driver, a DBConnection driver speaking ClickHouse's native TCP protocol (not HTTP).

Documentation: https://hexdocs.pm/clickhouse_adapter_ecto/

Installation

def deps do
  [
    {:clickhouse_adapter_ecto, "~> 1.0"}
  ]
end

Usage

defmodule MyApp.Repo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.ClickHouse
end

# config/config.exs
config :my_app, MyApp.Repo,
  hostname: "localhost",
  port: 9000,
  database: "my_app_dev",
  username: "default",
  password: ""

# So the mix ecto.* tasks know which repo to act on.
config :my_app, ecto_repos: [MyApp.Repo]

Then start the repo under your application's supervision tree:

# lib/my_app/application.ex
defmodule MyApp.Application do
  use Application

  @impl true
  def start(_type, _args) do
    children = [
      MyApp.Repo
    ]

    Supervisor.start_link(children, strategy: :one_for_one, name: MyApp.Supervisor)
  end
end

This is the standard Ecto.Repo setup, nothing ClickHouse-specific, but it's the step that actually starts the connection pool. Without it every query fails with could not lookup Ecto repo MyApp.Repo because it was not started or it does not exist.

A migration:

defmodule MyApp.Repo.Migrations.CreateEvents do
  use Ecto.Migration

  def change do
    create table(:events, primary_key: false, options: "ENGINE = MergeTree ORDER BY id") do
      add :id, :uuid, primary_key: true
      add :name, :string
      add :occurred_at, :utc_datetime
    end
  end
end

A schema and a query:

defmodule MyApp.Event do
  use Ecto.Schema

  @primary_key false
  schema "events" do
    field :id, :string
    field :name, :string
    field :occurred_at, :utc_datetime
  end
end

MyApp.Repo.insert!(%MyApp.Event{
  id: Ecto.UUID.generate(),
  name: "signup",
  occurred_at: DateTime.utc_now() |> DateTime.truncate(:second)
})

import Ecto.Query
MyApp.Repo.all(from e in MyApp.Event, where: e.name == "signup", order_by: e.occurred_at)

What's supported

For the exhaustive breakdown (and why), see the moduledocs of Ecto.Adapters.ClickHouse, Ecto.Adapters.ClickHouse.Connection, and Ecto.Adapters.ClickHouse.DDL.

ORDER BY/primary keys are not what they are in Postgres/MySQL

ClickHouse has no auto-increment and no unique-index enforcement at insert time. ORDER BY (MergeTree's sorting/indexing key) exists to make scans skip granules efficiently -- it is not a uniqueness constraint, and duplicate values are accepted silently.

In practice: use primary_key: false with an explicit, application-supplied id (Ecto.UUID.generate/0, a natural key, System.unique_integer/1, ...), as shown above, plus an explicit options: ENGINE/ORDER BY once the table matters for performance. See the "ORDER BY/PRIMARY KEY is not a Postgres-style primary key" section of Ecto.Adapters.ClickHouse.DDL's moduledoc for the full write-up.

Replicated clusters

mix ecto.create creates an Atomic database on one server by default. Set database_engine: "Replicated" and cluster: "{cluster}" on the repo to create it with ON CLUSTER ... ENGINE = Replicated instead. Tables still need Replicated*MergeTree engines to replicate their data, so also set default_table_engine: "ReplicatedMergeTree" for schema_migrations. Migrations take no lock, so run them from one place, and need every replica up. See the "Replicated clusters" section of Ecto.Adapters.ClickHouse.

Testing

Ecto.Adapters.SQL.Sandbox doesn't work here -- this adapter has no Ecto.Adapter.Transaction support (ChDriver.DBConnection's handle_begin/2/handle_commit/2/handle_rollback/2 are deliberate unimplemented stubs), and ClickHouse's own experimental transactions require a Keeper/ZooKeeper coordination layer.

Instead, create each table once per test module, TRUNCATE it before every test, and DROP it on exit. Ecto.Adapters.ClickHouse.TestCase (test/support/test_case.ex) wraps this pattern:

defmodule MyApp.SomeIntegrationTest do
  use ExUnit.Case, async: false
  import Ecto.Adapters.ClickHouse.TestCase

  defmodule TestRepo do
    use Ecto.Repo, otp_app: :my_app, adapter: Ecto.Adapters.ClickHouse
  end

  setup_clickhouse_tables TestRepo,
    widgets: "CREATE TABLE widgets (id UInt64, name String) ENGINE = MergeTree ORDER BY id"

  test "..." do
    TestRepo.insert!(%Widget{id: 1, name: "gizmo"})
    assert [%Widget{id: 1}] = TestRepo.all(Widget)
  end
end

Tests sharing a table this way can't run async: true against each other. For that, see Ecto.Adapters.ClickHouse.ConcurrentTestCase (per-connection temp-table shadowing) or a per-test-module database via storage_up/1/storage_down/1 -- both documented in their own moduledocs.

Running this repo's suite

docker compose up -d --wait   # ClickHouse + Kafka
mix test
docker compose -f docker-compose.cluster.yml up -d --wait
mix test --only cluster

The compose files publish on non-standard host ports so they can run alongside other local ClickHouse or Kafka containers. Set the variable before both docker compose up and mix test to move a port:

Repo layout

This repo is split into two Mix projects, layered bottom to top:

ch_driver  <-  clickhouse_adapter_ecto (this project)

• ch_driver -- the native-protocol DBConnection driver this adapter is built on, usable standalone. Includes ChDriver.Codec (a Rust NIF for LZ4 compression and CityHash checksums) backing the driver's opt-in :compression option.
• clickhouse_adapter_ecto (this project) -- the Ecto integration layer on top of ch_driver.

License

MIT