I'm running an application written in FastAPI using SQLAlchemy 2 and psycopg2, and using docker-compose and 2 postgres containers: one for development and one for automated testing.
I basically tried to dockerize the following application: following the README, the testing works perfectly, but somewhere along the way I screwed up.
Background: accessing the dev db through the backend server works flawlessly. However, when I try to run pytest in PyCharm that's configured to run using an interpreter based off Docker, I encounter the following error:
raise exc_value.with_traceback(exc_tb)
exc_tb = <traceback object at 0x1127fab80>
exc_type = <class 'psycopg2.OperationalError'>
exc_value = OperationalError('connection to server at "localhost" (::1), port 5432 failed: FATAL: password authentication failed for user "postgres"\n')
self = <sqlalchemy.util.langhelpers.safe_reraise object at 0x1127f8280>
traceback = None
type_ = None
value = None
My complete directory structure:
├── README.md
├── backend
│ ├── Dockerfile
│ ├── README.md
│ ├── __init__.py
│ ├── alembic.ini
│ ├── api
│ ├── config
│ │ ├── local.env
│ │ └── test.env
│ ├── config.py
│ ├── db.py
│ ├── main.py
│ ├── migrations
│ ├── models
│ ├── requirements.lock
│ ├── requirements.txt
│ ├── tests
│ │ ├── api
│ │ │ ├── __init__.py
│ │ │ └── guests
│ │ │ ├── __init.py__
│ │ │ ├── __pycache__
│ │ │ │ └── test_views.cpython-311-pytest-7.4.0.pyc
│ │ │ └── test_views.py
│ │ ├── conftest.py
│ │ ├── test_main.py
│ │ └── utils.py
│ └── utils
├── db.password.txt
├── docker-compose.yml
├── pyproject.toml
Docker-compose file:
version: "3.8"
services:
backend:
build: ./backend
environment:
- POSTGRES_USER=fastapi_traefik
- POSTGRES_PASSWORD=password
- POSTGRES_DB=fastapi_traefik
- APP_CONFIG_FILE=local
command: bash -c 'while !</dev/tcp/db/5432; do sleep 1; done; cd backend; echo "RUNNING ALEMBIC MIGRATIONS"; alembic upgrade head; cd ..; uvicorn backend.main:app --host 0.0.0.0 --port 80 --reload'
ports:
- 80:80
volumes:
- .:/app
depends_on:
db:
condition: service_healthy
secrets:
- db_password
networks:
- dock-db-test
db:
image: postgres:15-alpine
container_name: dev-db
volumes:
- postgres_data:/var/lib/postgresql/data/
environment:
- POSTGRES_USER=fastapi_traefik
- POSTGRES_PASSWORD=password
- POSTGRES_DB=fastapi_traefik
ports:
- 5432:5432
restart: unless-stopped
healthcheck:
test: [ "CMD-SHELL", "pg_isready -U fastapi_traefik" ]
interval: 5s
timeout: 5s
retries: 5
secrets:
- db_password
networks:
- dock-db-test
test_db:
image: postgres:15-alpine
container_name: test-db
environment:
- POSTGRES_USER=inclasstoday
- POSTGRES_PASSWORD=inclasstoday1
- POSTGRES_DB=student-data_test
- APP_CONFIG_FILE=test
- POSTGRES_HOST_AUTH_METHOD="trust"
restart: unless-stopped
volumes:
- postgres_data_test:/var/lib/postgresql/data/
ports:
- 5433:5433
healthcheck:
test: [ "CMD-SHELL", "pg_isready -U inclasstoday -D student-data_test" ]
interval: 5s
timeout: 5s
retries: 5
networks:
- dock-db-test
command: -p 5433
volumes:
postgres_data:
postgres_data_test:
secrets:
db_password:
file: db.password.txt
networks:
dock-db-test:
external: false
name: dock-db-test
Local.env
DB_URI=postgresql+asyncpg://fastapi_traefik:password@db:5432/fastapi_traefik
ECHO_SQL=False
Test.env
DB_URI=postgresql+asyncpg://inclasstoday:inclasstoday1@localhost:5433/student-data_test
ECHO_SQL=False
Conftest.py
from typing import AsyncGenerator, Generator
import pytest
from httpx import AsyncClient
from sqlalchemy import create_engine, event, text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.ext.asyncio import async_sessionmaker, create_async_engine
from sqlalchemy.orm import Session, SessionTransaction
from backend.db import get_session
from backend.main import app
from backend.models.base import Base
from backend.config import settings
@pytest.fixture
async def ac() -> AsyncGenerator:
async with AsyncClient(app=app, base_url="https://test") as c:
yield c
@pytest.fixture(scope="session")
def setup_db() -> Generator:
engine = create_engine(f"{settings.DB_URI.replace('+asyncpg', '')}")
conn = engine.connect()
conn.execute(text("commit"))
try:
conn.execute(text("drop database test"))
except SQLAlchemyError:
pass
finally:
conn.close()
conn = engine.connect()
conn.execute(text("commit"))
conn.execute(text("create database test"))
conn.close()
yield
conn = engine.connect()
conn.execute(text("commit"))
try:
conn.execute(text("drop database test"))
except SQLAlchemyError:
pass
conn.close()
@pytest.fixture(scope="session", autouse=True)
def setup_test_db(setup_db: Generator) -> Generator:
engine = create_engine(f"{settings.DB_URI.replace('+asyncpg', '')}/test")
with engine.begin():
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
yield
Base.metadata.drop_all(engine)
@pytest.fixture
async def session() -> AsyncGenerator:
# https://github.com/sqlalchemy/sqlalchemy/issues/5811#issuecomment-756269881
async_engine = create_async_engine(f"{settings.DB_URI}/test")
async with async_engine.connect() as conn:
await conn.begin()
await conn.begin_nested()
AsyncSessionLocal = async_sessionmaker(
autocommit=False,
autoflush=False,
bind=conn,
future=True,
)
async_session = AsyncSessionLocal()
@event.listens_for(async_session.sync_session, "after_transaction_end")
def end_savepoint(session: Session, transaction: SessionTransaction) -> None:
if conn.closed:
return
if not conn.in_nested_transaction():
if conn.sync_connection:
conn.sync_connection.begin_nested()
def test_get_session() -> Generator:
try:
yield AsyncSessionLocal
except SQLAlchemyError:
pass
app.dependency_overrides[get_session] = test_get_session
yield async_session
await async_session.close()
await conn.rollback()
config.py
from pydantic_settings import BaseSettings, SettingsConfigDict
import os
from pathlib import Path
class Settings(BaseSettings):
DB_URI: str
ECHO_SQL: bool
#https: // stackoverflow.com / questions / 9271464 / what - does - the - file - variable - mean - do
model_config = SettingsConfigDict(
env_file=Path(__file__).parent / f"config/{os.environ['APP_CONFIG_FILE']}.env",
case_sensitive=True,
)
settings = Settings.model_validate({})
Dockerfile
FROM python:3.11-slim-buster
WORKDIR /app
COPY ./requirements.lock ./
RUN pip install --no-cache-dir --upgrade -r requirements.lock
COPY . .
I've tried running from the command line as well, and after loading my venv and running the following:
python -m pytest backend (link
I get the following errors:
return self.loaded_dbapi.connect(*cargs, **cparams)
cargs = ()
cparams = {'dbname': 'student-data_test', 'host': 'localhost', 'password': 'inclasstoday1', 'port': 5433, ...}
self = <sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0x110d4bd50>
venv/lib/python3.11/site-packages/psycopg2/__init__.py:122: in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
E sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "localhost" (::1), port 5433 failed: server closed the connection unexpectedly
E This probably means the server terminated abnormally
E before or while processing the request
Have tried multiple different solutions, including only running the test db without the dev db running and mapping the port to 5432 (didn't work). Also tried using the test docker service's name instead of localhost in the DB_URI string, didn't work. Tried referencing the port as 5432 while referencing the docker service's name, didn't work. Have read through multiple different stackoverflow posts, but haven't found the solution yet.
docker ps of running containers. Observe that the test container is correctly listening at port 5433
Thanks for your time!
