Add support for ssl ca-cert for clickhouse

[anishjain94]

I am using goose to manage clickhouse migrations using cli. I was not able to find any example which uses ssl in cli. can you point me out to an example for the same.

[rudebono]

Hello, @anishjain94
Nice to meet you.
I am also experiencing some issues while using Goose to migrate ClickHouse, just like you.

I entered the command as follows:
goose clickhouse “tcp://myusername:[email protected]:9440” status

But I encountered the following error:
2024/07/19 14:26:30 goose run: failed to ensure DB version: read: EOF

There are no issues when testing on localhost,
but problems arise when using ClickHouse Cloud.
It seems to be related to SSL.

For reference, my ClickHouse Cloud version is Version 24.5.

[mfridman]

I wonder if there's a free trial/plan for clickhouse cloud, something to test against.

[rudebono]

Hello @mfridman,
Thank you for responding to the issue.

By default, ClickHouse offers a 30-day trial. Once the trial ends, you will need to use the “pay as you go” plan. However, since ClickHouse Cloud can be set to automatically shut down instances when not in use, so you won’t incur high costs.

In my personal opinion, since pressly/goose is introduced in the ClickHouse official documentation, if you contact the ClickHouse support team, you might be able to get long-term trial days.

If this method is not possible, you will need to pay for it directly. But I can support the ClickHouse costs for you through GitHub Sponsors because I want to use pressly/goose in production. I find other options too inconvenient to use.

Thank you.

[rudebono]

In ClickHouse, there is an option to support the MySQL protocol, so I tried running it as shown below.
Unfortunately, I was unable to connect.

goose mysql "myusername:mypassword@tcp(my.clickhouse.cloud:3306)/default" status

[mysql] 2024/07/19 17:45:35 connection.go:49: unexpected EOF
[mysql] 2024/07/19 17:45:36 connection.go:49: unexpected EOF
2024/07/19 17:45:36 goose run: failed to ensure DB version: invalid connection

I will research whether this can be resolved using a MySQL Proxy.

[rudebono]

Hello, @anishjain94, @mfridman,

I have some good news to share.
As a developer who primarily uses Elixir, I struggled a bit to find a solution because I am not very familiar with Go.
However, I have found a solution and would like to share it with you all :)

Firstly, according to cmd/goose/driver_clickhouse.go,
the pressly/goose package uses ClickHouse’s official Golang SQL database client.
If you carefully read the README.md in the ClickHouse's repository,
you will find instructions on how to set SSL/TLS parameters.
By referring to those instructions, I have confirmed that pressly/goose works properly with ClickHouse Cloud.

Example:

goose clickhouse "clickhouse://<username>:<password>@<subdomain>.clickhouse.cloud:9440/<databasename>?secure=true&skip_verify=false" status

2024/07/21 00:01:20     Applied At                  Migration
2024/07/21 00:01:20     =======================================
2024/07/21 00:01:20     Pending                  -- 20240719041332_create_user_grafana.sql
2024/07/21 00:01:20     Pending                  -- 20240720121627_create_user_otel.sql
...

I would like to express my gratitude to the contributors of pressly/goose.