Azure / Azure/data-api-builder

[Enh]: New pagination keywords

Open
#3,198 0 comments 0 reactions 0 assignees View on GitHub
2.2 enhancement graphql mcp-server rest usability
Dominant language
C#
Stars
1.5k
Forks
370
Avg merge
3d 22h
Merged PRs (30d)
9

Description

## Existing, unchanged keywords

* `$first`: return the first N items, similar to SQL `TOP`.
* `$after`: return items after this cursor; not offset-based.

## New keywords

* `$pageSize`: number of records per page
* `$pageNumber`: numeric page index (1-based)

### Default behavior

* `$pageSize` defaults to the configured DAB `page-size` value if not specified.
* `$pageNumber` defaults to `1` if not specified.

### Evaluation precedence

`$after → $pageSize → $pageNumber → $first`

They can all be used together.

## Keywords not being added

I include this section of the specification because these keywords were part of previous conversations and specifications. However, we are not going to add them anymore because they are redundant.

* `$take`: semantically identical to `$first`.
* `$skip`: semantically equivalent to `$pageSize` + `$pageNumber`. For example, `$skip=10` can be expressed as `$pageSize=5` and `$pageNumber=3`. Skip behavior can also be achieved using `$after`, optionally combined with `$first`.

## Examples in TSQL

```sql
-- Example 1: All parameters NULL (no paging applied)

DECLARE @First int = NULL;
DECLARE @After int = NULL;
DECLARE @PageSize int = NULL;
DECLARE @PageNumber int = NULL;

SELECT *
FROM dbo.Actor
ORDER BY Id;
```

```sql
-- Example 2: Page model ($pageSize + $pageNumber)
-- DAB requests PageSize + 1 to determine NextLink

DECLARE @First int = NULL;
DECLARE @After int = NULL;
DECLARE @PageSize int = 5;
DECLARE @PageNumber int = 3;

SELECT *
FROM dbo.Actor
ORDER BY Id
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT (@PageSize + 1) ROWS ONLY;
```

```sql
-- Example 3: $pageSize only (defaults pageNumber = 1)
-- DAB fetches PageSize + 1 for NextLink detection

DECLARE @First int = NULL;
DECLARE @After int = NULL;
DECLARE @PageSize int = 5;
DECLARE @PageNumber int = NULL;

SET @PageNumber = ISNULL(@PageNumber, 1);

SELECT *
FROM dbo.Actor
ORDER BY Id
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT (@PageSize + 1) ROWS ONLY;
```

```sql
-- Example 4: Cursor model ($first only)
-- DAB fetches First + 1 for NextLink detection

DECLARE @First int = 5;
DECLARE @After int = NULL;
DECLARE @PageSize int = NULL;
DECLARE @PageNumber int = NULL;

SELECT TOP (@First + 1) *
FROM dbo.Actor
ORDER BY Id;
```

```sql
-- Example 5: Cursor model ($first + $after)
-- DAB fetches First + 1 for NextLink detection

DECLARE @First int = 5;
DECLARE @After int = 10; -- simplified for this example
DECLARE @PageSize int = NULL;
DECLARE @PageNumber int = NULL;

SELECT TOP (@First + 1) *
FROM dbo.Actor
WHERE Id > @After
ORDER BY Id;
```

```sql
-- Example 6: $after + $pageSize
-- DAB fetches PageSize + 1 for NextLink detection

DECLARE @First int = NULL;
DECLARE @After int = 10; -- simplified for this example
DECLARE @PageSize int = 5;
DECLARE @PageNumber int = NULL;

SELECT TOP (@PageSize + 1) *
FROM dbo.Actor
WHERE Id > @After
ORDER BY Id;
```

```sql
-- Example 7: $first + $pageSize + $pageNumber
-- Page window fetches PageSize + 1
-- Final limiter applies after trimming NextLink row

DECLARE @First int = 2;
DECLARE @After int = NULL;
DECLARE @PageSize int = 5;
DECLARE @PageNumber int = 3;

WITH Paged AS
(
SELECT *
FROM dbo.Actor
ORDER BY Id
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT (@PageSize + 1) ROWS ONLY
)
SELECT TOP (@First)
*
FROM Paged
ORDER BY Id;
```

```sql
-- Example 8: ⛔ Incompatible: $after + $pageNumber

DECLARE @First int = NULL;
DECLARE @After int = 10; -- simplified for this example
DECLARE @PageSize int = NULL;
DECLARE @PageNumber int = 2;

IF @After IS NOT NULL AND @PageNumber IS NOT NULL
THROW 50000, '$after cannot be combined with $pageNumber.', 1;
```

```sql
-- Example 9: ⛔ Invalid: $pageNumber without $pageSize

DECLARE @First int = NULL;
DECLARE @After int = NULL;
DECLARE @PageSize int = NULL;
DECLARE @PageNumber int = 2;

IF @PageNumber IS NOT NULL AND @PageSize IS NULL
THROW 50000, '$pageNumber requires $pageSize.', 1;
```

```sql
-- Example 10: ⛔ Invalid negative values

DECLARE @First int = -5;
DECLARE @After int = NULL;
DECLARE @PageSize int = -10;
DECLARE @PageNumber int = NULL;

IF @First IS NOT NULL AND @First <= 0
THROW 50000, '$first must be greater than zero.', 1;

IF @PageSize IS NOT NULL AND @PageSize <= 0
THROW 50000, '$pageSize must be greater than zero.', 1;
```

Contributor guide

Open the contributing guide

Research direction

Start by tracing how DAB currently evaluates the existing $first and $after keywords and how the configured page-size value is applied. Use the T-SQL examples as behavioral cases, including precedence, defaults, invalid combinations, and negative values. Done means $pageSize and $pageNumber work with the specified combinations while preserving existing keyword behavior and NextLink detection.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
api, backend-api-design, databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.