Pagination & Streaming

Pagination and streaming are core features of SeaORM designed to handle large datasets efficiently. These systems allow developers to retrieve data in chunks (pages) or as a continuous flow (streams), reducing memory overhead and improving application responsiveness. The library provides both asynchronous and synchronous implementations to cater to different runtime environments.

Sources: src/lib.rs:36-40, sea-orm-sync/src/lib.rs:356-358, src/docs.rs:136-140

Pagination Architecture

The pagination system is built around the Paginator struct and the PaginatorTrait. It abstracts the complexity of SQL LIMIT and OFFSET clauses, providing a high-level API for navigating result sets.

Key Components

Sources: src/executor/paginator.rs:14-34, src/executor/paginator.rs:248-255

Paginator State and Logic

The Paginator maintains a reference to the database connection, the original query statement, and the pagination parameters. It computes page offsets dynamically when fetching data.

The diagram shows the transition from a standard selection to a paginated state where offsets and limits are calculated based on user input.
Sources: src/executor/paginator.rs:15-24, src/executor/paginator.rs:41-47

Data Retrieval Operations

Fetching Pages

Data can be retrieved by specific page index or by following the internal cursor of the paginator.

• fetch_page(page): Retrieves a specific page. Index starts at 0.
• fetch(): Retrieves the current page as tracked by the paginator.
• fetch_and_next(): Fetches the current page and automatically increments the internal page counter.

Sources: src/executor/paginator.rs:41-60, src/executor/paginator.rs:125-130

Counting and Metadata

To support UI components like pagination bars, the system provides methods to calculate the total dataset size.

• num_items(): Executes a subquery to count the total number of rows matching the query criteria. It automatically clears ORDER BY, LIMIT, and OFFSET from the counting query for performance.
• num_pages(): Calculates total pages based on num_items and page_size.
• num_items_and_pages(): Returns both values in a single call to minimize logic duplication.

Sources: src/executor/paginator.rs:63-88, src/executor/paginator.rs:104-111

Streaming Support

SeaORM allows converting a Paginator or a raw query into a stream. This is particularly useful for processing extremely large datasets that should not be loaded into memory all at once.

Async vs Sync Streaming

The implementation differs based on the presence of the sync feature flag:

1. Async Streaming: Uses async_stream to yield results as they become available from the database. It returns a Pin<Box<dyn Stream>>.
2. Sync Streaming: Utilizes a PaginatorStream wrapper that implements Iterator, allowing for while let Some loops in non-async environments.

Sources: src/executor/paginator.rs:191-215, sea-orm-sync/src/executor/paginator.rs:175-195

Streaming Sequence

The sequence diagram illustrates how the stream handles iterative database calls behind a standard stream/iterator interface.
Sources: src/executor/paginator.rs:201-207, src/executor/paginator.rs:292-302

Implementation Examples

Basic Pagination (Async)

// Create paginator with page size of 50
let mut paginator = cake::Entity::find()
    .order_by_asc(cake::Column::Id)
    .paginate(db, 50);

// Get metadata
let num_pages = paginator.num_pages().await?;

// Iterate through pages
while let Some(cakes) = paginator.fetch_and_next().await? {
    // Process Vec<cake::Model>
}

Sources: src/executor/paginator.rs:141-155, tests/paginator_tests.rs:56-60

Streaming (Sync)

In sea-orm-sync, the pagination API remains identical but operates without .await.

let mut cake_stream = cake::Entity::find()
    .order_by_asc(cake::Column::Id)
    .paginate(db, 50)
    .into_stream();

while let Some(cakes) = cake_stream.next() {
    // Process Result<Vec<cake::Model>, DbErr>
}

Sources: sea-orm-sync/src/executor/paginator.rs:159-170, sea-orm-sync/src/docs.rs:136-150

Technical Constraints and Behavior

• Validation: The page_size must not be zero; the system will panic via assert! if zero is provided to paginate.
• Counting Logic: The num_items method wraps the original query in a SELECT COUNT(*) FROM (...) AS sub_query to ensure accuracy even with complex joins or filters.
• Ordering: It is highly recommended to provide an order_by clause when paginating to ensure consistent results across different page fetches.

Sources: src/executor/paginator.rs:265, src/executor/paginator.rs:66-75, src/executor/paginator.rs:36

Summary

The Pagination and Streaming module provides a robust toolkit for data set management. By abstracting SQL-specific pagination logic into the Paginator and offering a unified Stream interface, SeaORM enables developers to build scalable APIs and background processing tasks that handle data efficiently regardless of total volume.