Running SQLAlchemy inside postgres docker container

Question

I have the following Dockerfile:

FROM postgres

RUN apt-get update && \
    apt-get install \
    --yes \
    --no-install-recommends \
    python3-pip libpq-dev

RUN pip3 install \
    --default-timeout=100 \
    sqlalchemy sqlalchemy-utils sqlalchemy-utils psycopg2-binary 

COPY database.py .

CMD python3 database.py

The database.py file is:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy_utils import database_exists, create_database

SQLALCHEMY_DATABASE_URL = (
    "postgresql+psycopg2://postgres:password@localhost/mydb"
)
# SQLALCHEMY_DATABASE_URL = "postgresql:///mydb"

engine = create_engine(SQLALCHEMY_DATABASE_URL)
if not database_exists(engine.url):
    print("Database did not exit. Creating it.")
    create_database(engine.url)

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

Base = declarative_base()

The build performs without issue, then I run:

docker run --name <SOME_NAME> -p 5432:5432 -e POSTGRES_PASSWORD=password -d <BUILD_TAG>

And this happens:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (::1), port 5432 failed: Cannot assign requested address
 Is the server running on that host and accepting TCP/IP connections?
(Background on this error at: https://sqlalche.me/e/20/e3q8)

I already tried specifying the port and trying with port 5433 for example. Also I tried removing the -p 5432:5432. I think that the container exposes the port 5432 to the outside world but not from the inside.

Answer 1

Since you're inheriting from the postgres base image, you are overwriting the database from starting up with the CMD statement. Change your docker file to this:

FROM postgres


RUN apt-get update && \
    apt-get install \
    --yes \
    --no-install-recommends \
    python3-pip libpq-dev

RUN pip3 install --break-system-packages \
    --default-timeout=100 \
    sqlalchemy sqlalchemy-utils sqlalchemy-utils psycopg2-binary 

COPY database.py .

EXPOSE 5432
CMD ["postgres"]

You will then be able to connect to the database running inside your docker container via your database.py file.