Database Schema
Learn how to get started developing new features from the database schema in your Makerkit Remix Supabase project.
Before writing the React Components, we want to tackle the task of fetching data from Supabase.
This involves a couple of steps:
- We need to define, on paper, what the data model of the Supabase tables looks like
- We need to update the SQL schema of the database to match the data model and write the required RLS policies to secure the data
- We need to write the hooks to fetch data from Supabase
Learning about RLS
Learning RLS is fundamental to understanding how to secure data in Supabase. The best resource (other than checking Makerkit's code) is the Supabase RLS docs.
Please check it out - and if you have any questions, please ask in the Makerkit Discord.
How is the data structured?
We can place tasks as our 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, description text, 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
:
export interface Task { id: number; name: string; organizationId: string; dueDate: string; done: boolean; description?: string;}
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 an RLS policy to the tasks
table to ensure that only users of the same organization can read and write tasks.
We know that a 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.
First, we enable RLS on the tasks
table:
alter table tasks enable row level security;
Then, we can add a policy to the tasks
table:
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 CLI.
To run this command, you need to ensure Supabase is running locally using npm run supabase:start
.
You can generate the types 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 to ensure that it is typed correctly, which helps us write type-safe queries with the Supabase client.
For example:
import type { SupabaseClient } from '@supabase/supabase-js';import type { Database } from '../../database.types';type Client = SupabaseClient<Database>;