Shopping cart

Subtotal 0,00 د.إ Subtotal: 0,00 د.إ

View cartCheckout

Filtering, Sorting & Pagination in Prisma

by SanjewaExpress Js
Front
Back
Right
Left
Top
Bottom
PRODUCTION

Production-Grade MySQL Queries with TypeScript

In every real-world application, you’re never fetching all records at once. Users search, filter, sort, and paginate. Get this wrong and your API is slow, expensive, and frustrating. Get it right and you have a professional-grade data layer. This blog covers everything Prisma gives you out of the box for MySQL — and how to build utilities that make it reusable.
WHERE
Prisma's Filter System

Where Clauses

Prisma’s `where` object is deeply composable. Every scalar field gets a full set of filter operators automatically based on its type. prisma.io/docs/reference/api-reference/prisma-client-reference#filter-conditions-and-operators
String Filters
Copy to clipboard
const users = await prisma.user.findMany({
  where: {
    email: { contains: '@gmail.com' },          // MySQL: LIKE '%@gmail.com%'
    name: { startsWith: 'John' },               // MySQL: LIKE 'John%'
    username: { endsWith: '_admin' },           // MySQL: LIKE '%_admin'
    bio: { not: null },                         // MySQL: IS NOT NULL
    role: { in: ['USER', 'MODERATOR'] },        // MySQL: IN (...)
    status: { notIn: ['BANNED', 'SUSPENDED'] }, // MySQL: NOT IN (...)
  },
});
MySQL Tip: MySQL string comparisons are case-insensitive by default with `utf8mb4_unicode_ci` collation. So `contains: 'john'` matches 'John', 'JOHN', 'john'. Add `mode: 'sensitive'` only if your MySQL collation is case-sensitive.
Numeric & Date Filters
Copy to clipboard
const posts = await prisma.post.findMany({
  where: {
    views: { gt: 1000 },            // > 1000
    likes: { gte: 50, lte: 500 },   // BETWEEN 50 AND 500
    createdAt: {
      gte: new Date('2027-01-01'),   // After Jan 1 2027
      lt: new Date('2027-07-01'),
    },
  },
});
LOGICAL
AND, OR, NOT

Logical Operators

Prisma supports full boolean logic with composable operators. prisma.io/docs/reference/api-reference/prisma-client-reference#and

Copy to clipboard
// OR — any condition matches
const users = await prisma.user.findMany({
  where: {
    OR: [
      { role: 'ADMIN' },
      { role: 'SUPERUSER' },
    ],
  },
});

// AND — all conditions must match (also default behavior)
const activeAdmins = await prisma.user.findMany({
  where: {
    AND: [
      { role: 'ADMIN' },
      { active: true },
      { emailVerified: true },
    ],
  },
});

// NOT — exclude matches
const nonDeleted = await prisma.user.findMany({
  where: { NOT: { status: 'DELETED' } },
});

// Nested logic
const complex = await prisma.user.findMany({
  where: {
    AND: [
      { active: true },
      {
        OR: [{ role: 'ADMIN' }, { role: 'MANAGER' }],
      },
    ],
  },
});
SORTING

Sorting with `orderBy`

Sort by any field, in any direction, across multiple fields. Sorting by a non-indexed field causes a full table scan in MySQL. Always add `@@index([fieldUsedInOrderBy])` for fields you sort by frequently. prisma.io/docs/concepts/components/prisma-client/filtering-and-sorting
Copy to clipboard
// Single field sort
const users = await prisma.user.findMany({
  orderBy: { createdAt: 'desc' },
});

// Multi-field sort (priority order)
const posts = await prisma.post.findMany({
  orderBy: [
    { published: 'desc' },  // Published first
    { createdAt: 'desc' },  // Then newest
    { title: 'asc' },       // Then alphabetically
  ],
});

// Sort by relation field (generates JOIN in MySQL)
const posts = await prisma.post.findMany({
  orderBy: { author: { name: 'asc' } },
  include: { author: { select: { name: true } } },
});
PAGINATION
`skip` + `take`

Offset Pagination

The simplest form of pagination — skip N records, take M. Offset pagination degrades at scale. On page 1000 with `limit=10`, MySQL scans and discards 9,990 rows before returning 10. Use cursor pagination for feeds or large datasets.

Copy to clipboard
// GET /posts?page=2&limit=10
const page = Number(req.query.page) || 1;
const limit = Math.min(Number(req.query.limit) || 10, 100); // Cap at 100
const skip = (page - 1) * limit;

const [posts, total] = await Promise.all([
  prisma.post.findMany({
    skip,
    take: limit,
    orderBy: { createdAt: 'desc' },
    select: { id: true, title: true, createdAt: true },
  }),
  prisma.post.count(), // MySQL: SELECT COUNT(*) FROM posts
]);

res.json({
  data: posts,
  meta: {
    total,
    page,
    limit,
    totalPages: Math.ceil(total / limit),
    hasNextPage: page < Math.ceil(total / limit),
    hasPrevPage: page > 1,
  },
});
PRODUCTION
Production Grade

Cursor-Based Pagination

Cursor pagination is O(1) regardless of page depth — perfect for infinite scroll, timelines, and large MySQL tables. prisma.io/docs/concepts/components/prisma-client/pagination#cursor-based-pagination

Copy to clipboard
// GET /posts?cursor=50&limit=10
const cursor = req.query.cursor ? Number(req.query.cursor) : undefined;
const limit = 10;

const posts = await prisma.post.findMany({
  take: limit + 1,           // Fetch one extra to detect hasNextPage
  ...(cursor && {
    cursor: { id: cursor },
    skip: 1,                 // Skip the cursor record itself
  }),
  orderBy: { id: 'asc' },   // Must match cursor field
  select: { id: true, title: true, createdAt: true },
});

const hasNextPage = posts.length > limit;
const items = hasNextPage ? posts.slice(0, limit) : posts;
const nextCursor = hasNextPage ? items[items.length - 1].id : null;

res.json({
  data: items,
  nextCursor,
  hasNextPage,
});
REUSABLE

Reusable Pagination Utility in TypeScript

Avoid duplicating pagination logic in every route:
Step 01: Seeding data
Update your prisma/seed.ts and run it `npm run seed`
Copy to clipboard
// prisma/seed.ts
import { PrismaClient } from '../src/generated/prisma';
import { PrismaMariaDb } from '@prisma/adapter-mariadb';
import dotenv from 'dotenv';

dotenv.config();

const adapter = new PrismaMariaDb({
    host: process.env.DATABASE_HOST!,
    user: process.env.DATABASE_USER!,
    password: process.env.DATABASE_PASSWORD!,
    database: process.env.DATABASE_NAME!,
    connectionLimit: 5,
});

const prisma = new PrismaClient({ adapter });

async function main() {
    console.log('Seeding database...');

    // Upsert to avoid duplicate errors on repeated seed runs
    for (let i = 1; i <= 100; i++) {
        await prisma.user.upsert({
            where: { email: `user${i}@example.com` },
            update: {},
            create: {
                email: `user${i}@example.com`,
                name: `User Number ${i}`,
                posts: {
                    create: [
                        { title: `Post A by user ${i}`, published: true },
                        { title: `Post B by user ${i}`, published: false },
                    ],
                },
            },
        });
    }

    console.log('Seeded successfully!');
}

main()
    .catch((e) => {
        console.error('Seed failed:', e);
        process.exit(1);
    })
    .finally(async () => {
        await prisma.$disconnect();
    });
Step 02: Create reusable pagination utility
Copy to clipboard
// src/utils/paginate.ts
interface PaginationParams {
  page?: number;
  limit?: number;
}

interface PaginatedResult<T> {
  data: T[];
  meta: {
    total: number;
    page: number;
    limit: number;
    totalPages: number;
    hasNextPage: boolean;
    hasPrevPage: boolean;
  };
}

export async function paginate<T>(
  model: any,
  args: any,
  { page = 1, limit = 10 }: PaginationParams
): Promise<PaginatedResult<T>> {
  const safePage = Math.max(1, page);
  const safeLimit = Math.min(Math.max(1, limit), 100);
  const skip = (safePage - 1) * safeLimit;

  const [data, total] = await Promise.all([
    model.findMany({ ...args, skip, take: safeLimit }),
    model.count({ where: args.where }),
  ]);

  const totalPages = Math.ceil(total / safeLimit);

  return {
    data,
    meta: {
      total,
      page: safePage,
      limit: safeLimit,
      totalPages,
      hasNextPage: safePage < totalPages,
      hasPrevPage: safePage > 1,
    },
  };
}
Step 03: Update your route
Copy to clipboard
// src/routes/users.ts
import { paginate } from '../utils/paginate';

router.get('/', async (req: Request, res: Response, next: NextFunction) => {
    try {
        const page  = parseInt(req.query.page as string)  || 1;
        const limit = parseInt(req.query.limit as string) || 10;

        const result = await paginate(
            prisma.user,
            {
                select: { id: true, email: true, name: true, createdAt: true },
                orderBy: { createdAt: 'desc' },
            },
            { page, limit }
        );

        res.json(result);
    } catch (err) {
        next(err);
    }
});
Step 04: Enjoy
Copy to clipboard
# Page 1 (first 10 users)
GET http://localhost:3000/users

# Page 2 with 10 per page
GET http://localhost:3000/users?page=2&limit=10

# Page 1 with 20 per page
GET http://localhost:3000/users?page=1&limit=20
SEARCH

Full-Text Search in MySQL

MySQL has native full-text search. Enable it in Prisma.

MySQL full-text search requires `previewFeatures = ["fullTextSearch"]` in schema. prisma.io/docs/concepts/components/prisma-client/full-text-search
Step 03: Update your route
Copy to clipboard
// schema.prisma — enable preview feature
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["fullTextSearch"]
}

model Post {
  id      Int    @id @default(autoincrement())
  title   String @db.VarChar(255)
  content String @db.Text

  @@fulltext([title, content])  // MySQL FULLTEXT index
  @@map("posts")
}
Step 04: Enjoy
Copy to clipboard
const posts = await prisma.post.findMany({
  where: {
    // MySQL MATCH AGAINST full-text search
    title: { search: 'prisma typescript' },
    // OR across multiple fields:
    content: { search: 'prisma typescript' },
  },
  orderBy: { _relevance: { fields: ['title', 'content'],
    search: 'prisma typescript', sort: 'desc' } },
});

Explore project snapshots or discuss custom web solutions.

More About Me

Premature optimization is the root of all evil, but that doesn't mean you should ignore obvious performance problems.

Donald Knuth Structured Programming with goto Statements

Thank You for Spending Your Valuable Time

I truly appreciate you taking the time to read blog. Your valuable time means a lot to me, and I hope you found the content insightful and engaging!
Front
Back
Right
Left
Top
Bottom
FAQ's

Frequently Asked Questions

Offset pagination (`skip/take`) requires MySQL to scan and discard all skipped rows — O(n) cost that grows with offset size. Cursor pagination uses a `WHERE id > cursor` index seek — O(1) cost regardless of position. For datasets > 10k records, always use cursor pagination.

MySQL with `utf8mb4_unicode_ci` collation is case-insensitive by default. For case-sensitive search, either change your column collation to `utf8mb4_bin`, or use a raw query: `prisma.$queryRaw` with `BINARY` keyword.

Build a dynamic `where` object: `const where = query ? { OR: [{ title: { contains: query } }, { content: { contains: query } }] } : {}`. Pass this to any `findMany` call.

Dramatically disseminate real-time portals rather than top-line action items. Uniquely provide access to low-risk high-yield products without dynamic products. Progressively re-engineer low-risk high-yield ideas rather than emerging alignments.

Cap it in your utility: `const limit = Math.min(Number(req.query.limit) || 10, 100)`. This prevents `limit=99999` from hammering your MySQL server.

Blogs

Related Blogs

Comments are closed