FastAPI and PostgreSQL in docker-compose file connection error

Question

This question has been asked already for example Docker: Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5432? but I still can't figure out how to properly connect the application to the database.

Files:

Dockerfile

FROM python:3.10-slim
WORKDIR /app
COPY . .
RUN pip install --upgrade pip
RUN pip install "fastapi[all]" sqlalchemy psycopg2-binary

docker-compose.yml

version: '3.8'
services:
  ylab:
    container_name: ylab
    build:
      context: .
    entrypoint: >
      sh -c "uvicorn main:app --reload --host 0.0.0.0"
    ports:
      - "8000:8000"
  postgres:
    container_name: postgr
    image: postgres:15.1-alpine
    environment:
        POSTGRES_DB: "fastapi_database"
        POSTGRES_PASSWORD: "password"
    ports:
      - "5433:5432"

main.py

import fastapi as _fastapi
import sqlalchemy as _sql
import sqlalchemy.ext.declarative as _declarative
import sqlalchemy.orm as _orm

DATABASE_URL = "postgresql://postgres:password@localhost:5433/fastapi_database"
engine = _sql.create_engine(DATABASE_URL)
SessionLocal = _orm.sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = _declarative.declarative_base()

class Menu(Base):
    __tablename__ = "menu"
    id = _sql.Column(_sql.Integer, primary_key=True, index=True)
    title = _sql.Column(_sql.String, index=True)
    description = _sql.Column(_sql.String, index=True)

app = _fastapi.FastAPI()

# Create table 'menu'
Base.metadata.create_all(bind=engine)

This works if I host only the postgres database in the container and my application is running locally, but if the database and application are in their own containers, no matter how I try to change the settings, the error always comes up:

"sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "localhost" (127.0.0.1), port 5433 failed: Connection refused ylab | Is the server running on that host and accepting TCP/IP connections? ylab | connection to server at "localhost" (::1), port 5433 failed: Cannot assign requested address ylab | Is the server running on that host and accepting TCP/IP connections?"

The error comes up in

Base.metadata.create_all(bind=engine)

I also tried

DATABASE_URL = "postgresql://postgres:password@postgres:5433/fastapi_database"

but still error:

"sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "postgres" (172.23.0.2), port 5433 failed: Connection refused ylab | Is the server running on that host and accepting TCP/IP connections?"

There is some kind of config file or something mentioned in the answer above but I can't figure out how to manage that config.

What happens if you try your config as `DATABASE_URL = "postgresql://postgres:password@postgres:5432/fastapi_database"` In your first example with the app running locally and postgres in the container this works as you map `localhost:5433` into the postgres containers port `5432`. When you try to do this in the app container it doesnt work since the database is not running locally in the app container. When you change `localhost` to `postgres` this will start targetting the correct container with the db, but the port the db runs on inside the container is port 5432 no port 5433 — Chris Doyle, Jan 21 '23 at 10:51
I tried to do as you suggested and it works! But I would like to say that since yesterday morning I have tried every possible and impossible combination, including yours. And it turns out that it works on the second try, i.e. i create containers with "docker-compose up" command and i get error. I stop the server and start it again with the same "docker-compose up" command and for some reason it works. In any case, you have been a great help to me. I am very grateful to you. — Evgeny Romensky, Jan 21 '23 at 12:08
You probably want to look at https://docs.docker.com/compose/startup-order/ as chances are fastapi will start up before the db and get a connection error. you can use depends on to tell the app not to start until the db is up and running. I will type up the answer for you to accept — Chris Doyle, Jan 21 '23 at 12:12

score 2 · Accepted Answer · answered Jan 21 '23 at 12:17

You should update your config to reference the service name of postgres and the port the database runs on inside the container

DATABASE_URL = "postgresql://postgres:password@postgres:5432/fastapi_database"

When your app was running locally on your machine with the database running in the container then localhost:5433 would work since port 5433 on the host was mapped to 5432 inside the db container.

When you then put the app in its own container but still refer to localhost then it will be looking for the postgres database inside the same container the app is in which is not right.

When you put the right service name but with port 5433 you will also get an error since port 5433 is only being mapped on the host running the containers not from inside the containers them self.

So what you want to do in the app container is just target the database service on port 5432 as thats the port postgres will be running on inside the container.

You also probably want to look at a depends on script that will not start the fast api app until the db is up and ready.

FastAPI and PostgreSQL in docker-compose file connection error

1 Answers1

Linked