2

I am trying to run my API inside a docker container. However, my API use CloudSQL and I therefor need Cloud SQL Proxy to run alongside my API.

My Dockerfile run the API with:

# Things needed to install requirements and more

CMD uvicorn app.main:app --host 0.0.0.0 --proxy-headers --forwarded-allow-ips='*'

And I tried to use docker-compose to run the API with Cloud SQL Proxy:

version: "3.7"


services:
  api:
    build:
      context: .
    container_name: api
    ports:
      - "8000:8000"
    depends_on:
      - cloudsql-proxy

  cloudsql-proxy:
    container_name: cloudsql-proxy
    image: gcr.io/cloudsql-docker/gce-proxy:1.31.1
    command: /cloud_sql_proxy -instances=[my-db-instance]=tcp:0.0.0.0:5432 -credential_file=/secrets/cloudsql/db-service-account.json -verbose=true
    ports:
      - "5432:5432"
    volumes:
      - ./db-service-account.json:/secrets/cloudsql/db-service-account.json
    restart: always

The API seems to work, but when I try a route using the db it doesn't work:

cloudsql-proxy      | 2022/07/20 15:58:09 current FDs rlimit set to 1048576, wanted limit is 8500. Nothing to do here.
cloudsql-proxy      | 2022/07/20 15:58:09 using credential file for authentication; [email protected]
cloudsql-proxy      | 2022/07/20 15:58:09 Listening on 0.0.0.0:5432 for lucid-shuttle-322508:europe-west1:staging
cloudsql-proxy      | 2022/07/20 15:58:09 Ready for new connections
cloudsql-proxy      | 2022/07/20 15:58:10 Generated RSA key in 433.214159ms
api                 | INFO:     Started server process [6]
api                 | INFO:     Waiting for application startup.
api                 | INFO:     Application startup complete.
api                 | INFO:     Uvicorn running on http://0.0.0.0:8000 (Press CTRL+C to quit)
api                 | INFO:     192.168.80.1:57500 - "GET / HTTP/1.1" 200 OK
api                 | INFO:     192.168.80.1:57504 - "GET /me HTTP/1.1" 307 Temporary Redirect
api                 | INFO:     192.168.80.1:57504 - "GET /me/ HTTP/1.1" 500 Internal Server Error
api                 | ERROR:    Exception in ASGI application
api                 | Traceback (most recent call last):
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3280, in _wrap_pool_connect
api                 |     return fn()
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 310, in connect
api                 |     return _ConnectionFairy._checkout(self)
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 868, in _checkout
api                 |     fairy = _ConnectionRecord.checkout(pool)
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 476, in checkout
api                 |     rec = pool._do_get()
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
api                 |     with util.safe_reraise():
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
api                 |     compat.raise_(
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
api                 |     raise exception
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
api                 |     return self._create_connection()
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 256, in _create_connection
api                 |     return _ConnectionRecord(self)
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 371, in __init__
api                 |     self.__connect()
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 665, in __connect
api                 |     with util.safe_reraise():
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
api                 |     compat.raise_(
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
api                 |     raise exception
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
api                 |     self.dbapi_connection = connection = pool._invoke_creator(self)
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 590, in connect
api                 |     return dialect.connect(*cargs, **cparams)
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 597, in connect
api                 |     return self.dbapi.connect(*cargs, **cparams)
api                 |   File "/usr/local/lib/python3.10/site-packages/psycopg2/__init__.py", line 122, in connect
api                 |     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
api                 | psycopg2.OperationalError: could not connect to server: Connection refused
api                 |   Is the server running on host "0.0.0.0" and accepting
api                 |   TCP/IP connections on port 5432?
api                 |
api                 |
api                 | The above exception was the direct cause of the following exception:
api                 |
api                 | Traceback (most recent call last):
api                 |   File "/usr/local/lib/python3.10/site-packages/uvicorn/protocols/http/h11_impl.py", line 403, in run_asgi
api                 |     result = await app(self.scope, self.receive, self.send)
api                 |   File "/usr/local/lib/python3.10/site-packages/uvicorn/middleware/proxy_headers.py", line 78, in __call__
api                 |     return await self.app(scope, receive, send)
api                 |   File "/usr/local/lib/python3.10/site-packages/fastapi/applications.py", line 269, in __call__
api                 |     await super().__call__(scope, receive, send)
api                 |   File "/usr/local/lib/python3.10/site-packages/starlette/applications.py", line 124, in __call__
api                 |     await self.middleware_stack(scope, receive, send)
api                 |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/errors.py", line 184, in __call__
api                 |     raise exc
api                 |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/errors.py", line 162, in __call__
api                 |     await self.app(scope, receive, _send)
api                 |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/base.py", line 68, in __call__
api                 |     response = await self.dispatch_func(request, call_next)
api                 |   File "/data/./app/services/fastapi_sqlalchemy/middleware.py", line 65, in dispatch
api                 |     response = await call_next(request)
api                 |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/base.py", line 46, in call_next
api                 |     raise app_exc
api                 |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/base.py", line 36, in coro
api                 |     await self.app(scope, request.receive, send_stream.send)
api                 |   File "/usr/local/lib/python3.10/site-packages/starlette/middleware/cors.py", line 84, in __call__
api                 |     await self.app(scope, receive, send)
api                 |   File "/usr/local/lib/python3.10/site-packages/starlette/exceptions.py", line 93, in __call__
api                 |     raise exc
api                 |   File "/usr/local/lib/python3.10/site-packages/starlette/exceptions.py", line 82, in __call__
api                 |     await self.app(scope, receive, sender)
api                 |   File "/usr/local/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 21, in __call__
api                 |     raise e
api                 |   File "/usr/local/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 18, in __call__
api                 |     await self.app(scope, receive, send)
api                 |   File "/usr/local/lib/python3.10/site-packages/starlette/routing.py", line 670, in __call__
api                 |     await route.handle(scope, receive, send)
api                 |   File "/usr/local/lib/python3.10/site-packages/starlette/routing.py", line 266, in handle
api                 |     await self.app(scope, receive, send)
api                 |   File "/usr/local/lib/python3.10/site-packages/starlette/routing.py", line 65, in app
api                 |     response = await func(request)
api                 |   File "/data/./app/api/routes/context.py", line 23, in custom_route_handler
api                 |     response = await original_route_handler(request)
api                 |   File "/usr/local/lib/python3.10/site-packages/fastapi/routing.py", line 217, in app
api                 |     solved_result = await solve_dependencies(
api                 |   File "/usr/local/lib/python3.10/site-packages/fastapi/dependencies/utils.py", line 531, in solve_dependencies
api                 |     solved = await run_in_threadpool(call, **sub_values)
api                 |   File "/usr/local/lib/python3.10/site-packages/starlette/concurrency.py", line 41, in run_in_threadpool
api                 |     return await anyio.to_thread.run_sync(func, *args)
api                 |   File "/usr/local/lib/python3.10/site-packages/anyio/to_thread.py", line 31, in run_sync
api                 |     return await get_asynclib().run_sync_in_worker_thread(
api                 |   File "/usr/local/lib/python3.10/site-packages/anyio/_backends/_asyncio.py", line 937, in run_sync_in_worker_thread
api                 |     return await future
api                 |   File "/usr/local/lib/python3.10/site-packages/anyio/_backends/_asyncio.py", line 867, in run
api                 |     result = context.run(func, *args)
api                 |   File "/data/./app/db/queries/user.py", line 103, in get_current_user
api                 |     user = self.get_user(email=token_data.email)
api                 |   File "/data/./app/services/fastapi_sqlalchemy/middleware.py", line 144, in _wrapper
api                 |     return func(*args, **kwargs)
api                 |   File "/data/./app/db/queries/user.py", line 137, in get_user
api                 |     return db.session.query(models.User).filter(models.User.email == email).first()
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2819, in first
api                 |     return self.limit(1)._iter().first()
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/query.py", line 2903, in _iter
api                 |     result = self.session.execute(
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1711, in execute
api                 |     conn = self._connection_for_bind(bind)
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1552, in _connection_for_bind
api                 |     return self._transaction._connection_for_bind(
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 747, in _connection_for_bind
api                 |     conn = bind.connect()
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3234, in connect
api                 |     return self._connection_cls(self, close_with_result=close_with_result)
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
api                 |     else engine.raw_connection()
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3313, in raw_connection
api                 |     return self._wrap_pool_connect(self.pool.connect, _connection)
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3283, in _wrap_pool_connect
api                 |     Connection._handle_dbapi_exception_noconnection(
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2117, in _handle_dbapi_exception_noconnection
api                 |     util.raise_(
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
api                 |     raise exception
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3280, in _wrap_pool_connect
api                 |     return fn()
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 310, in connect
api                 |     return _ConnectionFairy._checkout(self)
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 868, in _checkout
api                 |     fairy = _ConnectionRecord.checkout(pool)
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 476, in checkout
api                 |     rec = pool._do_get()
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 145, in _do_get
api                 |     with util.safe_reraise():
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
api                 |     compat.raise_(
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
api                 |     raise exception
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
api                 |     return self._create_connection()
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 256, in _create_connection
api                 |     return _ConnectionRecord(self)
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 371, in __init__
api                 |     self.__connect()
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 665, in __connect
api                 |     with util.safe_reraise():
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
api                 |     compat.raise_(
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
api                 |     raise exception
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
api                 |     self.dbapi_connection = connection = pool._invoke_creator(self)
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 590, in connect
api                 |     return dialect.connect(*cargs, **cparams)
api                 |   File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 597, in connect
api                 |     return self.dbapi.connect(*cargs, **cparams)
api                 |   File "/usr/local/lib/python3.10/site-packages/psycopg2/__init__.py", line 122, in connect
api                 |     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
api                 | sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: Connection refused
api                 |   Is the server running on host "0.0.0.0" and accepting
api                 |   TCP/IP connections on port 5432?
api                 |
api                 | (Background on this error at: https://sqlalche.me/e/14/e3q8)

I tried many things but I'm out of idea, any help ?

Improve this question

1 Answer 1

Reset to default
2

I finally find out how the problem. I was connecting to my database in python (with SQLAlchemy or psycopg, but it's the same for other library) using the host: 0.0.0.0

Instead, use the container name as host defined in the docker-compose: cloudsql-proxy.

For example, with psycopg:

connection = psycopg.connect(
    user="user",
    password="password",
    host="cloudsql-proxy",
    port=5432,
)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.