I wanted to use ClickHouse in Elixir with the same basic ergonomics I expect from an Ecto-backed application: familiar query syntax, migrations, and a great test workflow.

There are already Elixir options for ClickHouse, particularly over HTTP, but none matched the combination I wanted:

• Native TCP communication with ClickHouse
• Ecto integration
• Reversible migrations
• A practical way to express ClickHouse-specific table settings with support for secrets via the environment

The result is clickhouse_adapter_ecto, backed by a separate native TCP driver package, ch_driver.

This is not an argument that native TCP is always the right choice. HTTP is a good fit for many ClickHouse workloads and has a much smaller implementation and maintenance surface. I wanted to work directly with a protocol so we could support streaming results, and see how far an Ecto adapter could reasonably go.

While there are some technical details about building and ecto adapter, it is also a story about I used an LLM to support work. The useful part was not asking Claude to “build a ClickHouse adapter” in one prompt. It was creating a workflow that made the work small enough to inspect, test, and correct.

One shot prompt?

A project like this has too many moving parts for a single prompt:

• A stateful binary protocol
• Packet sequencing that varies by server revision
• Compression and checksums
• DBConnection behaviour
• Ecto’s expectations around schemas, migrations, and testing
• ClickHouse features that do not map neatly to a transactional, relational database

Agents can be very helpful across all of those areas, but only if each has a clear definition of the current task, explicit acceptance criteria, and a way to discover what depends on what.

For that, I used beads: a dependency-aware issue tracker that works well with agents. It is similar in spirit to Linear or Jira, but it encourages work to be broken down much more finely, into chunks an agent can work with.

The key difference was that I was not writing a single broad issue such as “implement the native protocol.” I was creating small, ordered pieces of work with a clear outcome.

Planning the work

After an initial research session, I decided a new library made more sense than extending Plausible’s HTTP-oriented ch package. The implementation would have a native protocol layer, a DBConnection integration, and an Ecto adapter.

The top-level epic looked roughly like this:

Native ClickHouse protocol + Ecto adapter
├── Design block-envelope framing
├── Implement block-envelope encoding and decoding
├── Review the implementation
├── Test the framing module
├── Design the native TCP driver
├── Implement the handshake
├── Implement queries and result decoding
├── Implement DBConnection callbacks
├── Test against a real ClickHouse server
├── Implement Ecto SQL callbacks
├── Implement adapter behaviours
└── Integration-test the Ecto adapter

For risky parts of the codebase, each capability followed the same pattern:

1. Design the behaviour and resolve open questions.
2. Implement against explicit acceptance criteria.
3. Review the resulting code against that design.
4. Test it in isolation and against a real server where appropriate.

That sounds process-heavy, but it meant that the agent's work could be checked and verified along the way, avoiding large chunks of untested / unverified code. It also gave reviews a useful target, not “does this look plausible?” but “does this satisfy the behaviour we agreed on?”

Test the protocol against the real thing

Before building much of the driver, I added a ClickHouse instance through Docker Compose and made integration testing an explicit part of the development process, not something to be tacked on at the end.

The native protocol documentation was useful, but it was not enough by itself. It describes packet structures, but a protocol driver also needs to know exactly when packets are expected, which fields apply to which protocol revision, and what the server is waiting for between messages.

An issue with the handshake made this obvious.

After receiving ServerHello, ClickHouse expects a raw addendum string before the client sends the next packet. Our first implementation omitted it. The result was predictably confusing: every subsequent byte was interpreted at the wrong offset, and the detection of the failure appeared much later than the actual mistake.

We found it by putting a small TCP proxy between clickhouse-client and ClickHouse, capturing the real traffic, then comparing it byte-for-byte with the driver’s output.

That was one of the most useful lessons from the project: for a wire protocol, the running server is the authority. Documentation is necessary, but it is not a substitute for an executable compatibility test.

Compression and a hard to fathom bug

ClickHouse transfers native data in blocks. Compressed blocks use a ClickHouse-specific envelope containing a checksum, a compression method marker, compressed and uncompressed sizes, and the payload.

[16 bytes] CityHash128 checksum (covers everything below)
[1 byte]   compression method marker (0x02 = NONE, 0x82 = LZ4, 0x90 = ZSTD)
[4 bytes]  compressed size, little-endian (includes this 9-byte header)
[4 bytes]  uncompressed size, little-endian
[...]      payload

The compression is not a standard LZ4 frame. It uses raw LZ4 blocks inside ClickHouse’s own envelope, and the checksum uses a specific historical CityHash implementation. That meant a general-purpose LZ4 package was not enough on its own.

We built a small Rust NIF, ch_codec, to handle compression, decompression, and CityHash:

ChDriver.Codec.Native.lz4_compress(data)
ChDriver.Codec.Native.lz4_decompress(data, expected_size)
ChDriver.Codec.Native.cityhash128(data)

One early piece of agentic research confidently stated that ClickHouse used CityHash 1.0.3. That was wrong: the required implementation was CityHash 1.0.2.

The bug stayed hidden because the two versions produced identical results for the small inputs used in early tests. It only appeared later, when compression was enabled for queries returning real result sets:

{:error, :checksum_mismatch}

The fix itself was tiny:

- let value = cityhash_rs::cityhash_103_128(data.as_slice());
+ let value = cityhash_rs::cityhash_102_128(data.as_slice());

Though the fix was small, it was the workflow that exposed the incorrect assumption through real testing.

Where Ecto and ClickHouse do not line up

Ecto carries some assumptions from transactional relational databases that do not apply to ClickHouse.

A ClickHouse primary key is not a uniqueness constraint in the PostgreSQL sense. It is tied to the table’s sort order and sparse primary index. In this adapter, migration columns marked primary_key: true are used to construct the table’s ORDER BY key. This distinction matters because sort-key design has a large effect on ClickHouse query performance and storage layout.

Similarly, ClickHouse does not provide the transaction-and-rollback model that Ecto.Adapters.SQL.Sandbox normally relies on. That meant the usual “wrap every test in a transaction and roll it back” approach was unavailable.

The initial implementation for testing was simple:

• Create tables once per test module.
• Truncate before each test.
• Drop tables at the end.

It worked, but because the tables were shared it prevented tests from running concurrently.

To support async: true, we eventually figured out a way using connection-scoped temporary tables. When a test checks out a connection through DBConnection.Ownership, the adapter creates temporary tables with the same names as the real tables on that connection. Queries from that test resolve to its private, session-scoped tables.

This is clearly not a transaction, but it gives integration tests a useful form of isolation without forcing the whole suite to use async: false.

defmodule MyApp.SomeIntegrationTest do
  use ExUnit.Case, async: true

  import Ecto.Adapters.ClickHouse.ConcurrentTestCase

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

  test "inserts and reads a widget" do
    TestRepo.insert!(%Widget{id: 1, name: "gizmo"})

    assert [%Widget{id: 1}] = TestRepo.all(Widget)
  end
end

Refactoring after the core work

Once the main backlog was complete, I compared the project with Postgrex. That was a useful point to pause, we had enough code for natural module boundaries to become obvious.

The comparison led to additional, more focussed modules, clearer ownership of protocol concerns, and more deliberate use of behaviours such as DBConnection.Query. I would not necessarily have come up with that final structure on day one. It was easier to see the right groupings after the real complexity had emerged.

The agent was useful here, but the review question mattered more than the agent itself:

How is this codebase organised compared with a mature adapter, and where are our boundaries unclear?

That is a much better prompt than “clean up the project.”

What shipped

The work produced 101 commits, 89 beads issues, and two Hex packages:

• ch_driver, the native ClickHouse TCP driver
• clickhouse_adapter_ecto, the Ecto adapter

The adapter supports joins, GROUP BY, HAVING, ClickHouse table-engine migrations, LZ4 wire compression, and streaming results.

There are also helpers for ClickHouse-specific table options. For example, a Kafka engine table can pass SETTINGS through a structured keyword list, with values such as broker addresses read from the environment at migration time rather than committed to source control.

create table(:events_queue, primary_key: false,
  options: Ecto.Adapters.ClickHouse.Migration.table_options(
    engine: "Kafka",
    settings: [
      kafka_broker_list: {:system, "KAFKA_BROKER_LIST"},
      kafka_topic_list: "events",
      kafka_group_name: "events_consumer",
      kafka_format: "JSONEachRow"
    ]
  )
) do
  add :id, :id
  add :payload, :string
end

What I would take from this project

The LLM did not remove the need for engineering judgment. It made progress faster when the work was constrained, observable, and easy to challenge.

The techniques that mattered the most:

• Break uncertain work into small, dependency-aware tasks.
• Define acceptance criteria before implementation.
• Review against those criteria instead of trusting confident output.
• Test protocols against real implementations early and repeatedly.
• Keep agent narration out of production comments and documentation.
• Use mature projects such as Postgrex as architectural references once the code has enough shape to compare.

That is the part I would reuse on the next project. Beads and Claude were useful tools to implement the code, but a more useful outcome was a workflow that made it easier to ensure consistency and self correction.