In this task, you will implement sorting, searching, filtering, and pagination for the Posts API to allow clients to retrieve posts based on specific criteria and requirements.
When working with large datasets, it’s essential to provide mechanisms for users to find the information they need efficiently. Sorting allows users to order the results based on a specific field, searching enables users to find posts containing specific keywords, and filtering allows users to narrow down the results based on various criteria.
Pagination is a common technique used to break up a large set of data into smaller chunks or pages. This allows clients to retrieve data incrementally and reduces the load on the server and client.
To test our new features effectively, we need a larger dataset with more varied content. Let’s update our seeding script to generate 100 posts with different dates and common keywords.
Update the seed.ts file in the db folder:
import { db, connection } from "./index";
import { posts } from "./schema";
import { faker } from "@faker-js/faker";
import { sql } from "drizzle-orm";
async function seed() {
console.log("Seeding the database...");
// Clean the tables
console.log("Cleaning existing data...");
await db.delete(posts);
// Reset the auto-increment counters
await db.run(sql`DELETE FROM sqlite_sequence WHERE name IN ('posts')`);
console.log("Inserting new seed data...");
const sampleKeywords = [
"technology",
"innovation",
"design",
"development",
"programming",
"software",
"hardware",
"AI",
"machine learning",
"data science",
"cloud computing",
"cybersecurity",
];
// Insert 100 sample posts
for (let i = 0; i < 100; i++) {
const randomKeywords = faker.helpers.arrayElements(sampleKeywords, {
min: 1,
max: 3,
});
const content = `${faker.lorem.sentence({ min: 10, max: 200 })} ${randomKeywords.join(" ")}`;
await db.insert(posts).values({
content,
date: faker.date.recent({
days: 5, // The range of days the date may be in the past.
}), // Generates a random date in the recent past.
});
}
console.log("Seeding completed successfully.");
}
seed()
.catch((e) => {
console.error("Seeding failed:");
console.error(e);
})
.finally(() => {
connection.close();
});Make sure to install the Faker.js library:
pnpm install @faker-js/fakerThis script will generate 100 posts with random dates in the past and content that includes some common tech-related keywords.
Now, let’s update the GET /posts route to allow sorting by date. We’ll use a query parameter sort to specify the sorting order.
Update the route in src/routes/posts.ts:
import { desc, asc } from "drizzle-orm";
// Get all posts with optional sorting
postRoutes.get("/posts", async (c) => {
const sort = c.req.query("sort");
let query = db.select().from(posts).$dynamic();
if (sort === "asc") {
query = query.orderBy(asc(posts.date));
} else if (sort === "desc") {
query = query.orderBy(desc(posts.date));
}
const allPosts = await query;
return c.json(allPosts);
});Now, clients can sort posts by date using /posts?sort=asc or /posts?sort=desc.
In the code above:
sort query parameter from the request.db.select().from(posts).$dynamic() to allow for conditional modifications.sort parameter is set to asc, we order the posts by date in ascending order using query.orderBy(asc(posts.date)).sort parameter is set to desc, we order the posts by date in descending order using query.orderBy(desc(posts.date)).sort parameter is not provided or is invalid, the posts will be returned in their default order.Notice the $dynamic() method used to create a dynamic query. This method allows us to modify the query based on conditions like sorting, filtering, or pagination.
Moreover, the asc and desc functions from Drizzle ORM are used to specify the sorting order. These functions generate the corresponding SQL syntax for ordering by a column in ascending or descending order.
Run the server and test the sorting functionality using Postman.
Let’s add a search functionality to find posts containing specific keywords. We’ll use a query parameter search for this.
Update the GET /posts route:
import { like } from "drizzle-orm";
// Get all posts with optional sorting, filtering, and searching
postRoutes.get("/posts", async (c) => {
const sort = c.req.query("sort");
const search = c.req.query("search");
let query = db.select().from(posts).$dynamic();
if (search) {
query = query.where(like(posts.content, `%${search}%`)).$dynamic();
}
if (sort === "asc") {
query = query.orderBy(asc(posts.date));
} else if (sort === "desc") {
query = query.orderBy(desc(posts.date));
}
const allPosts = await query;
return c.json(allPosts);
});Now, clients can search for posts using /posts?search=keyword.
In the code above:
search query parameter from the request.db.select().from(posts).$dynamic() to allow for conditional modifications.search parameter is provided, we add a condition to the query to filter posts where the content contains the search keyword.like function from Drizzle ORM to perform a case-insensitive search for the keyword in the content column.% symbols are used as wildcards to match any characters before and after the keyword.Run the server and test the search functionality using Postman.
Similar to searching, one can filter posts based on specific criteria. Currently, our posts don’t have any filterable fields, but you can add fields like author, category, or tags to the posts table and filter based on those fields. The implementation is similar to searching, where you add conditions to the query based on the filter criteria.
Finally, let’s implement pagination to limit the number of posts returned in a single request. We’ll use query parameters page and limit for this.
Update the GET /posts route one last time:
import { eq, asc, desc, like, count, SQL, and } from "drizzle-orm";
// Get all posts with optional sorting, filtering, searching, and pagination
postRoutes.get("/posts", zValidator("query", queryParamsSchema), async (c) => {
const { sort, search, page = 1, limit = 10 } = c.req.valid("query");
const whereClause: (SQL | undefined)[] = [];
if (search) {
whereClause.push(like(posts.content, `%${search}%`));
}
const orderByClause: SQL[] = [];
if (sort === "desc") {
orderByClause.push(desc(posts.date));
} else if (sort === "asc") {
orderByClause.push(asc(posts.date));
}
const offset = (page - 1) * limit;
const [allPosts, [{ totalCount }]] = await Promise.all([
db
.select()
.from(posts)
.where(and(...whereClause))
.orderBy(...orderByClause)
.limit(limit)
.offset(offset),
db
.select({ totalCount: count() })
.from(posts)
.where(and(...whereClause)),
]);
return c.json({
posts: allPosts,
page,
limit,
total: totalCount,
});
});Now, clients can paginate through posts using /posts?page=2&limit=5.
In the code above:
page and limit query parameters from the request. The page parameter specifies the page number, and the limit parameter specifies the number of posts per page.offset based on the page number and limit to determine the starting index of the posts to retrieve.limit clause to the query to restrict the number of posts returned per page.offset clause to the query to skip the first offset posts.posts table. The select({ totalCount: count() }) syntax is used to perform a count query. The SQL statement corresponding to this query is SELECT count(*) FROM posts.Notice that we dynamically construct the where and orderBy clauses based on the provided query parameters. This approach allows us to build complex queries with conditional filters, sorting, and pagination. The and function from Drizzle ORM is used to combine multiple conditions in the where clause.
Moreover, we use Promise.all to execute two queries in parallel: one to fetch the paginated posts and another to count the total number of posts. This approach improves performance by reducing the time taken to fetch the data.
Run the server and test the pagination functionality using Postman.
Similar to the previous tasks, you should add input validation for the query parameters sort, today, search, page, and limit to ensure that the values provided are valid and expected. You can use the Zod schemas to validate these query parameters.
export const queryParamsSchema = z.object({
sort: z.enum(["asc", "desc"]).optional(),
search: z.string().optional(),
page: z.coerce.number().int().positive().optional(),
limit: z.coerce.number().int().positive().optional(),
});You can then use this schema to validate the query parameters in the route handler:
// Get all posts
postRoutes.get(
"/posts",
zValidator("query", queryParamsSchema),
async (c) => {
const { sort, search, page = 1, limit = 10 } = c.req.valid("query");
// The rest of the route handler code
}
);In this task, you’ve implemented sorting, searching, filtering, and pagination for the Posts API. These features greatly enhance the API’s flexibility and efficiency, allowing clients to retrieve exactly the data they need.
You’ve learned how to:
These techniques are essential for building scalable and user-friendly APIs. By providing these features, you empower clients to interact with your API more effectively and efficiently.