Support for connection URLs

[jakob]

As far as I can tell, pg8000 doesn't support PostgreSQL Connection URLs.

Connection URLs are strings that look like this:

 postgresql://user@host:5432/database

They are documented here: https://www.postgresql.org/docs/15/libpq-connect.html#LIBPQ-CONNSTRING

I understand that it may not be trivial to support connection URLs, especially things like ?sslmode=verify-ca, but they are very useful.

Most PostgreSQL tools support connection URLs, and many cloud service providers provide connection URLs for their PostgreSQL servers.

With psycopg2 you can just do psycopg2.connect("postgresql://user@host:5432/database"). Would be nice if we could do the same with pg8000.dbapi.connect()

[tlocke]

Hi @jakob, thanks for your suggestion. I must admit that I'm in two minds about it. On the one hand I can see how it would be useful, with connection URLs being very popular. In particular SQLAlchemy uses them in the Python world. Also, I think we should be able to map the SSL options to SSLContext okay, so that wouldn't be a problem. The downside would be adding complexity for users of the library, in that now users would have two ways of connecting. I'm always very reluctant to add complexity for the users. A radical idea would be to only use a connection URL to connect, and drop support for the current way of connecting. What do you think?

[tlocke]

Looking at SQLAlchemy, the docs for connecting using pg8000 describe how there's a connection URL, but also an SSLContext parameter for people who want the fine control over SSL connections.

import ssl
ssl_context = ssl.create_default_context()
ssl_context.check_hostname = False
ssl_context.verify_mode = ssl.CERT_NONE
engine = sa.create_engine(
    "postgresql+pg8000://scott:tiger@192.168.0.199/test",
    connect_args={"ssl_context": ssl_context},
)

[tlocke]

There's another consideration, and that's the awkward case for parameters like user where pg8000 detects the type of the parameter (str or bytes), which wouldn't be possible if it were in a URL:

user The username to connect to the PostgreSQL server with. If your server character encoding is not ascii or utf8, then you need to provide user as bytes, eg. 'my_name'.encode('EUC-JP').