Pagination

FraiseQL supports two pagination modes:

• Offset — limit / offset, simple, good for small datasets
• Relay — cursor-based, spec-compliant, stable at scale

Both are enabled with a single flag on @fraiseql.query(). No manual Connection/Edge/PageInfo types to define.

---

Offset Pagination

The default for all list queries. limit, offset, where, and orderBy are automatically available on any query that returns list[T]:

@fraiseql.query(sql_source="v_post")
def posts() -> list[Post]:
    pass

That’s it. The compiled schema exposes:

posts(
  where: PostWhereInput
  orderBy: [PostOrderByInput!]
  limit: Int
  offset: Int
): [Post!]!

Usage

# Page 1
query { posts(limit: 20, offset: 0) { id title } }

# Page 2
query { posts(limit: 20, offset: 20) { id title } }

# Filtered and sorted
query {
  posts(
    where: { is_published: { _eq: true } }
    orderBy: [{ created_at: DESC }]
    limit: 10
  ) { id title created_at }
}

Opt out of auto params

If you don’t want any of the four automatic arguments:

@fraiseql.query(sql_source="v_post", auto_params=False)
def posts() -> list[Post]:
    pass

Opt out of auto params globally

If your project uses Relay pagination exclusively and you never want limit/offset on list queries, set project-wide defaults in fraiseql.toml instead of opting out per-query:

[query_defaults]
limit  = false
offset = false

Individual queries can still re-enable specific params:

# Re-enables limit/offset for this one admin query only
@fraiseql.query(sql_source="v_admin_log", auto_params={"limit": True, "offset": True})
def admin_logs() -> list[AdminLog]: ...

Offset limitations

Large offsets require PostgreSQL to scan and discard rows:

-- Must scan 1,000,000 rows to return 20
SELECT data FROM v_post OFFSET 1000000 LIMIT 20

For datasets above ~100k rows, use Relay pagination instead.

---

Relay Pagination

Relay pagination is opt-in with relay=True. It replaces limit/offset with first/after/last/before, generates the standard Connection, Edge, and PageInfo types automatically, and adds a global node(id: ID!) lookup — all without any extra type definitions from you.

Requirements

Relay pagination relies on the FraiseQL CQRS naming convention:

-- Write table
CREATE TABLE tb_post (
  pk_post    BIGINT  PRIMARY KEY,   -- sequential, internal, never in GraphQL
  id         UUID    UNIQUE NOT NULL, -- public identifier for node(id: ID!)
  -- ... other columns
);

-- Read view (used by FraiseQL)
CREATE VIEW v_post AS
  SELECT jsonb_build_object(
    'pk_post', pk_post,   -- included so FraiseQL can build cursors
    'id',      id,
    'title',   title
    -- ...
  ) AS data
  FROM tb_post;

pk_post drives keyset pagination — it stays in data JSONB for cursor generation, but is always stripped before being returned to the client.

fraiseql compile validates this at build time: missing pk_{entity} in the view is a hard error, not a runtime surprise.

Enabling

Mark the type and the query:

@fraiseql.type(relay=True)
class Post:
    id: UUID
    title: str
    created_at: datetime

@fraiseql.query(sql_source="v_post", relay=True)
def posts() -> list[Post]:
    pass

That produces:

interface Node {
  id: ID!
}

type Post implements Node {
  id: ID!
  title: String!
  createdAt: DateTime!
}

type PostEdge {
  node: Post!
  cursor: String!
}

type PostConnection {
  edges: [PostEdge!]!
  pageInfo: PageInfo!
  totalCount: Int
}

type PageInfo {
  hasNextPage:     Boolean!
  hasPreviousPage: Boolean!
  startCursor:     String
  endCursor:       String
}

type Query {
  posts(first: Int, after: String, last: Int, before: String): PostConnection!
  node(id: ID!): Node
}

All synthetic types are generated once per type — multiple relay queries on the same type share the same PostConnection.

Forward pagination

# First page
query {
  posts(first: 20) {
    edges {
      node { id title }
      cursor
    }
    pageInfo { hasNextPage endCursor }
  }
}

# Next page
query {
  posts(first: 20, after: "NDI=") {
    edges {
      node { id title }
      cursor
    }
    pageInfo { hasNextPage endCursor }
  }
}

Backward pagination

query {
  posts(last: 20) {
    edges {
      node { id title }
    }
    pageInfo { hasPreviousPage startCursor }
  }
}

query {
  posts(last: 20, before: "MjE=") {
    edges {
      node { id title }
    }
    pageInfo { hasPreviousPage startCursor }
  }
}

Node lookup

node(id: ID!) is automatically available whenever at least one type uses relay=True:

query {
  node(id: "UG9zdDo1NTBl...") {
    ... on Post { id title }
    ... on User { id name }
  }
}

The global ID encodes the type name and UUID (base64("Post:uuid-value")). The cursor is a separate encoding of the internal pk_post column — either a BIGINT (base64("42")) or UUID (base64("550e…")), depending on the column type.

Note

pk_post is never returned to clients. FraiseQL reads it from the data JSONB to construct cursors, then strips it from every edge’s node before serialization.

UUID primary keys

The cursor column (pk_{entity}) can be either BIGINT or UUID. FraiseQL detects the column type at compile time and selects the appropriate cursor encoding automatically.

-- BIGINT pk (original, most common)
pk_post BIGINT PRIMARY KEY

-- UUID pk (now also supported)
pk_post UUID PRIMARY KEY DEFAULT gen_random_uuid()

Both produce opaque base64 cursors on the wire — clients handle them identically. The encoding differs internally: BIGINT cursors encode a decimal string, UUID cursors encode the UUID string.

totalCount

PostConnection (and all generated Connection types) include a nullable totalCount: Int field. It returns the total number of rows matching the current where filter, ignoring cursor position:

query {
  posts(first: 20, where: { is_published: { _eq: true } }) {
    totalCount        # total matching rows, e.g. 847
    edges {
      node { id title }
    }
    pageInfo { hasNextPage endCursor }
  }
}

totalCount runs a separate COUNT(*) query. Omit it from your selection if you don’t need it to avoid the extra round-trip.

How cursors work

Value	Encoding	Purpose
pk_post = 42 (BIGINT)	base64("42") → "NDI="	Cursor (after / before)
pk_post = "550e…" (UUID)	base64("550e…")	Cursor (after / before)
id = "550e…"	base64("Post:550e…")	Global node ID

Keyset pagination on the primary key column means:

• WHERE pk_post > 42 ORDER BY pk_post ASC LIMIT 21 (forward)
• WHERE pk_post < 42 ORDER BY pk_post DESC LIMIT 21 (backward, reversed in Rust)

The +1 trick detects hasNextPage / hasPreviousPage without a COUNT query.

Client examples

Relay

import { usePaginationFragment } from 'react-relay';

function PostList({ queryRef }) {
  const { data, loadNext, hasNext } = usePaginationFragment(
    graphql`
      fragment PostList_query on Query
      @refetchable(queryName: "PostListPaginationQuery") {
        posts(first: 20, after: $after) @connection(key: "PostList_posts") {
          edges { node { id title } }
          pageInfo { hasNextPage endCursor }
        }
      }
    `,
    queryRef
  );

  return (
    <>
      {data.posts.edges.map(({ node }) => (
        <div key={node.id}>{node.title}</div>
      ))}
      {hasNext && <button onClick={() => loadNext(20)}>Load more</button>}
    </>
  );
}

Apollo Client

const POSTS_QUERY = gql`
  query GetPosts($first: Int, $after: String) {
    posts(first: $first, after: $after) {
      edges { node { id title } cursor }
      pageInfo { hasNextPage endCursor }
    }
  }
`;

function Posts() {
  const { data, fetchMore } = useQuery(POSTS_QUERY, { variables: { first: 20 } });

  return (
    <>
      {data?.posts.edges.map(({ node }) => (
        <div key={node.id}>{node.title}</div>
      ))}
      {data?.posts.pageInfo.hasNextPage && (
        <button onClick={() => fetchMore({
          variables: { after: data.posts.pageInfo.endCursor }
        })}>
          Load more
        </button>
      )}
    </>
  );
}

Relay vs offset comparison

	Offset	Relay (keyset)
Performance at 1M+ rows	Degrades (OFFSET scans)	Consistent O(limit)
Stable under concurrent inserts	No — rows shift	Yes — keyset unaffected
Client cache support	Manual	Automatic with Relay
Bidirectional	No	Yes (last/before)
Global object lookup	No	node(id: ID!)
Setup	Zero (auto_params)	relay=True on type + query

---

Ordering

orderBy is available on offset queries automatically. For relay queries, the sort order is fixed to pk_{entity} ASC (forward) or pk_{entity} DESC (backward) — relay keyset pagination does not support custom sort columns in the current release.

# Offset query with custom sort
query {
  posts(orderBy: [{ created_at: DESC }], limit: 20) {
    id title created_at
  }
}

Generated input type:

input PostOrderByInput {
  id:         OrderDirection
  title:      OrderDirection
  created_at: OrderDirection
}

enum OrderDirection {
  ASC
  DESC
  ASC_NULLS_FIRST
  ASC_NULLS_LAST
  DESC_NULLS_FIRST
  DESC_NULLS_LAST
}

---

Indexing

-- Relay: index on pk_{entity} (likely already the PK clustered index)
CREATE INDEX idx_post_pk ON tb_post(pk_post);

-- Offset with custom ordering
CREATE INDEX idx_post_created ON tb_post(created_at DESC);

-- Filtered + ordered offset queries
CREATE INDEX idx_post_published_created
  ON tb_post(is_published, created_at DESC)
  WHERE is_published = true;

---

Troubleshooting

Relay compile error: missing pk_{entity}

error: relay=True on query "posts" but pk_post not found in view v_post

Add pk_post to the view’s JSONB output — FraiseQL needs it to generate cursors.

relay=True on a non-list query

error: relay=True requires a list return type; "post() -> Post" is not a list

Relay only applies to list queries. Single-item lookups use node(id: ID!) instead.

node(id: ID!) returns null

Verify the global ID was generated by FraiseQL — it encodes TypeName:uuid. Manually constructed IDs or IDs from an offset-pagination query won’t work.

---

Filtering

Automatic Where — where input and operators

Performance

Performance — query optimization