SQLAlchemy queries, when fetching large datasets, can quickly bog down your Flask API. Pagination is the standard solution, but it’s not just about slicing results; it’s about intelligently managing the state of your query across requests.
Let’s see it in action with a simple Flask app fetching User objects.
from flask import Flask, request, jsonify
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import func
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
db = SQLAlchemy(app)
class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(80), unique=True, nullable=False)
email = db.Column(db.String(120), unique=True, nullable=False)
def __repr__(self):
return f'<User {self.username}>'
# Seed some data
with app.app_context():
db.create_all()
if not User.query.first():
for i in range(1, 101):
db.session.add(User(username=f'user{i}', email=f'user{i}@example.com'))
db.session.commit()
@app.route('/users', methods=['GET'])
def get_users():
page = request.args.get('page', 1, type=int)
per_page = request.args.get('per_page', 10, type=int)
# This is the core of pagination
users_paginated = User.query.paginate(page=page, per_page=per_page, error_out=False)
users_data = []
for user in users_paginated.items:
users_data.append({'id': user.id, 'username': user.username, 'email': user.email})
return jsonify({
'users': users_data,
'total_users': users_paginated.total,
'current_page': users_paginated.page,
'total_pages': users_paginated.pages,
'has_next': users_paginated.has_next,
'has_prev': users_paginated.has_prev
})
if __name__ == '__main__':
app.run(debug=True)
When you hit GET /users?page=3&per_page=5, you get a specific slice of users, along with metadata about the total number of users, pages, and navigation links. The paginate method on SQLAlchemy’s Query object is the key. It takes page and per_page arguments and returns a Pagination object. This object not only holds the items (the actual user objects for the current page) but also crucial metadata like total (the total count of all records matching the query), pages (the total number of pages), and booleans like has_next and has_prev to guide client-side navigation.
The real power here is how paginate abstracts away the underlying SQL. Internally, SQLAlchemy is generating SQL with LIMIT and OFFSET clauses based on your per_page and page values. For example, page=3, per_page=5 would translate to something like SELECT ... LIMIT 5 OFFSET 10. Crucially, it also performs a COUNT(*) query separately to get the total count, so your pagination metadata is accurate without needing to fetch all the data.
You have direct control over the page and per_page parameters via request arguments. For more advanced scenarios, you can apply other SQLAlchemy query modifiers before calling paginate. For instance, to paginate users sorted by username:
@app.route('/users/sorted', methods=['GET'])
def get_users_sorted():
page = request.args.get('page', 1, type=int)
per_page = request.args.get('per_page', 10, type=int)
users_paginated = User.query.order_by(User.username).paginate(
page=page, per_page=per_page, error_out=False
)
# ... rest is the same as get_users
users_data = []
for user in users_paginated.items:
users_data.append({'id': user.id, 'username': user.username, 'email': user.email})
return jsonify({
'users': users_data,
'total_users': users_paginated.total,
'current_page': users_paginated.page,
'total_pages': users_paginated.pages,
'has_next': users_paginated.has_next,
'has_prev': users_paginated.has_prev
})
The error_out=False argument is important. If True (the default), requesting a page number beyond the total number of pages will raise a BadRequest exception in Flask. Setting it to False allows you to return an empty list for out-of-bounds pages, which is often more graceful for API clients.
What most developers miss is that the Pagination object is also aware of its own state and can generate URLs for navigation. If you’re using Flask’s url_for, you can construct links to the next and previous pages. For example, on the users_paginated object, users_paginated.next_url and users_paginated.prev_url would contain the generated URLs if you were to pass endpoint='get_users' and per_page=per_page to the paginate method. This allows your API to return hypermedia controls, making it easier for clients to navigate through collections.
The next logical step is to implement cursor-based pagination, which is more performant for very large datasets and avoids the "shifting pages" problem.