Supabase: Data Fetching

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

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

My convention (used throughout the boilerplate) is to write functions that inject the Supabase SDK Client as a parameter: this allows us to reuse these functions in both the browser and the server.

Fetching a list of tasks

First, we want to write a function that is responsible for fetching the paginated list of tasks using the Supabase Postgres client.

Below, we will write the query necessary to fetching the tasks for a given project:

paginated using the pageIndex and perPage parameters
filtered by the query parameter that is matched against the name column.

lib/tasks/queries.ts

import type { SupabaseClient } from '@supabase/supabase-js';
import { TASKS_TABLE } from '~/lib/db-tables';
import type Task from '~/lib/tasks/types/task';
import type { Database } from '../../database.types';
type Client = SupabaseClient<Database>;
const TASKS_PAGE_SIZE = 10;
export function getTasks(
 client: Client,
 params: {
   organizationId: number;
   pageIndex: number;
   perPage?: number;
   query?: string;
 },
) {
 const { organizationId, perPage, pageIndex } = params;
 const { startOffset, endOffset } = getPaginationOffsets(pageIndex, perPage);
 let query = client
   .from(TASKS_TABLE)
   .select<string, Task>(
     `
     id,
     name,
     organizationId: organization_id,
     dueDate: due_date,
     done,
     description
   `,
     {
       count: 'exact',
     },
   )
   .order('name', { ascending: true })
   .eq('organization_id', organizationId)
   .range(startOffset, endOffset);
 if (params.query) {
   query = query.textSearch('name', params.query);
 }
 return query;
}
function getPaginationOffsets(pageIndex: number, perPage?: number) {
 const pageSize = perPage || TASKS_PAGE_SIZE;
 const startOffset = pageIndex * pageSize;
 const endOffset = startOffset + pageSize;
 return {
   startOffset,
   endOffset,
 };
}

Fetching a single task

Next, we want to write a function that is responsible for fetching a single task when viewing the task details page. We can place this function in the same file as the getTasks function.

lib/tasks/queries.ts

export function getTask(client: Client, id: number) {
 return client
   .from(TASKS_TABLE)
   .select(
     `
     id,
     name,
     organizationId: organization_id,
     dueDate: due_date,
     description,
     done
   `,
   )
   .eq('id', id)
   .single();
}

We now have all the queries we need to fetch tasks from Supabase, both as a paginated list and as a single task. In the next section, we will learn how to use these queries from the React Server Components.

Fetching a list of tasks

First, we want to write a function that is responsible for fetching the paginated list of tasks using the Supabase Postgres client.

Below, we will write the query necessary to fetching the tasks for a given project:

paginated using the pageIndex and perPage parameters

filtered by the query parameter that is matched against the name column.

lib/tasks/queries.ts

import type { SupabaseClient } from '@supabase/supabase-js';
import { TASKS_TABLE } from '~/lib/db-tables';
import type Task from '~/lib/tasks/types/task';
import type { Database } from '../../database.types';
type Client = SupabaseClient<Database>;
const TASKS_PAGE_SIZE = 10;
export function getTasks(
 client: Client,
 params: {
   organizationId: number;
   pageIndex: number;
   perPage?: number;
   query?: string;
 },
) {
 const { organizationId, perPage, pageIndex } = params;
 const { startOffset, endOffset } = getPaginationOffsets(pageIndex, perPage);
 let query = client
   .from(TASKS_TABLE)
   .select<string, Task>(
     `
     id,
     name,
     organizationId: organization_id,
     dueDate: due_date,
     done,
     description
   `,
     {
       count: 'exact',
     },
   )
   .order('name', { ascending: true })
   .eq('organization_id', organizationId)
   .range(startOffset, endOffset);
 if (params.query) {
   query = query.textSearch('name', params.query);
 }
 return query;
}
function getPaginationOffsets(pageIndex: number, perPage?: number) {
 const pageSize = perPage || TASKS_PAGE_SIZE;
 const startOffset = pageIndex * pageSize;
 const endOffset = startOffset + pageSize;
 return {
   startOffset,
   endOffset,
 };
}

Fetching a single task

Next, we want to write a function that is responsible for fetching a single task when viewing the task details page. We can place this function in the same file as the getTasks function.

lib/tasks/queries.ts

export function getTask(client: Client, id: number) {
 return client
   .from(TASKS_TABLE)
   .select(
     `
     id,
     name,
     organizationId: organization_id,
     dueDate: due_date,
     description,
     done
   `,
   )
   .eq('id', id)
   .single();
}