API Pagination with FastAPI and SQLAlchemy

When building APIs with FastAPI and SQLAlchemy, it’s important to implement pagination to efficiently handle large datasets.

Using FastAPI Pagination

FastAPI provides a convenient way to implement pagination using query parameters in the API endpoint. Here’s an example of how to implement pagination with FastAPI:

    
    from fastapi import FastAPI
    from fastapi_pagination import Page, pagination_params
    from fastapi_pagination.ext.sqlalchemy import paginate
    
    app = FastAPI()
    
    @app.get("/items/")
    async def get_items(page: int = pagination_params.page, size: int = pagination_params.size):
        items = paginate(Item, page=page, page_size=size)
        return Page(items=items)
    
    

Using SQLAlchemy for Pagination

With SQLAlchemy, you can easily implement pagination by using the limit and offset functions in a query. Here’s an example:

    
    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker
    
    engine = create_engine("sqlite:///test.db")
    Session = sessionmaker(bind=engine)
    
    def get_items(page: int, size: int):
        session = Session()
        items = session.query(Item).limit(size).offset(page * size).all()
        session.close()
        return items
    
    

Conclusion

Implementing pagination in APIs built with FastAPI and SQLAlchemy is crucial for handling large datasets efficiently. By following the examples above, you can easily paginate through your API responses and improve performance for your users.