API pagination with FastAPI and SQLAlchemy

Pagination is the ability to sequence a collection of items into smaller chunks. For APIs, pagination is incredibly important to safeguard the health of our services. Imagine you have a very large collection of items in your database (millions of records). If you were to return the whole collection to every user in a single fetch, that would put tremendous pressure on your system and cause performance issues. It'd be very inconvenient for the users too, since they'd have to process too much data in one go and would consume a ton of bandwidth. API pagination helps with that - it allows us to split the collection of items into smaller sets and retrieve one at a time. Typically we use pagination in collection endpoints. Collection endpoints are endpoints that represent a collection of items. For example, GET /products may represent a collection of products on our website. Pagination is a good addition for those endpoints. There are various patterns for API pagination. Check out a summary of the patterns on this website: https://microservice-api-patterns.org/patterns/quality/dataTransferParsimony/Pagination.html. In this video, we implement explicit pagination. Explicit pagination allows users to specify which page they want to retrieve and how many items per page they want to see. Good pagination also allows users to sort the items and filter them out. The video shows how to implement pagination using FastAPI and SQLAlchemy. I want to show you the end-to-end implementation since the real challenge is passing the pagination parameters correctly to the database layer. In the coming weeks, I'll post the equivalent video for Flask and Django. My recommendation: keep this video handy. Pagination is always the same, and you can bring this recipe to any API. This video is a snippet from my course "Build APIs with Python: FastAPI Edition" (https://learn.microapis.io/). I've provided context about the implementation, however, if something isn't clear, please reach out for clarifications and/or help. If you're interested in learning to build production-grade APIs with FastAPI and SQLAlchemy, make sure you sign up for "Build APIs with Python". Use the coupon 30-OFF-340EF7ED to obtain a 30% discount: https://sso.teachable.com/secure/1592930/checkout/4737131/build-apis-with-fastapi?coupon_code=30-OFF-340EF7ED 00:00 Introduction 04:52 Preparing the pagination query (understanding LIMIT and OFFSET) 06:57 Setting the LIMIT 07:14 Setting the OFFSET 08:43 Sorting the result 09:34 Running the query 09:45 Calculating the number of pages (COUNT query) 10:52 Returning the response 11:58 Wrapping up If you liked this video, please like it and share it with your network! You can also subscribe to my channel! All this goes a long way to support me to continue creating this kind of content. I'm also the author of "Microservice APIs". Get a copy through this link: http://mng.bz/jy4x and use the following code to get a 40% discount: slperalta (reach out to me if you have issues using the code). You can also download two chapters of the book for free from this link: https://microapis.io/resources/microservice-apis-in-python Check out my newsletter for advanced content on APIs and distributed systems: https://microapis.substack.com/ Let me know also what other kinds of topics you'd like me to address in future videos!