Database: Inserts and RLS

Tables and Schemas

Before you insert data, you need a table to hold it. Supabase gives you two ways to create tables: the Dashboard's table editor and raw SQL via the SQL Editor. The Dashboard editor is great for beginners — you pick column names, types, defaults, and constraints with a visual interface. But for anything serious, you will want to write SQL. It is repeatable, version-controllable, and far more expressive. You can create a table in seconds with the SQL Editor:

CREATE TABLE posts (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT,
  user_id UUID NOT NULL REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT now()
);

This creates a posts table with an auto-incrementing primary key, a required title, optional content, a foreign key linking each row to an authenticated user, and a default timestamp. The user_id column is critical — it ties every row to the person who created it, which is how Row Level Security knows who owns what.

Inserting Data

With a table ready, you insert rows using the Supabase JavaScript client's .insert() method. The simplest call passes an object:

const { data, error } = await supabase
  .from('posts')
  .insert({ title: 'Hello World', content: 'My first post', user_id: user.id })
  .select();

The .select() at the end tells Supabase to return the newly inserted row. Without it, you get back nothing but a success signal. Always call .select() if you need the inserted data — for example, to get the auto-generated id or created_at.

You can insert multiple rows at once by passing an array:

const { data, error } = await supabase
  .from('posts')
  .insert([
    { title: 'Post One', content: 'First content', user_id: user.id },
    { title: 'Post Two', content: 'Second content', user_id: user.id }
  ])
  .select();

Batch inserts are transactional — either all rows succeed or none do. Use them when seeding data or importing in bulk, but keep payloads reasonable (a few hundred rows at most) to avoid timeout issues.

Row Level Security (RLS)

RLS is the single most important security feature Supabase offers. It is a PostgreSQL feature that restricts which rows a user can see, insert, update, or delete based on a policy expression. Without RLS, every authenticated user — and even anonymous users — can read and write every row in your table. That is almost never what you want.

Think of RLS as a WHERE clause that Postgres appends to every query automatically. When a user requests rows, Postgres checks the policy; if the row does not satisfy the policy expression, it is invisible to that user. This gives you tenant isolation (users see only their own data), data privacy (users cannot read each other's records), and a security layer that cannot be bypassed by the client.

Enabling RLS

In the Supabase Dashboard, open your table and toggle "Enable RLS". Or, more permanently, run this SQL in the SQL Editor:

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

That single line locks down the table. Now nobody can read or write — not even authenticated users — until you create at least one policy. This is a good safety position: start locked and open only what you need.

Creating RLS Policies

A policy is a rule that says "allow operation X when condition Y is true." Policies have two key clauses:

• USING — determines which rows are visible or targeted (used for SELECT, UPDATE, DELETE).
• WITH CHECK — determines whether a new or modified row is allowed (used for INSERT, UPDATE).

The classic example: users can insert only their own posts.

CREATE POLICY "Users can insert their own posts"
ON posts FOR INSERT
TO authenticated
WITH CHECK (auth.uid() = user_id);

This says: when an authenticated user tries to insert a row, check that the user_id on the new row matches their authenticated user ID. If it does not, the insert is rejected.

For SELECT, you use USING instead:

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

Now users can only see rows where user_id matches their own. The same pattern extends to UPDATE and DELETE: USING filters which rows can be targeted, and WITH CHECK (on UPDATE) validates the final row.

Common Pitfalls

• Forgetting to enable RLS. Your table is wide open until you flip that switch. Always verify RLS is on before deploying.
• Overly permissive policies. Using USING (true) or TO public when you meant TO authenticated can leak data. Be explicit about roles.
• Policy order. Policies are additive — if any policy allows access, the row is accessible. If you have a permissive policy and a restrictive one, the permissive one wins. Design policies to be mutually exclusive or use USING (false) as a base deny.

// Full client-side example with auth context
const { data: { user } } = await supabase.auth.getUser();

const { data, error } = await supabase
  .from('posts')
  .insert({ title: 'My Post', content: 'Content here', user_id: user.id })
  .select();

if (error) console.error('Insert failed', error);
else console.log('Inserted', data);