How to Create a Custom Table in Supabase

Open the SQL Editor from the left sidebar in the Dashboard. Write a CREATE TABLE statement with your desired columns, data types, and constraints. This is the recommended approach because it gives you full control over every aspect of the table. Use lowercase names with underscores (snake_case) for both table and column names. Always include a primary key — use UUID for user-facing IDs that appear in URLs, or bigint with auto-increment for internal sequential IDs.

1-- Create a projects table with UUID primary key
2CREATE TABLE public.projects (
3  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
4  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
5  name text NOT NULL CHECK (char_length(name) > 0),
6  description text,
7  status text DEFAULT 'active',
8  created_at timestamptz DEFAULT now(),
9  updated_at timestamptz DEFAULT now()
10);
11
12-- Alternative: bigint auto-incrementing primary key
13CREATE TABLE public.categories (
14  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
15  name text UNIQUE NOT NULL,
16  created_at timestamptz DEFAULT now()
17);

For a no-code approach, click Table Editor in the left sidebar, then click New Table. Enter the table name, and Supabase automatically adds an id column (bigint) and a created_at column. Click Add Column to add your custom columns — select the data type from the dropdown, set nullable/required, and add default values. You can also add foreign key relationships by clicking the chain link icon on a column. The Table Editor generates the SQL behind the scenes, so you can switch to the SQL Editor later for more complex changes.

Foreign keys establish relationships between tables. The most important relationship in most Supabase apps is linking a table to auth.users so each row belongs to a specific user. Add a user_id column of type uuid that references auth.users(id). Use ON DELETE CASCADE so that when a user is deleted, their related records are automatically removed. You can also create foreign keys between your own tables to build one-to-many and many-to-many relationships.

1-- Link a table to auth.users (one-to-many: one user has many projects)
2ALTER TABLE public.projects
3  ADD COLUMN user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE;
4
5-- Link between your own tables (one-to-many: one project has many tasks)
6CREATE TABLE public.tasks (
7  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
8  project_id uuid REFERENCES public.projects(id) ON DELETE CASCADE NOT NULL,
9  title text NOT NULL,
10  completed boolean DEFAULT false,
11  created_at timestamptz DEFAULT now()
12);

Every table in the public schema should have Row Level Security enabled. Without RLS, anyone with your anon key can read, insert, update, and delete all rows in the table. After enabling RLS with no policies, all access is denied — queries return empty arrays, not errors. This is the intended behavior and the most common source of confusion for new Supabase developers. You must create explicit policies to allow access.

1-- Enable RLS
2ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY;
3
4-- IMPORTANT: With RLS enabled and no policies, ALL access is denied.
5-- You must create at least one policy for the table to be usable via the API.

RLS policies define who can perform which operations on which rows. The most common pattern is owner-based access — users can only access rows where user_id matches their own auth.uid(). Create separate policies for SELECT, INSERT, UPDATE, and DELETE, or use a single FOR ALL policy. The USING clause filters which existing rows are visible, and the WITH CHECK clause validates new or updated rows. Always wrap auth.uid() in a subselect for per-statement caching.

1-- Users can view their own projects
2CREATE POLICY "Users can view own projects"
3  ON public.projects FOR SELECT
4  TO authenticated
5  USING ((SELECT auth.uid()) = user_id);
6
7-- Users can create projects (user_id must match)
8CREATE POLICY "Users can create projects"
9  ON public.projects FOR INSERT
10  TO authenticated
11  WITH CHECK ((SELECT auth.uid()) = user_id);
12
13-- Users can update their own projects
14CREATE POLICY "Users can update own projects"
15  ON public.projects FOR UPDATE
16  TO authenticated
17  USING ((SELECT auth.uid()) = user_id)
18  WITH CHECK ((SELECT auth.uid()) = user_id);
19
20-- Users can delete their own projects
21CREATE POLICY "Users can delete own projects"
22  ON public.projects FOR DELETE
23  TO authenticated
24  USING ((SELECT auth.uid()) = user_id);

After creating the table with RLS and policies, test it from your application using the Supabase JS client. Insert a record, read it back, and confirm the policies are working correctly. Use the authenticated user's session to ensure the user_id is set correctly. If the insert returns no data or the select returns an empty array, recheck your RLS policies in the Dashboard under Authentication > Policies.

1import { supabase } from '@/lib/supabase'
2
3// Insert a new project (user must be authenticated)
4const { data: { user } } = await supabase.auth.getUser()
5
6const { data, error } = await supabase
7  .from('projects')
8  .insert({
9    user_id: user.id,
10    name: 'My First Project',
11    description: 'Testing the new table'
12  })
13  .select()
14
15console.log('Inserted:', data)
16
17// Read back all projects for the current user
18const { data: projects } = await supabase
19  .from('projects')
20  .select('*')
21  .order('created_at', { ascending: false })
22
23console.log('My projects:', projects)