ClickHouse connection strings

[olli2017]

Use case

Now you can connect to ClickHouse by specifiying separately host, port, username, password and other parameters.
clickhouse-client --host=... --port=... --user=... --password=...

It is already possible to specify the connection string as a URI in many databases. Example from PostgreSQL
postgresql://localhost:5433/my_database

It is simple, convenient and frequently used. You can just copy the link and connect.

Describe the solution you'd like

I want to achieve next general form of the connection URL:

clickhouse://[userspec@][hostspec][/dbname][?paramspec]
where userspec is:
user[:password]
where hostspec is:
[host][:port][,...]
and paramspec is:
name=value[&...]

Ex: clickhouse://login:password@host:port/database
There are many params for connecting to ClickHouse. Therefore last [?paramspec] is necessary.
Credentials will be verified after connection string accepted if it is not specified.

I propose this solution because it is convenient and general way. It is similar on example from PostgreSQL(details below).

Describe alternatives you've considered

Other implementations are similar:

1. PostgreSql
   postgresql://[userspec@][hostspec][/dbname][?paramspec]

where userspec is:
user[:password]
and hostspec is:
[host][:port][,...]
and paramspec is:
name=value[&...]

The following examples illustrate valid URI syntax:

postgresql://
postgresql://localhost
postgresql://localhost:5433
postgresql://localhost/mydb
postgresql://user@localhost
postgresql://user:secret@localhost
postgresql://other@localhost/otherdb?connect_timeout=10&application_name=myapp
postgresql://host1:123,host2:456/somedb?target_session_attrs=any&application_name=myapp

https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING

2. JDBC driver(SQL Server)
   jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

where:
- jdbc:sqlserver:// (Required) is known as the subprotocol and is constant.
- serverName (Optional) is the address of the server to connect to. This address can be a DNS or IP address, or it can be localhost or 127.0.0.1 for the local computer. If not specified in the connection URL, the server name must be specified in the properties collection.
- instanceName (Optional) is the instance to connect to on serverName. If not specified, a connection to the default instance is made.
- portNumber (Optional) is the port to connect to on serverName. The default is 1433. If you're using the default, you don't have to specify the port, nor its preceding ':', in the URL.
- property (Optional) is one or more option connection properties. For more information, see Setting the connection properties. Any property from the list can be specified. Properties can only be delimited by using the semicolon (';'), and they can't be duplicated.

Examples:

jdbc:sqlserver://localhost;user=MyUserName;password=*****;
jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks;integratedSecurity=true;

https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-ver15

3. ODBC driver(SQL Server 2000)
   Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
   https://www.connectionstrings.com/microsoft-sql-server-odbc-driver/

4. JDBC Oracle

Connect to Oracle Database SID. In some older versions of the Oracle database, the database is defined as a SID

jdbc:oracle:thin:[<user>/<password>]@<host>[:<port>]:<SID>
Ex: jdbc:oracle:thin:@myoracle.db.server:1521:my_sid

Connect to Oracle Database Service Name

jdbc:oracle:thin:[<user>/<password>]@//<host>[:<port>]/<service>
Ex: jdbc:oracle:thin:@//myoracle.db.server:1521/my_servicename

https://www.baeldung.com/java-jdbc-url-format

5. JDBC MySQL
   protocol//[hosts][/database][?properties]

jdbc:mysql://mysql.db.server:3306/my_database?useSSL=false&serverTimezone=UTC
protocol – jdbc:mysql:  // specific param for mysql. There are a lot of values.
host – mysql.db.server:3306
database – my_database
properties – useSSL=false&serverTimezone=UTC

https://www.baeldung.com/java-jdbc-url-format

Another examples for JDBC provided in screen:

https://www.tutorialspoint.com/jdbc/jdbc-db-connections.htm

[filimonov]

in golang
https://github.com/ClickHouse/clickhouse-go#dsn
https://github.com/golang-migrate/migrate/tree/master/database/clickhouse

in python sqlalchemy (supports both http and native)
https://pypi.org/project/clickhouse-sqlalchemy/

in python driver
https://clickhouse-driver.readthedocs.io/en/latest/misc.html

[statico]

Seconded. For now I'm going to settle for setting an env var like CLICKHOUSE_CONFIG={host:"...",username:"...",password:"..."} and parsing that before creating the client.

[Demilivor]

@rschu1ze Please assign this task to me. I just started working on it.