How to autogenerate and apply migrations with alembic when the database runs in a container?

Question

I find the workflow for working with database migrations in a containerized environment confusing. I have a web API with an attached database. The API runs in one container and the database in another. The project file structure is as follows

.
├── docker-compose.yml
├── Dockerfile
└── app
|    ├── __init__.py
|    ├── database
|    |    ├── alembic/
|    |    ├── __init__.py
|    |    ├── db.py
|    |    └── models.py
|    ├── other
|    ├── source
|    └── files
├── other
└── files

In order for the API container to be able to access the database the sqlalchemy.url in the ini-file is set to:

postgresql://{username}:{password}@db:5432/{database}

However when I want to do a migration, for example add a table column, I will change the model in app/database/models.py change directory to app/database and run alembic revision --autogenerate -m "Description". This is where the problem occurs, I get the error:

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) could not translate host name "db" to address: Name or service not known

If I change the hostname to localhost it works but then the docker-compose breaks since it has to reference the container name.

This workflow does not seem right. How do people work with databases in projects which uses containers?

The docker-compose.yml file looks like this:

version: "3"

services:
  db:
    image: postgres
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_USER=username
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=database

  app:
    build: .
    command: bash -c "cd app/database && alembic upgrade head && cd ../.. && python app/main.py"
    volumes:
      - .:/code
    ports:
      - "5000:5000"
    depends_on:
      - db

score 1 · Accepted Answer · answered Jul 02 '21 at 13:51

Since your database has published ports:, you can access it directly from the host. The application running outside a container on the host and the same application running in a Compose setup are different environments, and it's appropriate to use environment variables to specify this. Do not hard-code a database location in your application.

If you can use the standard PostgreSQL environment variables, then it's fairly easy to specify this.

# To run migrations:
cd app/database
PGUSER=username PGPASSWORD=password PGDATABASE=database \
  alembic revision --autogenerate -m "Description"
# (assumes default PGHOST=localhost)

# To run the application:
version: '3.8'
services:
  db: { ... }
  app:
    build: .
    environment:
      PGHOST: db
      PGUSER: username
      PGPASSWORD: password
      PGDATABASE: database
    ports:
      - "5000:5000"
    depends_on:
      - db

score 1 · Answer 2 · answered Jul 02 '21 at 15:02

It seems you are missing the network attached to both containers so the DNS can resolve to DB. So, add the same network to both containers as follows:

services:
  db:
    image: postgres
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_USER=username
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=database
    networks:
      - app-network    
  app:
    build: .
    command: bash -c "cd app/database && alembic upgrade head && cd ../.. && python app/main.py"
    volumes:
      - .:/code
    ports:
      - "5000:5000"
    depends_on:
      - db
    networks:
      - app-network
networks:
  app-network:

As a side note, you have the ports mapped to 5432 on your localhost. There is a special docker DNS that points to the localhost where the containers are running, so you can use host.docker.internal inside your app container, which will resolve to your localhost.

score 1 · Answer 3 · answered Oct 14 '22 at 00:44

Adding health checks to @paltaa answer.

version: '3.7'
services:
  db:
    image: postgres:14.0
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD-SHELL", "pg_isready"]
      interval: 10s
      timeout: 5s
      retries: 5
    environment:
      - POSTGRES_USER=username
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=database
    networks:
      - app-network    
  app:
    build: .
    command: bash -c "cd app/database && alembic upgrade head && cd ../.. && python app/main.py"
    volumes:
      - .:/code
    ports:
      - "5000:5000"
    depends_on:
      db:
        condition: service_healthy
    networks:
      - app-network
networks:
  app-network:

How to autogenerate and apply migrations with alembic when the database runs in a container?

3 Answers3