Clickhouse support

[tmcw]

We're strongly considering using Clickhouse as part of our architecture. Clickhouse supports a syntax that is "identical to ANSI SQL" in many cases 😆 https://clickhouse.com/docs/en/sql-reference

It'd be awesome for Drizzle to support a Clickhouse adapter. I see that two folks have asked about this in discord, but didn't see any issues or discussions.

[205g0]

We're strongly considering using Clickhouse as part of our architecture.

Why? Seriously interested.

I couldn't find anything exciting, maybe I've missed something though.

• Clickhouse seems not to have solid edge drivers, I just found only one with three stars
• Clickhouse doesn't have a good multi-region replication story, they can do this but since it's consistent over replicas (so they're not just read-replicas), writes will have huge latencies in a multi-region setup and you need to think yourself about an extra caching layer
• Too expensive in general, and no low tiers

[tmcw]

It definitely doesn't have good edge drivers yet, but, y'know - that could be drizzle! Their storage on disk is compressed, so it's probably cheaper than it seems - and we'd be running the open source code on our own servers anyway. We're not multi-region yet - this is a three-person startup, not a corporation - and anyway, it's not going to be the main transactional database anyway, it's for a specific, big, mostly-just-writes table.

[205g0]

it's for a specific, big, mostly-just-writes table.

then, just write to them with raw sql, drizzle would be nice but you really need it once you have a complex data model with relations

[nathanclevenger]

It definitely doesn't have good edge drivers yet, but, y'know - that could be drizzle! Their storage on disk is compressed, so it's probably cheaper than it seems - and we'd be running the open source code on our own servers anyway. We're not multi-region yet - this is a three-person startup, not a corporation - and anyway, it's not going to be the main transactional database anyway, it's for a specific, big, mostly-just-writes table.

Not sure when this was written, but Clickhouse natively supports HTTPS and streaming responses in both self-hosted open source and in the cloud managed offering, so it is arguably more edge-friendly than any other database.

Additionally, the https://www.npmjs.com/package/@clickhouse/client-web package works great in a browser, on Cloudflare Workers, anywhere ...

[nathanclevenger]

• Clickhouse doesn't have a good multi-region replication story, they can do this but since it's consistent over replicas (so they're not just read-replicas), writes will have huge latencies in a multi-region setup and you need to think yourself about an extra caching layer

FYI - as part of the HTTPS interface, you can do POST or GET, and GETs are super-easy to cache ... we use Cloudflare and Workers to globally cache queries from our 20TB Clickhouse cluster which is blazing fast ...

• Too expensive in general, and no low tiers

The managed cloud offering isn't super cheap, but it's not expensive at all to self-host on fly.io, where machines can auto-sleep if only occasionally used.

[alisalahio]

We'd love this as well!

[LilaRest]

Supporting this one also!

[NicholasKao1029]

This would be amazing

[maxdeichmann]

I would love this.

[waynehamadi]

Hmm I think I know why haha (hey Max!)

Very good for logs use cases. Completes postgres very well.

[yekta]

Is this planned for any time soon? Current ClickHouse tooling isn’t exactly great. Drizzle would make a solid difference.

[nathanclevenger]

I would give this a huge upvote as well! Schema management in Clickhouse is challenging ...

One other possibility would be if drizzle could tap into the Postgres and/or MySQL interface that Clickhouse exposes:
https://clickhouse.com/docs/interfaces/postgresql
https://clickhouse.com/docs/interfaces/mysql

I've tried to get these to work, and it may be possible and I'm just doing something wrong ... or it may also be easier to create a Clickhouse-flavor via one of these other wire protocols.

[danpiths]

hey, do we have any idea when this is shipping? it'd be really really helpful while working with clickhouse. raw SQL is killing me, no types and wrong types are the worst