I’m setting up a Flask app with a PostgreSQL database using Docker Compose, i used vscode having wsl2 ubuntus terminal for this. I keep getting a “database ‘user’ does not exist” error when I run the app. It has been quite frustrating debugging and not getting the issue solved. Here’s a summary of my setup and what I’ve tried so far. I have tried to hide sensitive info with a generic name.

Setup Details

Docker Compose: I’m using Docker Compose to run two services, db for PostgreSQL and backend for the Flask app.

Database URI: In my Flask configuration, the SQLALCHEMY_DATABASE_URI is set to postgresql://<db_user>:<db_password>@db:5432/<db_name>. I’ve tried hard coding it in my config file and also setting it as an environment variable in .env and docker-compose.yml

Problem

When I run docker-compose up, the backend service tries to connect to the PostgreSQL database, but I keep getting this error:

FATAL: database "user" does not exist

It seems like SQLAlchemy is treating the database user as the database name instead of recognizing them as separate fields.

Troubleshooting Steps

Environment Variables: I have Verified that both .env and docker-compose.yml have the correct values for db_user, db_password, and db_name.

Direct Database Connection: I can manually connect to the database within the container using docker exec -it <db_container_name> psql -U <db_user> <db_name>, so the database itself seems fine, but the error message persist with everything i have tried so far.

Database URI Variations: I have tried a few variations of the database URI, including different placements of variables, but the error remains the same.

My Question

Could something be misconfigured in how the environment variables are being read by SQLAlchemy or Flask?

Are there common issues or special settings needed when connecting Flask to a PostgreSQL database in Docker?

The environmental variables i installed in the docker are:

Docker Compose version 3.8

PostgreSQL image: postgres:latest

Flask: 2.0.3

Flask-SQLAlchemy: 2.5.1

psycopg2-binary: 2.9.1.

I will really appreciate any insights that will help to answer the above questions and help in solving the problem. Thank you