limit / offset

limit and offset are methods for limiting the number of query results and implementing pagination. These are essential methods for performance optimization and implementing paging functionality.

Basic Usage

limit

Limits the maximum number of results to return.

const users = await puri.table("users")
  .select({ id: "users.id", name: "users.name" })
  .limit(10);

// SELECT users.id, users.name FROM users LIMIT 10

offset

Skips a specified number of results.

const users = await puri.table("users")
  .select({ id: "users.id", name: "users.name" })
  .offset(20)
  .limit(10);

// SELECT users.id, users.name FROM users LIMIT 10 OFFSET 20

offset is typically used with limit for pagination.

Pagination

Basic Pagination

// Page 1: Items 1-10
const page1 = await puri.table("posts")
  .select({ id: "posts.id", title: "posts.title" })
  .orderBy("posts.created_at", "desc")
  .limit(10)
  .offset(0);

// Page 2: Items 11-20
const page2 = await puri.table("posts")
  .select({ id: "posts.id", title: "posts.title" })
  .orderBy("posts.created_at", "desc")
  .limit(10)
  .offset(10);

// Page 3: Items 21-30
const page3 = await puri.table("posts")
  .select({ id: "posts.id", title: "posts.title" })
  .orderBy("posts.created_at", "desc")
  .limit(10)
  .offset(20);

Pagination Function

function paginate(page: number, perPage: number) {
  return {
    limit: perPage,
    offset: (page - 1) * perPage
  };
}

// Get page 3 with 20 items per page
const { limit, offset } = paginate(3, 20);

const posts = await puri.table("posts")
  .select({ id: "posts.id", title: "posts.title" })
  .orderBy("posts.created_at", "desc")
  .limit(limit)
  .offset(offset);

// Returns items 41-60

Cursor-Based Pagination

Cursor-based pagination is more efficient for large datasets than offset-based pagination.

Basic Cursor Pagination

// First page: Latest 10 posts
const firstPage = await puri.table("posts")
  .select({ id: "posts.id", title: "posts.title", created_at: "posts.created_at" })
  .orderBy("posts.created_at", "desc")
  .limit(10);

// Get last item's cursor
const lastPost = firstPage[firstPage.length - 1];
const cursor = lastPost.created_at;

// Next page: 10 items after cursor
const nextPage = await puri.table("posts")
  .select({ id: "posts.id", title: "posts.title", created_at: "posts.created_at" })
  .where("posts.created_at", "<", cursor)
  .orderBy("posts.created_at", "desc")
  .limit(10);

Cursor Pagination with ID

// First page
const firstPage = await puri.table("users")
  .select({ id: "users.id", name: "users.name" })
  .orderBy("users.id", "asc")
  .limit(20);

// Next page cursor
const lastUserId = firstPage[firstPage.length - 1].id;

// Next page
const nextPage = await puri.table("users")
  .select({ id: "users.id", name: "users.name" })
  .where("users.id", ">", lastUserId)
  .orderBy("users.id", "asc")
  .limit(20);

Practical Examples

Infinite Scroll
Data Table
Batch Processing
Latest Items

// Component state
let lastCursor: Date | null = null;
let allPosts: Post[] = [];

async function loadMore() {
  let query = puri.table("posts")
    .select({
      id: "posts.id",
      title: "posts.title",
      created_at: "posts.created_at"
    })
    .orderBy("posts.created_at", "desc")
    .limit(20);

  // Add cursor condition after first load
  if (lastCursor) {
    query = query.where("posts.created_at", "<", lastCursor);
  }

  const posts = await query;

  // Update state
  allPosts = [...allPosts, ...posts];
  lastCursor = posts[posts.length - 1]?.created_at || null;

  return posts;
}

// Load first page
await loadMore();

// Load on scroll
window.addEventListener("scroll", async () => {
  if (isNearBottom()) {
    await loadMore();
  }
});

interface PaginationParams {
  page: number;      // Current page (1-indexed)
  perPage: number;   // Items per page
}

async function getUsers(params: PaginationParams) {
  const { page, perPage } = params;
  const offset = (page - 1) * perPage;

  // Get data
  const users = await puri.table("users")
    .select({
      id: "users.id",
      name: "users.name",
      email: "users.email",
      created_at: "users.created_at"
    })
    .orderBy("users.created_at", "desc")
    .limit(perPage)
    .offset(offset);

  // Get total count
  const { count } = await puri.table("users")
    .select({ count: Puri.count() })
    .first();

  return {
    data: users,
    pagination: {
      page,
      perPage,
      total: count,
      totalPages: Math.ceil(count / perPage)
    }
  };
}

// Example usage
const result = await getUsers({ page: 2, perPage: 25 });
console.log(result);
// {
//   data: [...25 users...],
//   pagination: { page: 2, perPage: 25, total: 1000, totalPages: 40 }
// }

async function processAllUsers() {
  const batchSize = 100;
  let offset = 0;
  let hasMore = true;

  while (hasMore) {
    // Fetch batch
    const users = await puri.table("users")
      .select({ id: "users.id", email: "users.email" })
      .limit(batchSize)
      .offset(offset);

    // Process batch
    for (const user of users) {
      await sendEmail(user.email);
    }

    // Check if more data exists
    hasMore = users.length === batchSize;
    offset += batchSize;

    console.log(`Processed ${offset} users`);
  }
}

// With cursor for better performance
async function processAllUsersCursor() {
  const batchSize = 100;
  let lastId = 0;
  let hasMore = true;

  while (hasMore) {
    const users = await puri.table("users")
      .select({ id: "users.id", email: "users.email" })
      .where("users.id", ">", lastId)
      .orderBy("users.id", "asc")
      .limit(batchSize);

    for (const user of users) {
      await sendEmail(user.email);
    }

    hasMore = users.length === batchSize;
    lastId = users[users.length - 1]?.id || lastId;
  }
}

// Latest 10 posts
const latestPosts = await puri.table("posts")
  .select({
    id: "posts.id",
    title: "posts.title",
    created_at: "posts.created_at"
  })
  .orderBy("posts.created_at", "desc")
  .limit(10);

// Top 5 bestsellers
const topProducts = await puri.table("products")
  .select({
    id: "products.id",
    name: "products.name",
    sales: "products.sales"
  })
  .orderBy("products.sales", "desc")
  .limit(5);

// Recently active users
const activeUsers = await puri.table("users")
  .select({
    id: "users.id",
    name: "users.name",
    last_active: "users.last_active"
  })
  .orderBy("users.last_active", "desc")
  .limit(20);

Performance Optimization

1. offset is Slow for Large Datasets

// ❌ Bad: Offset scans all skipped rows
await puri.table("posts")
  .limit(10)
  .offset(10000);  // Scans 10,000 rows then skips them

// ✅ Good: Use cursor-based pagination
await puri.table("posts")
  .where("posts.id", ">", lastId)
  .limit(10);  // Directly finds target rows

2. Use with orderBy

// ⚠️ Caution: Results may be inconsistent without ordering
await puri.table("users")
  .limit(10)
  .offset(20);

// ✅ Good: Guarantee consistent ordering
await puri.table("users")
  .orderBy("users.id", "asc")
  .limit(10)
  .offset(20);

3. Optimize COUNT Queries

// ❌ Bad: Separate queries for data and count
const users = await puri.table("users")
  .limit(10)
  .offset(20);

const { count } = await puri.table("users")
  .select({ count: Puri.count() })
  .first();

// ✅ Good: Use findMany that combines both
const { rows, total } = await UserModel.findMany("A", {
  page: 3,
  num: 10
});
// One query returns both data and total

offset vs Cursor

offset Method

Advantages:

Simple implementation
Can jump to any page
Suitable for numbered pages

Disadvantages:

Slow for large offsets
Inconsistent results when data changes
Always scans from beginning

// offset: Suitable for page numbers
<button onClick={() => goToPage(5)}>Page 5</button>

Cursor Method

Advantages:

Fast regardless of position
Consistent results
Efficient memory usage

Disadvantages:

Cannot jump to arbitrary pages
Slightly complex implementation
Cursor management needed

// Cursor: Suitable for infinite scroll
<button onClick={() => loadMore()}>Load More</button>

Cautions

1. Missing orderBy

// ❌ Dangerous: Inconsistent order
await puri.table("users")
  .limit(10);

// ✅ Safe: Explicit ordering
await puri.table("users")
  .orderBy("users.id", "asc")
  .limit(10);

2. Large offset

// ❌ Very slow
await puri.table("posts")
  .limit(10)
  .offset(1000000);

// ✅ Fast
await puri.table("posts")
  .where("posts.id", ">", lastId)
  .limit(10);

3. limit Without WHERE

// ⚠️ Caution: Returns ALL data without limit
const allUsers = await puri.table("users").selectAll();

// ✅ Safe: Always use limit
const users = await puri.table("users")
  .selectAll()
  .limit(1000);

4. Pagination and Data Changes

// Problem: Items may appear twice or be skipped
// when data changes between page loads

// Solution 1: Use cursor pagination
await puri.table("posts")
  .where("posts.id", ">", lastId)
  .limit(10);

// Solution 2: Use timestamp
await puri.table("posts")
  .where("posts.created_at", "<=", snapshotTime)
  .limit(10)
  .offset(20);

Type Safety

Type safety is maintained when using limit and offset.

const users = await puri.table("users")
  .select({ id: "users.id", name: "users.name" })
  .limit(10)
  .offset(20);

// Type: Array<{ id: number; name: string }>
users[0].id;    // number
users[0].name;  // string

Next Steps

order-by

Sort results

where

Filter with conditions

select

Select fields to query

advanced-methods

Advanced query methods

Get Started

Core Concepts

Database

API Development

Frontend Integration

Testing

Advanced Features

Tools & CLI

Configuration

API Reference

Troubleshooting

FAQ

Basic Usage

limit

offset

Practical Examples

Performance Optimization

1. offset is Slow for Large Datasets

2. Use with orderBy

3. Optimize COUNT Queries

offset vs Cursor

offset Method

Cursor Method

Cautions

1. Missing orderBy

2. Large offset

3. limit Without WHERE

Type Safety

Next Steps

order-by

where

select

advanced-methods

Get Started

Core Concepts

Database

API Development

Frontend Integration

Testing

Advanced Features

Tools & CLI

Configuration

API Reference

Troubleshooting

FAQ

​Basic Usage

​limit

​offset

​Pagination

​Basic Pagination

​Pagination Function

​Cursor-Based Pagination

​Basic Cursor Pagination

​Cursor Pagination with ID

​Practical Examples

​Performance Optimization

​1. offset is Slow for Large Datasets

​2. Use with orderBy

​3. Optimize COUNT Queries

​offset vs Cursor

​offset Method

​Cursor Method

​Cautions

​1. Missing orderBy

​2. Large offset

​3. limit Without WHERE

​4. Pagination and Data Changes

​Type Safety

​Next Steps

order-by

where

select

advanced-methods

Basic Usage

limit

offset

Pagination

Basic Pagination

Pagination Function

Cursor-Based Pagination

Basic Cursor Pagination

Cursor Pagination with ID

Practical Examples

Performance Optimization

1. offset is Slow for Large Datasets

2. Use with orderBy

3. Optimize COUNT Queries

offset vs Cursor

offset Method

Cursor Method

Cautions

1. Missing orderBy

2. Large offset

3. limit Without WHERE

4. Pagination and Data Changes

Type Safety

Next Steps