Home > Mobile >  FastAPI Cloud SQL Proxy in a docker-compose failed
FastAPI Cloud SQL Proxy in a docker-compose failed

Time:07-23

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 ?

CodePudding user response:

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,
)
  • Related