FastAPI Tutorial #50: How to Implement Many-to-Many Relationships with SQLModel in Python FastAPI

In this tutorial, we will be implementing Many-to-Many relationships in FastAPI using SQLModel. FastAPI is a modern, fast (high-performance), web framework for building APIs with Python 3.7+ based on standard Python type hints. SQLModel is a Python framework that provides a simple way to interact with databases using Python type annotations.

Before we start, make sure you have FastAPI and SQLModel installed. You can install them using pip:

pip install fastapi
pip install sqlmodel

Let’s start by creating a new FastAPI project. Create a new directory for your project and navigate to it in the terminal. Then create a new file called main.py and add the following code to it:

from fastapi import FastAPI
from sqlmodel import SQLModel, Field, Session, select

# Create FastAPI app
app = FastAPI()

# Your database connection details
DATABASE_URL = "sqlite:///test.db"

# Create a SQLite database session
def create_session():
    from sqlalchemy import create_engine
    from sqlmodel import SQLModel, Session

    engine = create_engine(DATABASE_URL)
    SQLModel.metadata.create_all(engine)
    with Session(engine) as session:
        yield session

# Define models
class User(SQLModel, table=True):
    id: int = Field(primary_key=True)
    name: str
    email: str
    articles: list['Article'] = Field(sa_column_kwargs={"secondary": "user_article"})

class Article(SQLModel, table=True):
    id: int = Field(primary_key=True)
    title: str
    body: str
    authors: list['User'] = Field(sa_column_kwargs={"secondary": "user_article"})

# Create tables
with Session(DATABASE_URL) as session:
    session.exec(
        """CREATE TABLE IF NOT EXISTS user (
            id INTEGER PRIMARY KEY,
            name VARCHAR(100),
            email VARCHAR(100)
        );"""
    )
    session.exec(
        """CREATE TABLE IF NOT EXISTS article (
            id INTEGER PRIMARY KEY,
            title VARCHAR(100),
            body TEXT
        );"""
    )
    session.exec(
        """CREATE TABLE IF NOT EXISTS user_article (
            user_id INTEGER REFERENCES user(id),
            article_id INTEGER REFERENCES article(id)
        );"""
    )

@app.post("/users/")
def create_user(user: User):
    with create_session() as session:
        session.add(user)
        session.commit()
        session.refresh(user)

    return user

@app.post("/articles/")
def create_article(article: Article):
    with create_session() as session:
        session.add(article)
        session.commit()
        session.refresh(article)

    return article

@app.post("/users/{user_id}/articles/{article_id}/")
def assign_article_to_user(user_id: int, article_id: int):
    with create_session() as session:
        user = session.get(User, user_id)
        article = session.get(Article, article_id)

        if user and article:
            user.articles.append(article)
            session.add(user)
            session.commit()
            session.refresh(user)

    return user

In this code, we have defined two models – User and Article, with a Many-to-Many relationship between them. A user can have multiple articles, and an article can have multiple users. We have also defined the database tables for these models and created a SQLite database session.

We have also defined three API endpoints – /users/, /articles/, and /users/{user_id}/articles/{article_id}/ for creating users, articles, and assigning articles to users respectively.

To run the FastAPI server, you can use uvicorn. Install uvicorn using pip:

pip install uvicorn

To start the FastAPI server, run the following command in your terminal:

uvicorn main:app --reload

Now you can test the API endpoints using a tool like Postman or curl. Create some users and articles using the /users/ and /articles/ endpoints, and then assign articles to users using the /users/{user_id}/articles/{article_id}/ endpoint.

That’s it! You have successfully implemented Many-to-Many relationships in FastAPI using SQLModel. Feel free to customize the code and add more features as needed.