StackPuz

Posted on Oct 17 • Originally published at blog.stackpuz.com on Oct 14

Create a CRUD API with FastAPI

#fastapi #crud

CRUD operation: Create, Read, Update, and Delete are fundamental to any web application that interacts with a database. In this example, we will demonstrate how to build a complete CRUD API using FastAPI, a modern Python web framework, along with MySQL as the database. You'll learn how to set up database models using SQLAlchemy, handle incoming requests, and implement the full range of CRUD functionality.

Prerequisites

Python 3.10
MySQL

Setup project

pip install fastapi sqlalchemy pymysql uvicorn python-dotenv

Create a testing database named "example" and run the database.sql file to import the table and data.

Project structure

├─ .env
└─ app
   ├─ __init__.py
   ├─ db.py
   ├─ main.py
   ├─ models
   │  └─ product.py
   ├─ routers
   │  └─ product.py
   ├─ schemas
   │  └─ product.py
   └─ static
      └─ index.html

__init__.py is used to mark the directory as a Python package.

Project files

.env

DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=example
DB_USER=root
DB_PASSWORD=

This file holds the configuration details for connecting to the database.

db.py

import os
from dotenv import load_dotenv
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

load_dotenv()
url = f"mysql+pymysql://{os.getenv('DB_USER')}:{os.getenv('DB_PASSWORD')}@{os.getenv('DB_HOST')}:{os.getenv('DB_PORT')}/{os.getenv('DB_DATABASE')}"
engine = create_engine(url)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

The db.py module sets up the database connection for a FastAPI application using SQLAlchemy. It loads database credentials from environment variables, creates a SQLAlchemy engine for a MySQL database, and establishes a session factory for database interactions. The get_db function provides a database session that can be used in dependency injection, ensuring proper session management by closing the session after use.

models\product.py

from sqlalchemy import *
from app.db import Base

class Product(Base):
    __tablename__ = "Product"
    id = Column(INTEGER, primary_key=True)
    name = Column(VARCHAR)
    price = Column(DECIMAL)

The models\product.py module defines a SQLAlchemy model for the Product table, mapping its columns for the product's ID, name, and price. It extends the Base class from the db module, enabling easy interaction with product data in the database.

schemas\product.py

from pydantic import BaseModel
from decimal import Decimal

class ProductCreate(BaseModel):
    name: str
    price: Decimal

class ProductUpdate(BaseModel):
    name: str
    price: Decimal

The schemas/product.py module defines Pydantic models for validating and serializing product data in a FastAPI application. It includes two classes: ProductCreate, which is used for creating new products by specifying the name and price, and ProductUpdate, which is designed for updating existing product information. Both classes ensure that the provided data adheres to the expected types.

routers\product.py

from fastapi import APIRouter, Depends
from sqlalchemy.orm import Session
from app.db import get_db
from app.models.product import Product
from app.schemas.product import ProductCreate, ProductUpdate

router = APIRouter()

@router.get("/products")
def index(db: Session = Depends(get_db)):
    return db.query(Product).all()

@router.get("/products/{id}")
def get(id: int, db: Session = Depends(get_db)):
    return db.query(Product).filter(Product.id == id).first()

@router.post("/products")
def create(payload: ProductCreate, db: Session = Depends(get_db)):
    product = Product(**payload.model_dump())
    db.add(product)
    db.commit()
    db.refresh(product)
    return product

@router.put("/products/{id}")
def update(id: int, payload: ProductUpdate, db: Session = Depends(get_db)):
    product = db.query(Product).filter(Product.id == id).first()
    product.name = payload.name
    product.price = payload.price
    db.commit()
    db.refresh(product)
    return product

@router.delete("/products/{id}")
def delete(id: int, db: Session = Depends(get_db)):
    db.query(Product).filter(Product.id == id).delete()
    db.commit()

The routers/product.py module defines a FastAPI router for managing product-related API endpoints. It provides the following functionalities:

index function returns a list of all products from the database.
get function fetches a specific product by its ID.
create function adds a new product to the database using the data provided in the ProductCreate schema.
update function modifies an existing product's details based on its ID and the provided ProductUpdate data.
delete function removes a product from the database by its ID.

All operations depend on a database session obtained through the get_db dependency, ensuring proper session management for each request.

main.py

import uvicorn
from fastapi import FastAPI
from fastapi.responses import FileResponse
from app.routers.product import router

app = FastAPI()
app.include_router(router, prefix="/api")
@app.get("/")
async def read_index():
    return FileResponse("app/static/index.html")

if __name__ == " __main__":
    uvicorn.run(app, host="127.0.0.1")

The main.py module serves as the entry point for a FastAPI application. It initializes the FastAPI app and includes a router for product-related endpoints under the /api prefix. The module also defines a root endpoint that serves an index.html file from the static directory. Finally, the application is configured to run with Uvicorn when executed directly, listening on 127.0.0.1.

index.html

<!DOCTYPE html>
<head>
    <style>
        li {
            margin-bottom: 5px;
        }
        textarea {
            width: 100%;
        }
    </style>
</head>
<body>
    <h1>Example CRUD</h1>
    <ul>
        <li><button onclick="getProducts()">Get Products</button></li>
        <li><button onclick="getProduct()">Get Product</button></li>
        <li><button onclick="createProduct()">Create Product</button></li>
        <li><button onclick="updateProduct()">Update Product</button></li>
        <li><button onclick="deleteProduct()">Delete Product</button></li>
    </ul>
    <textarea id="text_response" rows="20"></textarea>
    <script>
        function showResponse(res) {
            res.text().then(text => {
                let contentType = res.headers.get('content-type')
                if (contentType && contentType.startsWith('application/json')) {
                    text = JSON.stringify(JSON.parse(text), null, 4)
                }
                document.getElementById('text_response').innerHTML = text
            })
        }
        function getProducts() {
            fetch('/api/products').then(showResponse)
        }
        function getProduct() {
            let id = prompt('Input product id')
            fetch('/api/products/' + id).then(showResponse)
        }
        function createProduct() {
            let name = prompt('Input product name')
            let price = prompt('Input product price')
            fetch('/api/products', {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/json'
                },
                body: JSON.stringify({ name, price })
            }).then(showResponse)
        }
        function updateProduct() {
            let id = prompt('Input product id to update')
            let name = prompt('Input new product name')
            let price = prompt('Input new product price')
            fetch('/api/products/' + id, {
                method: 'PUT',
                headers: {
                    'Content-Type': 'application/json'
                },
                body: JSON.stringify({ name, price })
            }).then(showResponse)
        }
        function deleteProduct() {
            let id = prompt('Input product id to delete')
            fetch('/api/products/' + id, {
                method: 'DELETE'
            }).then(showResponse)
        }
    </script>
</body>
</html>

This file will be used to create a basic user interface for testing our API.

Many other articles will use Postman as the HTTP client to test the API, but this article I will use JavaScript instead. This will help you understand more detail when working with HTTP request on the client-side.
To keep this file is clean and readable, we will only use basic HTML and JavaScript. There are no additional libraries such as the CSS Framework or Axios here.
All CRUD functions will use the appropriate HTTP Method to invoke the API.
showResponse(res) will formatting the JSON object to makes it easier to read.

Run project

uvicorn app.main:app

Open the web browser and goto http://localhost:8000

Testing

Get All Products

Click the "Get Products" button. The API will return all products data.

Get Product By Id

Click the "Get Product" button and enter "1" for the product id. The API will return a product data.

Create Product

Click the "Create Product" button and enter "test-create" for the product name and "100" for the price. The API will return a newly created product.

Update Product

Click the "Update Product" button and enter "101" for the product id and "test-update" for the name and "200" for the price. The API will return an updated product.

Delete Product

Click the "Delete Product" button and enter "101" for the product id. The API will return nothing, which is acceptable as we do not return anything from our API.

Conclusion

In this article, you have learned how to set up FastAPI to create a CRUD API with SQLAlchemy as the ORM for interacting with the database. We’ve walked through the steps to define models, handle CRUD operations, and test the API using JavaScript. I hope you find this guide helpful and enjoy building with FastAPI.

Source code: https://github.com/StackPuz/Example-CRUD-FastAPI

Create a CRUD Web App: https://stackpuz.com

DEV Community