Supabase: Data Fetching

Learn how to fetch data from Supabase in your React applications.

Before writing the React Components, we want to tackle the task of fetching data from Supabase.

This involves a couple of steps:

  1. We need to define, on paper, what the data model of the Supabase tables looks like
  2. We need to update the SQL schema of the database to match the data model and write the requires RLS policies to secure the data
  3. We need to write the hooks to fetch data from Supabase
How is the data structured?

We can place tasks as its own Postgres table tasks. Let's create an SQL table that defines the following columns:

create table tasks (
  id bigint generated always as identity primary key,
  organization_id bigint not null references public.organizations,
  name text not null,
  done bool not null,
  due_date timestamptz not null
);

Because tasks belong to an organization, we need to ensure that only users of that organization can read and write those tasks by adding a foreign key to each task named organization_id.

Replicating the tasks table as a Type

We can define a Task model at src/lib/tasks/types/task.ts:

lib/tasks/types/task.ts
export interface Task {
  id: number;
  name: string;
  organizationId: string;
  dueDate: string;
  done: boolean;
}

NB: this may not be necessary if you generate the types from the SQL schema. Up to you.

How do we protect the data with RLS?

We can add a RLS policy to the tasks table to ensure that only users of the same organization can read and write tasks.

We know that an user belongs to an organization using the memberships table.

The memberships table has two foreign keys: user_id and organization_id: we can use these foreign keys to ensure that only users of the same organization can read and write tasks.

create policy "Tasks can be read by users of the organizations to which it belongs" on tasks
  for all
    using (exists (
      select
        1
      from
        memberships
      where
        user_id = auth.uid () and tasks.organization_id = memberships.organization_id));

Generating the types from the SQL schema

We can generate the types from the SQL schema using the supabase-js library. To run this command, you need to ensure Supabase is running running npm run supabase:start.

You can do so by running the following command:

npm run typegen

The types will be generated at app/database-types.ts. We will import the types whenever we use the Supabase client.

For example:

import type { SupabaseClient } from '@supabase/supabase-js';
import type { Database } from '../../database.types';
 
type Client = SupabaseClient<Database>;

Data Fetching: React Hooks to read data from Supabase Postgres

With our tables and RLS in place, we can finally start writing our queries using the Supabase Postgres client.

I recommend writing your entities' hooks at lib/tasks/hooks. Let's start with a React Hook to fetch all the organization's tasks.

First, we want to write a function that is responsible for fetching the tasks using the Supabase Postgres client. We can write this function at lib/tasks/queries.ts:

lib/tasks/queries.ts
import type { SupabaseClient } from '@supabase/supabase-js';
import type { Database } from '../../database.types';
import { TASKS_TABLE } from '~/lib/db-tables';
import Task from '~/lib/tasks/types/task';
 
type Client = SupabaseClient<Database>;
 
export function getTasks(client: Client, organizationId: number) {
  return client
    .from(TASKS_TABLE)
    .select<string, Task>(
      `
      id,
      name,
      organization_id: organizationId,
      dueDate: due_date,
      done
    `
    )
    .eq('organization_id', organizationId)
    .throwOnError();
}

The query above will be fetching all the tasks of the organization with the ID organizationId.

As you can see, my convention (used throughout the boilerplate) is to write queries that inject the Supabase client as a parameter. This allows me to reuse these functions in both the browser and the server.

Additionally, I also like to write queries that throw errors. This allows me to catch errors using try/catch blocks rather than using the error property. This is a personal preference, feel free to use the error property instead.

Below is the tasks route, where we fetch the data from the server component and pass it down to the client components:

app/(app)/tasks/page.tsx
import React, { use } from 'react';
import { RectangleStackIcon } from '@heroicons/react/24/outline';
import { cookies } from 'next/headers';
 
import { parseOrganizationIdCookie } from '~/lib/server/cookies/organization.cookie';
import getSupabaseServerClient from '~/core/supabase/server-client';
import { getTasks } from '~/lib/tasks/queries';
import AppHeader from '~/app/(app)/components/AppHeader';
import Trans from '~/core/ui/Trans';
import AppContainer from '~/app/(app)/components/AppContainer';
import TasksContainer from '~/app/(app)/tasks/components/TasksListContainer';
 
function TasksPage() {
  const tasks = use(loadTasksData());
 
  return (
    <>
      <AppHeader>
        <span className={'flex space-x-2'}>
          <RectangleStackIcon className="w-6" />
 
          <span>
            <Trans i18nKey={'common:tasksTabLabel'} />
          </span>
        </span>
      </AppHeader>
 
      <AppContainer>
        <TasksContainer tasks={tasks} />
      </AppContainer>
    </>
  );
}
 
export default TasksPage;

Below is the function that is responsible for fetching the tasks from Supabase:

async function loadTasksData() {
  const organizationId = await parseOrganizationIdCookie(cookies());
  const client = getSupabaseServerClient();
 
  const { data: tasks, error } = await getTasks(client, Number(organizationId));
 
  if (error) {
    console.error(`Error fetching tasks: ${error.message}`);
 
    return [];
  }
 
  return tasks;
}

Displaying Tasks data

To display the tasks, let's create a new client component named TasksListContainer:

app/(app)/tasks/components/TasksListContainer.tsx
'use client';
 
import Heading from '~/core/ui/Heading';
import Button from '~/core/ui/Button';
 
import type Task from '~/lib/tasks/types/task';
import TaskListItem from '~/app/(app)/tasks/components/TaskListItem';
 
const TasksContainer: React.FC<{
  tasks: Task[];
}> = ({ tasks }) => {
  if (!tasks || tasks.length === 0) {
    return <TasksEmptyState />;
  }
 
  return (
    <div className={'flex flex-col space-y-4'}>
      <div className={'mt-2 flex justify-end'}>
        <CreateTaskButton>New Task</CreateTaskButton>
      </div>
 
      <TasksList tasks={tasks} />
    </div>
  );
};
 
function TasksEmptyState() {
  return (
    <div
      className={
        'flex h-full flex-col items-center justify-center space-y-4 p-24'
      }
    >
      <div>
        <Heading type={5}>No tasks found</Heading>
      </div>
 
      <CreateTaskButton>Create your first Task</CreateTaskButton>
    </div>
  );
}
 
function TasksList({
  tasks,
}: React.PropsWithChildren<{
  tasks: Task[];
}>) {
  return (
    <div className={'flex flex-col space-y-4'}>
      {tasks.map((task) => {
        return <TaskListItem task={task} key={task.id} />;
      })}
    </div>
  );
}
 
function CreateTaskButton(props: React.PropsWithChildren) {
  return <Button href={'/tasks/new'}>{props.children}</Button>;
}
 
export default TasksContainer;

Since the component "TaskLiskItem" is pretty complex and requires more files, we will define it separately. You will find it below after we explain the mutation hooks that the component will use.


Subscribe to our Newsletter
Get the latest updates about React, Remix, Next.js, Firebase, Supabase and Tailwind CSS