Table
Ssr
⚒️ Work In Progress ⚒️

Introduction

In this documentation, we will explore how to implement server-side rendering (SSR) tables in a Next.js application using Prisma for database access. The goal is to provide an efficient way to retrieve and display paginated, sorted, and filtered data in an SSR environment. Prisma will serve as the ORM layer, enabling easy interaction with the database, while Next.js will handle rendering the data on the server side for performance.

We are building

To start with, let's define the flat data model for our application. We will be working with a simple tenant table.

type TenantResponse = {
  id: string;
  name: string;
  contactEmail: string;
  address?: string | null;
  oib?: string | null;
  contactPhoneNumber?: string | null;
};
Most of the code examples are referd to Tenants table. It can serve as a reference for any other model.

(some more complex, some simple)

Tenant table example

Foler structure

├──ssr
│   ├── components
│   │   ├── ClickableRow.tsx
│   │   ├── SortBtn.tsx
│   │   ├── Filters
│   │   ├── Pagination
│   │   │   ├── Pagination.tsx
│   │   │   ├── PaginationNavigation.tsx
│   │   │   ├── SelectItemPerPage.tsx
│   ├── helpers
│   │   ├── generateQueryOptionsCacheKey.ts
│   │   ├── generateSortingParams.ts
│   │   ├── getOrderBy.ts
│   │   ├── getSkipTake.ts
│   ├── hooks
│   │   ├── useSetSearchParams.ts
│   ├── constants.ts
│   ├── tenant-example
│   │   ├── page.tx
│   │   ├── queries.ts
│   │   ├── tenantService.ts
│   │   ├── Table
│   │   │   ├── Table
│   │   │   │   ├── queryBuidlers
│   │   │   │   │   ├── buildTenantQueryOptions.ts
│   │   │   ├── TenantTable.tsx

Source of truth (query parameters)

In the context of server-side rendering (SSR) tables, the source of truth refers to the parameters that dictate how data is fetched and displayed. For our SSR table, the source of truth is derived from the query parameters in the URL.

Params: ?page=1&itemsPerPage=10
These parameters are essential as they determine the pagination, sorting, and filtering of the data displayed in the table.

By embedding the state into the URL, the application ensures consistency and allows the user to navigate, share, or bookmark specific views with ease.

Data model

In this section, we’ll define the structure of our data using Prisma models and corresponding validation schemas. The focus will be on ensuring efficient data retrieval and management, with performance considerations like pagination, sorting, and filtering, especially in a server-side rendering (SSR) environment.

Here's the schema.prisma model definition:

model Tenant {
  id                 String                 @id @default(uuid())
  name               String
  address            String?
  oib                String?
  contactEmail       String
  contactPhoneNumber String?
  createdAt          DateTime               @default(now())
  updatedAt          DateTime               @updatedAt
  deletedAt          DateTime?
 
  @@map("tenants")
}

And the corresponding zod.schema for type validation:

export const TenantResponseSchema = z.object({
  id: z.string(),
  name: z.string(),
  contactEmail: z.string().email(),
  address: z.string().nullable(),
  oib: z.string().nullable(),
  contactPhoneNumber: z.string().nullish(),
});
 
export type TenantResponse = z.infer<typeof TenantResponseSchema>;

The zod.schema output for a tenant query would look like this:

type TenantResponse = {
  id: string;
  name: string;
  contactEmail: string;
  address?: string | null;
  oib?: string | null;
  contactPhoneNumber?: string | null;
};

Query Builder (QB)

Prisma provides a powerful query builder that allows us to construct complex queries and perform advanced operations on our data. We will use this tool to retrieve data from the database and format it for display in our application.

In this example we are using simple flat data model, but in real world application you most likely will have more complex data models.

Defining Query Options

The TenantQueryOptions type is used to define the structure of query options for fetching tenant data using Prisma.

Prisma.TenantOrderByWithRelationInput | TenantInclude | TenantWhereInput, are part of the Prisma Client API, which allows you to interact with your database using a type-safe query language.

  • skip and take are used to define pagination
  • orderBy for sorting
  • include for including related data.
  • where for filtering
export type TenantQueryOptions = {
  skip: number;
  take: number;
  orderBy?: Prisma.TenantOrderByWithRelationInput;
  include?: Prisma.TenantInclude;
  where?: Prisma.TenantWhereInput;
};

Query Builder (tenant example)

Simple builder for tenant table with pagiantion, sorting and filtering

import { Prisma } from "@prisma/client";
import getOrderBy from "@/components/table/ssr/helpers/getOrderBy";
import getSkipTake from "@/components/table/ssr/helpers/getSkipTake";
 
export default function buildTenantQueryOptions(searchParams: Record<string, string>): TenantQueryOptions {
  const { skip, take } = getSkipTake(searchParams);
  const orderBy = getOrderBy(searchParams);
 
  const { nameOrAddress, emailOrNumber } = searchParams;
 
  return {
    skip,
    take,
    orderBy,
    where: {
      AND: [
        {
          OR: [
            { name: { contains: nameOrAddress, mode: "insensitive" } },
            { address: { contains: nameOrAddress, mode: "insensitive" } },
          ],
        },
        {
          OR: [
            { contactEmail: { contains: emailOrNumber, mode: "insensitive" } },
            { contactPhoneNumber: { contains: emailOrNumber, mode: "insensitive" } },
          ],
        },
      ],
    },
  };
}

Pagination (opens in a new tab)

For pagination we are converting page and itemsPerPage params to skip and take for prisma query options

  • page current page number

  • itemsPerPage number of items to show


  • skip: Skips a certain number of records (useful for defining an offset).

  • take: Limits the number of records returned in a query.

import { initialNumberOfItemsPerPage, initialPageNumber } from "@/components/table/ssr/constants";
 
export default function getSkipTake(searchParams: Record<string, string>) {
  const { page, itemsPerPage } = searchParams;
 
  const _page = Number(page) || initialPageNumber;
  const _itemsPerPage = Number(itemsPerPage) || initialNumberOfItemsPerPage;
 
  const skip = (_page - 1) * _itemsPerPage;
  const take = _itemsPerPage;
 
  return { skip, take };
}

Sorting (opens in a new tab) (asc and desc)

Sorting is done by orderBy query options

Example of most basic sorting helper for flat data. The more nested data there is, this helper will become more complex.

  • orderByField: target field to sort by
  • sortOrder: asc or desc
  • nullsLast (opens in a new tab): if true, nulls will be last (can only be defined for fields that are optional, from tenant prisma.modal: oib, address, contactPhoneNumber)
export default function getOrderBy(searchParams: Record<string, string>) {
  const { orderByField, sortOrder, nullsLast } = searchParams;
 
  if (!orderByField || !sortOrder) return {};
 
  if (nullsLast) {
    return { [orderByField]: { sort: sortOrder, nulls: "last" } };
  }
 
  return { [orderByField]: sortOrder };
}

Filtering (opens in a new tab)

Filtering is done by where query options

where filtering query options will be different depending on the prisma model you are handling.

In the example above, filtering is done by name or address and email or contactPhoneNumber

where: {
    AND: [
        {
            OR: [
            { name: { contains: nameOrAddress, mode: "insensitive" } },
            { address: { contains: nameOrAddress, mode: "insensitive" } },
            ],
        },
        {
            OR: [
            { contactEmail: { contains: emailOrNumber, mode: "insensitive" } },
            { contactPhoneNumber: { contains: emailOrNumber, mode: "insensitive" } },
            ],
        },
    ],
},

Table Data

Order of operations:

Query

Every time query options are changed (page.tsx re-renders), getTableTenants is called.

export const getTableTenants = async (
  searchParams: Record<string, string>
): Promise<
  OperationResponse<{
    tenants: TenantResponse[];
    totalCount: number;
  }>
> => {
  try {
    // Query builder for tenants
    const queryOptions = buildTenantQueryOptions(searchParams);
    // Cache key based on the searchParams and rootKey(keyPrefix)
    const cacheKey = generateQueryOptionsCacheKey({
      keyPrefix: `${Route.Tenants}-table`,
      searchParams,
    });
 
    // https://nextjs.org/docs/app/api-reference/functions/unstable_cache#parameters
    const tenants = await unstable_cache(() => TenantService.getTableTenants(queryOptions), [...cacheKey], {
      revalidate: cacheTTL,
      tags: [...cacheKey],
    })();
 
    return successResponse(tenants);
  } catch (e) {
    logger.error(e);
    return errorResponse("Greška prilikom dohvaćanja zakupnika.");
  }
};

Cache QB

@param searchParams: Record of key value pairs: {page: "1", itemsPerPage: "10"}

@param keyPrefix:

  • generally the current root route: routeRoute-usedFor > /zakupnici-table
  • or define a constant as cache key

@returns ["/zakupnici-table", "/zakupnici-table-page-1-itemsPerPage-10"]

import { initialNumberOfItemsPerPage, initialPageNumber } from "@/components/table/ssr/constants";
 
type QueryOptions = {
  searchParams: Record<string, string>;
  keyPrefix: string;
};
 
export default function generateQueryOptionsCacheKey({ searchParams, keyPrefix }: QueryOptions): string[] {
  const { page, itemsPerPage } = searchParams;
  /**
   * In case of no searchParams, set the default key to ?page=1&itemsPerPage=10
   * navigate to > /zakupnici
   * cache key will be > /zakupnici-table-page-1-itemsPerPage-10
   */
  if (!page) {
    searchParams.page = initialPageNumber.toString();
  }
  if (!itemsPerPage) {
    searchParams.itemsPerPage = initialNumberOfItemsPerPage.toString();
  }
 
  /**
   * format searchParams to key:
   * > ?page=1&itemsPerPage=10 > {page: 1, itemsPerPage: 10} > page-1-itemsPerPage-10
   */
  const searchParamsKey = Object.entries(searchParams)
    .map(([key, value]) => `${key}-${value}`)
    .join("-");
 
  // keyPrefix > general tag for the cache, revalidating the keyPrefix will revalidate all the keys
  return [`${keyPrefix}`, `${keyPrefix}-${searchParamsKey}`];
}

Service

Data access layer, using prisma client and the transaction pattern (opens in a new tab) to ensure consistency

.count is used for pagination, to get the total number of records.

const prisma = new PrismaClient();
const logger = logWithMetadata(AppModule.TENANT, ModuleLayer.DATA_ACCESS);
 
export const TenantService = {
  async getTableTenants(queryOptions: TenantQueryOptions): Promise<{ tenants: TenantResponse[]; totalCount: number }> {
    try {
      const [tenants, totalCount] = await prisma.$transaction([
        prisma.tenant.findMany(queryOptions),
        prisma.tenant.count({ where: queryOptions.where }),
      ]);
 
      return {
        tenants: TenantResponseSchema.array().parse(tenants),
        totalCount,
      };
    } catch (e) {
      logger.error(e);
      throw new Error(prismaErrorMapper(e));
    }
  },
};

Table UI

This table UI is constructed using React's Composition principle, where each part of the table—such as filters, headers, pagination and sorting buttons—is encapsulated into modular, reusable components. This approach allows for flexible and maintainable UI design by letting components be combined, configured, and extended as needed.

The table layout, filters, pagination, and sorting functionality are composed together seamlessly, making it easy to update individual elements without affecting the overall structure. Each component focuses on a specific responsibility, contributing to a clean, organized, and scalable codebase.

For this table we are using Shadcn table (opens in a new tab), it can be replaced with html table:

(<table/>, <theader/>, <thead/>, <tbody/>, <tr/>, <td/>, ...)

It's a slightly different approach to handling server-side state using query parameters. You can think of it as capturing a snapshot of data with each render, allowing navigation and sorting links to adjust accordingly.

Table Root

page.tsx

async function Page({ searchParams }: { searchParams: Record<string, string> }) {
  const cols = Number(searchParams.itemsPerPage) || initialNumberOfItemsPerPage;
 
  return (
    <div className="content-wrapper">
      <Suspense fallback={<TableLoader rows={3} cols={cols} pagination filters />}>
        <TenantTable searchParams={searchParams} />
      </Suspense>
    </div>
  );
}
 
export default Page;

TenantTable.tsx

export default async function TenantTable({ searchParams }: { searchParams: Record<string, string> }) {
  const response = await getTableTenants(searchParams);
 
  if (!response.success) {
    return <ErrorView fullHeight error={response} />;
  }
 
  const { tenants, totalCount } = response.data;
 
  return (
    <div>
      <TenantFilters searchParams={searchParams} />
 
      <Table>
        <TenantTHeader searchParams={searchParams} />
        <TenantTBody tenants={tenants} />
      </Table>
 
      <Pagination itemCount={totalCount} searchParams={searchParams} rootRoute={Route.Tenants} />
    </div>
  );
}

Table Filters

const TenantFilters = ({ searchParams }: { searchParams: Record<string, string> }) => {
  return (
    <FilterLayout
      contentRight={
        <Button asChild size="sm">
          <Link href="/zakupnici/create" className="flex">
            <PlusIcon className="mr-2 h-4 w-4" />
            <p>Kreiraj zakupnika</p>
          </Link>
        </Button>
      }
    >
      <SearchFilter searchParams={searchParams} fieldKey="nameOrAddress" placeholder="Pretraži ime ili adresu" />
      <PopUpSearchFilter
        fieldKey="emailOrNumber"
        btnTitle="Email"
        placeholder="Pretraži email ili broj telefeona"
        searchParams={searchParams}
        className="p-2"
      />
    </FilterLayout>
  );
};

Table Header

const TenantHeader = ({ searchParams }: { searchParams: Record<string, string> }) => {
  const sortBtnProps = { searchParams, rootRoute: Route.Tenants };
 
  return (
    <TableHeader className="t-header">
      <TableRow>
        <TableHead className="w-1/3">
          <div className="flex items-center">
            <p>Zakupnik</p>
            <SortBtn fieldKey="name" {...sortBtnProps} />
          </div>
        </TableHead>
        <TableHead className="w-1/3">
          <div className="flex items-center">
            <p>Email</p>
            <SortBtn fieldKey="contactEmail" {...sortBtnProps} />
          </div>
        </TableHead>
        <TableHead className="w-1/3">
          <div className="flex items-center">
            <p>Oib</p>
            <SortBtn fieldKey="oib" {...sortBtnProps} nullsLast />
          </div>
        </TableHead>
      </TableRow>
    </TableHeader>
  );
};

SortBtn

The SortBtn component generates sorting links with an icon that indicates ascending or descending order. Each click cycles between ascending, descending, and default states.

type SortBtnProps = {
  fieldKey: string;
  rootRoute: Route;
  searchParams: Record<string, string>;
  nullsLast?: boolean; // can only be set if fieldKey? is optional(?) in schema.prisma
};
 
export function SortBtn({ fieldKey, rootRoute, nullsLast, searchParams }: SortBtnProps) {
  const { queryParams, sortOrder, isSorted } = generateSortingParams({
    searchParams,
    fieldKey,
    nullsLast,
  });
 
  return (
    <Link href={`${rootRoute}?${queryParams}`} className="inline-flex">
      <div className="inline-block p-2">
        {(!sortOrder || !isSorted) && <ArrowsUpDownIcon className="h-4 w-4" />}
        {isSorted && sortOrder === "asc" && <ArrowUpIcon className="h-4 w-4" />}
        {isSorted && sortOrder === "desc" && <ArrowDownIcon className="h-4 w-4" />}
      </div>
    </Link>
  );
}

Sorting helper (generateSortingParams)

The generateSortingParams function manages the sorting state based on user interactions. It toggles between ascending, descending, and no sorting by manipulating query parameters.

type SortingParams = {
  searchParams: URLSearchParams;
  fieldKey: string;
  nullsLast: boolean;
};
 
export const generateSortingParams = ({ searchParams, fieldKey, nullsLast }: SortingParams) => {
  const queryParams = new URLSearchParams(searchParams);
 
  const sortOrder = queryParams.get("sortOrder");
  const isSorted = queryParams.get("orderByField") === fieldKey;
 
  if (nullsLast) {
    queryParams.set("nullsLast", "true");
  } else {
    queryParams.delete("nullsLast");
  }
 
  // First click: set sorting to asc
  if (!isSorted) {
    queryParams.set("orderByField", fieldKey);
    queryParams.set("sortOrder", "asc");
  }
 
  // Second click: set sorting to desc
  if (isSorted && sortOrder === "asc") {
    queryParams.set("sortOrder", "desc");
  }
 
  // Last click, reset all sorting keys, back to initial.
  if (isSorted && sortOrder === "desc") {
    queryParams.delete("orderByField");
    queryParams.delete("sortOrder");
    queryParams.delete("nullsLast");
  }
 
  return { queryParams, sortOrder, isSorted };
};

Table Body

const TenantBody = ({ tenants }: { tenants: TenantResponse[] }) => {
  const hasData = tenants.length > 0;
 
  return (
    <TableBody className="t-body">
      {hasData &&
        tenants.map(({ id, name, address, contactEmail, contactPhoneNumber, oib }, index) => {
          const isEven = index % 2 === 0;
 
          return (
            <ClickableRow
              key={`ssr-table-row-${id}-${index}`}
              route={`${Route.Tenants}/${id}`}
              className={cn("t-row", isEven && "bg-white")}
            >
              <TableCell>
                <p className="text-base font-bold text-foreground">{name}</p>
                <p>{address}</p>
              </TableCell>
              <TableCell>
                <p>{contactEmail}</p>
                <p>{contactPhoneNumber}</p>
              </TableCell>
              <TableCell>
                <p>{oib}</p>
              </TableCell>
            </ClickableRow>
          );
        })}
      {!hasData && <EmptyTable colSpan={3} />}
    </TableBody>
  );
};

Clickable Row

Converting a table row (<tr/>) to client seid component to allow for navigation and onClick event.

This is not ideal because of accessibility, to improve it, we can add role="button" to the row and add onKeyDown event to handle Enter key press.

Wrapping <tr/> with <Link/> component will break html table structure and its rules (opens in a new tab). One solution is to add <Link/> component to all the <td/> elements and adjust the css from the table and accessibility will be worse.

"use client";
 
import { useCallback } from "react";
import { useRouter } from "nextjs-toploader/app";
import { cn } from "@/lib/utils";
import { TableRow } from "@/components/ui/table";
 
type ClickableRowProps = {
  children: JSX.Element[];
  route: string;
  className?: string;
};
 
export default function ClickableRow({ children, route, className }: ClickableRowProps) {
  const router = useRouter();
  const handleRowClick = useCallback(() => router.push(route), [route, router]);
 
  return (
    <TableRow onClick={handleRowClick} className={cn("cursor-pointer", className)}>
      {children}
    </TableRow>
  );
}

Table Pagination

import { initialNumberOfItemsPerPage, initialPageNumber } from "@/components/table/ssr/constants";
 
import PaginationNavigation from "@/components/table/ssr/components/Pagination/PaginationNavigation";
import SelectItemPerPage from "@/components/table/ssr/components/Pagination/SelectItemPerPage";
 
import { Route } from "@/lib/constants";
 
export default function Pagination({
  itemCount,
  searchParams,
  rootRoute,
}: {
  itemCount: number;
  searchParams: Record<string, string>;
  rootRoute: Route;
}) {
  const queryParams = new URLSearchParams(searchParams);
 
  const page = Number(searchParams?.page) || initialPageNumber;
  const itemsPerPage = Number(searchParams?.itemsPerPage) || initialNumberOfItemsPerPage;
 
  const numberOfPages = Math.ceil(itemCount / itemsPerPage);
 
  const firstItem = (page - 1) * itemsPerPage + 1;
  const lastItem = firstItem + itemsPerPage - 1;
 
  return (
    <div className="w-full rounded-b-md border bg-white p-4 shadow-md">
      <div className="flex w-full flex-col flex-wrap items-center justify-between lg:flex-row">
        <div className="flex items-center gap-2">
          <p className="text-element-inactive">Rezultata po stranici: </p>
          <SelectItemPerPage itemsPerPage={itemsPerPage} />
          <span className="flex items-center gap-1 text-nowrap text-element-inactive">
            {`${firstItem}-${lastItem}`} od {itemCount}
          </span>
        </div>
 
        <PaginationNavigation
          rootRoute={rootRoute}
          currentPage={page}
          numberOfPages={numberOfPages}
          queryParams={queryParams}
        />
      </div>
    </div>
  );
}

Pagination Navigation

import Link from "next/link";
import { Route } from "@/lib/constants";
import { cn } from "@/lib/utils";
import { paginate } from "@/lib/helpers/paginate";
 
import { ChevronLeftIcon, ChevronRightIcon, EllipsisHorizontalIcon } from "@heroicons/react/24/outline";
 
type PaginationNavigationProps = {
  rootRoute: Route;
  queryParams: URLSearchParams;
  currentPage: number;
  numberOfPages: number;
};
 
export default function PaginationNavigation({ numberOfPages, ...props }: PaginationNavigationProps) {
  return (
    <div className="flex flex-wrap justify-center">
      <BtnPrevious {...props} />
      <PaginationPages {...props} numberOfPages={numberOfPages} />
      <BtnNext {...props} numberOfPages={numberOfPages} />
    </div>
  );
}

Pagination Pages

const PaginationPages = ({ currentPage, numberOfPages, rootRoute, queryParams }: PaginationNavigationProps) => {
  const pagination = paginate({ current: currentPage, max: numberOfPages });
 
  return pagination?.items.map((item) => {
    const isActive = item === currentPage;
 
    // random string key if 2 null values are present, e.g. ellipsis
    const randomKey = (Math.random() + 1).toString(36).substring(7);
 
    queryParams.set("page", `${item}`);
 
    return (
      <div className="flex items-center" key={`page-${item ? item : randomKey}`}>
        {typeof item === "number" && (
          <Link href={`${rootRoute}?${queryParams}`} className={cn("pagination-nav-item", isActive && "border")}>
            {item}
          </Link>
        )}
        {item === null && <EllipsisHorizontalIcon width={22} />}
      </div>
    );
  });
};
Paginate Helper
export type Paginate = {
  current: number;
  prev: number | null;
  next: number | null;
  items: Array<number | null>;
};
 
export function paginate({ current, max }: { current: number; max: number }): Paginate | null {
  // Return null if current or max is not provided or invalid
  if (!current || !max || current < 1 || max < 1) return null;
 
  const isFirstPage = current === 1;
  const isLastPage = current === max;
 
  // Calculate previous and next page numbers
  const prev = isFirstPage ? null : current - 1;
  const next = isLastPage ? null : current + 1;
 
  // Initialize items array with the first page number
  const items: Array<number | null> = [1];
 
  // If there are more than 3 pages, add a null placeholder
  if (current > 3) items.push(null);
 
  // Calculate range around current page to display
  const range = 1;
  const start = Math.max(2, current - range);
  const end = Math.min(max, current + range);
 
  // Add page numbers within the range to items array
  for (let i = start; i <= end; i++) {
    items.push(i);
  }
 
  // Add null placeholder(...) if there are more pages after the displayed range
  if (end + 1 < max) items.push(null);
 
  // Add last page number if it's not already included
  if (end < max) items.push(max);
 
  return { current, prev, next, items };
}

Back and Next Buttons

type BtnProps = {
  currentPage: number;
  rootRoute: Route;
  queryParams: URLSearchParams;
};
const BtnPrevious = ({ rootRoute, queryParams, currentPage }: BtnProps) => {
  if (currentPage === 1) return null;
 
  queryParams.set("page", `${currentPage - 1}`);
 
  return (
    <div className="flex items-center">
      <Link className="pagination-nav-item" href={`${rootRoute}?${queryParams}`} prefetch>
        <ChevronLeftIcon className="h-5 w-5" />
      </Link>
    </div>
  );
};
const BtnNext = ({ currentPage, rootRoute, queryParams, numberOfPages }: BtnProps & { numberOfPages: number }) => {
  if (currentPage === numberOfPages) return null;
 
  queryParams.set("page", `${currentPage + 1}`);
 
  return (
    <div className="flex items-center">
      <Link className="pagination-nav-item" href={`${rootRoute}?${queryParams}`} prefetch>
        <ChevronRightIcon className="h-5 w-5" />
      </Link>
    </div>
  );
};

Hooks

useSetSearchParams

import { usePathname, useSearchParams } from "next/navigation";
import { useRouter } from "nextjs-toploader/app";
import { useCallback, useMemo } from "react";
 
export function useSetSearchParams() {
  const router = useRouter();
  const searchParams = useSearchParams();
  const pathname = usePathname();
 
  const params = useMemo(() => new URLSearchParams(searchParams), [searchParams]);
 
  return useCallback(
    (newParams: Record<string, string | null | undefined>) => {
      Object.entries(newParams).forEach(([key, value]) => {
        if (value === null || value === undefined || value === "") {
          params.delete(key);
        } else {
          params.set(key, value);
        }
      });
 
      router.replace(`${pathname}?${params}`, { scroll: false });
    },
    [params, router, pathname]
  );
}
ReactRbac