PostgreSQL cannot connect to fastapi applicaiton via SQLalchemy utilizing docker-compose

Question

test-api_1  | sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not connect to server: Connection refused
test-api_1  |   Is the server running on host "0.0.0.0" and accepting
test-api_1  |   TCP/IP connections on port 5432?
test-api_1  | 
test-api_1  | (Background on this error at: http://sqlalche.me/e/e3q8)
partnerup-api_test-api_1 exited with code 1

This is the error I am getting--- it is relatively vague and does not provide much more information.

From the docker-compose cli, I get the following:

my_postgres | 2019-11-03 23:05:21.746 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
my_postgres | 2019-11-03 23:05:21.746 UTC [1] LOG:  listening on IPv6 address "::", port 5432
my_postgres | 2019-11-03 23:05:21.758 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
my_postgres | 2019-11-03 23:05:21.817 UTC [51] LOG:  database system was shut down at 2019-11-03 23:05:21 UTC
my_postgres | 2019-11-03 23:05:21.847 UTC [1] LOG:  database system is ready to accept connections

This is confusing because it seems to be listening but the base api application doesn't recognize via sql alchemy.

I have tried to navigate to postgres.conf but found that listen_adress = '*'

Beyond this I dont know what could be the issue.

database.py

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


SQLALCHEMY_TRACK_MODIFICATIONS = False
## from sql alchemy docs --- format: postgresql://<user>:<password>@<host>:<port>/<database>
SQLALCHEMY_DATABASE_URI = 'postgresql://postgres:[email protected]:5432/partnerup'
engine = create_engine(SQLALCHEMY_DATABASE_URI)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

docker-compose.yml

version: '3' #docker-compose version
services:       #containers in the network
  test-api:
    build: .
    ports:
      - 5000:5000 #port to go to to get a local version of the app
    volumes:
      - ./apps/api/app:/app
    working_dir: /app
    depends_on: 
      - db
    command:
      ['uvicorn', 'main:app', '--host', '127.0.0.1', '--port', '5000']
      #may integrate later
  db:
    image: "postgres:11"
    container_name: "my_postgres"
    restart: always 
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: 'postgres'
      POSTGRES_PASSWORD: 'dbpw'
      POSTGRES_DATABASE: 'partnerup'
      POSTGRES_HOST: 'localhost'
      POSTGRES_PORT: '5432'
    volumes:
          # this command will create database as described in init file 
     # - .:/docker-entrypoint-initdb.d/       
      - db_volume:/var/lib/postgresql
volumes:
  db_volume: {}

I expect that I should be able to verify a connection but I keep getting that pesky error message.

Are your environment entries valid in docker compose? I have only seen them in - NAME=value format. You might also want to try appending /data to the postgres volume mount path — Tomanow
– Tomanow, Commented Nov 4, 2019 at 0:49
@Tomanow, yes they are valid, I tried switching them just for kicks and no luck -- unfortunately. I also made the change to volumes as you suggested. I can't help but think I have some sort of fundamental misunderstanding about what I am doing. Thank you for your feedback nonetheless. — David Migl
– David Migl, Commented Nov 4, 2019 at 1:35

David Migl · Accepted Answer · 2019-12-01 17:39:04Z

5

When using docker-compose and trying to connect to a database container, you need to connect to the service instead of specifying, in my instance:

0.0.0.0:5432

in

SQLALCHEMY_DATABASE_URI = 'postgresql://postgres:[email protected]:5432/partnerup'

instead it should name the service like so:

SQLALCHEMY_DATABASE_URI = 'postgresql://postgres:dbpw@db/partnerup'

where db is the name of the service container.

There is also another problem one could face:

if the database container is not done spinning up and the api service tries to connect, one will encounter a connection error as well. One way to fix this is by adding some sort of wait script in bash code, probably preferred.

an alternative, less clean, approach would be to take advantage of docker-compose restart functionality like so.

in your api service simply add

restart: on-failure

which tells you api service to keep restarting until it is successful. This is an ugly way to give database service time to start up in my opinion.

**** note: the

depends_on: - db

in the test-api service, does effect the order in which your services will start up, however will not check the health of the db service to ensure that it is done spinning up. In some cases this may be all you need to do. In my case it was not sufficient as the db container took a little while.

answered Dec 1, 2019 at 17:39

David Migl

511 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michael Paccione Over a year ago

I love this ugly way you've posted -- just what I need in local dev

Sun Bee Over a year ago

What worked for me was to use 127.0.0.1 instead of 0.0.0.0.

Collectives™ on Stack Overflow

PostgreSQL cannot connect to fastapi applicaiton via SQLalchemy utilizing docker-compose

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related