Database URI Misinterpretation in Dockerized Flask Application (PostgreSQL connection issues)

Docker Engine General
1

Background:

I’m working on a Flask application that connects to a PostgreSQL database running in Docker. The database URI seems to be misinterpreted by SQLAlchemy, causing the error:

FATAL:  database "user" does not exist

(1) Docker Compose Setup:

  • I am using Docker Compose with two services: backend and db.
  • The db service runs a PostgreSQL container, and the backend service runs the Flask application.
  • I have set the environment variables correctly in both .env and docker-compose.yml, but the issue persists.

(2) Database URI Configuration:

  • The database URI is set to:
postgresql://<db_user>:<db_password>@db:5432/<db_name>
  • Flask’s SQLALCHEMY_DATABASE_URI is set to this value, both as an environment variable and hardcoded in config.py.

(3) Error Output:

The error logs show the following:

y

2024-11-14 09:35:54.165 UTC [1143] FATAL:  database "<db_user>" does not exist

(4) Database Connection:

When I enter the PostgreSQL container and use psql to connect, I can connect to the <db_name> database without issues:

docker exec -it <your_postgres_container> psql -U <db_user> <db_name>

Troubleshooting Attempts:

  1. Verified that both .env and docker-compose.yml are correctly passing the necessary values.
  2. Tried both hardcoded and environment variable-based configurations for SQLALCHEMY_DATABASE_URI.
  3. Manually connected to the database using psql, but the application still fails with the “user” database error.

My Question:

  1. What could cause Flask’s SQLAlchemy to treat <db_user> as the database name instead of the database user?
  2. Are there any special considerations when using Docker Compose with PostgreSQL and Flask that I might be missing?
  3. Is there a specific configuration for connecting to a PostgreSQL database in Docker that I should use?

Setup Details:

  • Docker Compose Version: 3.8
  • PostgreSQL Image: postgres:latest
  • Flask Version: 2.0.3
  • Flask-SQLAlchemy Version: 2.5.1
  • SQLAlchemy Version: 1.4.35
  • psycopg2-binary Version: 2.9.1
  • SpeechRecognition Version: 3.8.1
  • requests Version: 2.26.0
  • werkzeug Version: 2.2.3
  • python-dotenv Version: (insert version)

Additional Info:

  • I’m using flask run to start the Flask app inside the container.
  • I’ve tried multiple variations of the database URI, but they all result in the same error.

Tags: docker, flask, postgresql, sqlalchemy