# How to Create a Custom Table in Supabase

- Tool: Supabase
- Difficulty: Beginner
- Time required: 10-15 min
- Compatibility: Supabase (all plans), Dashboard Table Editor and SQL Editor
- Last updated: March 2026

## TL;DR

To create a custom table in Supabase, use the SQL Editor or the Table Editor in the Dashboard. The SQL Editor gives you full control over data types, constraints, defaults, and foreign keys. After creating the table, always enable Row Level Security and add at least one policy so the table is accessible through the API. Choose UUID or bigint for primary keys, add NOT NULL constraints where needed, and link to auth.users for user-owned data.

## Creating Custom Tables in Supabase

Every Supabase project is a full PostgreSQL database, and creating tables is one of the first things you will do after setting up a project. Supabase provides two ways to create tables: the visual Table Editor for quick setup, and the SQL Editor for full control. This tutorial covers both approaches, explains when to use each, and shows you the essential steps that every table needs — primary keys, constraints, RLS, and policies.

## Before you start

- A Supabase project (free tier works)
- Access to the Supabase Dashboard at app.supabase.com
- Basic understanding of database tables, columns, and data types

## Step-by-step guide

### 1. Create a table using the SQL Editor

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.

```
-- Create a projects table with UUID primary key
CREATE TABLE public.projects (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  name text NOT NULL CHECK (char_length(name) > 0),
  description text,
  status text DEFAULT 'active',
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- Alternative: bigint auto-incrementing primary key
CREATE TABLE public.categories (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text UNIQUE NOT NULL,
  created_at timestamptz DEFAULT now()
);
```

**Expected result:** The table is created in the public schema and appears in the Table Editor sidebar.

### 2. Create a table using the visual Table Editor

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.

**Expected result:** A new table appears in the Table Editor with the columns you configured, ready for data.

### 3. Add a foreign key to link tables

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.

```
-- Link a table to auth.users (one-to-many: one user has many projects)
ALTER TABLE public.projects
  ADD COLUMN user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE;

-- Link between your own tables (one-to-many: one project has many tasks)
CREATE TABLE public.tasks (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  project_id uuid REFERENCES public.projects(id) ON DELETE CASCADE NOT NULL,
  title text NOT NULL,
  completed boolean DEFAULT false,
  created_at timestamptz DEFAULT now()
);
```

**Expected result:** The foreign key constraint is created, and the Table Editor shows the relationship with a link icon.

### 4. Enable Row Level Security on the new table

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.

```
-- Enable RLS
ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY;

-- IMPORTANT: With RLS enabled and no policies, ALL access is denied.
-- You must create at least one policy for the table to be usable via the API.
```

**Expected result:** RLS is enabled on the table. All API queries return empty results until you add policies in the next step.

### 5. Create RLS policies for the table

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.

```
-- Users can view their own projects
CREATE POLICY "Users can view own projects"
  ON public.projects FOR SELECT
  TO authenticated
  USING ((SELECT auth.uid()) = user_id);

-- Users can create projects (user_id must match)
CREATE POLICY "Users can create projects"
  ON public.projects FOR INSERT
  TO authenticated
  WITH CHECK ((SELECT auth.uid()) = user_id);

-- Users can update their own projects
CREATE POLICY "Users can update own projects"
  ON public.projects FOR UPDATE
  TO authenticated
  USING ((SELECT auth.uid()) = user_id)
  WITH CHECK ((SELECT auth.uid()) = user_id);

-- Users can delete their own projects
CREATE POLICY "Users can delete own projects"
  ON public.projects FOR DELETE
  TO authenticated
  USING ((SELECT auth.uid()) = user_id);
```

**Expected result:** Authenticated users can CRUD their own projects. Unauthenticated requests and attempts to access other users' data return empty results.

### 6. Verify the table works with the Supabase JS client

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.

```
import { supabase } from '@/lib/supabase'

// Insert a new project (user must be authenticated)
const { data: { user } } = await supabase.auth.getUser()

const { data, error } = await supabase
  .from('projects')
  .insert({
    user_id: user.id,
    name: 'My First Project',
    description: 'Testing the new table'
  })
  .select()

console.log('Inserted:', data)

// Read back all projects for the current user
const { data: projects } = await supabase
  .from('projects')
  .select('*')
  .order('created_at', { ascending: false })

console.log('My projects:', projects)
```

**Expected result:** The insert succeeds, and the select returns the newly created project. The table is fully functional through the API.

## Complete code example

File: `create-table-migration.sql`

```sql
-- =============================================
-- Complete table creation with all best practices
-- Save as: supabase/migrations/create_projects.sql
-- =============================================

-- 1. Create the table
CREATE TABLE public.projects (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
  name text NOT NULL CHECK (char_length(name) > 0),
  description text,
  status text DEFAULT 'active' CHECK (status IN ('active', 'archived', 'deleted')),
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- 2. Enable Row Level Security
ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY;

-- 3. Create RLS policies
CREATE POLICY "Users can view own projects"
  ON public.projects FOR SELECT TO authenticated
  USING ((SELECT auth.uid()) = user_id);

CREATE POLICY "Users can create projects"
  ON public.projects FOR INSERT TO authenticated
  WITH CHECK ((SELECT auth.uid()) = user_id);

CREATE POLICY "Users can update own projects"
  ON public.projects FOR UPDATE TO authenticated
  USING ((SELECT auth.uid()) = user_id)
  WITH CHECK ((SELECT auth.uid()) = user_id);

CREATE POLICY "Users can delete own projects"
  ON public.projects FOR DELETE TO authenticated
  USING ((SELECT auth.uid()) = user_id);

-- 4. Add performance indexes
CREATE INDEX idx_projects_user_id ON public.projects (user_id);
CREATE INDEX idx_projects_status ON public.projects (status);

-- 5. Auto-update the updated_at timestamp
CREATE OR REPLACE FUNCTION public.update_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON public.projects
  FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
```

## Common mistakes

- **Creating a table without enabling RLS, leaving all data publicly accessible via the API** — undefined Fix: Always run ALTER TABLE your_table ENABLE ROW LEVEL SECURITY immediately after creating any table in the public schema.
- **Enabling RLS but not creating any policies, which blocks all access and causes empty query results** — undefined Fix: After enabling RLS, create at least a SELECT policy for the table. Empty results with no errors almost always means missing policies.
- **Using uppercase or mixed-case table and column names which require quoting everywhere** — undefined Fix: Always use lowercase with underscores (snake_case) for table and column names. PostgreSQL folds unquoted identifiers to lowercase, so mixed-case names cause confusion.
- **Not adding ON DELETE CASCADE to the auth.users foreign key, causing errors when users are deleted** — undefined Fix: Add ON DELETE CASCADE when creating the foreign key: REFERENCES auth.users(id) ON DELETE CASCADE. This ensures user data is cleaned up automatically.

## Best practices

- Always create tables in the public schema — this schema is automatically exposed through the Supabase REST API
- Enable RLS on every public table and create explicit policies before using it in production
- Use UUID primary keys for tables where IDs appear in URLs to prevent enumeration attacks
- Add NOT NULL constraints on columns that should always have a value to catch errors early
- Include created_at and updated_at timestamps with sensible defaults on every table
- Link user-owned tables to auth.users via a user_id foreign key with ON DELETE CASCADE
- Add CHECK constraints for columns with limited valid values (e.g., status IN ('active', 'archived'))
- Create indexes on columns used in WHERE clauses, JOINs, and RLS policies

## Frequently asked questions

### Should I use the Table Editor or the SQL Editor to create tables?

Use the SQL Editor for production tables because it gives you full control over constraints, indexes, and RLS policies. Use the Table Editor for quick prototyping or when you are learning the available data types.

### Should I use UUID or bigint for primary keys?

Use UUID (gen_random_uuid()) when IDs will be visible to users, such as in URLs, because they cannot be guessed sequentially. Use bigint with GENERATED ALWAYS AS IDENTITY for internal tables where a simple sequential ID is sufficient.

### Can I change a table after creating it?

Yes. Use ALTER TABLE to add columns, modify constraints, and change defaults. You can also use the Table Editor to add columns visually. For production apps, make changes through migration files so they are tracked in version control.

### Why do my queries return empty results after creating a table?

If RLS is enabled and you have not created any policies, all access is denied and queries return empty arrays (not errors). This is the most common issue new Supabase developers encounter. Create at least a SELECT policy.

### Do I need to create tables for auth.users and storage.objects?

No. Supabase creates these automatically. The auth and storage schemas are managed internally. Create your own tables in the public schema and reference auth.users via foreign keys.

### Can I create tables using the Supabase CLI?

Yes. Create a migration file with supabase migration new create_my_table, write your CREATE TABLE SQL in the generated file, then apply it locally with supabase migration up or deploy with supabase db push.

### Can RapidDev help me design my database schema in Supabase?

Yes. RapidDev's engineering team can help you design an optimal database schema with proper relationships, indexes, RLS policies, and migration workflows for your Supabase project.

---

Source: https://www.rapidevelopers.com/supabase-tutorial/how-to-create-a-custom-table-in-supabase
© RapidDev — https://www.rapidevelopers.com/supabase-tutorial/how-to-create-a-custom-table-in-supabase