Support for Cloud SQL host within the dsn

[gvbgduh]

Hi,

I'm trying to use asyncpg to connect to Google Cloud SQL together with [databases](https://github.com/encode/databases/), alembic and sometimes sqlalchemy. It's quite convenient to have one database url that can be used in different cases.

But there's one problem as the Cloud SQL host is in the format /cloudsql/<project>:<region>:<db_instance>.

Unfortunately, it breaks the parsing around https://github.com/MagicStack/asyncpg/blob/master/asyncpg/connect_utils.py#L182, so the port and host cannot be distinguished unless host is explicitly passed as kwarg.

Would it be possible to consider adding the support for such a case?

Naive solution seems to be to use rpartition there, so the url
'postgresql://USER:PWD@/DB?host=/cloudsql/::<db_instance>:5432'
can be used, but I can investigate this one more thorough if seems like a reasonable case.

[elprans]

Can you show the URL you are using with sqlalchemy?

[gvbgduh]

The url is

f'postgresql://user:password@/db_name?host=/cloudsql/project-name:region:instance-name&port=5432'

so host is

/cloudsql/project-name:region:instance-name

but I have to make a workaround to pass host additionally as a kwarg, so asyncpg.connection. connect receives dsn and host as above as host from the kwargs takes precedence.

[gvbgduh]

It could be either

f'postgresql://user:password@/db_name?host=/cloudsql/project-name:region:instance-name:5432'

the result is similar.
but it's parsed as

host = "/cloudsql/project-name"
port = "region:instance-name:5432"

[leosussan]

Bumping this - am having the exact same issue.

[ioistired]

Try URL encoding the colons?

[Lawouach]

Hi there,

I'm trying to figure the state of this issue and it's not clear to me. Are we able to pass the CloudSQL /cloudsql/<project>:<region>:<db_instance> format? I tried to urlencode but I'm getting the same issue that actual string is not kep as it should.

[Lawouach]

Looking through the code a bit more. It seems the logic is to assume Host:Port from the get go (and call _parse_hostlist). Maybe a check before calling that function for the host starting with a /? Or change its name and make the check from within?

[maxzheng]

This is working now in the latest version (with #470), maybe someone can close this ticket to avoid confusion?

[gvbgduh]

awesome news @maxzheng!

[JeffryCA]

Hi @maxzheng Can you tell me in detail how you made it work? I have been trying:

postrgres://user:passw@/cloudsql/projectId:region:instance-name/postgres (dbname)

[david-dest01]

Hi @maxzheng Can you tell me in detail how you made it work? I have been trying:

postrgres://user:passw@/cloudsql/projectId:region:instance-name/postgres (dbname)

Anyone actually answer this for you? I'm not able to get this going and there doesn't seem to be a clear example/overview of expected format.

[geminixiang]

Cloud run can't connect to Cloud SQL, have someone has correct url format for Cloud SQL

postgresql+asyncpg://username:password@/mydb?host=/cloudsql/project:asia-east1:instance

[geminixiang]

Cloud run can't connect to Cloud SQL, have someone has correct url format for Cloud SQL

postgresql+asyncpg://username:password@/mydb?host=/cloudsql/project:asia-east1:instance

this is correct url format

postgresql+asyncpg://username:password@/mydb?host=/cloudsql/project:asia-east1:instance

and cloud run must be configured to specify which instance you wanna connect to

gcloud beta run deploy $SERVICE \
--set-cloudsql-instances="$INSTANCE_CONNECTION_NAME"  # Remember to set

[ofer-pd]

FYI - When I tried setting the scheme to "postgresql+asyncpg" as recommended in the previous comment, I got the following error:

asyncpg.exceptions._base.ClientConfigurationError: invalid DSN: scheme is expected to be either "postgresql" or "postgres", got 'postgresql+asyncpg'

Using asyncpg v0.29.0.