Learn how to load data from the Supabase database

In this page we learn how to load data from the Supabase database and display it in our Tanstack Start application.

Now that our database supports the data we need, we can start loading it into our application. In TanStack Start we load data in a route loader that calls a server function, then read it in the component with Route.useLoaderData().

In the snippet below, we will:

Read the authenticated user's id from authFunctionMiddleware (context.user).
Load the user's tasks from the database in a server function, with pagination and search.
Wire the server function to the route loader, deriving its inputs from the URL search params.
Display the tasks in a table with a search input.

Let's take a look at the code:

import { createFileRoute } from '@tanstack/react-router';
import { createServerFn } from '@tanstack/react-start';
import * as z from 'zod';
import { authFunctionMiddleware } from '@kit/function-middleware/functions';
import { getSupabaseServerClient } from '@kit/supabase/server-client';
import { Heading } from '@kit/ui/heading';
import { If } from '@kit/ui/if';
import { Input } from '@kit/ui/input';
import { PageBody } from '@kit/ui/page';
import { Trans } from '@kit/ui/trans';
import { TasksTable } from './-components/tasks-table';
import { UserAccountHeader } from './-components/user-account-header';
const PAGE_SIZE = 10;
const TasksSearchSchema = z.object({
  page: z.number().int().min(1).catch(1),
  query: z.string().catch(''),
});
// Server function: runs on the server, gated by `authFunctionMiddleware`
// (injects `context.user`). The handler and its Supabase graph are stripped
// from the client bundle by the compiler.
const fetchUserTasksFunction = createServerFn({ method: 'GET' })
  .middleware(authFunctionMiddleware)
  .validator(TasksSearchSchema)
  .handler(async ({ data, context: { user } }) => {
    const client = getSupabaseServerClient();
    const from = (data.page - 1) * PAGE_SIZE;
    let query = client
      .from('tasks')
      .select('*', { count: 'exact' })
      .eq('account_id', user.id)
      .range(from, from + PAGE_SIZE - 1);
    if (data.query) {
      query = query.ilike('title', `%${data.query}%`);
    }
    const { data: tasks, count, error } = await query;
    if (error) {
      throw error;
    }
    return {
      data: tasks ?? [],
      page: data.page,
      pageSize: PAGE_SIZE,
      pageCount: Math.ceil((count ?? 0) / PAGE_SIZE),
      count: count ?? 0,
    };
  });
export const Route = createFileRoute('/_authenticated/dashboard/')({
  // Validate + type the URL search params (?page=&query=).
  validateSearch: TasksSearchSchema,
  // Re-run the loader when the search params change.
  loaderDeps: ({ search }) => search,
  loader: ({ deps }) => fetchUserTasksFunction({ data: deps }),
  // `head` replaces Next.js `generateMetadata`.
  head: () => ({ meta: [{ title: 'Home' }] }),
  component: UserHomePage,
});
function UserHomePage() {
  const result = Route.useLoaderData();
  const { query } = Route.useSearch();
  return (
    <>
      <UserAccountHeader
        title={<Trans i18nKey={'common.homeTabLabel'} />}
        description={<Trans i18nKey={'common.homeTabDescription'} />}
      />
      <PageBody className={'space-y-4'}>
        <div className={'flex items-center justify-between'}>
          <div>
            <Heading level={4}>
              <Trans i18nKey={'tasks.tasksTabLabel'} defaults={'Tasks'} />
            </Heading>
          </div>
          <div className={'flex items-center space-x-2'}>
            <form className={'w-full'}>
              <Input
                name={'query'}
                defaultValue={query}
                className={'w-full lg:w-[18rem]'}
                placeholder={'Search tasks'}
              />
            </form>
          </div>
        </div>
        <div className={'flex flex-col space-y-8'}>
          <If condition={result.count === 0 && query}>
            <p>
              <Trans i18nKey={'tasks.noTasksFound'} values={{ query }} />
            </p>
          </If>
          <TasksTable {...result} />
        </div>
      </PageBody>
    </>
  );
}

Let's break this down a bit:

We import the necessary components and functions.
We define TasksSearchSchema to validate and type the URL search params.
We define fetchUserTasksFunction, a server function gated by authFunctionMiddleware, that loads the paginated/filtered tasks for context.user.
We wire the server function to the route loader, deriving its inputs from the search params via loaderDeps.
We use the route head option (instead of Next.js generateMetadata) to set the page title.
The UserHomePage component reads the loader result with Route.useLoaderData() and renders the tasks in a table with a search input.

Displaying the tasks in a table

Now, let's show the tasks table component:

import { Link } from '@tanstack/react-router';
import { ColumnDef } from '@tanstack/react-table';
import { Pencil } from 'lucide-react';
import { useTranslations } from 'use-intl';
import { Button } from '@kit/ui/button';
import { DataTable } from '@kit/ui/enhanced-data-table';
import { Database } from '#/lib/database.types';
type Task = Database['public']['Tables']['tasks']['Row'];
export function TasksTable(props: {
  data: Task[];
  page: number;
  pageSize: number;
  pageCount: number;
}) {
  const columns = useGetColumns();
  return (
    <div>
      <DataTable {...props} columns={columns} />
    </div>
  );
}
function useGetColumns(): ColumnDef<Task>[] {
  const t = useTranslations('tasks');
  return [
    {
      header: t('task'),
      cell: ({ row }) => (
        <Link
          className={'hover:underline'}
          to={`/dashboard/tasks/${row.original.id}`}
        >
          {row.original.title}
        </Link>
      ),
    },
    {
      header: t('createdAt'),
      accessorKey: 'created_at',
    },
    {
      header: t('updatedAt'),
      accessorKey: 'updated_at',
    },
    {
      header: '',
      id: 'actions',
      cell: ({ row }) => {
        const id = row.original.id;
        return (
          <div className={'flex justify-end space-x-2'}>
            <Link to={`/dashboard/tasks/${id}`}>
              <Button variant={'ghost'} size={'icon'}>
                <Pencil className={'h-4'} />
              </Button>
            </Link>
          </div>
        );
      },
    },
  ];
}

In this snippet, we define the TasksTable component that renders the tasks in a table. We use the DataTable component from the @kit/ui/enhanced-data-table package to render the table.

We also define the useGetColumns hook that returns the columns for the table. We use the useTranslations hook from use-intl to translate the column headers.