How to run FastAPI and connect to a MySQL database using a Docker compose file?

Question

I am trying to deploy a FastAPI application on a VPS running Linux CentOS 7 by using Docker and Docker compose, but I am running into issues connecting to a database I have stored on the server. Either the database instance won't connect or the FastAPI workers can't attach themselves to a port.

I am using FastAPI to run my server and databases to interact with the MySQL database. Below are my files:

Docker

FROM tiangolo/uvicorn-gunicorn-fastapi:python3.9

COPY ./requirements.txt /app/requirements.txt
RUN pip install --no-cache-dir --upgrade -r /app/requirements.txt
COPY ./app /app

docker-compose.yml

services:

  backend:
    container_name: backend
    build: ./
    restart: always
    ports:
      - 8080:80
    extra_hosts:
      - "host.docker.internal:host-gateway"

main.py

from fastapi import FastAPI
from database import db
from endpoints import ingredient, recipe, recipe_ingredient

app = FastAPI()

app.include_router(ingredient.router)
app.include_router(recipe_ingredient.router)
app.include_router(recipe.router)

@app.on_event("startup")
async def startup():
    await db.connect()

@app.on_event("shutdown")
async def shutdown():
    await db.disconnect()

database.py

import databases
from starlette.config import Config

config = Config(".env")

# Database configurations
_user = config("USER", cast=str)
_password = config("PASSWORD", cast=str)
_host = config("HOST", cast=str)
_database = config("DATABASE", cast=str)
_port = config("PORT", cast=int, default=3306)

db = databases.Database(
    f'mysql://{_user}:{_password}@{_host}/{_database}?port={_port}'
)

In this configuration I set the environment variables to:

USER=root
PASSWORD=***
HOST=host.docker.internal
DATABSE=test_database

However, I think this configuration causes connection issues as what happens is that the uvicorn workers continually wait for the application to start (since the database connection happens before the instantiation of app = FastAPI()).

Error Logs

Attaching to backend
backend  | Checking for script in /app/prestart.sh
backend  | Running script /app/prestart.sh
backend  | Running inside /app/prestart.sh, you could add migrations to this file, e.g.:
backend  |
backend  | #! /usr/bin/env bash
backend  |
backend  | # Let the DB start
backend  | sleep 10;
backend  | # Run migrations
backend  | alembic upgrade head
backend  |
backend  | [2022-10-18 05:41:31 +0000] [1] [INFO] Starting gunicorn 20.1.0
backend  | [2022-10-18 05:41:31 +0000] [1] [INFO] Listening at: http://0.0.0.0:80 (1)
backend  | [2022-10-18 05:41:31 +0000] [1] [INFO] Using worker: uvicorn.workers.UvicornWorker
backend  | [2022-10-18 05:41:31 +0000] [7] [INFO] Booting worker with pid: 7
backend  | [2022-10-18 05:41:31 +0000] [8] [INFO] Booting worker with pid: 8
backend  | [2022-10-18 05:41:31 +0000] [9] [INFO] Booting worker with pid: 9
backend  | [2022-10-18 05:41:32 +0000] [10] [INFO] Booting worker with pid: 10
backend  | [2022-10-18 05:41:32 +0000] [8] [INFO] Started server process [8]
backend  | [2022-10-18 05:41:32 +0000] [8] [INFO] Waiting for application startup.
backend  | [2022-10-18 05:41:32 +0000] [7] [INFO] Started server process [7]
backend  | [2022-10-18 05:41:32 +0000] [7] [INFO] Waiting for application startup.
backend  | [2022-10-18 05:41:32 +0000] [9] [INFO] Started server process [9]
backend  | [2022-10-18 05:41:32 +0000] [9] [INFO] Waiting for application startup.
backend  | [2022-10-18 05:41:32 +0000] [10] [INFO] Started server process [10]
backend  | [2022-10-18 05:41:32 +0000] [10] [INFO] Waiting for application startup.

Here the workers are kept waiting for application startup indefinitely.

I have also tried a different configuration, changing the docker-compose file to:

services:

  backend:
    container_name: backend
    build: ./
    restart: always
    ports:
      - 8080:80
    network_mode: "host"

And then then HOST=127.0.0.0 as the environment variable, but this produces a completely different error. Now, instead of hanging FastAPI says that port 80 is in use!

Attaching to backend
backend  | Checking for script in /app/prestart.sh
backend  | Running script /app/prestart.sh
backend  | Running inside /app/prestart.sh, you could add migrations to this file, e.g.:
backend  |
backend  | #! /usr/bin/env bash
backend  |
backend  | # Let the DB start
backend  | sleep 10;
backend  | # Run migrations
backend  | alembic upgrade head
backend  |
backend  | [2022-10-18 15:44:43 +0000] [1] [INFO] Starting gunicorn 20.1.0
backend  | [2022-10-18 15:44:43 +0000] [1] [ERROR] Connection in use: ('0.0.0.0', 80)
backend  | [2022-10-18 15:44:43 +0000] [1] [ERROR] Retrying in 1 second.
backend  | [2022-10-18 15:44:44 +0000] [1] [ERROR] Connection in use: ('0.0.0.0', 80)
backend  | [2022-10-18 15:44:44 +0000] [1] [ERROR] Retrying in 1 second.
backend  | [2022-10-18 15:44:45 +0000] [1] [ERROR] Connection in use: ('0.0.0.0', 80)
backend  | [2022-10-18 15:44:45 +0000] [1] [ERROR] Retrying in 1 second.
backend  | [2022-10-18 15:44:46 +0000] [1] [ERROR] Connection in use: ('0.0.0.0', 80)
backend  | [2022-10-18 15:44:46 +0000] [1] [ERROR] Retrying in 1 second.
backend  | [2022-10-18 15:44:47 +0000] [1] [ERROR] Connection in use: ('0.0.0.0', 80)
backend  | [2022-10-18 15:44:47 +0000] [1] [ERROR] Retrying in 1 second.

etc.

This is especially weird because port 80 is not in use! It's open. Running sudo ss -tulpn | grep :80 reveals

tcp    LISTEN     0      128       *:80                    *:*                   users:(("httpd",pid=29213,fd=3),("httpd",pid=29202,fd=3),("httpd",pid=29184,fd=3),("httpd",pid=29157,fd=3),("httpd",pid=29155,fd=3),("httpd",pid=29152,fd=3),("httpd",pid=4823,fd=3))
tcp    LISTEN     0      128    [::]:80                 [::]:*                   users:(("httpd",pid=29213,fd=4),("httpd",pid=29202,fd=4),("httpd",pid=29184,fd=4),("httpd",pid=29157,fd=4),("httpd",pid=29155,fd=4),("httpd",pid=29152,fd=4),("httpd",pid=4823,fd=4))

I am all out of ideas. Any suggestions as to what I could try next?

Edit

Where is the database running, and where is the fastapi service running?

My database is running on the VPS. A service called phpMyAdmin came installed with the server, and I uploaded my existing database using this service. The hosting provider is Namecheap.

FastAPI is running in a Docker container, also on the VPS, located in directory specific to my subdomain.

Is the database running on the host?

Yes. phpMyAdmin tells me it is running on localhost:3306

If the fastapi service is running in the container, where are you setting the environment variables?

I have placed a .env in with the container. It is in the same directory as my main.py files. I have not noticed any issues with the reading in of these environment variables - all processes seem to access them fine.

I am unclear on several things: where is the database running, and where is the fastapi service running? Your docker-compose.yaml only shows a single service (and doesn't specify an image for it). Is the database running on the host? If the fastapi service is running in the container, where are you setting the environment variables? — larsks
– larsks, Commented Oct 18, 2022 at 18:43
This question really needs an minimal reproducible example that makes it easy for us to reproduce the problem you're asking about. Also, it's still not clear where you're setting those environment variables: I don't see evidence of that in either your Dockerfile or in your docker-compose.yaml. — larsks
– larsks, Commented Oct 18, 2022 at 19:20
Hi @larsks thanks for your comments. I really appreciate you taking the time to read through my question and helping me out. Your questions got me thinking about lots of different things, so thanks. I've responded to your questions as best I could in the edits section of my post. The minimal reproducible example is hard since there are a lot of moving parts. I will however think about it and update my question when I can. — jda5
– jda5, Commented Oct 18, 2022 at 19:29
You're getting a "port is already in use" when running with network: host because your FastAPI app is trying to open port 80, and something on your host already has port 80 open. Port publishing (8080:80) is a no-op when using host network mode. I would just remove that entire section of the question, because it's distracting from other issues. — larsks
– larsks, Commented Oct 18, 2022 at 19:42
Okay, thank you for your comment. I will look into it tomorrow. — jda5
– jda5, Commented Oct 18, 2022 at 20:29

larsks · Accepted Answer · 2022-10-19 11:23:36Z

It's a little bit hard to tell from your description, but it sounds like the problem is probably with your database. You've said you're running the database on a remote VPS; if that's the case, your database hostname would be the address of that VPS.

I've attempted to reproduce things here.

First, I have a database server running on host 192.168.1.175. I've created a test database there called, appropriately, test_database. Let's first verify we can connect to it from our host:

$ mysql -u root -p -h 192.168.1.175 test_database
Enter password:
[...]
MariaDB [test_database]> select * from test_table;
+----+-------+
| id | name  |
+----+-------+
|  1 | alice |
|  2 | bob   |
+----+-------+
2 rows in set (0.000 sec)

That works. If you can't get past this test, stop here, you've probably discovered the problem!

My local directory looks like this:

$ tree
.
├── app
│   ├── database.py
│   ├── .env
│   └── main.py
├── Dockerfile
└── requirements.txt

1 directory, 7 files

Where app/database.py is:

import databases
from starlette.config import Config

config = Config(".env")

# Database configurations
_user = config("DB_USER", cast=str)
_password = config("DB_PASSWORD", cast=str)
_host = config("DB_HOST", cast=str)
_database = config("DB_NAME", cast=str)
_port = config("DB_PORT", cast=int, default=3306)

db = databases.Database(f"mysql://{_user}:{_password}@{_host}/{_database}?port={_port}")

And app/main.py is:

from database import db
import os

from fastapi import FastAPI

app = FastAPI()

@app.on_event("startup")
async def startup():
    await db.connect()

@app.on_event("shutdown")
async def shutdown():
    await db.disconnect()

@app.get('/')
async def index():
    query = 'SELECT * FROM test_table'
    res = await db.fetch_all(query=query)
    return res

And app/.env is:

DB_USER=root
DB_PASSWORD=secret
DB_HOST=192.168.1.175
DB_NAME=test_database

You'll notice I've changed variable names here; the environment variable USER conflicts with a system maintained variable, making it a pain when I was testing things in my environment.

The Dockerfile looks exactly like yours, and my docker-compose.yaml looks like:

version: "3"

services:

  backend:
    build: ./
    ports:
      - 8080:80

If I bring up this environment, it all seems to work:

$ docker-compose up
[...]
backend_1  | [2022-10-18 20:33:14 +0000] [26] [INFO] Waiting for application startup.
backend_1  | [2022-10-18 20:33:14 +0000] [26] [INFO] Application startup complete.

And from another terminal, I can query my sample application:

$ curl localhost:8080
[{"id":1,"name":"alice"},{"id":2,"name":"bob"}]

If your container is running on the same host as the database, then we only need to make a couple of changes to the above configuration:

We need to add an extra_hosts block to docker-compose.yaml mapping the hostname host.docker.local (or any hostname we prefer) to the special host-gateway value:
```
version: "3"

services:

  backend:
    build: ./
    ports:
      - 8080:80
    extra_hosts:
      - "host.docker.local:host-gateway"
```

We need to update our .env file:

DB_USER=root
DB_PASSWORD=secret
DB_HOST=host.docker.local
DB_NAME=test_database

And we need to rebuild the image and restart our stack:
```
$ docker-compose down
$ docker-compose up --build
```

With this in place, everything works.

You can find all the files I referenced in this answer in this repository.

Hi larsks, thanks for your comment. I am not sure if I made this clear in my questions, but both the database and the code are running on the VPS, so I would look to establish the connection remotely. You have really helped me with new ideas, but I get the feeling that my hosting provider is messing things up. What service do you use to host your database?
I've updated the answer to reflect the situation where your container is running on the same host as the database. W/r/t "what service do you use to host your database?", I'm not using a hosting provider; I'm running the database on another host on my local network. That said, there are very few things a service provider could do to interfere with communication between two services running on the same host.

Collectives™ on Stack Overflow

How to run FastAPI and connect to a MySQL database using a Docker compose file?

Edit

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Edit

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related