Chapter 57: Docker Projects Part 1: Websites, Databases, LAMP, LEMP, WordPress, 3-Tier and Every Language with MySQL and MongoDB

57.5 Project 5: Python FastAPI with MySQL, MongoDB and PostgreSQL (with Alembic migrations as a one-off job)

By Ravindra Bagale · Last updated: 25 October 2026

Why: the problem

Suppose we are at Zomato and a new team builds a small notes API for restaurant partners in Python. One team wants MySQL, another already uses MongoDB, and the data team wants PostgreSQL with proper schema migrations. Without Docker every developer installs Python 3.12, a virtual environment, three database servers and their drivers. Pip packages like mysqlclient need C compilers and break on Windows. With Docker we write one FastAPI image and run it against each database with only a change of environment variables and one small driver file. For Postgres we also learn a very important real-world pattern: database migrations with Alembic, run as a one-off docker compose run --rm job before the app starts. By the end we have one Python image, three databases, and a clear way to change the schema safely.

What: every term in plain English

• FastAPI — A modern Python web framework. It gives automatic API docs at /docs.
• Uvicorn — The server that runs a FastAPI app. We run it as the container command.
• PyMySQL — A pure Python MySQL driver. No C compiler needed, so the image stays simple.
• PyMongo — The official MongoDB driver for Python.
• SQLAlchemy — A Python library to talk to SQL databases. Alembic is built on it.
• Alembic — A migration tool. Each schema change is a small versioned Python file.
• One-off job — A container that runs one command and exits, like docker compose run --rm migrate.
• DB_KIND — Our own environment variable that chooses mysql, mongo or postgres.

How: step by step

1. Write the FastAPI app with a small storage layer. One file per database kind.
2. Write requirements.txt and a slim Dockerfile. python:3.12-slim, non-root, uvicorn.
3. Run with MySQL. compose.mysql.yaml
4. Run with MongoDB. compose.mongo.yaml
5. Run with Postgres and Alembic. compose.pg.yaml with a migrate job.
6. Test with curl and /docs. Same API on all three.
7. Clean up. down -v for each file.

Architecture

Folder layout

py-notes/
  app/
    __init__.py
    main.py
    store_sql.py
    store_mongo.py
  migrations/
    env.py
    script.py.mako
    versions/
      0001_create_notes.py
      0002_add_created_at.py
  sql/mysql.sql
  alembic.ini
  requirements.txt
  Dockerfile
  compose.mysql.yaml
  compose.mongo.yaml
  compose.pg.yaml

Example 1: The app, the drivers and the Dockerfile

Goal. The app, the drivers and the Dockerfile.

requirements.txt

fastapi==0.115.2
uvicorn[standard]==0.32.0
PyMySQL==1.1.1
pymongo==4.10.1
SQLAlchemy==2.0.36
psycopg[binary]==3.2.3
alembic==1.13.3

app/main.py

import os
from fastapi import FastAPI, HTTPException
from pydantic import BaseModel

KIND = os.environ.get("DB_KIND", "mysql")
if KIND == "mongo":
    from app import store_mongo as store
else:
    from app import store_sql as store

app = FastAPI(title="Partner notes API")

class NoteIn(BaseModel):
    restaurant: str
    text: str

@app.get("/healthz")
def health():
    store.ping()
    return {"status": "ok", "db": KIND}

@app.get("/notes")
def list_notes():
    return store.list_notes()

@app.post("/notes", status_code=201)
def add_note(n: NoteIn):
    if not n.text.strip():
        raise HTTPException(400, "text is required")
    return store.add_note(n.restaurant, n.text)

app/store_sql.py (MySQL and Postgres)

import os, time
from sqlalchemy import create_engine, text

URL = os.environ["DATABASE_URL"]
engine = create_engine(URL, pool_pre_ping=True)

def ping():
    for attempt in range(10):
        try:
            with engine.connect() as c:
                c.execute(text("SELECT 1"))
            return
        except Exception:
            time.sleep(2)
    raise RuntimeError("database not reachable")

def list_notes():
    with engine.connect() as c:
        rows = c.execute(text("SELECT id, restaurant, text FROM notes ORDER BY id"))
        return [dict(r._mapping) for r in rows]

def add_note(restaurant, body):
    with engine.begin() as c:
        c.execute(text("INSERT INTO notes (restaurant, text) VALUES (:r, :t)"),
                  {"r": restaurant, "t": body})
    return {"restaurant": restaurant, "text": body}

app/store_mongo.py

import os
from pymongo import MongoClient

client = MongoClient(os.environ["MONGO_URL"], serverSelectionTimeoutMS=20000)
col = client["partners"]["notes"]

def ping():
    client.admin.command("ping")

def list_notes():
    return list(col.find({}, {"_id": 0}))

def add_note(restaurant, body):
    col.insert_one({"restaurant": restaurant, "text": body})
    return {"restaurant": restaurant, "text": body}

app/init.py

# empty file: makes app a Python package

Dockerfile

FROM python:3.12-slim
ENV PYTHONDONTWRITEBYTECODE=1 PYTHONUNBUFFERED=1
WORKDIR /srv
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt
COPY app/ ./app/
COPY alembic.ini ./
COPY migrations/ ./migrations/
RUN useradd --create-home appuser
USER appuser
EXPOSE 8000
HEALTHCHECK --interval=10s --timeout=3s --retries=5 \
  CMD python -c "import urllib.request; urllib.request.urlopen('http://localhost:8000/healthz')"
CMD ["uvicorn", "app.main:app", "--host", "0.0.0.0", "--port", "8000"]

What each line does

• DB_KIND — Chooses which storage file the app imports.
• pool_pre_ping=True — SQLAlchemy checks a connection before using it, so dead connections are replaced.
• ping() with retries — The app retries for 20 seconds. This is app-level wait, on top of Compose health checks.
• text(... :r, :t) — Named placeholders, safe against SQL injection.
• serverSelectionTimeoutMS — PyMongo waits up to 20 s for MongoDB.
• PYTHONUNBUFFERED=1 — Logs appear at once in docker logs.
• --no-cache-dir — pip does not keep download cache, so the image is smaller.
• --host 0.0.0.0 — Listen on all interfaces. 127.0.0.1 would not be reachable from outside the container.

Green output

$ docker build -t py-notes:1.0 .
 => [5/8] RUN pip install --no-cache-dir -r requirements.txt
 => naming to docker.io/library/py-notes:1.0
$ docker images py-notes
py-notes   1.0   0b7d1c2e3f4a   8 seconds ago   231MB

Expected result. One image works with three databases. Only environment variables change.

Example 2: Run with MySQL, then with MongoDB

Goal. Run with MySQL, then with MongoDB.

compose.mysql.yaml

services:
  api:
    build: .
    image: py-notes:1.0
    ports: ["8000:8000"]
    environment:
      DB_KIND: mysql
      DATABASE_URL: mysql+pymysql://notes:change-me-notes@db:3306/notes
    depends_on:
      db: { condition: service_healthy }
  db:
    image: mysql:8.4
    environment:
      MYSQL_DATABASE: notes
      MYSQL_USER: notes
      MYSQL_PASSWORD: change-me-notes
      MYSQL_ROOT_PASSWORD: change-me-root
    volumes:
      - mysql-data:/var/lib/mysql
      - ./sql/mysql.sql:/docker-entrypoint-initdb.d/1.sql:ro
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-uroot", "-pchange-me-root"]
      interval: 5s
      retries: 20
volumes:
  mysql-data:

sql/mysql.sql

CREATE TABLE notes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  restaurant VARCHAR(100) NOT NULL,
  text VARCHAR(500) NOT NULL
);

compose.mongo.yaml

services:
  api:
    build: .
    image: py-notes:1.0
    ports: ["8000:8000"]
    environment:
      DB_KIND: mongo
      MONGO_URL: mongodb://root:change-me-mongo@mongo:27017/?authSource=admin
    depends_on:
      mongo: { condition: service_healthy }
  mongo:
    image: mongo:7
    environment:
      MONGO_INITDB_ROOT_USERNAME: root
      MONGO_INITDB_ROOT_PASSWORD: change-me-mongo
    volumes: [mongo-data:/data/db]
    healthcheck:
      test: ["CMD", "mongosh", "--quiet", "--eval", "db.adminCommand('ping').ok"]
      interval: 10s
      retries: 10
volumes:
  mongo-data:

Commands

docker compose -f compose.mysql.yaml -p notes-mysql up -d --build --wait
curl -s localhost:8000/healthz
curl -s -X POST localhost:8000/notes -H "Content-Type: application/json" -d '{"restaurant":"Vaishali","text":"Delivery door is at the back"}'
curl -s localhost:8000/notes
docker compose -f compose.mysql.yaml -p notes-mysql down

docker compose -f compose.mongo.yaml -p notes-mongo up -d --wait
curl -s localhost:8000/healthz
curl -s -X POST localhost:8000/notes -H "Content-Type: application/json" -d '{"restaurant":"Goodluck Cafe","text":"Closed on Monday"}'
curl -s localhost:8000/notes

What each line does

• mysql+pymysql:// — SQLAlchemy URL: use PyMySQL to reach MySQL at host db.
• -p notes-mysql — A project name. Two stacks with different names never mix containers or volumes.
• --wait — up returns only after every service is healthy.
• authSource=admin — The root user lives in the admin database in MongoDB.
• Same image py-notes:1.0 — Built once, used by both stacks.

Green output

{"status":"ok","db":"mysql"}
{"restaurant":"Vaishali","text":"Delivery door is at the back"}
[{"id":1,"restaurant":"Vaishali","text":"Delivery door is at the back"}]
...
{"status":"ok","db":"mongo"}
{"restaurant":"Goodluck Cafe","text":"Closed on Monday"}
[{"restaurant":"Goodluck Cafe","text":"Closed on Monday"}]

Expected result. The same API works on MySQL and MongoDB. Open http://localhost:8000/docs to try it in the browser.

Example 3: Postgres with Alembic migrations as a one-off job

Goal. Postgres with Alembic migrations as a one-off job.

alembic.ini

[alembic]
script_location = migrations

migrations/env.py

import os
from alembic import context
from sqlalchemy import create_engine

def run():
    engine = create_engine(os.environ["DATABASE_URL"])
    with engine.connect() as conn:
        context.configure(connection=conn, target_metadata=None)
        with context.begin_transaction():
            context.run_migrations()

run()

migrations/script.py.mako

revision = ${repr(up_revision)}
down_revision = ${repr(down_revision)}
from alembic import op
import sqlalchemy as sa

def upgrade():
    ${upgrades if upgrades else "pass"}

def downgrade():
    ${downgrades if downgrades else "pass"}

migrations/versions/0001_create_notes.py

from alembic import op
import sqlalchemy as sa

revision = "0001"
down_revision = None

def upgrade():
    op.create_table(
        "notes",
        sa.Column("id", sa.Integer, primary_key=True),
        sa.Column("restaurant", sa.String(100), nullable=False),
        sa.Column("text", sa.String(500), nullable=False),
    )

def downgrade():
    op.drop_table("notes")

migrations/versions/0002_add_created_at.py

from alembic import op
import sqlalchemy as sa

revision = "0002"
down_revision = "0001"

def upgrade():
    op.add_column("notes", sa.Column("created_at", sa.DateTime, server_default=sa.func.now()))

def downgrade():
    op.drop_column("notes", "created_at")

compose.pg.yaml

x-db-url: &dburl postgresql+psycopg://notes:change-me-notes@pg:5432/notes

services:
  migrate:
    build: .
    image: py-notes:1.0
    command: ["alembic", "upgrade", "head"]
    environment:
      DATABASE_URL: *dburl
    depends_on:
      pg: { condition: service_healthy }
    restart: "no"

  api:
    image: py-notes:1.0
    ports: ["8000:8000"]
    environment:
      DB_KIND: postgres
      DATABASE_URL: *dburl
    depends_on:
      migrate: { condition: service_completed_successfully }

  pg:
    image: postgres:16-alpine
    environment:
      POSTGRES_DB: notes
      POSTGRES_USER: notes
      POSTGRES_PASSWORD: change-me-notes
    volumes: [pg-data:/var/lib/postgresql/data]
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U notes -d notes"]
      interval: 5s
      retries: 20
volumes:
  pg-data:

Commands

docker compose -f compose.pg.yaml -p notes-pg up -d --build --wait
docker compose -f compose.pg.yaml -p notes-pg logs migrate
docker compose -f compose.pg.yaml -p notes-pg run --rm migrate alembic current
docker compose -f compose.pg.yaml -p notes-pg exec pg psql -U notes -d notes -c "\d notes"
docker compose -f compose.pg.yaml -p notes-pg run --rm migrate alembic downgrade -1
docker compose -f compose.pg.yaml -p notes-pg run --rm migrate alembic upgrade head

What each line does

• x-db-url: &dburl — A YAML anchor. We write the URL once and reuse it with *dburl.
• command: alembic upgrade head — Applies every migration that is not applied yet, then exits.
• service_completed_successfully — The API starts only after migrate exits with code 0.
• restart: "no" — The job must not restart after it finishes.
• run --rm migrate alembic current — A one-off container that prints the current schema version.
• downgrade -1 — Undo the last migration. Good for testing; be careful in production.

Green output

notes-pg-migrate-1  | INFO  [alembic.runtime.migration] Running upgrade  -> 0001, create notes
notes-pg-migrate-1  | INFO  [alembic.runtime.migration] Running upgrade 0001 -> 0002, add created_at
0002 (head)
   Column   |            Type             | Nullable |      Default
 id         | integer                     | not null | nextval('notes_id_seq'::regclass)
 restaurant | character varying(100)      | not null |
 text       | character varying(500)      | not null |
 created_at | timestamp without time zone |          | now()
INFO  [alembic.runtime.migration] Running downgrade 0002 -> 0001, add created_at
INFO  [alembic.runtime.migration] Running upgrade 0001 -> 0002, add created_at

Expected result. Schema changes are versioned files. The migrate job runs them before the API starts, every deploy, safely.

Every command, step by step (plain docker and Compose)

1. Make folders

   mkdir -p py-notes/app py-notes/sql py-notes/migrations/versions && cd py-notes
   

   What it does: Creates the folders. Then add every file from the examples.

   (no output)
   

2. Build once

   docker build -t py-notes:1.0 .
   

   What it does: Builds the Python image used by all three stacks.

   naming to docker.io/library/py-notes:1.0
   

3. Check the image user

   docker run --rm py-notes:1.0 whoami
   

   What it does: Must print appuser, not root.

   appuser
   

4. Start the MySQL stack

   docker compose -f compose.mysql.yaml -p notes-mysql up -d --wait
   

   What it does: API plus MySQL, waits for healthy.

   Container notes-mysql-api-1 Healthy
   

5. Test it

   curl -s localhost:8000/healthz
   

   What it does: The API reports which database it uses.

   {"status":"ok","db":"mysql"}
   

6. Open the docs

   curl -s -o /dev/null -w '%{http_code}\n' localhost:8000/docs
   

   What it does: FastAPI builds a docs page automatically.

   200
   

7. Stop the MySQL stack

   docker compose -f compose.mysql.yaml -p notes-mysql down
   

   What it does: Frees port 8000 for the next stack.

   Container notes-mysql-api-1 Removed
   

8. Start the Mongo stack

   docker compose -f compose.mongo.yaml -p notes-mongo up -d --wait
   

   What it does: Same image, DB_KIND=mongo.

   Container notes-mongo-api-1 Healthy
   

9. Stop it and start Postgres

   docker compose -f compose.mongo.yaml -p notes-mongo down
   docker compose -f compose.pg.yaml -p notes-pg up -d --wait
   

   What it does: The migrate job runs first, then the API.

   Container notes-pg-migrate-1 Exited (0)
   Container notes-pg-api-1 Healthy
   

10. See all projects

    docker compose ls -a
    

    What it does: Lists every Compose project and its state.

    NAME         STATUS
    notes-mysql  exited(0)
    notes-mongo  exited(0)
    notes-pg     running(2)
    

11. Clean up everything

    for f in mysql mongo pg; do docker compose -f compose.$f.yaml -p notes-$f down -v; done
    docker image rm py-notes:1.0
    

    What it does: Removes all three stacks, their volumes and the image.

    Volume notes-pg_pg-data Removed
    Untagged: py-notes:1.0
    

When it is red 1

sqlalchemy.exc.ProgrammingError: (psycopg.errors.UndefinedTable) relation "notes" does not exist

Cause. The API started before migrations ran, or migrate failed.

Fix, step by step

1. Check docker compose logs migrate.
2. Make sure api depends_on migrate with service_completed_successfully.
3. Run docker compose run --rm migrate alembic upgrade head.

When it is red 2

pymongo.errors.OperationFailure: Authentication failed., full error: {'ok': 0.0, 'code': 18}

Cause. authSource=admin is missing, or the password changed after the first start.

Fix, step by step

1. Add ?authSource=admin to MONGO_URL.
2. Check the password matches MONGO_INITDB_ROOT_PASSWORD.
3. Reset with down -v only on practice data.

When it is red 3

Bind for 0.0.0.0:8000 failed: port is already allocated

Cause. Another stack still runs on port 8000.

Fix, step by step

1. Run docker compose ls to see running projects.
2. Stop the other one with down, or change the port to 8001:8000.

When it is red 4

ModuleNotFoundError: No module named 'app'

Cause. app/init.py is missing or the COPY path is wrong.

Fix, step by step

1. Create the empty app/init.py.
2. Check the Dockerfile has COPY app/ ./app/ and WORKDIR /srv.
3. Build again.

Practice tasks

docker build -t py-notes:1.0 .
docker run --rm py-notes:1.0 whoami

docker compose -f compose.mysql.yaml -p notes-mysql up -d --wait
curl -s -X POST localhost:8000/notes -H 'Content-Type: application/json' -d '{"restaurant":"A","text":"B"}'

Open http://localhost:8000/docs, POST /notes, Try it out

docker compose -f compose.mysql.yaml -p notes-mysql down
docker compose -f compose.mongo.yaml -p notes-mongo up -d --wait

docker compose -f compose.pg.yaml -p notes-pg up -d --wait
docker compose -f compose.pg.yaml -p notes-pg run --rm migrate alembic current

docker compose -f compose.pg.yaml -p notes-pg run --rm migrate alembic downgrade -1
docker compose -f compose.pg.yaml -p notes-pg run --rm migrate alembic upgrade head

curl -s -X POST localhost:8000/notes -H 'Content-Type: application/json' -d '{"restaurant":"A","text":" "}'

op.add_column("notes", sa.Column("rating", sa.Integer, nullable=True))
docker compose -f compose.pg.yaml -p notes-pg run --rm migrate

docker compose -f compose.pg.yaml -p notes-pg up -d --scale api=2

for f in mysql mongo pg; do docker compose -f compose.$f.yaml -p notes-$f down -v; done

Recap

• One FastAPI image talks to MySQL, MongoDB or Postgres by env vars.
• Use pure Python drivers to keep the image simple.
• Retry in the app as well as health checks in Compose.
• Run Alembic as a one-off migrate job.
• service_completed_successfully makes the API wait for migrations.
• Project names (-p) keep stacks apart.

FAQ

Q. Why python:3.12-slim and not alpine?

Many Python wheels are built for glibc. Alpine uses musl and often needs compiling, which is slow and fragile.

Q. Do I need Alembic for MongoDB?

No. MongoDB has no fixed schema. Teams use small scripts or tools like mongock for data changes.

Q. Can I use Django instead of FastAPI?

Yes. Django has its own migrations: run python manage.py migrate as the one-off job.

Q. How do I see SQL errors?

docker compose logs api shows the full Python traceback because PYTHONUNBUFFERED is set.

Q. Is 231 MB too big?

It is fine to start. Multi-stage builds and removing unused drivers can cut it to about 150 MB.

Q. Should migrate run on every deploy?

Yes. alembic upgrade head does nothing when the schema is already current.

Interview questions

1. How do you run database migrations in a containerised app?

   Model answer: As a one-off job (compose run --rm, a Kubernetes Job, or an ECS task) before the new app version starts, never per replica.

2. How do you handle the app starting before the database?

   Model answer: Database healthcheck plus depends_on, and retries with pool_pre_ping in the app.

3. How do you make one image work in many environments?

   Model answer: Configuration through environment variables (12-factor), no settings baked into the image.

4. Why bind uvicorn to 0.0.0.0?

   Model answer: 127.0.0.1 inside a container is only the container itself; port publishing needs the app on all interfaces.

5. What is a YAML anchor in Compose?

   Model answer: &name defines a value once and *name reuses it, to avoid repeating config.

Samajla ka?

Ravi Sir's Cards for this lesson

Revision cardInterview card

मराठी cards