Support HTTPS URLs when specifying a SqlAlchemy URI

[SStorm]

There are two ways to specify a server to connect when using SqlAlchemy. The official one recommended by the crate docs is to use the connect_args like this:

engine = sa.create_engine(
     'crate://',
     connect_args={
         'servers': ['198.51.100.1:4200', '198.51.100.2:4200'],
         'verify_ssl_cert': True,
         'ca_cert': '<PATH_TO_CA_CERT>',
     }
)

There is a simpler way to do this, by using just the URI:

crate://user:pass@host

however, in this case the crate driver defaults to HTTP, and there is no way to specify that it is a HTTPS connection. This should be supported for ease of use.

[amotl]

Hi Romanas,

thanks a stack for your report. [1] and [2] outline how to use custom query parameters to adjust the DB-URL parameter to use SSL connections by either adding ssl=true (and even ssl_ca and such) or by passing a dictionary to the optional connect_args parameter.

I fully agree that crate[sqlalchemy] should honor that way of enabling SSL when using the SQLAlchemy dialect.

With kind regards,
Andreas.

[1] https://stackoverflow.com/questions/48742736/using-ssl-with-sqlalchemy
[2] https://docs.sqlalchemy.org/en/13/core/engines.html#custom-dbapi-connect-arguments-on-connect-routines

[jayeff]

@amotl I ran into this issue when using CrateDB with Apache Superset and used connect_args["servers"] as workaround as described in the following quick tutorial: https://community.crate.io/t/workaround-for-connecting-cratedb-and-apache-superset-with-https/969/1

Regarding fixing this issue I think it's useful to stick to Postgres connection control functions and use sslmode=require as kwarg.