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 ?
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,
)