tortoise
Cannot connect to PosgreSQL using unix domain socket
I can connect to PostgreSQL database using asyncpg with specifying DSN string as follows:
conn = await asyncpg.connect(
'postgres:///sampledb?host=/cloudsql/xxx:asia-northeast1:yyy/.s.PGSQL.5432&user=postgres&password=pass')
(This PostgreSQL database is GCP Cloud SQL)
UsingTortoise.init(), however, I cannot connect to the database with the same DSN string.
await Tortoise.init(
config={
'connections': {
'default': 'postgres:///sampledb?host=/cloudsql/xxx:asia-northeast1:yyy/.s.PGSQL.5432&user=postgres&password=pass'
},
'apps': {
'models': {
'models': ['__main__'],
'default_connection': 'default',
}
}})
The code above raises OSError: Multiple exceptions: [Errno 61] Connect call failed ('::1', 5432, 0, 0), [Errno 61] Connect call failed ('127.0.0.1', 5432)
I also tried the following connection settings, but TypeError: __init__() missing 1 required positional argument: 'port' occurred.
await Tortoise.init(
config={
'connections': {
'default': {
"engine": "tortoise.backends.asyncpg",
"credentials": {
"database": 'sampledb',
"host": '/cloudsql/xxx:asia-northeast1:yyy/.s.PGSQL.5432',
"password": "pass",
"user": "postgres",
}
}
'apps': {
'models': {
'models': ['__main__'],
'default_connection': 'default',
}
}})
How can I connect to the database using unix socket?
Hello. Using db_url instead of config works well?
await Tortoise.init(
db_url='postgres:///sampledb?host=/cloudsql/xxx:asia-northeast1:yyy/.s.PGSQL.5432&user=postgres&password=pass',
...
)
Maybe or this form?
await Tortoise.init(
config={
'connections': {
'default': 'postgres:///sampledb?host=/cloudsql/xxx:asia-northeast1:yyy/.s.PGSQL.5432&user=postgres&password=pass'
},
'apps': {
'models': {
'models': ['__main__'],
'default_connection': 'default',
}
}})
Thank you, @lntuition
Unfortunately using db_url argument didn't work.
It raised OSError: Multiple exceptions: [Errno 61] Connect call failed ('::1', 5432, 0, 0), [Errno 61] Connect call failed ('127.0.0.1', 5432) as well.
I found that I can connect by giving port key to the credentials dict. Any number is ok for its value.
await Tortoise.init(
config={
'connections': {
'default': {
"engine": "tortoise.backends.asyncpg",
"credentials": {
"database": 'sampledb',
"host": '/cloudsql/xxx:asia-northeast1:yyy/.s.PGSQL.5432',
"password": "pass",
"user": "postgres",
"port": -1
}
}
'apps': {
'models': {
'models': ['__main__'],
'default_connection': 'default',
}
}})
I have solved my problem.
If it's not a bug that we can't connect using a unix domain socket's DSN, it's ok to close this issue.
Are there any news? I have been trying the solutions above but to no success.
await Tortoise.init(
config={
'connections': {
'default': {
"engine": "tortoise.backends.asyncpg",
"credentials": {
"database": 'sampledb',
"host": '/cloudsql/xxx:asia-northeast1:yyy/.s.PGSQL.5432',
"password": "pass",
"user": "postgres",
"port": -1
}
}
'apps': {
'models': {
'models': ['__main__'],
'default_connection': 'default',
}
}})
generates the error
ERROR: Traceback (most recent call last):
File ".../env/lib/python3.9/site-packages/starlette/routing.py", line 621, in lifespan
async with self.lifespan_context(app):
File ".../env/lib/python3.9/site-packages/starlette/routing.py", line 518, in __aenter__
await self._router.startup()
File ".../env/lib/python3.9/site-packages/starlette/routing.py", line 598, in startup
await handler()
File ".../project/./app/main.py", line 39, in init_orm
await Tortoise.init(config)
File ".../env/lib/python3.9/site-packages/tortoise/__init__.py", line 598, in init
await cls._init_connections(connections_config, _create_db)
File ".../env/lib/python3.9/site-packages/tortoise/__init__.py", line 390, in _init_connections
await connection.create_connection(with_db=True)
File ".../env/lib/python3.9/site-packages/tortoise/backends/asyncpg/client.py", line 103, in create_connection
self._pool = await asyncpg.create_pool(None, password=self.password, **self._template)
File ".../env/lib/python3.9/site-packages/asyncpg/pool.py", line 407, in _async__init__
await self._initialize()
File ".../env/lib/python3.9/site-packages/asyncpg/pool.py", line 435, in _initialize
await first_ch.connect()
File ".../env/lib/python3.9/site-packages/asyncpg/pool.py", line 127, in connect
self._con = await self._pool._get_new_connection()
File ".../env/lib/python3.9/site-packages/asyncpg/pool.py", line 477, in _get_new_connection
con = await connection.connect(
File ".../env/lib/python3.9/site-packages/asyncpg/connection.py", line 2045, in connect
return await connect_utils._connect(
File ".../env/lib/python3.9/site-packages/asyncpg/connect_utils.py", line 790, in _connect
raise last_error
File ".../env/lib/python3.9/site-packages/asyncpg/connect_utils.py", line 776, in _connect
return await _connect_addr(
File ".../env/lib/python3.9/site-packages/asyncpg/connect_utils.py", line 671, in _connect_addr
return await __connect_addr(params, timeout, False, *args)
File ".../env/lib/python3.9/site-packages/asyncpg/connect_utils.py", line 720, in __connect_addr
tr, pr = await compat.wait_for(connector, timeout=timeout)
File ".../env/lib/python3.9/site-packages/asyncpg/compat.py", line 66, in wait_for
return await asyncio.wait_for(fut, timeout)
File "/usr/local/Cellar/[email protected]/3.9.7_1/Frameworks/Python.framework/Versions/3.9/lib/python3.9/asyncio/tasks.py", line 481, in wait_for
return fut.result()
File "/usr/local/Cellar/[email protected]/3.9.7_1/Frameworks/Python.framework/Versions/3.9/lib/python3.9/asyncio/unix_events.py", line 244, in create_unix_connection
await self.sock_connect(sock, path)
File "/usr/local/Cellar/[email protected]/3.9.7_1/Frameworks/Python.framework/Versions/3.9/lib/python3.9/asyncio/selector_events.py", line 502, in sock_connect
return await fut
File "/usr/local/Cellar/[email protected]/3.9.7_1/Frameworks/Python.framework/Versions/3.9/lib/python3.9/asyncio/selector_events.py", line 507, in _sock_connect
sock.connect(address)
FileNotFoundError: [Errno 2] No such file or directory
ERROR: Application startup failed. Exiting.```
I can confirm this solution with credentials dict in 2022.
Other forms of url don't work
I'm using tortoise-orm v0.23.0 + asyncpg v0.29.0, and I'm in the same situation.
- I need to use a domain socket to access my Google Cloud SQL instance from a Google Cloud Run container
- My code's been working fine using asyncpg directly, but now I want to add Tortoise to my stack.
- I only got it working by using the
configparam as demonstrated by otsuka. (I'd have preferred to stick with thedb_urlandmodulesparams.)
Three more caveats I'd like to mention for the benefit of others in the same situation:
- I was using a
postgresql://URI before, whichasyncpgsupports, but had to change it topostgres://to satisfy Tortoise. (Fortunately,asyncpgsupports both.) - I had to add
/.s.PGSQL.5432to the host to make Tortoise work, whereas I could leave it off before. (asyncpg will automatically add it for you.) - You don't actually have to specify `"default_connection": "default"', because that's the default.
Update: I found a hack that works. :) I feel dirty even sharing, but...
from tortoise.backends.base.config_generator import DB_LOOKUP
DB_LOOKUP[ "asyncpg" ][ "vmap" ].pop( "hostname" )
Tortoise.init(
db_url = "postgres://USER:PASS@/DBNAME?host=/cloudsql/myproj:us-central1:mydb/.s.PGSQL.5432",
modules=dict( myapp=[ "path.to.models" ] ),
)
Now you can pass a unix-domain-socket-style URI — asyncpg will respect the host and ignore the port. Note, however, that this hack depends on internal details and has only been tested against v0.23.0 — and will almost certainly break some day.
The real solution is to modify Tortoise itself to support unix domain socket paths properly. I think the smallest change to make this possible is to add a few lines of code to Tortoise.init() that check if db_url is a dict, and to treat that like the "credentials" block in the config instead of calling "expand_db_url()" on it as if it were a URI to be parsed.
I don't have much experience contributing code to FOSS projects, but I can try to create a PR for this if the maintainers are interested.