Unable to connect to the database

[NikSan3452]

Hello. I really like Piccolo ORM and would like to use it.

• I am trying to start a new project with "piccolo asgi new".
• I start it up and go to http://localhost:8000/admin/
• I enter my login and password, and then I click to log in
• An error occurs: OSError: Multiple exceptions: [Errno 111] Connect call failed ('127.0.0.1', 5433), [Errno 99] Cannot assign requested address.

I am using a postgres image in docker. The database is created in a container and I can connect to it. But piccolo can't see it and every time it displays the message Unable to connect to the database. Please help me to solve this problem. Thank you in advance.

dockerfile:

FROM python:3.10.7-bullseye
WORKDIR /backend
ENV PYTHONDONTWRITEBYTECODE 1
ENV PYTHONBUFFERED 1
# install system dependencies
RUN apt-get update \
    && apt-get -y install netcat gcc postgresql \
    && apt-get clean
RUN apt-get update
# install python dependencies
RUN pip install --upgrade pip
COPY ./requirements.txt /backend/requirements.txt
RUN pip install -r requirements.txt
COPY . /backend

docker-compose:

version: '3.8'
services:
  server:
    build:
      context: ./backend
      dockerfile: Dockerfile
    volumes:
      - ./backend/:/backend/
    # command: uvicorn app:app --reload --workers 1 --host 0.0.0.0 --port 8000
    command: python ./main.py
    env_file:
      - ./backend/.env
    ports:
      - 8000:8000
    depends_on:
      - db
  db:
    image: postgres:15.1-alpine
    volumes:
      - postgres_data:/var/lib/postgresql/data/
    env_file:
      - ./backend/.env
    ports:
      - 5432:5432
  adminer:
    image: adminer
    restart: always
    depends_on:
      - db
    ports:
      - 8080:8080
  redis:
    image: 'redis/redis-stack'
    restart: always
    ports:
      - '6379:6379'
      - '8001:8001'
volumes:
  postgres_data: #

Exeptions:

adminer_1  | [Wed Nov 30 09:23:49 2022] PHP 7.4.33 Development Server (http://[::]:8080) started
db_1       |
db_1       | PostgreSQL Database directory appears to contain a database; Skipping initialization
db_1       |
db_1       | 2022-11-30 09:23:48.813 UTC [1] LOG:  starting PostgreSQL 15.1 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit
db_1       | 2022-11-30 09:23:48.813 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
db_1       | 2022-11-30 09:23:48.813 UTC [1] LOG:  listening on IPv6 address "::", port 5432
db_1       | 2022-11-30 09:23:48.826 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
db_1       | 2022-11-30 09:23:48.837 UTC [23] LOG:  database system was shut down at 2022-11-30 09:23:42 UTC
redis_1    | 9:C 30 Nov 2022 09:23:48.764 # oO0OoO0OoO0Oo Redis is starting oO0OoO0OoO0Oo
redis_1    | 9:C 30 Nov 2022 09:23:48.764 # Redis version=6.2.7, bits=64, commit=00000000, modified=0, pid=9, just started
redis_1    | 9:C 30 Nov 2022 09:23:48.764 # Configuration loaded
db_1       | 2022-11-30 09:23:48.852 UTC [1] LOG:  database system is ready to accept connections
redis_1    | 9:M 30 Nov 2022 09:23:48.765 * monotonic clock: POSIX clock_gettime
redis_1    | 9:M 30 Nov 2022 09:23:48.766 # A key '__redis__compare_helper' was added to Lua globals which is not on the globals allow list nor listed on the deny list.
redis_1    | 9:M 30 Nov 2022 09:23:48.767 * Running mode=standalone, port=6379.
redis_1    | 9:M 30 Nov 2022 09:23:48.767 # Server initialized
redis_1    | 9:M 30 Nov 2022 09:23:48.767 # WARNING overcommit_memory is set to 0! Background save may fail under low memory condition. To fix this issue add 'vm.overcommit_memory = 1' to /etc/sysctl.conf and then reboot or run the command 'sysctl vm.overcommit_memory=1' for this to take effect.
redis_1    | 9:M 30 Nov 2022 09:23:48.781 * <search> Redis version found by RedisSearch : 6.2.7 - oss
redis_1    | 9:M 30 Nov 2022 09:23:48.781 * <search> RediSearch version 2.4.16 (Git=HEAD-b10e5644)
redis_1    | 9:M 30 Nov 2022 09:23:48.781 * <search> Low level api version 1 initialized successfully
redis_1    | 9:M 30 Nov 2022 09:23:48.782 * <search> concurrent writes: OFF, gc: ON, prefix min length: 2, prefix max expansions: 200, query timeout (ms): 500, timeout policy: return, cursor read size: 1000, cursor max idle (ms): 300000, max doctable size: 1000000, max number of search results:  10000, search pool size: 20, index pool size: 8,
redis_1    | 9:M 30 Nov 2022 09:23:48.783 * <search> Initialized thread pool!
redis_1    | 9:M 30 Nov 2022 09:23:48.783 * <search> Enabled diskless replication
redis_1    | 9:M 30 Nov 2022 09:23:48.783 * <search> Enabled role change notification
redis_1    | 9:M 30 Nov 2022 09:23:48.784 * Module 'search' loaded from /opt/redis-stack/lib/redisearch.so
redis_1    | 9:M 30 Nov 2022 09:23:48.805 * <graph> Starting up RedisGraph version 2.8.20.
redis_1    | 9:M 30 Nov 2022 09:23:48.810 * <graph> Thread pool created, using 12 threads.
redis_1    | 9:M 30 Nov 2022 09:23:48.810 * <graph> Maximum number of OpenMP threads set to 12
redis_1    | 9:M 30 Nov 2022 09:23:48.811 * Module 'graph' loaded from /opt/redis-stack/lib/redisgraph.so
redis_1    | 9:M 30 Nov 2022 09:23:48.814 * <timeseries> RedisTimeSeries version 10617, git_sha=c32b0cb0d8ebc1d31b74c624b8b0424628ff7887
redis_1    | 9:M 30 Nov 2022 09:23:48.814 * <timeseries> Redis version found by RedisTimeSeries : 6.2.7 - oss
redis_1    | 9:M 30 Nov 2022 09:23:48.814 * <timeseries> loaded default CHUNK_SIZE_BYTES policy: 4096
redis_1    | 9:M 30 Nov 2022 09:23:48.814 * <timeseries> loaded server DUPLICATE_POLICY: block
redis_1    | 9:M 30 Nov 2022 09:23:48.814 * <timeseries> Setting default series ENCODING to: compressed
redis_1    | 9:M 30 Nov 2022 09:23:48.815 * <timeseries> Detected redis oss
redis_1    | 9:M 30 Nov 2022 09:23:48.816 * <timeseries> Enabled diskless replication
redis_1    | 9:M 30 Nov 2022 09:23:48.816 * Module 'timeseries' loaded from /opt/redis-stack/lib/redistimeseries.so
redis_1    | 9:M 30 Nov 2022 09:23:48.818 * <ReJSON> version: 20200 git sha: 7584706 branch: HEAD
redis_1    | 9:M 30 Nov 2022 09:23:48.818 * <ReJSON> Exported RedisJSON_V1 API
redis_1    | 9:M 30 Nov 2022 09:23:48.818 * <ReJSON> Exported RedisJSON_V2 API
redis_1    | 9:M 30 Nov 2022 09:23:48.818 * <ReJSON> Enabled diskless replication
redis_1    | 9:M 30 Nov 2022 09:23:48.818 * <ReJSON> Created new data type 'ReJSON-RL'
redis_1    | 9:M 30 Nov 2022 09:23:48.818 * Module 'ReJSON' loaded from /opt/redis-stack/lib/rejson.so
redis_1    | 9:M 30 Nov 2022 09:23:48.818 * <search> Acquired RedisJSON_V1 API
redis_1    | 9:M 30 Nov 2022 09:23:48.818 * <graph> Acquired RedisJSON_V1 API
redis_1    | 9:M 30 Nov 2022 09:23:48.819 * <bf> RedisBloom version 2.2.18 (Git=8b6ee3b)
redis_1    | 9:M 30 Nov 2022 09:23:48.819 * Module 'bf' loaded from /opt/redis-stack/lib/redisbloom.so
redis_1    | 9:M 30 Nov 2022 09:23:48.820 * Ready to accept connections
server_1   | INFO:     Will watch for changes in these directories: ['/backend']
server_1   | INFO:     Uvicorn running on http://0.0.0.0:8000 (Press CTRL+C to quit)
server_1   | INFO:     Started reloader process [1] using StatReload
server_1   | INFO:     Started server process [8]
server_1   | INFO:     Waiting for application startup.
server_1   | /usr/local/lib/python3.10/asyncio/events.py:80: Warning: Unable to fetch server version: Multiple exceptions: [Errno 111] Connect call failed ('127.0.0.1', 5433), [Errno 99] Cannot assign requested address

server_1   |   File "/backend/piccolo_conf.py", line 6, in <module>
server_1   |     DB = PostgresEngine(
server_1   |   File "/usr/local/lib/python3.10/site-packages/piccolo/engine/postgres.py", line 290, in __init__
server_1   |     super().__init__()
server_1   |   File "/usr/local/lib/python3.10/site-packages/piccolo/engine/base.py", line 28, in __init__
server_1   |     run_sync(self.prep_database())
server_1   |   File "/usr/local/lib/python3.10/site-packages/piccolo/utils/sync.py", line 24, in run_sync
server_1   |     return future.result()
server_1   |   File "/usr/local/lib/python3.10/concurrent/futures/_base.py", line 458, in result
server_1   |     return self.__get_result()
server_1   |   File "/usr/local/lib/python3.10/concurrent/futures/_base.py", line 403, in __get_result
server_1   |     raise self._exception
server_1   |   File "/usr/local/lib/python3.10/concurrent/futures/thread.py", line 58, in run
server_1   |     result = self.fn(*self.args, **self.kwargs)
server_1   |   File "/usr/local/lib/python3.10/asyncio/runners.py", line 44, in run
server_1   |     return loop.run_until_complete(main)
server_1   |   File "/usr/local/lib/python3.10/asyncio/base_events.py", line 646, in run_until_complete
server_1   |     return future.result()
server_1   |   File "/usr/local/lib/python3.10/site-packages/piccolo/engine/postgres.py", line 330, in prep_database
server_1   |     await self._run_in_new_connection(
server_1   |   File "/usr/local/lib/python3.10/site-packages/piccolo/engine/postgres.py", line 432, in _run_in_new_connection
server_1   |     connection = await self.get_new_connection()
server_1   |   File "/usr/local/lib/python3.10/site-packages/piccolo/engine/postgres.py", line 389, in get_new_connection
server_1   |     return await asyncpg.connect(**self.config)
server_1   |   File "/usr/local/lib/python3.10/site-packages/asyncpg/connection.py", line 2092, in connect
server_1   |     return await connect_utils._connect(
server_1   |   File "/usr/local/lib/python3.10/site-packages/asyncpg/connect_utils.py", line 895, in _connect
server_1   |     raise last_error
server_1   |   File "/usr/local/lib/python3.10/site-packages/asyncpg/connect_utils.py", line 881, in _connect
server_1   |     return await _connect_addr(
server_1   |   File "/usr/local/lib/python3.10/site-packages/asyncpg/connect_utils.py", line 773, in _connect_addr
server_1   |     return await __connect_addr(params, timeout, True, *args)
server_1   |   File "/usr/local/lib/python3.10/site-packages/asyncpg/connect_utils.py", line 825, in __connect_addr
server_1   |     tr, pr = await compat.wait_for(connector, timeout=timeout)
server_1   |   File "/usr/local/lib/python3.10/site-packages/asyncpg/compat.py", line 56, in wait_for
server_1   |     return await asyncio.wait_for(fut, timeout)
server_1   |   File "/usr/local/lib/python3.10/asyncio/tasks.py", line 445, in wait_for
server_1   |     return fut.result()
server_1   |   File "/usr/local/lib/python3.10/site-packages/asyncpg/connect_utils.py", line 684, in _create_ssl_connection
server_1   |     tr, pr = await loop.create_connection(
server_1   |   File "/usr/local/lib/python3.10/asyncio/base_events.py", line 1072, in create_connection
server_1   |     raise OSError('Multiple exceptions: {}'.format(
server_1   | OSError: Multiple exceptions: [Errno 111] Connect call failed ('127.0.0.1', 5433), [Errno 99] Cannot assign requested address
db_1       | 2022-11-30 09:28:48.847 UTC [21] LOG:  checkpoint starting: time
db_1       | 2022-11-30 09:28:48.865 UTC [21] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.006 s, sync=0.002 s, total=0.018 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB

[dantownsend]

Looks like you're trying to connect to Postgres on 127.0.0.1, but when running the app inside the container this won't work. The host is probably db (the name of the Postgres service in your Docker Compose file).

Make sure you set the host correctly for PostgresEngine in piccolo_conf.py.

[dantownsend]

Will close for now - but feel free to comment if you need help.

[NikSan3452]

Thank you very much for your help. Your solution worked. I set the "host": "db" and the error disappeared. However, a new error has appeared: asyncpg.exceptions.UndefinedTableError: relation "piccolo_user" does not exist when I try to login to the admin panel. When I try to perform migrations an error appears: socket.gaierror: [Errno 11001] getaddrinfo failed

Traceback (most recent call last):
server_1   |   File "/usr/local/lib/python3.10/site-packages/uvicorn/protocols/http/h11_impl.py", line 407, in run_asgi
server_1   |     result = await app(  # type: ignore[func-returns-value]
server_1   |   File "/usr/local/lib/python3.10/site-packages/uvicorn/middleware/proxy_headers.py", line 78, in __call__
server_1   |     return await self.app(scope, receive, send)
server_1   |   File "/usr/local/lib/python3.10/site-packages/fastapi/applications.py", line 270, in __call__
server_1   |     await super().__call__(scope, receive, send)
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/applications.py", line 124, in __call__
server_1   |     await self.middleware_stack(scope, receive, send)
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/errors.py", line 184, in __call__
server_1   |     raise exc
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/errors.py", line 162, in __call__
server_1   |     await self.app(scope, receive, _send)
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/exceptions.py", line 79, in __call__
server_1   |     raise exc
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/exceptions.py", line 68, in __call__
server_1   |     await self.app(scope, receive, sender)
server_1   |   File "/usr/local/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in __call__
server_1   |     raise e
server_1   |   File "/usr/local/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in __call__
server_1   |     await self.app(scope, receive, send)
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/routing.py", line 706, in __call__
server_1   |     await route.handle(scope, receive, send)
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/routing.py", line 443, in handle
server_1   |     await self.app(scope, receive, send)
server_1   |   File "/usr/local/lib/python3.10/site-packages/fastapi/applications.py", line 270, in __call__
server_1   |     await super().__call__(scope, receive, send)
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/applications.py", line 124, in __call__
server_1   |     await self.middleware_stack(scope, receive, send)
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/errors.py", line 174, in __call__
server_1   |     response = await self.handler(request, exc)
server_1   |   File "/usr/local/lib/python3.10/site-packages/piccolo_admin/endpoints.py", line 331, in log_error
server_1   |     raise exc
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/errors.py", line 162, in __call__
server_1   |     await self.app(scope, receive, _send)
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/base.py", line 106, in __call__
server_1   |     response = await self.dispatch_func(request, call_next)
server_1   |   File "/usr/local/lib/python3.10/site-packages/piccolo_api/csrf/middleware.py", line 179, in dispatch
server_1   |     return await call_next(request)
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/base.py", line 80, in call_next
server_1   |     raise app_exc
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/base.py", line 69, in coro
server_1   |     await self.app(scope, receive_or_disconnect, send_no_error)
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/exceptions.py", line 79, in __call__
server_1   |     raise exc
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/exceptions.py", line 68, in __call__
server_1   |     await self.app(scope, receive, sender)
server_1   |   File "/usr/local/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in __call__
server_1   |     raise e
server_1   |   File "/usr/local/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in __call__
server_1   |     await self.app(scope, receive, send)
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/routing.py", line 706, in __call__
server_1   |     await route.handle(scope, receive, send)
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/routing.py", line 443, in handle
server_1   |     await self.app(scope, receive, send)
server_1   |   File "/usr/local/lib/python3.10/site-packages/fastapi/applications.py", line 270, in __call__
server_1   |     await super().__call__(scope, receive, send)
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/applications.py", line 124, in __call__
server_1   |     await self.middleware_stack(scope, receive, send)
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/errors.py", line 174, in __call__
server_1   |     response = await self.handler(request, exc)
server_1   |   File "/usr/local/lib/python3.10/site-packages/piccolo_admin/endpoints.py", line 331, in log_error
server_1   |     raise exc
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/errors.py", line 162, in __call__
server_1   |     await self.app(scope, receive, _send)
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/exceptions.py", line 79, in __call__
server_1   |     raise exc
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/exceptions.py", line 68, in __call__
server_1   |     await self.app(scope, receive, sender)
server_1   |   File "/usr/local/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in __call__
server_1   |     raise e
server_1   |   File "/usr/local/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in __call__
server_1   |     await self.app(scope, receive, send)
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/routing.py", line 706, in __call__
server_1   |     await route.handle(scope, receive, send)
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/routing.py", line 443, in handle
server_1   |     await self.app(scope, receive, send)
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/base.py", line 106, in __call__
server_1   |     response = await self.dispatch_func(request, call_next)
server_1   |   File "/usr/local/lib/python3.10/site-packages/piccolo_api/rate_limiting/middleware.py", line 173, in dispatch
server_1   |     return await call_next(request)
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/base.py", line 80, in call_next
server_1   |     raise app_exc
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/base.py", line 69, in coro
server_1   |     await self.app(scope, receive_or_disconnect, send_no_error)
server_1   |   File "/usr/local/lib/python3.10/site-packages/starlette/endpoints.py", line 42, in dispatch
server_1   |     response = await handler(request)
server_1   |   File "/usr/local/lib/python3.10/site-packages/piccolo_api/session_auth/endpoints.py", line 246, in post
server_1   |     user_id = await self._auth_table.login(
server_1   |   File "/usr/local/lib/python3.10/site-packages/piccolo/apps/user/tables.py", line 202, in login
server_1   |     await cls.select(cls._meta.primary_key, cls.password)
server_1   |   File "/usr/local/lib/python3.10/site-packages/piccolo/query/base.py", line 203, in run
server_1   |     results = await engine.run_querystring(
server_1   |   File "/usr/local/lib/python3.10/site-packages/piccolo/engine/postgres.py", line 458, in run_querystring
server_1   |     return await self._run_in_pool(query, query_args)
server_1   |   File "/usr/local/lib/python3.10/site-packages/piccolo/engine/postgres.py", line 423, in _run_in_pool
server_1   |     response = await connection.fetch(query, *args)
server_1   |   File "/usr/local/lib/python3.10/site-packages/asyncpg/connection.py", line 620, in fetch
server_1   |     return await self._execute(
server_1   |   File "/usr/local/lib/python3.10/site-packages/asyncpg/connection.py", line 1658, in _execute
server_1   |     result, _ = await self.__execute(
server_1   |   File "/usr/local/lib/python3.10/site-packages/asyncpg/connection.py", line 1683, in __execute
server_1   |     return await self._do_execute(
server_1   |   File "/usr/local/lib/python3.10/site-packages/asyncpg/connection.py", line 1710, in _do_execute
server_1   |     stmt = await self._get_statement(
server_1   |   File "/usr/local/lib/python3.10/site-packages/asyncpg/connection.py", line 397, in _get_statement
server_1   |     statement = await self._protocol.prepare(
server_1   |   File "asyncpg/protocol/protocol.pyx", line 168, in prepare
server_1   | asyncpg.exceptions.UndefinedTableError: relation "piccolo_user" does not exist

File "C:\Users\satri\AppData\Local\Programs\Python\Python310\lib\socket.py", line 955, in getaddrinfo
    for res in _socket.getaddrinfo(host, port, family, type, proto, flags):
socket.gaierror: [Errno 11001] getaddrinfo failed

[dantownsend]

You're right - the migrations need to be run for the admin.

As for the error - it looks like you're running the migrations on your Windows machine. db isn't a valid hostname on Windows, just within the Docker container.

Try running the migrations inside the Docker container instead:

docker compose exec server piccolo migrations forwards all

[NikSan3452]

Thank you very much. That really helped to solve the problem.