sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not translate host name "postgresql" to address: Name or service not known

Question

Good evening, I have this problem that I can't solve, I've tried different ways without success.

I'm getting this error when I try to manually run the command inside the container: alembic upgrade head

SQLALCHEMY_DATABASE_URL=postgresql://admin:admin@postgresql:5432/medication_api

alembic.ini

[alembic]
script_location = alembic
output_encoding = utf-8
target_metadata = app.database.database.Base.metadata

sqlalchemy.url = postgresql://admin:admin@postgresql:5432/medication_api

[loggers]
keys = root,sqlalchemy,alembic

[handlers]
keys = console

[formatters]
keys = generic

[logger_root]
level = WARN
handlers = console
qualname =

[logger_sqlalchemy]
level = WARN
handlers =
qualname = sqlalchemy.engine

[logger_alembic]
level = INFO
handlers =
qualname = alembic

[handler_console]
class = StreamHandler
args = (sys.stderr,)
level = NOTSET
formatter = generic

[formatter_generic]
format = %(levelname)-5.5s [%(name)s] %(message)s
datefmt = %H:%M:%S

database.py:

import os
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

from dotenv import load_dotenv
load_dotenv()

SQLALCHEMY_DATABASE_URL = os.getenv("SQLALCHEMY_DATABASE_URL")

engine = create_engine(SQLALCHEMY_DATABASE_URL)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

env.py:

from logging.config import fileConfig
from sqlalchemy import engine_from_config
from sqlalchemy import pool
from alembic import context
from dotenv import load_dotenv
from app.database.database import Base
import os

load_dotenv()

config = context.config

database_url = os.getenv("SQLALCHEMY_DATABASE_URL")
config.set_main_option('sqlalchemy.url', database_url)
fileConfig(config.config_file_name)
target_metadata = Base.metadata


def run_migrations_offline():
    context.configure(
        url=database_url,
        target_metadata=target_metadata,
        literal_binds=True,
    )

    with context.begin_transaction():
        context.run_migrations()


def run_migrations_online():
    engine = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix='sqlalchemy.',
        poolclass=pool.NullPool,
    )

    connection = engine.connect()
    context.configure(
        connection=connection,
        target_metadata=target_metadata,
    )

    try:
        with context.begin_transaction():
            context.run_migrations()
    finally:
        connection.close()


if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()

models.py:

from pydantic import BaseModel
from datetime import date
from enum import Enum
from sqlalchemy import Column, String, Date, Integer
from app.database.database import Base
from typing import Optional


class MedicationRequestStatus(str, Enum):
    """Enumeration representing the status of a MedicationRequest."""
    active = "active"
    completed = "completed"


class MedicationRequest(Base):
    """Represents a medication request."""
    __tablename__ = "medication_requests"

    id: int = Column(
        Integer,
        primary_key=True,
        index=True,
        doc="Unique identifier for the medication request."
    )
    patient_reference: str = Column(
        String,
        doc="Reference to the patient for whom the medication is requested."
    )
    clinician_reference: str = Column(
        String,
        doc="Reference to the clinician who prescribed the medication."
    )
    medication_reference: str = Column(
        String,
        doc="Reference to the medication being requested."
    )
    reason_text: str = Column(
        String,
        doc="Text describing the reason for the medication request."
    )
    prescribed_date: date = Column(
        Date,
        doc="Date when the medication was prescribed."
    )
    start_date: date = Column(
        Date,
        doc="Date when the medication should start."
    )
    end_date: Optional[date] = Column(
        Date,
        nullable=True,
        doc="Optional end date for the medication."
    )
    frequency: str = Column(
        String,
        doc="Frequency of medication dosage."
    )
    status: str = Column(
        String,
        doc="Current status of the medication request."
    )


class MedicationRequestIn(BaseModel):
    """Input model for creating or updating a medication request."""
    patient_reference: str
    clinician_reference: str
    medication_reference: str
    reason_text: str
    prescribed_date: date
    start_date: date
    frequency: str
    status: MedicationRequestStatus


class MedicationRequestOut(BaseModel):
    """Output model for returning a medication request."""
    id: int
    patient_reference: str
    clinician_reference: str
    medication_reference: str
    reason_text: str
    prescribed_date: date
    start_date: date
    end_date: Optional[date]
    frequency: str
    status: MedicationRequestStatus

Dockefile:

# Use the official Python image as base image
FROM python:3.11-slim-bullseye

# Set environment variables
ENV PYTHONDONTWRITEBYTECODE 1
ENV PYTHONUNBUFFERED 1

# Set the working directory in the container
WORKDIR /app

# Install system dependencies
RUN apt-get update \
    && apt-get install -y netcat-traditional curl

# Copy only the requirements.txt file to the container
COPY requirements.txt /app/

# Install project dependencies
RUN pip install --no-cache-dir -r requirements.txt \
    && pip install psycopg2-binary

# Install Alembic
RUN pip install alembic

# Copy the source code into the container
COPY . /app

# Expose the port that FastAPI runs on
EXPOSE 8000

# Initialize alembic
RUN rm -rf alembic && alembic init alembic

# Upgrade the database
CMD alembic upgrade head && uvicorn app.main:app --host 0.0.0.0 --port 8000

docker-compose:

version: "3.11"

services:
  medication_request_api:
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - "8000:8000"
    depends_on:
      postgresql:
        condition: service_healthy
    container_name: FastAPI_Medication

  postgresql:
    image: postgres:latest
    ports:
      - "5432:5432"
    environment:
      POSTGRES_DB: medication_api
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: admin
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U admin -d medication_api"]
      interval: 10s
      retries: 5
      start_period: 30s
    container_name: postgresql
    volumes:
      - postgres_data:/var/lib/postgresql/data

  pgadmin:
    image: dpage/pgadmin4
    environment:
      PGADMIN_DEFAULT_EMAIL: "[email protected]"
      PGADMIN_DEFAULT_PASSWORD: "admin"
    ports:
      - 5050:80
    depends_on:
      - postgresql
      - medication_request_api

volumes:
  postgres_data:

I've already tried changing the database host to "localhost", "IPAddress" and it didn't work.

Can anyone please help me just connect alambic with postgresql?