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
pageIndexandperPageparameters - filtered by the
queryparameter that is matched against thenamecolumn.
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.
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.