Implementing Database-Backed Pagination in a Lumen API Microservice

The Problem and the Takeaway

When a Lumen API returns thousands of rows in a single response, clients time out, memory spikes, and the database suffers. The fix is to page results at the database level so each request fetches only the slice the caller needs. Lumen ships with Eloquent's paginate() and simplePaginate() methods, which handle the page query parameter, run the necessary COUNT(*) (or skip it), and return a JSON payload the frontend can consume directly. This guide walks through enabling the feature, choosing the right paginator, wiring a controller, and verifying the output.

Prerequisites

• Lumen 8.x or 9.x (the last actively maintained LTS lines). Newer Laravel components may not be compatible; confirm your composer.json locks laravel/lumen-framework to ^8.0 or ^9.0.
• Eloquent enabled in bootstrap/app.php. Uncomment $app->withEloquent(); and ensure $app->register(\Illuminate\Pagination\PaginationServiceProvider::class); is present (it is registered by default when Eloquent is enabled).
• Database connection configured in .env (DB_CONNECTION, DB_DATABASE, credentials) and migrations run so the target table exists.
• Model or query builder ready for the resource you want to paginate (e.g., App\Models\User).

Choosing Between paginate() and simplePaginate()

Both methods accept a perPage integer and read the page query string automatically. The difference is the COUNT(*) query:

Method	Total count	Last page	Links	Best for
paginate(15)	Yes	Yes	First, last, prev, next	Small-to-medium tables where clients need full navigation
simplePaginate(15)	No	No	Only prev/next	Large or heavily filtered tables where COUNT(*) is slow

If your table exceeds a few hundred thousand rows or the query includes expensive joins, start with simplePaginate(). You can switch later without changing the client contract beyond the missing total and last_page fields.

Procedure: Add a Paginated Endpoint

1. Verify service providers

Open bootstrap/app.php and confirm these lines are uncommented:

$app->withEloquent();
$app->register(\Illuminate\Pagination\PaginationServiceProvider::class);

Without withEloquent(), model-based pagination throws BadMethodCallException because the model lacks the newQuery() method.

2. Create or update the controller

In app/Http/Controllers/Api/UserController.php:

<?php

namespace App\Http\Controllers\Api;

use App\Models\User;
use Laravel\Lumen\Routing\Controller;

class UserController extends Controller
{
    public function index()
    {
        $perPage = (int) request('per_page', 15);
        $perPage = max(1, min($perPage, 100)); // clamp

        // Use simplePaginate for large datasets; swap to paginate() if totals are needed
        $users = User::query()
            ->orderBy('id')
            ->simplePaginate($perPage);

        return response()->json($users);
    }
}

Notes:

• request('per_page', 15) lets the client override page size; clamp it to avoid abuse.
• orderBy('id') (or another deterministic column) is required; without it, page boundaries are non-deterministic across requests.
• The paginator instance implements Arrayable and Jsonable, so response()->json() serializes it automatically.

3. Register the route

In routes/web.php (or routes/api.php if you split them):

$router->get('/users', 'App\Http\Controllers\Api\UserController@index');

4. Optional: Custom page parameter name

If your API spec demands page_number instead of page, add a resolver in a service provider's boot() method (e.g., App\Providers\AppServiceProvider):

use Illuminate\Pagination\Paginator;

public function boot()
{
    Paginator::currentPageResolver(function () {
        return request('page_number', 1);
    });
}

This affects all paginators globally; scope it to a single request by calling Paginator::currentPageResolver() inside the controller if needed.

Expected Checks

First request (default page)

GET /users
Accept: application/json

Response should contain:

• data: array of up to per_page user objects
• current_page: 1
• per_page: 15 (or your clamped value)
• next_page_url: URL with ?page=2 (or page_number=2 if customized)
• prev_page_url: null
• With paginate() only: total, last_page, first_page_url, last_page_url

Second page

GET /users?page=2

Verify current_page is 2 and data contains a different slice. The prev_page_url should point back to page 1.

Per-page override

GET /users?per_page=5

Confirm per_page in the response matches 5 and the array length is ≤ 5.

Empty result

GET /users?page=9999

Returns data: [], current_page: 9999, and next_page_url: null. No 404—this is correct paginator behavior.

Performance Considerations and Limitations

• Offset pagination degrades with deep pages. Page 10,000 forces the database to scan and discard 150,000 rows. For infinite-scroll UIs, consider cursor-based pagination (where('id', '>', $lastId)->limit($perPage)) instead.
• COUNT(*) on complex queries. paginate() clones the query and runs select count(*). On tables with millions of rows and non-sargable filters, this can take seconds. simplePaginate() skips the count entirely.
• Version differences. Lumen 8.x uses Laravel 8 pagination classes; Lumen 9.x aligns with Laravel 9. Method signatures (toArray() keys, links() structure) are stable, but always test against your locked version.
• Maintenance status. Lumen is in maintenance mode; new projects should evaluate Laravel Octane, Slim, or API Platform. This guide applies to existing Lumen codebases.

Verification Steps

1. Seed the database with at least 20 test users: php artisan db:seed --class=UserSeeder (run from project root, requires CLI access).
2. Start the dev server: php -S localhost:8000 -t public.
3. Run the checks above with curl or a REST client. Example:

   curl -s 'http://localhost:8000/users?per_page=5' | jq '.current_page, .per_page, .data | length'

   Expect 1, 5, 5.
4. Add a feature test in tests/Feature/PaginationTest.php:

   public function test_user_pagination_returns_expected_structure()
   {
       $this->get('/users?per_page=5')
            ->seeStatusCode(200)
            ->seeJsonStructure([
                'data',
                'current_page',
                'per_page',
                'next_page_url',
                'prev_page_url'
            ]);
   }

   Run with ./vendor/bin/phpunit --filter test_user_pagination_returns_expected_structure.

Recovery Options

If pagination breaks after a dependency update:

1. Check composer.lock for illuminate/pagination version drift. Pin to the version matching your Lumen line (e.g., ^8.0 for Lumen 8).
2. Ensure bootstrap/app.php still loads the pagination provider; a framework upgrade may have overwritten the file.
3. If simplePaginate() returns total unexpectedly, clear config cache: php artisan config:clear (Lumen doesn't cache config by default, but attached Laravel packages might).
4. Rollback: revert the controller to return User::all() temporarily while you debug, but note this removes the protection pagination provides.

Quick Reference

Task	Command / Code
Enable Eloquent	Uncomment $app->withEloquent(); in bootstrap/app.php
Basic pagination	Model::query()->orderBy('id')->paginate(15)
Count-free pagination	Model::query()->orderBy('id')->simplePaginate(15)
Custom page param	Paginator::currentPageResolver(fn() => request('page_number', 1))
Clamp per_page	$perPage = max(1, min((int)request('per_page', 15), 100))