Step by Step Guide: Building a FastAPI App with MySQL Database

FastAPI is a modern, fast (high-performance), web framework for building APIs with Python 3.6+ based on standard Python type hints. In this tutorial, I will show you how to build a FastAPI app with a MySQL database. We will be using the asyncpg library to interact with the MySQL database asynchronously.

Before we get started, make sure you have Python 3.6 or higher installed on your system. You also need to install FastAPI, uvicorn, and asyncpg. You can install them using pip:

pip install fastapi uvicorn asyncpg

Now, let’s create a new FastAPI app and set up the MySQL database connection.

Step 1: Create a new FastAPI app

Create a new directory for your FastAPI project and change into that directory:

mkdir fastapi_mysql_app
cd fastapi_mysql_app

Create a new Python file called main.py and open it in your favorite code editor:

touch main.py

Step 2: Set up the MySQL database connection

First, import the necessary modules in main.py:

from fastapi import FastAPI, HTTPException
import asyncpg

Next, create a FastAPI app instance:

app = FastAPI()

Now, create a function to connect to the MySQL database:

async def connect_to_db():
    conn = await asyncpg.connect(
        user="your_username",
        password="your_password",
        database="your_database",
        host="your_host",
    )
    return conn

Replace your_username, your_password, your_database, and your_host with your MySQL database credentials.

Step 3: Create API endpoints to interact with the MySQL database

Let’s create a simple API endpoint to fetch all records from a users table in the MySQL database.

@app.get("/users/")
async def get_users():
    conn = await connect_to_db()
    users = await conn.fetch("SELECT * FROM users")
    await conn.close()
    return users

In this endpoint, we create a connection to the MySQL database, fetch all records from the users table, close the connection, and return the results.

Step 4: Run the FastAPI app with uvicorn

To run the FastAPI app, use the following command:

uvicorn main:app --reload

Now, you can access your API at http://localhost:8000/users/ and see the records from the users table.

That’s it! You have successfully built a FastAPI app with a MySQL database. You can now expand your app by adding more API endpoints to perform CRUD operations on the database. Have fun building your FastAPI app!