Connecting SQLAlchemy with PostgreSQL on Docker

Hello, guys! Let me try to help you to connect a Flask Web App to a PostgreSQL database, both running on Docker.

Please read everything before trying to copy and paste code

First of all you should have installed these python modules:

• SQLAlchemy
• psycopg2

You should install them using pip.

psycopg2 is one of the PostgreSQL drivers needed to connect SQLAlchemy to your PostgreSQL database.

I'm showing you my docker-compose.yml file.

version: '3'
services:

  web:
     #YourWebServiceConfiguration
     #YourWebServiceConfiguration
     #YourWebServiceConfiguration

  db_postgres:
    image: postgres:latest
    container_name: postgres_db_container
    ports:
        - "5432:5432"
    volumes:
        - postgres_db_vol:/var/lib/postgresql/data

    environment:
        POSTGRES_USER: yourUserDBName
        POSTGRES_PASSWORD: yourUserDBPassword
        POSTGRES_DB: yourDBName

Now let's take a look at my python code! This is a test.py file.

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker


conn_url = 'postgresql+psycopg2://yourUserDBName:yourUserDBPassword@yourDBDockerContainerName/yourDBName'

engine = create_engine(conn_url)

db = scoped_session(sessionmaker(bind=engine))


query_rows = db.execute("SELECT * FROM anyTableName").fetchall()
for register in query_rows:
    print(f"{register.col_1_name}, {register.col_2_name}, ..., {register.col_n_name}")
    # Note that this Python way of printing is available in Python3 or more!!

If you want to learn more you can take a look at the following links.

• SQLAlchemy docs

  General database connection url:
  dialect+driver://username:password@host:port/database

• Simple App with Flask, SQLAlchemy and Docker - Haseeb Majid

  If using Docker you need to replace the host value for the docker container name.

As your flask app and Postgres images are not in the same docker container you cannot access the database via localhost !!

in your database URL replace localhost the name of Postgres Service in docker-compose/

engine = create_engine('postgresql://user:user_password@{}:5432/database'.format('service_name_of_postgres'))

kudos to this answer.

I had the same problem. After a few tries, it occurs that the problem is in the connection URL string given to the sqlalchemy.create_engine()

Firstly I had similarly as you:

db_URL = f"postgresql://{db_login}:{db_password}@localhost:5432/postgres"

Fixed one:

db_URL = f"postgresql+psycopg2://{db_login}:{db_password}@db:5432/postgres"

A bit dumb approach from my side doing two alterations at once, but I've checked it and looks like the problem was localhost. Changing server address to container_name stated in the docker_compose.yml should fix the problem with the connection.

db:
    image: postgres:12.7-alpine
    container_name: db

I had the same problem, and the origin of the problem is that the flask application its not running in the container. So u have two possibles solutions

The first one, initialize a container with flask application togheter with the container postgress, in the same docker compose, or u can add a network drive to your postgress container, like that

version: '3.1'
services:
  #CREATING A SERVER, INITIALIZED A CONTAINER
  db:
    image: postgres:16rc1-alpine
    restart: always
    volumes:
      - volumeofpostgres:/var/lib/postgresql/data
    container_name: postgres_database
    environment:
      POSTGRES_USER_FILE: /run/secrets/username-postgres
      POSTGRES_PASSWORD_FILE: /run/secrets/password-postgres
      #POSTGRES_PASSWORD: EXAMPLE
    secrets:
      - username-postgres
      - password-postgres
    ports:
      - 5432:5432
    networks:
      - bound
     
#VOLUME ITS NOT A SERVICE ITS A CONFIG    
volumes:
  volumeofpostgres:
    #external: true
    #name: postgres_volume

secrets:
  password-postgres:
    file: password-postgres.secret
  username-postgres:
    file: username-postgres.secret

networks:
  bound:
    driver: bridge

than your code with localhost will be working

engine = create_engine("postgresql://user:passqword@localhost:5432/postgres")

change your db host name to your yaml service name

version: '3.8'

services:
  fast-api:
    build:
      context: .
      dockerfile: Dockerfile 
    container_name: location_micro_service
    ports:
      - "8085:8085"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=admin123
      - POSTGRES_DB=location_service
      - POSTGRES_PORT=5432  # Use the default PostgreSQL port inside the container
      - POSTGRES_HOST_NAME=postgres # Use the service name as the hostname
    command: uvicorn app.main:app --reload --host 0.0.0.0 --port 8085
    volumes:
      - ./app:/app/app
  postgres:
    image: postgres:latest
    container_name: postgres_container
    ports:
      - "5433:5432"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=admin123
      - POSTGRES_DB=location_service
    volumes:
      - postgres_data:/var/lib/postgresql/data  # Persist data to the host machine

volumes:
  postgres_data: